PostgreSQL at Scale: Query Optimisation Patterns for Production SaaS

Last Update: 28 March 20269 min read

Table Of Contents (5 topics)

When a SaaS application starts to slow down, the instinct is to reach for horizontal scaling - more servers, bigger instances, a CDN layer. But in the vast majority of production incidents, the bottleneck is not compute. It is a database query that scans millions of rows without an index, a JOIN that materialises an intermediate result set ten times larger than necessary, or an ORM that fires forty-seven queries to render a single dashboard page. PostgreSQL is extraordinarily capable, but it rewards the teams who understand how it executes queries and build accordingly.

1. Reading EXPLAIN ANALYZE: The Skill That Pays Compound Interest

Every query optimisation investigation starts in the same place: `EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)`. This is the most important PostgreSQL command for any engineer working on a production database, and yet most developers who use PostgreSQL daily have never run it. `EXPLAIN ANALYZE` actually executes the query and reports how PostgreSQL's query planner chose to execute it, how long each step took in wall-clock time, and - critically - whether the planner's row count estimates matched reality. A large gap between estimated rows and actual rows is almost always the source of a bad execution plan: the planner chose a sequential scan when an index scan would have been faster, or chose a nested loop join when a hash join on a larger result set would have been orders of magnitude more efficient. `BUFFERS` adds cache hit/miss information: how many 8KB buffer pages were read from shared memory (fast) versus fetched from disk (slow). A query that reads 50,000 blocks from disk and 200 from cache on every execution is a candidate for both index optimisation and memory configuration tuning. Understanding this output is the prerequisite for every other optimisation technique.

Key Takeaway

Run EXPLAIN (ANALYZE, BUFFERS) on every slow query before touching the schema. The output tells you exactly what PostgreSQL did - sequential scan vs. index scan, estimated vs. actual rows, cache hits vs. disk reads. Optimise based on evidence, not intuition.

2. Index Strategy: Beyond the Obvious Foreign Key

Most developers add indexes reactively - a query is slow, they add an index on the WHERE column, the query gets faster. This works for simple cases but misses the patterns that matter at scale. Partial indexes dramatically reduce index size and maintenance overhead for queries that always filter on a status or boolean condition. An index on `(created_at) WHERE status = 'pending'` covers the common "find all pending tasks ordered by creation time" query while being a fraction of the size of a full index on `created_at`. Since PostgreSQL only needs to maintain the partial index for rows matching the condition, inserts and updates on rows that do not match `status = 'pending'` do not touch the index at all. Composite index column order follows a strict rule: equality conditions first, range conditions last. An index on `(user_id, created_at)` covers `WHERE user_id = $1 AND created_at > $2` efficiently - PostgreSQL uses the `user_id` equality condition to seek to the right section of the index, then scans forward through the `created_at` range. The same index on `(created_at, user_id)` cannot efficiently serve this query because the range condition on the leading column prevents a targeted seek on `user_id`.

sql

--  Full table scan: no index covers this common query pattern
SELECT id, title, created_at
FROM tasks
WHERE user_id = '550e8400-e29b-41d4-a716-446655440000'
  AND status = 'pending'
ORDER BY created_at DESC
LIMIT 20;

-- Composite + partial index: covers equality, filter, and sort in one index scan
CREATE INDEX idx_tasks_user_pending
  ON tasks (user_id, created_at DESC)
  WHERE status = 'pending';

--  Covering index: includes all SELECT columns  -  zero heap fetches required
CREATE INDEX idx_tasks_user_pending_covering
  ON tasks (user_id, created_at DESC)
  INCLUDE (id, title)
  WHERE status = 'pending';

3. The N+1 Problem and ORM Query Batching

The N+1 query problem is the single most common source of database performance issues in ORM-driven applications. The pattern: you fetch a list of N records, then for each record, you fire an additional query to fetch a related resource. A dashboard that loads 50 projects and then fetches the owner's name for each one generates 51 database round trips - 1 for the project list, 50 for the owner lookups - when a single JOIN would produce the same result in one round trip. ORMs obscure N+1 problems because each individual query looks trivially simple. The developer writes `project.owner.name` in a template and the ORM lazily loads the owner when the property is accessed. In a test environment with five projects, this is imperceptible. In production with 500 projects and a database connection pool under load, the 501 queries add hundreds of milliseconds of latency and consume connection pool slots that other concurrent requests are waiting for. The fix varies by ORM. In Prisma, use `include` to eager-load relations in the same query. In TypeORM, use `leftJoinAndSelect`. In raw SQL, write the JOIN. In all cases, the principle is the same: fetch related data in the same database round trip as the parent records, not in a loop. Tools like `prisma-query-inspector` or enabling query logging with `DEBUG=prisma:query` reveal N+1 problems immediately in development before they reach production.

Key Takeaway

Enable ORM query logging in development and inspect every page load. If you see the same query repeated N times with different IDs, you have an N+1 problem. Fix it with eager loading or a JOIN before shipping.

4. Connection Pooling: The Bottleneck Most Teams Hit Too Late

PostgreSQL creates a new OS process for each database connection. This is a deliberate architectural decision that provides strong isolation, but it means that connections are expensive - each one consumes roughly 5-10MB of server memory and has significant startup overhead. A database server configured with `max_connections = 100` (a common default) can handle at most 100 concurrent active connections before additional connection attempts are queued or rejected. Serverless and edge function deployments exacerbate this dramatically. A Next.js application deployed on Vercel can spawn hundreds of function instances under load, each attempting to establish its own database connection. A database with `max_connections = 100` becomes the hard ceiling on your application's concurrency - not your compute layer. PgBouncer in transaction pooling mode solves this by maintaining a small pool of long-lived connections to PostgreSQL and multiplexing many application connections through them. An application that appears to have 500 concurrent connections actually shares 20 real PostgreSQL connections, with each connection handed off between transactions rather than held for the lifetime of a request. This allows a modest PostgreSQL instance to serve hundreds of concurrent application processes without running out of connections.

Key Takeaway

If you are deploying to serverless or edge environments, PgBouncer in transaction pooling mode is not optional - it is the architectural component that prevents database connection exhaustion from becoming your production ceiling.

5. Partitioning, Vacuuming, and the Long Game

Tables that grow without bound eventually cross performance thresholds that indexes alone cannot address. An events table with 500 million rows and a time-series access pattern - where queries almost always filter by a recent time window - is a candidate for range partitioning by month or week. PostgreSQL's partition pruning means that a query for "events in the last 7 days" only scans the relevant partition rather than a 500M-row table, and each partition's indexes are proportionally smaller and faster. Vacuum is the background process PostgreSQL runs to reclaim storage from dead tuples - rows that have been updated or deleted but remain on disk until vacuum collects them. In tables with high update rates, dead tuples accumulate faster than autovacuum can process them. Table bloat increases the physical size of the table, slows sequential scans, and in extreme cases triggers table-level locks when PostgreSQL wraps around its transaction ID counter. Monitoring `pg_stat_user_tables` for tables with large `n_dead_tup` counts and tables where `last_autovacuum` is hours or days ago identifies vacuum backlog before it becomes a production incident. Tuning `autovacuum_vacuum_scale_factor` and `autovacuum_vacuum_cost_delay` for high-traffic tables ensures autovacuum keeps pace with the write load rather than falling behind.

Summary

PostgreSQL performance at scale is not about throwing more hardware at the problem - it is about understanding how the query planner makes decisions, how indexes serve specific query patterns, how ORMs generate queries you never explicitly wrote, and how connection architecture limits concurrency. The teams who ship fast SaaS products on modest database infrastructure have internalized EXPLAIN ANALYZE, write indexes with column order deliberateness, instrument their ORM for N+1 detection, configure connection pooling before serverless deploys, and monitor vacuum health as part of their operational routine. These are not advanced topics - they are the fundamentals of treating PostgreSQL as an engineering discipline rather than a black box.