Notes for the problems that show up after launch: bad plans, awkward migrations, index debt, vacuum pressure, replica lag, and the small decisions that make PostgreSQL easier to operate.
A single connection sitting in 'idle in transaction' can hold locks, pin the vacuum horizon, and quietly bloat every table in the database. I learned this the hard way from a debugger someone left paused overnight.
Long analytics queries on our replica kept getting cancelled mid-run. Turning on hot_standby_feedback stopped the cancellations instantly — and then the primary started bloating. That trade is the whole story.
An index that had grown to three times the size of its table was the reason a lookup got slow. B-tree indexes bloat from page splits and churn, and the fix is more nuanced than 'just REINDEX'.
LISTEN/NOTIFY is a lovely way to push events without extra infrastructure, until the global notify queue fills, a slow listener blocks commits, and you learn it was never built to be your message bus.
A table with one million live rows was holding five million dead ones, and queries were scanning all six. Understanding xmin, xmax, and how PostgreSQL decides what a query can see explains where the dead rows come from.
Switching PgBouncer to transaction pooling tripled our connection efficiency and broke prepared statements, SET commands, and advisory locks all at once. The fix starts with understanding what a pooled connection no longer guarantees.
A one-line ALTER COLUMN TYPE took an ACCESS EXCLUSIVE lock and rewrote a 150-million-row table for forty minutes. Some type changes are free; the ones that aren't have a safe pattern that avoids the outage.
A nightly export OOM-killed itself trying to load ten million rows into memory at once. The fix wasn't more RAM — it was telling PostgreSQL to hand the rows over a batch at a time with a server-side cursor.
VACUUM FULL would have locked a 200GB table for over an hour in the middle of the day. pg_repack rebuilt it online with only a momentary lock, and nobody noticed. Here's how it works and when to trust it.
Every query takes lightweight relation locks you never think about. Cross the fast-path limit of 16 per backend, usually via heavy partitioning, and the shared lock manager turns into a bottleneck under concurrency.
Parallel query can turn a 30-second scan into 6 seconds, or it can spawn six workers that fight over the same disk and finish slower than one. Knowing when it helps is mostly knowing when it doesn't.
Temp file spills are often blamed on work_mem, but the real fix may be a better index, smaller input, different join, or query rewrite. Measure the spill before raising memory.