01
where the orm stops paying off
Django's ORM is the right tool for application code: loading an object, checking a permission, saving a change. Reports are a different kind of query. Once a dashboard needs daily buckets in the customer's time zone, days with no orders filled in as zeros, a rolling seven-day sum and a filter that only counts paid orders, the ORM version turns into a stack of annotate(), Subquery() and OuterRef() calls whose generated SQL nobody reads, and a stray field in .values() can quietly change the grouping.
SQL already has names for everything a report needs. CTEs split a query into named steps you can read top to bottom, window functions compute running totals and ranks without pulling rows into Python, FILTER (WHERE ...) replaces most Case/When gymnastics, and generate_series produces the calendar. The rule we'd use to decide where code goes: if it mostly shapes rows into a report (time buckets, cohorts, rankings, deduplication, aggregate math), write SQL. If it enforces application behaviour (validation, state changes, workflows), keep it in Python.
The rest of this post is about doing that without losing what Django gives you: SQL kept in versioned files, installed through migrations, read through unmanaged models, refreshed on a schedule, and tested against a real Postgres. Everything was run on Postgres 18 with Django 6.1.
02
write the query in a file, not a string
Put each report in its own .sql file inside the app, so it gets reviewed like code and you can paste it straight into psql for EXPLAIN (ANALYZE, BUFFERS). Here's a 90-day revenue report with a rolling weekly total:
A few details in there are doing real work. The days CTE isn't decoration: rows between 6 preceding and current row counts rows, not days, so without a row for every calendar day a gap would silently stretch the window past seven days. The first six rows of the result cover less than a week, which the UI should either show or trim. The explicit at time zone matters because casting a timestamptz to date uses the session's TimeZone. Django sets that to UTC when USE_TZ is on, while psql or a cron job may use the server's setting, and the same query would put a 23:30 UTC order on different days depending on who ran it. And sum() over a bigint column returns numeric, so cast it back if Python should get an int.
Loading and running the file takes a few lines. The %(days)s placeholders are ordinary bound parameters, so user input never ends up in the SQL text:
run_report("daily_revenue", {"days": 90}) returns a list of dicts ready for a serializer. Only pass file names from your own code here, never from a request.
03
ctes and the planner
Some teams still avoid CTEs because they used to be optimization fences. That changed in Postgres 12, not in any later release: since 12, a CTE that has no side effects, isn't recursive and is referenced once gets inlined into the outer query, so the planner can push filters into it and use indexes as if you'd written a subquery. A CTE referenced more than once is still computed once and reused.
You can override this per CTE. with paid as materialized (...) forces the old behaviour, which helps when an expensive step is referenced several times or when you want to stop the planner from making a bad choice inside it. not materialized forces inlining of a CTE that's referenced more than once. The difference is easy to see in EXPLAIN: with an index on orders (created_at), the default plan for a CTE filtered from outside on created_at is a bitmap index scan on orders, while the materialized version shows a CTE Scan over a sequential scan of the whole table, with the filter applied afterwards. Write the report for readability first, then check the plan and add a hint only where it changes something.
04
ship views through migrations and read them with unmanaged models
When a report is read often, turn it into a view or a materialized view and install it with a migration, so it's versioned and deployed with the code that depends on it. The SQL stays in files next to the migration:
The down file is just drop materialized view reporting_daily_revenue;. One caution with this layout: the migration reads the file at the time it runs, so editing daily_revenue_mv.up.sql later changes what an old migration does on a fresh database. When the view changes, add new files and a new migration rather than editing the old ones.
An unmanaged model gives you the ORM, serializers and the admin on top of the view, and Django never tries to create or alter it:
The primary_key=True on day is honest here, because the unique index guarantees one row per day. Without the casts in the view, revenue_cents would come back as numeric while the model claims it's a big integer, which works until something compares types.
05
materialized views and refresh locking
A plain view runs its query on every read, which is fine while the query is fast. A materialized view stores the result and trades freshness for read speed, and the refresh is where the operational cost hides.
REFRESH MATERIALIZED VIEW without options takes an ACCESS EXCLUSIVE lock, so every dashboard reading the view waits until the refresh finishes. REFRESH MATERIALIZED VIEW CONCURRENTLY takes an EXCLUSIVE lock instead, which still allows plain reads, at the cost of computing a diff against the old contents. It has conditions: the view must have at least one unique index that uses only column names, with no expression and no WHERE clause, and it must already be populated. Without the index, Postgres refuses with cannot refresh materialized view ... concurrently and a hint to create one. Only one refresh of a view can run at a time either way. The Postgres docs also note that the concurrent form is faster when few rows change, and that a refresh which rewrites most of the view is cheaper without it, so a nightly full rebuild of a view nobody reads at night doesn't need CONCURRENTLY.
Something has to run the refresh. If you can install extensions, pg_cron keeps the schedule inside the database:
pg_cron has to be in shared_preload_libraries and needs a restart to enable, which rules it out on some managed hosts. The other option is a periodic Celery task or a management command run by your scheduler. Whichever you pick, run it as the view's owner or, on Postgres 17 and later, a role that has been granted the MAINTAIN privilege on the view, and tell the people reading the dashboard how fresh it is. "Updated every five minutes" in the UI prevents a lot of confused bug reports.
If refreshes keep getting slower, the view is probably doing too much. Pre-aggregating into a smaller summary table, or splitting one large view into several narrow ones, usually helps more than tuning the refresh.
06
test it against real postgres
Reporting SQL needs tests more than most code, because a wrong number on a revenue dashboard looks exactly like a right one. Those tests have to run against Postgres, the same major version as production. SQLite can't run this report at all, since it has no at time zone and no materialized views, and where it does run something similar, it differs on types, grouping and time zones in exactly the places reports depend on. Mocking the database is worse still, because then nothing checks the SQL.
This costs less than people expect. Django's TestCase already wraps every test in a transaction that's rolled back at the end, so tests against a real database don't need truncation between them, and pytest-django gives each pytest-xdist worker its own test database by default. In CI, run Postgres as a service container from the official postgres image. Because migrations run when the test database is created, the materialized view exists in tests just like in production:
The last order is the test that earns its keep: 23:30 UTC on 1 January is already 2 January in Prague, and a report that buckets in UTC fails here instead of in a finance meeting. A plain REFRESH works fine inside the test transaction.
If you'd rather test the SQL layer in SQL, pgTAP does the same job with assertions written in the database, and pg_prove runs the files and reports TAP output in CI:
Two pgTAP details trip people up. is() compares typed values, so comparing a numeric sum against a bigint literal fails with "function is(numeric, bigint, unknown) does not exist", which is one more reason for the casts in the view. And has_materialized_view('public', 'reporting_daily_revenue') with two plain strings resolves to the (view, description) overload, so it looks for a materialized view called public and fails. Casting both names to name, or adding a description as the third argument, picks the schema-qualified version. Keep Python tests for endpoints, permissions and serialization, and let the SQL tests own the numbers.
