MVCC, WAL, autovacuum, replication — how the server really works, where it tends to break, the signals worth watching, and a runbook for each incident.
PostgreSQL is famously easy to run for the first year, and famously hard to run for the fifth.
The defaults work. Until autovacuum cannot keep up with a high-churn table and dead tuples pile up. Until a forgotten replication slot retains WAL forever and fills the disk. Until age(datfrozenxid) crosses 2 billion and the database refuses writes to avoid wraparound corruption. Until a long-running transaction silently blocks every vacuum across the cluster. Until one slow query takes an AccessExclusiveLock that blocks every other transaction. Until a checkpoint storm turns a steady write workload into a stop-the-world I/O spike.
These guides are written for engineers who already run PostgreSQL, not for people learning what an index is. The goal is to give you the mental model of how the server actually behaves under load, the failure patterns that keep recurring, the monitoring story that catches problems before they page anyone, and the runbooks you wish someone had handed you before your last incident.
PostgreSQL is not a single process. It is a postmaster supervising a per-connection backend, several background processes, a chunk of shared memory, and a strict contract with the storage layer. Most production failures live between these layers, not inside any one of them.
Why this matters: a query can be slow because of a missing index, a stale plan, a lock wait, a temp-file spill, a checkpoint flush, an autovacuum I/O storm, an OS page-cache miss, or a slow disk fsync. The symptom is the same — slow query — but each layer has a different signal and a different fix.
Most PostgreSQL incidents fall into a small set of recurring patterns. Recognise the shape, and triage gets dramatically faster.
FATAL: sorry, too many clients already. Applications fail to acquire connections; new sessions are refused. Underneath it is usually max_connections set too low for the workload, an application leak, idle-in-transaction sessions piling up, or no PgBouncer in front of the database.
One slow transaction takes a lock; everything else queues behind it. A migration takes AccessExclusiveLock on a hot table; the entire app stalls. A row-level lock contends; deadlock detector fires every deadlock_timeout. The database keeps running while the workload grinds to a halt.
A long-running transaction prevents dead tuple cleanup. Bloat accumulates on hot tables. Sequential scans get slower. Indexes balloon. Autovacuum eventually catches up — at the worst possible time, competing with peak load. The fix is rarely "tune autovacuum harder"; it is "find the long transaction."
PostgreSQL stops accepting writes when transaction IDs come within ~3 million of wraparound. WARNING: database must be vacuumed within X transactions escalates to ERROR: database is not accepting commands. Recovery is single-user mode and VACUUM FREEZE. Prevention is monitoring age(datfrozenxid) long before it matters.
A logical or physical replication slot stops being consumed. The primary cannot recycle WAL because the slot retains it. pg_wal grows without bound until the disk fills. The primary then refuses writes. The fix in the moment is to drop the slot; the prevention is alerting on slot lag and max_slot_wal_keep_size.
A burst of dirty pages forces a checkpoints_req ahead of schedule. Buffered writes drain to disk in a spike; fsync latency climbs; query latency follows. Logs show checkpoints are occurring too frequently. The fix is almost always max_wal_size, not checkpoint_timeout.
Netdata monitors PostgreSQL with per-second metrics, pre-built dashboards, and ML-powered anomaly detection. Correlate connection saturation, lock waits, autovacuum progress, replication lag, and checkpoint I/O against the rest of your stack so you catch the incidents in these runbooks before they page anyone.
A ranked review of the tools teams actually shortlist here, what each one is genuinely good at, and how the pricing behaves as you scale.
PostgreSQL observability works in four practical levels. Each is a complete operation, not a stepping stone. Pick the level that matches how much your database matters. Most production databases should land at the second level.
Survival monitoring is the floor. With these signals you can answer one question: is the database still functioning? You will not learn what broke, but you will learn that something broke before users do. Survival is enough for dev environments and hobby clusters.
Operational monitoring is what most production databases should target. Survival tells you something is wrong; operational tells you what. With this coverage your team can usually diagnose an incident on its own: bloat, replication lag, slow queries, checkpoint pressure, lock waits.
Mature monitoring catches problems before they wake anyone up. age(datfrozenxid) climbing, replication slot lag drifting, statistics going stale, plan cache regressing to a generic plan, temp file rate creeping. None of these will page you on day one. They become page-out incidents on day thirty.
Expert signals enter your stack the day after a specific incident proved you needed them. wait-event sampling, autovacuum I/O accounting per table, btree split rates, ProcArray contention, replication apply conflicts on hot_standby. Most teams never need every signal here. Add the ones your incident history says you do.
The traps PostgreSQL teams keep falling into. Each has a clear, well-known fix. Most teams only learn it after an incident.
PostgreSQL is process-per-connection. Each backend costs ~5–10 MB even idle. Five hundred backends is 5 GB of memory and serious context-switch overhead. PgBouncer in transaction mode lets you serve thousands of clients with 50 server connections.
Wraparound is the silent killer. Default <code>autovacuum_freeze_max_age</code> is 200M. The hardcoded shutdown threshold is around 2.147B. Alert at 500M and 1B; ignore both and you will eventually meet a database that refuses writes.
A slot retains WAL until consumed. A forgotten or stalled slot is the #1 root cause of pg_wal filling the disk. Alert on slot lag bytes and active=false on any persistent slot.
fsync is what makes PostgreSQL durable. Disabling it can corrupt the cluster on any unclean shutdown. If you genuinely need extra write performance, tune synchronous_commit, not fsync.
An untested backup is not a backup. Schedule a quarterly restore drill on a separate host. The first time you discover that backups don't restore must not be during an incident.
Disabling autovacuum on "hot" tables to "avoid I/O" is how teams meet wraparound emergencies. Tune <code>autovacuum_vacuum_scale_factor</code> and <code>autovacuum_vacuum_cost_delay</code> per table; never set <code>autovacuum_enabled = off</code> in production.
An idle-in-transaction session holds xmin and prevents cleanup of any tuple newer than its snapshot. Set <code>idle_in_transaction_session_timeout</code> on every production cluster (60s–5min depending on workload).
The OS page cache also caches Postgres pages. Above ~40% of RAM in shared_buffers, the kernel cache starves and you pay double for the same data. 25–40% is the well-known sweet spot.
Each guide is a focused runbook for one symptom or topic. Pick one when you have an incident, or use the categories to learn the area.
If you're starting from scratch, the monitoring checklist is the path of least regret. If you're mid-incident, jump straight to the symptom that matches what you're seeing.
