PostgreSQL Troubleshooting

Find out whether the database is waiting, working, or blocked — then find on what.

Purpose

Use this when:

• FATAL: sorry, too many clients already, or connection timeouts from applications.
• Queries that were fast are now slow.
• Sessions are blocked, or a deployment's migration is hanging.
• Replication lag is growing.
• Autovacuum warnings, table bloat, or transaction ID wraparound messages.

Do not use this when:

• The problem is the host underneath — use linux-performance.
• The problem is network reachability to the database — use linux-network-debugging.
• The database is healthy and the application is slow — use production-debugging.

What this skill assumes: a psql connection with sufficient privileges to read pg_stat_activity in full (a superuser or pg_monitor role; other roles see truncated query text), and ideally pg_stat_statements installed. State the PostgreSQL version — behaviour around vacuum, locks and monitoring views has changed materially across recent versions.

Operating Procedure

1. Establish version and configuration. max_connections, shared_buffers, work_mem, and whether a pooler is in front.
2. Look at what is happening right now. pg_stat_activity is the single most informative view.
3. Classify. Is the database blocked (locks), busy (CPU/IO), waiting (client or replication), or out of a resource (connections, disk, transaction IDs)?
4. Find the specific query or session. Not "slow queries" — the query text and the PID.
5. Understand before acting. Killing a session aborts its transaction and rolls it back, which can take longer than letting it finish.
6. Fix at the right layer. Index, query, pooler configuration, or vacuum settings.
7. Verify with the metric that showed the problem.

Query plans from EXPLAIN are Observed. A plan from a different environment, or from before a statistics refresh, is Unverified — planner behaviour depends on the actual table statistics and data volume.

Investigation

Step 1 - what is happening now

SELECT pid, usename, application_name, client_addr, state,
       wait_event_type, wait_event,
       now() - xact_start  AS xact_age,
       now() - query_start AS query_age,
       left(query, 120) AS query
FROM pg_stat_activity
WHERE backend_type = 'client backend' AND pid <> pg_backend_pid()
ORDER BY xact_start NULLS LAST;

This one query answers most questions. Read state first:

idle in transaction with a large xact_age is one of the most damaging states in PostgreSQL. It holds whatever locks the transaction took, and it holds back the transaction horizon so vacuum cannot reclaim dead rows anywhere in the database. A single forgotten BEGIN in an application can bloat every table.

wait_event_type tells you what the database is waiting for: Lock (another transaction), IO (disk), Client (the application is not reading results), LWLock (internal contention).

Step 2 - connections

SELECT count(*), state FROM pg_stat_activity GROUP BY state;
SHOW max_connections;
SELECT count(*) FROM pg_stat_activity;

Connection exhaustion is nearly always an application problem, not a database sizing problem. Each PostgreSQL connection is a separate process with its own memory; raising max_connections to 1000 trades one failure mode for a worse one — memory exhaustion and context-switch overhead.

The correct fix is a connection pooler (PgBouncer in transaction mode) between the application and the database. Check the pooler's own saturation too:

-- from the pgbouncer admin console
SHOW POOLS;
SHOW CLIENTS;

cl_waiting above zero means clients are queuing for a server connection — the pool is the bottleneck, not the database.

A count of connections that grows steadily and never falls indicates a leak: the application is not returning connections to its pool.

Step 3 - locks and blocking

SELECT blocked.pid        AS blocked_pid,
       blocked.query      AS blocked_query,
       blocking.pid       AS blocking_pid,
       blocking.query     AS blocking_query,
       blocking.state     AS blocking_state,
       now() - blocking.xact_start AS blocking_xact_age
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;

pg_blocking_pids() gives the blocking chain directly. Follow it to the root: the session at the end of the chain is the one to deal with, and it is frequently idle in transaction rather than running anything.

The classic production version of this: a migration takes ACCESS EXCLUSIVE on a table, is itself blocked by a long-running read, and every subsequent query on that table queues behind the migration. One slow SELECT stops all writes. This is why migrations should always set a short lock_timeout:

SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN status text;

Without it, the migration waits indefinitely and takes the application down while doing so.

Step 4 - slow queries

SELECT calls, round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       rows, left(query, 120) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

Order by total_exec_time, not by mean_exec_time. A query taking 3ms called two million times costs more than one taking 8 seconds called twice — and it is the one worth fixing. Sorting by mean finds the dramatic query; sorting by total finds the expensive one.

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

What to look for in the plan:

Note that EXPLAIN ANALYZE executes the query. For INSERT, UPDATE or DELETE, wrap it in a transaction you roll back, or use plain EXPLAIN.

SELECT schemaname, relname, seq_scan, idx_scan, n_live_tup
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan AND n_live_tup > 100000
ORDER BY seq_scan DESC;

Large tables scanned sequentially far more often than by index are index candidates.

Step 5 - vacuum, bloat and wraparound

SELECT schemaname, relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC LIMIT 20;

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

Dead tuples above roughly 20% mean vacuum is not keeping up. Causes, in order: a long-running transaction holding the horizon open (see step 1), a replication slot that is not being consumed, an unusually high write rate, or autovacuum settings that are too conservative for the table.

age(datfrozenxid) approaching 2 billion is an emergency. At that point PostgreSQL stops accepting writes to protect against transaction ID wraparound. Warnings begin around 200 million — treat them as a real deadline, not as noise, and find what is preventing vacuum from freezing rows.

SELECT slot_name, active, restart_lsn,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots;

An inactive replication slot retains WAL indefinitely and holds the vacuum horizon. It is a common cause of both a full disk and unexplained bloat: a replica was decommissioned and its slot was never dropped.

Step 6 - replication

SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS replay_lag
FROM pg_stat_replication;

-- on the replica
SELECT now() - pg_last_xact_replay_timestamp() AS replication_delay;

Where the lag appears tells you the cause: a gap between sent and write is network, between write and flush is replica disk, and between flush and replay is the replica applying changes — usually a conflict with a long-running query on the replica, or a single-threaded replay falling behind a heavy write load.

Decision Tree

PostgreSQL problem
├── "too many clients" → connection exhaustion
│   ├── Many idle → application pool leak
│   ├── Many idle in transaction → application not committing
│   └── Genuinely busy → add a pooler, do not raise max_connections
├── Queries blocked → pg_blocking_pids to the root
│   ├── Root is idle in transaction → application bug
│   ├── Root is a migration → lock_timeout; run it differently
│   └── Root is a long report → move it to a replica
├── Slow queries
│   ├── Seq Scan on a large table → index
│   ├── Estimates far from actual → ANALYZE
│   ├── Sort on disk → work_mem
│   └── High buffer reads → cache or IO → linux-performance
├── Bloat / vacuum behind
│   ├── Long transaction holding the horizon
│   ├── Inactive replication slot
│   └── Autovacuum too conservative for this table
├── xid_age approaching 2 billion → emergency; find what blocks freezing
└── Replication lag → locate the gap: network, replica disk, or replay

Common Causes

Safety

Read-only

All the SELECT queries above against pg_stat_*, pg_locks, pg_replication_slots and pg_stat_statements, plus plain EXPLAIN. These read internal state.

Two exceptions worth being precise about:

• EXPLAIN ANALYZE executes the statement. On anything other than a SELECT it performs the write. Wrap it in BEGIN; ... ROLLBACK; or use plain EXPLAIN.
• Querying pg_stat_activity from a session that then sits idle makes you part of the problem. Do not hold a transaction open while investigating.

Potentially disruptive

• SELECT pg_cancel_backend(pid) — cancels the running query, leaving the connection and the transaction open. This is the gentler option and the correct first attempt.
• ANALYZE <table> — updates statistics, takes a light lock, safe during normal operation.
• CREATE INDEX CONCURRENTLY — does not block writes, which is why it is the only acceptable form on a live table. It is slower, cannot run inside a transaction, and can leave an INVALID index if it fails, which must then be dropped. Plain CREATE INDEX takes a lock that blocks all writes for the duration — on a large table, that is an outage.
• VACUUM ANALYZE <table> — safe to run manually; adds IO load. VACUUM FULL is a different thing entirely, listed below.
• Raising work_mem — it is allocated per sort or hash node, per query, not per connection. A generous global value multiplied by concurrent queries exhausts server memory. Set it per session for the query that needs it.
• Failing over to a replica — may lose unreplicated writes. Check lag first.

Destructive

• SELECT pg_terminate_backend(pid) — kills the session and rolls back its transaction. The rollback itself can take longer than letting the transaction finish, and during it the table is still locked. Required first: try pg_cancel_backend, capture the query text for the investigation, and confirm the session is not a migration mid-way through something that must complete.
• VACUUM FULL — takes an ACCESS EXCLUSIVE lock for its entire duration, rewriting the whole table. Nothing can read or write it. On a large table this is hours of total unavailability, and it needs free disk equal to the table size. Required first: a maintenance window, confirmed free space, and consideration of pg_repack, which achieves the same result without the exclusive lock.
• DROP INDEX / DROP TABLE / TRUNCATE TABLE — TRUNCATE removes every row with no practical recovery path, and is not undone by a simple restore of a subsequent backup. Required first: a verified backup, explicit confirmation of the object name, and a check that nothing references it. Dropping an index that a query depends on turns a fast query into a table scan immediately.
• Dropping a replication slot — the replica that used it can no longer catch up and must be rebuilt from a base backup. Required first: confirm the slot is genuinely orphaned, not just temporarily disconnected.
• Promoting a replica — creates a second primary. Without fencing the old one, both accept writes and the data diverges permanently. Required first: confirm the old primary is fenced.
• DROP DATABASE — irreversible. Not part of troubleshooting.

Verification

Before: 198/200 connections used; 40 sessions idle in transaction; p99 query 8.2s
After:  62/200 connections; 0 idle in transaction over 30 min; p99 query 40ms

SELECT count(*) FILTER (WHERE state = 'active')               AS active,
       count(*) FILTER (WHERE state = 'idle in transaction')  AS idle_in_txn,
       count(*)                                               AS total
FROM pg_stat_activity WHERE backend_type = 'client backend';

SELECT count(*) AS blocked FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

SELECT calls, round(mean_exec_time::numeric,2) AS mean_ms, left(query,80)
FROM pg_stat_statements WHERE query LIKE '%<fragment>%';

Reset pg_stat_statements with SELECT pg_stat_statements_reset(); before verifying a query fix, or the historical average will mask the improvement for hours.

Verify over a full traffic cycle. An index added at low traffic that has not met the daily peak is unverified — and confirm the index is actually being used with EXPLAIN, because the planner may decline it.

Anti-Patterns

Bad: Raise max_connections when connections run out. Better: Each connection is a process with its own memory. 1000 connections trades a clear error for memory exhaustion and scheduler thrash. Use a pooler.

Bad: pg_terminate_backend on the blocking session immediately. Better: Try pg_cancel_backend first, and capture the query text — it is usually the evidence you need. Termination rolls back, which can take longer than waiting.

Bad: CREATE INDEX on a live table. Better: It blocks all writes for the duration. Always CONCURRENTLY in production.

Bad: VACUUM FULL to fix bloat. Better: It locks the table exclusively for hours. Fix the cause — a long transaction or an orphaned slot — and use pg_repack if a rewrite is genuinely needed.

Bad: Run a migration without lock_timeout. Better: The migration queues behind any long read and then blocks every subsequent query on the table. A short lock_timeout fails fast and retries instead of taking the application down.

Bad: Set work_mem globally to something generous. Better: It is per sort node, per query. Concurrency multiplies it into an out-of-memory condition. Set it for the session that needs it.

Bad: Ignore wraparound warnings. Better: At 2 billion transactions the database stops accepting writes. The warning is a deadline, and the fix — finding what blocks freezing — takes time.

Composition

• production-debugging — the database is one layer of a wider investigation.
• linux-performance — disk latency and memory pressure beneath PostgreSQL.
• observability-debugging — correlating database metrics with application symptoms.
• kubernetes-troubleshooting — when PostgreSQL runs in a StatefulSet.

References

• PostgreSQL: pg_stat_activity
• PostgreSQL: Routine Vacuuming and wraparound
• PostgreSQL: Explicit Locking — which statements take which lock mode.