We inherited a SaaS product where the dashboard loaded in 12 seconds. The backend team insisted the queries were fine. We ran EXPLAIN ANALYZE and found a sequential scan on a 4-million-row table with no index. Three hours later, the dashboard loaded in 400ms. Here's the systematic approach.
Start with EXPLAIN ANALYZE
Never optimise without measurement. Run EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) on your slow query. Look for: sequential scans on large tables (Seq Scan), high estimated vs actual row counts (stale statistics), nested loop joins on large datasets, and sort operations that spill to disk.
Index strategy
Most missing indexes are on foreign keys and filter columns in WHERE clauses. Check pg_stat_user_tables for tables with high seq_scan counts relative to idx_scan counts. Those are your indexing targets. Composite indexes should match your query's WHERE and ORDER BY clauses — column order matters.
Partial indexes are underused. If you always filter on WHERE status = 'active', a partial index on active records only is much smaller and faster than a full index. Index only what you query.
The N+1 query problem
The most common ORM-generated performance problem. You fetch 100 orders, then for each order you fetch its line items — 100 separate queries. Fix with eager loading (Prisma's include, Sequelize's include) or with a single JOIN query. Use the pg_stat_statements extension to identify queries that run thousands of times per second.
Pagination
OFFSET-based pagination degrades as page number increases — PostgreSQL must scan and discard all preceding rows. Use cursor-based pagination for large datasets: WHERE id > last_seen_id ORDER BY id LIMIT 20. Constant time regardless of page number, and no risk of missing or duplicating rows when data changes between requests.
Connection pooling
Each PostgreSQL connection is a heavyweight process consuming ~5MB of memory. A serverless application creating a new connection per request will exhaust the connection limit and cause cascading failures. Use PgBouncer in transaction pooling mode, or Prisma Accelerate. Keep the pool size at 10–20 connections for most workloads.
Analyse and vacuum
PostgreSQL's query planner relies on table statistics. Stale statistics lead to bad query plans — the classic case is estimating 1 row when the actual result is 100,000. Run ANALYZE after large data loads. Monitor for table bloat (dead tuples from updates and deletes) and ensure autovacuum is running effectively.