Postgres Under Load: CPU Saturation, Write Amplification, and Plan Regressions

I promised a follow-up to the migration post about what happened after we moved onto PostgreSQL. This is it.

Short version: the data landed perfectly and then the database spent a few months teaching us that our old access patterns were not its access patterns. Three incidents stand out, because each one represents a different way Postgres tells you that you've got it wrong.

Incident One: CPU at the Ceiling

The first serious one looked like a capacity problem. CPU pinned near 100% during peak submission windows, latency climbing across every endpoint, and the obvious temptation to resize the instance.

We didn't resize. We looked at pg_stat_statements first, ordered by total_exec_time rather than mean time — because the query that's killing you is usually not the slowest one, it's the moderately slow one called constantly.

SELECT calls,
       round(total_exec_time::numeric, 0) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       rows / GREATEST(calls, 1)          AS avg_rows,
       query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Top of the list was an unremarkable lookup running millions of times an hour and doing a sequential scan every time. It had been fine in the document store, where that access pattern was the primary key. In the relational model it was a filtered query on a column with no index.

One index. CPU dropped by more than half.

The lesson: after a migration, your worst queries aren't the complicated ones you worried about. They're the trivially simple ones that used to be free and now aren't. Sort by total time, not mean time.

Incident Two: Write Amplification

The second one was subtler and took longer to see. Write latency degrading gradually over weeks, table sizes growing faster than row counts, autovacuum constantly busy and never quite finishing.

The cause was our own enthusiasm. Fixing incident one taught us that indexes solve problems, so we'd added rather a lot of them — including several on columns in our hottest update path.

Postgres has an optimization called HOT (heap-only tuple) updates. When you update a row and don't touch any indexed column, and there's room on the page, it can write the new row version without updating every index. It's a large win on update-heavy tables.

Index a column that gets updated frequently, and you forfeit that. Every update now writes to the heap and touches every index. Ten indexes means ten times the write work, ten times the WAL, and a lot more for autovacuum to clean up.

Two fixes:

• Delete indexes nobody uses. pg_stat_user_indexes shows idx_scan per index. Several of ours were zero. An unused index is pure write tax.
• Lower fillfactor on hot tables. HOT updates need free space on the page. Default fillfactor of 100 leaves none, so the first update spills to a new page and takes the slow path.

SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan < 50
ORDER BY pg_relation_size(indexrelid) DESC;

The lesson: indexes are not free, and the cost lands on writes where you're not looking for it. On an update-heavy table, ask what each index costs before asking what it gives.

Incident Three: The Plan That Changed

The worst one, because nothing changed.

A report query that had run in a couple of seconds for months started taking minutes. No deploy, no schema change, no code change. Same query, same data shape, wildly different behaviour.

The planner had switched strategies. As the table grew past a certain point and the statistics were refreshed, the estimated cost of a nested loop crossed the estimated cost of a hash join, and the planner picked differently. Its estimate was wrong, because the column distribution was badly skewed — a small number of payers accounted for most of the rows, and the planner's default statistics didn't capture that.

Three things helped:

Increase the statistics target on skewed columns. The default samples relatively few values. On a column with heavy skew, ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 1000 followed by ANALYZE gives the planner a much better histogram.

Use extended statistics for correlated columns. If two columns are correlated — and in claims data, plenty are — Postgres assumes independence and gets the row estimate badly wrong. CREATE STATISTICS on the pair fixes that.

Read EXPLAIN (ANALYZE, BUFFERS) properly. The number that matters is the gap between estimated and actual row counts. When the planner thinks a node returns 50 rows and it returns 500,000, everything above that node was planned on a fiction. Find the lowest node where estimate and reality diverge; that's your problem.

The lesson: plan regressions arrive without a deploy. If your monitoring only reacts to releases, this class of incident is invisible until a user reports it.

What Stuck

A few habits came out of those months that we still keep:

• pg_stat_statements is always on. Not a debugging tool you enable during an incident — a permanent instrument.
• Alert on query time, not just CPU. CPU is a symptom several different causes share.
• Review indexes on a schedule. Additions get reviewed; nobody ever proposes a deletion unless it's somebody's job.
• Load-test with production-shaped data. Uniformly distributed synthetic data produces beautiful plans that mean nothing. Skew is the whole problem.

None of this was exotic. No extensions, no sharding, no clever architecture. Indexing strategy, query rewrites, and statistics — under real production load, with real production skew.

The migration was the interesting engineering. This was the work that made it actually pay off.