PostgreSQL Production Problems: 4 Real Failures and How to Fix Them

PostgreSQL usually does not fail out of nowhere. The incident that gets everyone's attention is often the last step in a chain: connections accumulate for days, a transaction stays open, a migration waits for a lock, or a forgotten replication slot retains WAL until the disk is full.

Public incident reports make this pattern hard to ignore. Sentry lost most of a working day to transaction ID wraparound. GitLab has documented WAL backlog incidents and a customer cluster where replication slots contributed to cascading disk failures. Locking investigations from pganalyze show how a migration that needs a lock for a few milliseconds can still stall an application.

This guide turns those lessons into a troubleshooting runbook. The diagnostic queries are read-only. The commands that change roles, tables, sessions, or replication slots are explained separately—do not run those against production until you understand their blast radius.

Production warning: Start with the read-only diagnostic queries. Do not terminate sessions, alter roles or tables, drop replication slots, or change timeout settings without testing the change and understanding its rollback path. Keep a verified backup and use a maintenance window for disruptive work. Never manually delete files from pg_wal.

1. “FATAL: sorry, too many clients already”

The tempting fix is to increase max_connections. Sometimes that is appropriate, but it can also give a connection leak more room to grow while increasing the server's memory and process overhead. PostgreSQL sizes some resources directly from this setting, and changing it requires a server restart.

First find out whether the connections are active, idle, or stuck inside transactions:

SELECT state, count(*) AS sessions
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state
ORDER BY sessions DESC;

Then inspect the sessions that are not plain idle connections:

SELECT pid,
       usename,
       application_name,
       client_addr,
       state,
       now() - xact_start AS transaction_age,
       now() - state_change AS last_state_change,
       left(query, 100) AS current_query
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND state <> 'idle'
ORDER BY xact_start NULLS LAST;

A large group of idle in transaction sessions usually points to application behavior: a request began a transaction, then waited on another service, crashed without a rollback, or returned without releasing the connection. Raising the connection limit does not repair any of those paths.

The practical fix

• Make transaction scope as short as possible. Do not hold a database transaction open across an HTTP call, queue wait, file operation, or user interaction.
• Ensure every application path releases or rolls back its connection, including exceptions and timeouts.
• Calculate the total pool demand across all application instances and workers. Ten replicas with a pool of 20 each can request 200 connections before background jobs and administrative sessions are included.
• Add a pooler such as PgBouncer when the application creates more client connections than PostgreSQL should serve directly.

PgBouncer's transaction pooling is efficient, but it changes session semantics. Features such as session-level settings, LISTEN, holdable cursors, SQL PREPARE, and session advisory locks need compatibility checks. Its official feature matrix is worth reading before switching modes; newer prepared-statement support also has explicit configuration requirements.

For an application that repeatedly abandons transactions, a guardrail can help:

ALTER ROLE app_user
SET idle_in_transaction_session_timeout = '5min';

This terminates sessions that remain idle inside an open transaction beyond the limit. Choose a value that matches real application behavior and test it first; it is not a substitute for fixing the leak. Be even more cautious with idle_session_timeout around poolers, because unexpected connection closure can confuse middleware.

If you must terminate a session during an incident, inspect its PID and application first, then target only that PID. Avoid scripts that kill every idle transaction matching a broad pattern—they can create exactly the data inconsistency you are trying to prevent.

2. A “fast” migration blocks every query on a table

An ALTER TABLE may need an ACCESS EXCLUSIVE lock only briefly. The outage happens when it cannot acquire that lock because another transaction is using the table. While the DDL waits, new statements can queue behind it—including plain SELECT queries.

This lock-queue effect is the dangerous part, and it is well explained in pganalyze's real-world locking guide. The migration itself might take 50 milliseconds once it starts. The wait behind an old transaction is what turns it into an incident.

Find blocked sessions and the PIDs in front of them:

SELECT pid,
       pg_blocking_pids(pid) AS blocked_by,
       state,
       wait_event_type,
       wait_event,
       now() - query_start AS query_age,
       left(query, 120) AS query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

pg_blocking_pids() can reveal more than one blocker and can include sessions that are only indirectly part of the queue. Inspect each reported PID instead of assuming the longest-running query is automatically guilty:

SELECT pid,
       usename,
       application_name,
       state,
       now() - xact_start AS transaction_age,
       left(query, 120) AS current_query
FROM pg_stat_activity
WHERE pid = 12345;  -- replace with a PID reported above

An idle in transaction blocker is especially common. pg_cancel_backend(pid) cancels a currently running statement, but it does not terminate an idle session. pg_terminate_backend(pid) closes the session and rolls back its transaction. Either operation needs an explicit decision: canceling a report is very different from terminating a payment worker halfway through its work.

Make migrations fail quickly instead of forming a queue

Set timeouts in the migration session:

SET lock_timeout = '5s';
SET statement_timeout = '60s';

ALTER TABLE orders
ADD COLUMN IF NOT EXISTS customer_note text;

If the lock is unavailable, the migration fails instead of waiting indefinitely. Investigate the blocker and retry during a safe window rather than restarting the migration in a tight loop. The statement timeout is only an example; choose one appropriate to the migration.

Also enable log_lock_waits on production systems where you can. It records lock waits that exceed deadlock_timeout, giving you evidence before users start reporting timeouts. For index work, use the concurrent strategy covered in our PostgreSQL slow-query guide, and remember that even “online” DDL still has lock-acquisition and failure-mode considerations.

3. Autovacuum is running, but bloat and transaction age keep growing

PostgreSQL's MVCC model leaves old row versions behind after updates and deletes. Normal VACUUM makes that space reusable, updates the visibility map, and helps prevent transaction ID wraparound. It does not usually shrink the table file and return space to the operating system.

Start with table and database age statistics:

SELECT relname,
       n_live_tup,
       n_dead_tup,
       last_autovacuum,
       last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

The tuple counts are estimates, not a precise “bloat percentage.” Look for trends, tables with heavy churn, and whether vacuum is falling behind. Then check for old transactions that can prevent cleanup:

SELECT pid,
       usename,
       application_name,
       state,
       now() - xact_start AS transaction_age,
       left(query, 100) AS current_query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

Long-running transactions, abandoned prepared transactions, and some replication slots can hold back the vacuum horizon. Disabling autovacuum removes a symptom from your monitoring for a while, but allows a much larger problem to develop.

Sentry's 2015 wraparound postmortem shows how serious this can become: its write-heavy service stopped accepting writes for hours during recovery. Read it for the operational lessons, not as a configuration template. PostgreSQL's current vacuum documentation has updated wraparound-recovery guidance and explicitly explains why old transactions and replication slots must be resolved.

The practical fix

Keep autovacuum enabled and fix whatever prevents it from making progress. For a genuinely high-churn table, a per-table setting is usually safer than applying an aggressive global value everywhere:

ALTER TABLE events
SET (autovacuum_vacuum_scale_factor = 0.05);

VACUUM (VERBOSE, ANALYZE) events;

The 0.05 value is an example to evaluate against table size, write rate, and I/O capacity—not a universal recommendation. Watch the result and adjust deliberately.

Do not reach for VACUUM FULL as the first fix. It rewrites the table, requires an ACCESS EXCLUSIVE lock, and can temporarily need roughly the table's size in additional disk space. Normal vacuum is the routine maintenance tool. Also monitor age(datfrozenxid) and investigate long before PostgreSQL emits wraparound warnings.

4. pg_wal grows until the database runs out of disk

WAL retention is a safety feature. PostgreSQL keeps write-ahead logs that a replica, logical subscriber, CDC consumer, or backup process may still need. The problem appears when that consumer stops advancing and nobody notices the retained WAL growing.

Check replication slots on the primary:

SELECT slot_name,
       slot_type,
       active,
       wal_status,
       pg_size_pretty(
         pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
       ) AS wal_retained
FROM pg_replication_slots;

An inactive slot with growing retained WAL deserves investigation. Check whether the downstream service still exists, whether it is simply offline, and whether it can catch up from an archive if its slot is invalidated.

If WAL archiving is enabled, inspect the archiver too:

SELECT archived_count,
       failed_count,
       last_archived_wal,
       last_archived_time,
       last_failed_wal,
       last_failed_time
FROM pg_stat_archiver;

A rising failed_count or repeated failures for one WAL file can mean the archive destination or archive_command is broken. A GitLab production incident involved slow WAL archiving, I/O, locks, and long transactions at the same time—another reminder that these failure modes can overlap.

A separate GitLab customer-cluster report describes how replication-slot retention contributed to cascading disk exhaustion during failover. PostgreSQL's own replication-slot documentation explicitly warns that slots can retain enough WAL to fill pg_wal.

The practical fix

• Restore the failed archiver or downstream consumer first when possible.
• Alert on free disk space, archiver failures, inactive slots, and replication lag—not only database process health.
• Use max_slot_wal_keep_size as a disk-safety boundary where appropriate. It can protect the primary, but crossing the boundary can invalidate the slot and require the replica or subscriber to be rebuilt. Choose between those failure modes consciously.
• Drop a slot only after proving that its consumer is permanently retired:

SELECT pg_drop_replication_slot('decommissioned_slot');

• Remember that max_wal_size is a soft checkpointing target, not a hard guarantee that pg_wal can never exceed a safe disk threshold.

Never delete files from pg_wal manually to make space. That can make the cluster unrecoverable. If the disk is already under pressure, free unrelated files or add suitable storage while you address the retention cause.

A monitoring checklist that catches these earlier

You do not need a huge observability project to start. Track these signals and keep the corresponding queries in your incident runbook:

1. Connections: total sessions by state, pool totals across every application instance, and oldest transaction age.
2. Locks: blocked sessions, pg_blocking_pids(), and lock-wait logs.
3. Vacuum health: dead-tuple trends, last autovacuum/analyze, long transactions, and age(datfrozenxid).
4. WAL and storage: free disk, archiver failures, inactive replication slots, and retained WAL per slot.
5. Recovery: backups with scheduled restore drills, because none of these monitoring signals replaces a recoverable backup. Our PostgreSQL backup and restore guide covers that side.

The useful habit is simple: preserve evidence before restarting anything. A restart may make the dashboard green while erasing the session, lock, or slot information needed to prevent the next outage. Diagnose the chain, apply the smallest safe fix, and then add an alert for the signal that would have warned you earlier.