Security

PostgreSQL under load

Indexes that earn their write cost, reading a plan properly, pool sizing arithmetic, and the lock contention that only appears at peak.

Written forEngineers whose database is the slow part and who would rather not shard

Reading time12 min read

Last reviewed2026-08-25

A well-configured PostgreSQL instance on modest hardware will serve most institutional workloads for years. The systems we are asked to rescue are rarely at a genuine scale limit — they are running a handful of pathological queries, carrying indexes nobody measured, and exhausting a connection pool that was sized by guessing.

An index is a write tax you pay for a read discount

Every index must be maintained on every insert, update and delete of the rows it covers. Teams add indexes to fix a slow read and never measure what the write path now costs — which is how a bulk import that took four minutes starts taking forty.

sql

-- Indexes nobody uses, ordered by what they cost to keep.
SELECT relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan < 50
ORDER BY pg_relation_size(indexrelid) DESC;

-- Tables taking the most sequential scans: the real candidates.
SELECT relname, seq_scan, seq_tup_read, idx_scan,
       seq_tup_read / NULLIF(seq_scan, 0) AS avg_rows_per_scan
FROM pg_stat_user_tables
ORDER BY seq_tup_read DESC
LIMIT 20;

Two queries worth running on any inherited database before you add anything.

Composite index column order follows the query, not intuition: equality columns first, then the range or sort column. An index on (status, created_at) serves "status = x ORDER BY created_at" — the reverse order does not.
Partial indexes are the most underused tool in the box. If 98% of rows are status = closed and every query wants the open ones, index WHERE status = 'open' and the index becomes a fraction of the size with none of the write cost on closed rows.
A covering index (INCLUDE) turns an index scan plus heap fetch into an index-only scan. On a hot list endpoint that is often the single largest win available.
Sequential scans are not automatically wrong. On a small table they are faster than an index, and the planner knows it. Optimise what the plan actually does, not what offends you.

Read the plan, and read the right numbers

EXPLAIN without ANALYZE shows the plan the planner intends; EXPLAIN (ANALYZE, BUFFERS) shows what actually happened and how much of it came off disk. Only the second is evidence. The three lines to look at first are rarely the ones at the top.

What to look for	What it means	Usual fix
rows estimate far from actual	The planner is working from stale or insufficient statistics and will choose badly downstream	ANALYZE the table; raise the statistics target on the skewed column
Rows Removed by Filter, in thousands	The index found the rows and then most were thrown away	Move the filter into the index — often a partial index
Nested Loop over a large outer set	A join strategy chosen on a bad row estimate	Fix the statistics first; do not reach for a hint
Heap Fetches high on an index-only scan	The visibility map is stale, so the index-only scan is not one	Vacuum the table; check autovacuum is keeping up
shared read high, shared hit low	The working set is not in cache and every request pays disk	Raise effective_cache_size and shared_buffers, or reduce the working set

Pool sizing is arithmetic, not a preference

The instinct is to raise the pool when the database is slow. That reliably makes it slower: more concurrent queries contend for the same cores and disk, each one takes longer, and the queue grows. A useful starting point is a small multiple of the core count, not a multiple of the request rate.

text

pool_size ≈ (cores × 2) + effective_spindles

  4 vCPU, SSD storage        ->  ~10 connections
  8 vCPU, SSD storage        ->  ~18 connections

Application instances × pool_size must stay below
max_connections, with headroom for migrations and
a human with psql during an incident.

  6 instances × 18 = 108   against max_connections 100  -> outage

A starting point, then measured. Note that pgbouncer changes the arithmetic entirely.

The contention that only appears at peak

Lock problems do not show up in a load test that hits distinct rows. They show up when three hundred people act on the same row in the same minute — a session opening, a deadline, a results announcement.

Keep transactions short, and do nothing else inside themAn HTTP call inside a transaction holds row locks for the duration of somebody else's network. Commit, then call.

Order writes consistentlyDeadlocks between two transactions touching the same two tables in opposite orders. Fix by convention — always parent then child — not by retrying.

Use advisory locks for singleton workScheduled jobs running on every instance simultaneously is a common cause of duplicate side effects. pg_try_advisory_lock is cheaper and clearer than a row-based mutex.

Take counters off the hot rowA single counter row updated on every request serialises the whole workload. Insert increments and aggregate, or hold the counter outside the database entirely.

Add indexes concurrentlyCREATE INDEX takes a lock that blocks writes for the duration. CREATE INDEX CONCURRENTLY does not, takes longer, and is the only acceptable option on a live table.

Autovacuum is not optional, and its defaults assume a small table

Default autovacuum thresholds scale with table size, so a large, busy table is vacuumed proportionally less often exactly when it needs it most. Bloat grows, index-only scans stop being index-only, and query times drift upward for reasons no code change explains. Tune the thresholds per table on anything hot.

sql

ALTER TABLE pass_events SET (
  autovacuum_vacuum_scale_factor  = 0.02,   -- default 0.2
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_cost_limit    = 2000
);