The 5 Queries I Run First When Postgres Is Slow

#database #postgres #sql #devops

When an API slows down and the database is the suspect, the hardest part isn't the fix. It's knowing where to look while everyone is watching. Here are five read-only queries that find the most common causes of "Postgres is slow" in production, in the order I run them. All of them are safe on a busy primary. Run them as a role with pg_monitor so you can see other sessions' queries.

Tip: start each session with SET statement_timeout = '5s'; SET lock_timeout = '1s'; so even a diagnostic query can't pile onto the problem.

1. Is anything blocked, and by whom?

One session holding a lock can stall hundreds of requests. Find every waiting query and the session it's waiting for:

SELECT w.pid AS waiting_pid,
       now() - w.query_start AS waiting_for,
       left(w.query, 80) AS waiting_query,
       b.pid AS blocker_pid,
       b.state AS blocker_state,
       now() - b.xact_start AS blocker_xact_age,
       left(b.query, 80) AS blocker_query
FROM pg_stat_activity w
CROSS JOIN LATERAL unnest(pg_blocking_pids(w.pid)) AS bp(pid)
JOIN pg_stat_activity b ON b.pid = bp.pid
WHERE w.wait_event_type = 'Lock';

Look at blocker_state. If it says idle in transaction, an application (or a forgotten psql session) opened a transaction and walked away while holding locks. A trap worth knowing: a migration waiting for an ACCESS EXCLUSIVE lock also blocks every later SELECT on that table, so a quick ALTER TABLE stuck behind one slow query can freeze a whole table.

2. Who is sitting in an open transaction?

SELECT pid, usename, application_name,
       now() - xact_start AS xact_age,
       now() - state_change AS idle_for,
       left(query, 80) AS last_query
FROM pg_stat_activity
WHERE state LIKE 'idle in transaction%'
  AND now() - state_change > interval '1 minute'
ORDER BY state_change;

These sessions hold locks and stop vacuum from cleaning up. The usual cause is application code that makes an HTTP call or does slow work inside a transaction. The guardrail is ALTER ROLE app SET idle_in_transaction_session_timeout = '60s';.

3. How close are we to max_connections?

SELECT count(*) AS used,
       current_setting('max_connections')::int AS max,
       count(*) FILTER (WHERE state = 'active') AS active,
       count(*) FILTER (WHERE state = 'idle') AS idle,
       count(*) FILTER (WHERE state LIKE 'idle in transaction%') AS idle_in_tx
FROM pg_stat_activity
WHERE backend_type = 'client backend';

Mostly idle means app pools are oversized (pool size × processes × instances). Mostly active means slow queries are holding connections. Raising max_connections is rarely the fix.

4. Which foreign keys have no index?

Deleting a parent row makes Postgres look up child rows by the FK columns. Without an index, that's a full scan of the child table, inside your transaction, holding locks.

SELECT c.conrelid::regclass AS table_name, c.conname,
       (SELECT string_agg(a.attname, ', ')
          FROM unnest(c.conkey) k(n)
          JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = k.n) AS columns
FROM pg_constraint c
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid
      AND (string_to_array(i.indkey::text, ' ')::int2[])[1:array_length(c.conkey, 1)] @> c.conkey
      AND (string_to_array(i.indkey::text, ' ')::int2[])[1:array_length(c.conkey, 1)] <@ c.conkey);

Fix it with CREATE INDEX CONCURRENTLY, outside a transaction. In Django that's AddIndexConcurrently in a migration with atomic = False; in Rails, algorithm: :concurrently with disable_ddl_transaction!.

5. Is any ID about to run out?

Not a slowdown, but the outage nobody sees coming: an integer primary key stops at 2,147,483,647.

SELECT s.schemaname || '.' || s.sequencename AS seq, s.last_value,
       round(100.0 * s.last_value / 2147483647, 1) AS pct_of_int_max
FROM pg_sequences s
WHERE s.last_value > 0.5 * 2147483647
ORDER BY s.last_value DESC;

(This simple version assumes integer columns. The full check in the kit looks up each sequence's actual column type.)

What I don't run first

VACUUM FULL (an exclusive lock for the whole rewrite), restarting the server (it wipes the evidence and the cache), or raising settings at random. Diagnose first.

Turning this into a tool

I packaged these five checks as a free, MIT-licensed CLI that runs them read-only and writes a ranked report: github.com/azadhmhd/postgres-triage-kit. The full Postgres Triage Kit adds 15 more checks (top queries, vacuum lag, wraparound, unused and duplicate indexes, replication slots and more) and 9 incident runbooks: Postgres Triage Kit on Gumroad.

All queries tested on PostgreSQL 13–18. Originally published at azadhmhd.com.

DEV Community