Postgres performance for application teams: the queries that kill you at scale

01Find the problem before theorising about it

Enable pg_stat_statements and sort by total execution time rather than mean. The query that takes 40 milliseconds and runs two million times a day costs far more than the nightly report that takes 30 seconds, and teams almost always optimise the visible slow query rather than the expensive one.

Then read the actual plan with EXPLAIN (ANALYZE, BUFFERS). The BUFFERS output is the part most people skip and the part that tells you whether you are reading from memory or from disk. A plan that looks reasonable but shows enormous shared read counts is doing physical I/O that a better index or a higher cache ratio would eliminate.

• Sort pg_stat_statements by total_exec_time, not mean_exec_time.
• Always use ANALYZE and BUFFERS - estimated plans mislead.
• Compare estimated vs actual row counts; large divergence means stale statistics.
• Check for sequential scans on large tables inside nested loops.

02The N+1 that ORMs make invisible

The single most common scaling failure is not a slow query but a fast one executed thousands of times. An ORM lazily loads a relation inside a loop, and a page that issued three queries in development issues three thousand in production. Each is sub-millisecond, so nothing looks slow, but the round trips dominate response time.

The fix is eager loading the relations you know you will need, and the discipline is logging query counts per request in development with a threshold that fails loudly. Teams that add this check find existing N+1s immediately and stop introducing new ones, because the feedback arrives before the code merges.

03Indexes: the ones people miss

Single-column indexes on foreign keys are usually present. What is usually missing is the composite index matching the actual query shape, and the column order in a composite index is not arbitrary - it must follow equality conditions first, then range conditions, then the ordering column.

An index on (tenant_id, created_at) serves a query filtering by tenant and sorting by date. The reverse order does not, and the planner will ignore it. This ordering rule explains a large share of 'I added an index and nothing improved' reports.

04Connection pooling is not optional

Each Postgres connection is an operating system process with meaningful memory overhead. A few hundred connections will consume resources that should be serving queries, and serverless platforms make this dramatically worse by opening connections per invocation with no reuse.

Run a pooler - PgBouncer or the managed equivalent - in transaction mode for most application workloads. Be aware that transaction mode breaks session-level features: prepared statements, advisory locks held across statements, and session variables. Most application code does not use these, but the ones that do fail in confusing intermittent ways, so audit before switching.

05Vacuum, bloat and the slow decline

Postgres does not overwrite rows on update; it writes a new version and marks the old one dead. Autovacuum reclaims that space, and when it cannot keep up, tables and indexes bloat. Queries slow gradually as they read more pages for the same data, and because the decline is slow nobody notices until it is severe.

High-churn tables frequently need per-table autovacuum tuning more aggressive than the defaults, which were chosen for a much smaller era of hardware. Monitor dead tuple ratios and transaction ID age. The latter matters existentially: if wraparound protection engages, Postgres will stop accepting writes to protect data integrity, and the emergency vacuum that follows is not a fast operation.