PostgreSQL Common Problems

Connection slots exhausted

FATAL: sorry, too many clients already. Usually the application opened more connections than max_connections, often because a pool was resized or instances were scaled up.

Raising max_connections treats the symptom and costs memory. Put a pooler in front and cap the total — see PgBouncer and the connection exhaustion playbook.

Table and index bloat

Symptoms: disk usage grows while row counts do not, and sequential scans get slower over time. The cause is nearly always something holding the transaction horizon back — a long transaction, an idle-in-transaction session, or an inactive replication slot. See Table Bloat.

Transaction ID wraparound warnings

database is not accepting commands to avoid wraparound data loss. Autovacuum has not frozen old rows fast enough. Alert on age(relfrozenxid) long before this point; recovery requires a vacuum that can run for hours.

A plan that changed overnight

A query that was fast yesterday now scans sequentially. The usual causes are stale statistics after a bulk load, a table that crossed the size where a different plan became cheaper, or a parameter value that is unrepresentative of the typical case.

ANALYZE orders;
EXPLAIN (ANALYZE, BUFFERS) SELECT …;   -- compare estimated with actual rows

If the estimate is far from the actual, the fix is better statistics — extended statistics for correlated columns, or a higher statistics target — not a query rewrite. See Query Planning.

Deadlocks

ERROR: deadlock detected
DETAIL: Process 4711 waits for ShareLock on transaction 89231; blocked by process 4712.

PostgreSQL detects deadlocks and aborts one transaction; the application must retry. Reduce their frequency by acquiring locks in a consistent order across code paths, keeping transactions short, and updating rows in a deterministic sequence. Enable log_lock_waits to see what precedes them.

Idle in transaction sessions

The most damaging state a session can be in. It holds locks and blocks vacuum indefinitely.

ALTER ROLE app_service SET idle_in_transaction_session_timeout = '30s';

The underlying cause is usually application code that opens a transaction and then performs an external call or waits on user input.

Replication lag on a standby

Identify which component is lagging — send, flush or replay — before acting. Replay lag is often caused by long queries on the standby conflicting with WAL apply, or by a single-threaded apply that cannot keep up with a bulk write on the primary. See the replication lag playbook.

Out-of-memory kills

A backend killed by the kernel causes the postmaster to restart every backend, dropping all connections. Nearly always caused by work_mem multiplied across nodes, workers and concurrency. See Memory Settings.

Disk full

Extension missing after a restore

A restore into a cluster without the same extensions installed fails or silently loses functionality. Extensions are part of the environment, not the dump. Record the extension list alongside the backup:

SELECT extname, extversion FROM pg_extension ORDER BY extname;