Runbook: Postgres Runaway Query

Last updated 2 min read
Report issue

Runbook: Postgres Runaway Query#

When you see: control-plane API latency >2s, panel pages timing out, or pg_stat_activity showing a query running for >5 minutes.

Severity: SEV-2 if it's blocking control-plane writes; SEV-3 if read-only.

Symptoms#

  • API endpoints returning 504 Gateway Timeout
  • Panel pages slow to render
  • htop on the database host shows a postgres process at 100% CPU
  • pg_stat_activity shows a query in state active for >5 minutes

Diagnose#

BASH
# On the database host:
sudo -u postgres psql -d <admin_or_docs_db> -c "
  SELECT pid, now()-query_start AS runtime, state, wait_event, query
  FROM pg_stat_activity
  WHERE state='active' AND now()-query_start > interval '30 seconds'
  ORDER BY runtime DESC LIMIT 10;
"

Look at the query column. Common offenders:

  • A SELECT … WHERE … IN (long list) — usually from an unoptimized panel-side filter
  • An UPDATE without an index on the WHERE clause
  • A migration in progress (alembic upgrade)
  • A VACUUM (FULL) someone kicked off without thinking

Mitigate#

If the query is non-essential (you can lose it):

BASH
sudo -u postgres psql -d <db> -c "SELECT pg_cancel_backend(<pid>);"
# if cancel doesn't work after 30s:
sudo -u postgres psql -d <db> -c "SELECT pg_terminate_backend(<pid>);"

If the query is a migration: don't kill it unless you're absolutely sure. Killing a migration mid-write can leave the schema inconsistent. Wait it out.

Fix (durable)#

  1. Add an index for the missing WHERE-clause column. Use CREATE INDEX CONCURRENTLY to avoid lock.
  2. If the query came from panel UI, file a GitHub issue with the slow query + the panel page that triggered it. We'll add a query-plan budget to that endpoint.
  3. Consider connection-level statement_timeout — set a sensible default (say 30s for read APIs):
SQL
ALTER ROLE app_user SET statement_timeout = '30s';

This makes any query that exceeds 30s fail loudly instead of consuming resources silently.

Prevention#

  • Enable log_min_duration_statement = 1000 in postgresql.conf — every query >1s gets logged
  • Set up pg_stat_statements extension and add a Grafana panel for top-N slow queries