I have inherited several products where "the dashboard takes 30 seconds" was the opening line of the engagement. Not once was the answer "Postgres can't handle it". Postgres handles billions of rows fine. What it cannot fix is how we talk to it.
One of those dashboards went from 31 seconds to under 400 milliseconds without new hardware, using nothing below. Here are the eight causes I keep finding, roughly in the order I look for them, with the method that finds them at the end.
1. N+1 queries, the eternal classic
The list view runs one query for 50 rows, then two more per row for related data: 101 queries per page load. In Django, select_related for foreign keys and prefetch_related for many to many relations. In raw SQL, joins or a second batched query with WHERE id = ANY(...).
How to spot it: log query counts per request in development. Anything above about 15 queries for one page is a suspect, and above 50 is a conviction.
2. Missing composite indexes for real query shapes
You indexed user_id and created_at separately, but the dashboard filters WHERE org_id = ? AND status = ? ORDER BY created_at DESC. That wants one composite index matching the whole pattern:
CREATE INDEX idx_orders_org_status_created
ON orders (org_id, status, created_at DESC);Run EXPLAIN ANALYZE on your five slowest queries. Sequential scans on large tables and sorts spilling to disk are your treasure map, and the composite index that matches filter plus order is usually the whole fix.
3. COUNT(*) on every request
Exact counts on big tables are expensive, and the UI usually does not need them. Cache counts with a short TTL, maintain counters on write, use estimates for "about 12,400 results", or redesign pagination so totals are unnecessary, which leads directly to the next point.
4. OFFSET pagination on deep pages
OFFSET 50000 LIMIT 50 reads and discards fifty thousand rows to show fifty. Keyset pagination reads exactly what it returns:
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC LIMIT 50;Page one thousand costs the same as page one. This single change permanently closed a "report page times out" ticket that had been bouncing between developers for months.
5. Aggregating at read time what you could aggregate at write time
The dashboard computes revenue per day for the last 90 days by scanning the orders table on every load, for every viewer, every time. Precompute it: a nightly rollup table, an incrementally maintained summary, or a materialized view on a refresh schedule. Dashboards should read answers, not raw history. The 31 second dashboard above was mostly this one; the rollup table did more than every index combined.
6. SELECT * hauling bloat
Wide tables with JSON blobs and long text columns get serialized, transferred and parsed for screens that show four fields. Select what the screen shows. In Django, .only() or .values(). Halving payload width regularly halves response time on list screens, which feels like magic and is just arithmetic.
7. Connection churn, especially serverless
Every request opening a fresh connection adds latency and eventually exhausts max_connections, the classic serverless plus Postgres failure. Use a pooler for runtime traffic and keep direct connections for migrations. If you are on a platform like Supabase or RDS, the pooled endpoint exists precisely for this; use it and stop paying connection setup tax per request.
8. The frontend asking for too much
Sometimes the slow query is six sequential API calls the UI makes to assemble one screen. Batch them server side into one endpoint shaped like the screen, run the underlying queries concurrently, return one payload. Profile the request waterfall, not just the SQL; the network round trips between the calls are often bigger than the queries inside them.
The method that finds all of this
Measure, fix the top offender, measure again. The tools are unglamorous: the slow query log, EXPLAIN ANALYZE, and a request waterfall in the browser. Resist optimizing anything you have not measured; intuition about database performance is wrong just often enough to be expensive. The list above is not theory. It is simply where the bodies were buried, every time I have gone digging.
Got a dashboard that makes users make coffee? Send it my way. Contact.
Need this built?
These services can turn the ideas in this article into production software.
