Postgres runs almost every system behind the case studies on this site, and its production personality has three moods worth mastering: vacuum, indexes and connections. Get those right and it is one of the most boring pieces of infrastructure you will ever operate — which, in a money system, is the highest compliment available.
Vacuum, or the rent you always owe
Postgres uses MVCC, which means an update does not overwrite a row — it writes a new version and leaves the old one dead. Vacuum reclaims those dead tuples. This is not a flaw; it is the mechanism that lets readers avoid blocking writers, and the price is a background process you must respect.
Autovacuum defaults are tuned for polite workloads. A table taking thousands of updates a minute — a balances projection, a session table, a job queue — will outrun the default thresholds. The result is bloat: dead tuples accumulating faster than they are reclaimed, inflating storage and slowing every sequential scan because the pages are mostly rubbish.
The fix is per-table settings with a much lower scale factor on your hot tables. Over-vacuuming costs some I/O; under-vacuuming costs storage you cannot reclaim without a rewrite that needs a maintenance window. The asymmetry is clear enough to make the decision easy.
Then the one that pages you at 3 AM: transaction-ID wraparound. Postgres tracks row visibility using a transaction counter that is finite. As it approaches exhaustion, Postgres protects your data integrity by refusing new writes — and your ledger becomes read-only at the worst possible hour, in a way no amount of application-level resilience helps.
Monitor the age of the oldest unfrozen transaction. Alert well before the threshold. This is the single most important Postgres metric for a write-heavy system, because it is the only failure mode with a hard stop rather than a gradual degradation.
Long-running transactions make everything worse. An open transaction holds back the freeze horizon, which means one forgotten BEGIN in a psql session, or an analytics query running for hours, prevents vacuum from cleaning anything newer than it. Monitor for transactions open longer than a few minutes, and treat them as bugs rather than as someone working.
Indexes as a portfolio
Every index taxes every write to subsidise some reads. That framing — a portfolio with carrying costs — is more useful than treating indexes as free performance.
Find the freeloaders. pg_stat_user_indexes reports scan counts. An index never scanned and always maintained is pure cost, and most mature databases carry several: created for a feature since removed, or for a query since rewritten. Dropping them speeds up writes measurably.
Find the gaps properly. Read pg_stat_statements ordered by total time, not by mean time. The query that takes 40 milliseconds and runs ten thousand times an hour matters more than the one that takes four seconds and runs nightly, and mean-time sorting hides exactly that.
Use the specialised forms. A partial index on the hot slice — WHERE status = 'pending' on a queue table where pending rows are a fraction of the total — is dramatically smaller and cheaper to maintain than the full index. Covering indexes let a query be answered without touching the heap at all, which matters on your highest-frequency lookups.
Add them concurrently. CREATE INDEX CONCURRENTLY avoids the exclusive lock that would otherwise block writes for the duration of the build. It is slower and can leave an invalid index behind if it fails — drop and retry — and it is the only acceptable form on a busy table.
Connections
Each Postgres connection is an operating-system process with real memory overhead. Hundreds of mostly-idle application connections is not a scaling strategy, it is memory arson — and it fails in the ugliest way, by degrading everything simultaneously rather than one thing clearly.
PgBouncer in transaction mode is the standard answer: your application opens many cheap connections to the pooler, which multiplexes them onto a small number of real ones. The catch worth knowing is that transaction-mode pooling breaks session-level features — prepared statements held across transactions, SET that expects to persist, advisory locks — so the application has to be written knowing a pooler exists.
Schema changes under load
Two rules that prevent most outages caused by deployments.
Know which operations take an exclusive lock. Adding a nullable column without a default is fast in modern Postgres. Adding a column with a volatile default, changing a type, or adding a constraint that requires validation can rewrite the table — and holding an exclusive lock on a busy table for minutes takes your application down as effectively as any crash.
Use a lock timeout. Set lock_timeout on migrations so a statement that cannot acquire its lock quickly fails rather than queuing — because a queued exclusive lock blocks every subsequent query on that table, turning a slow migration into a full outage. Failing fast and retrying is strictly better than waiting hopefully.
The expand-migrate-contract pattern in the migration article exists partly to keep every individual schema step in the fast category.
Backups, stated bluntly
A backup that has never been restored is a hypothesis. Restore one, on a schedule, into a scratch environment — and record how long it takes, because that duration is your actual recovery time and it is invariably longer than anyone’s estimate.
This is the check I run on day one of every fractional CTO engagement, and it fails more often than any other single check. Not because teams are careless, but because backups are configured once, succeed silently forever, and nobody has a reason to test them until the reason is very expensive.
The five queries worth keeping in a snippet file
Not a tuning guide — just the diagnostics I reach for in the first ten minutes of any Postgres problem.
Oldest transaction-ID age, against the wraparound threshold. If this is unhealthy, nothing else matters yet.
Currently running queries ordered by duration, which finds both the long-running transaction holding back vacuum and the accidental sequential scan someone deployed this morning.
Table and index bloat estimates, to distinguish “this table is large” from “this table is mostly dead tuples”.
pg_stat_statements by total time, which is where missing indexes actually reveal themselves.
Unused indexes by scan count, for the periodic cleanup that speeds up every write.
Having these ready matters more than knowing them, because the moment you need them is the moment you least want to be composing SQL from memory. Keep them in the same repository as the application, so they are versioned alongside the schema they interrogate rather than pasted from a chat thread.
Related reading
/blog/zero-downtime-migration — schema changes on live systems · /blog/ledger-design — the schema this database usually holds · /work/ledger-rebuild — these disciplines in a real deployment · /services/architecture — reviews that include the database
Questions I actually get
What is the one thing to monitor if I can only monitor one?
Transaction-ID age. Bloat degrades performance gradually and gives you warning; wraparound protection stops writes entirely and gives you very little. Everything else can be diagnosed after the fact — that one has to be caught before it arrives.
How aggressive should autovacuum be on a hot table?
Far more aggressive than the defaults, which were chosen for polite workloads. On a table taking thousands of updates a minute, per-table settings with a much lower scale factor are appropriate. The failure mode of over-vacuuming is some wasted I/O; the failure mode of under-vacuuming is bloat you cannot reclaim without downtime.
Do I need a connection pooler?
If you have more than a couple of hundred application connections, yes. Each Postgres connection is an operating-system process with real memory cost, and hundreds of mostly-idle ones is memory arson. PgBouncer in transaction mode is the standard answer.
How do I find missing indexes?
Read pg_stat_statements for the queries costing the most total time, then explain those specifically. Chasing individually slow queries is less useful than chasing the moderately slow query that runs ten thousand times an hour.
Is it safe to add an index on a large live table?
With CREATE INDEX CONCURRENTLY, yes — it avoids the exclusive lock that would otherwise block writes for the duration. It is slower and can leave an invalid index if it fails, which you then drop and retry. Never use the plain form on a busy table.
How do I know my backups work?
By restoring one. A backup that has never been restored is a hypothesis, and the restore is also the only way to learn how long it takes — which is the number that matters during an incident. Test it on a schedule, not after an outage.