Skip to content
DevOps AI ToolKit

PostgreSQL Production Troubleshooting

The database failures that take an app down: connection exhaustion, locks, sequence/constraint errors, and recovery.

0 of 3 modules complete

0%

Who it’s for
Engineers who own or operate a Postgres database in production.
Prerequisites
Basic SQL and psql familiarity.

Skills you’ll build

  • ✓Diagnose connection/pool exhaustion
  • ✓Resolve lock and constraint errors
  • ✓Recover from common failure modes
3 modules · self-paced

  1. Module 1

    Work the common Postgres errors

    Systematically resolve the highest-frequency Postgres errors.

    PostgreSQL database errors hub Browse the Postgres stack

    Exercise

    Inserts fail with "duplicate key value violates unique constraint" after a data import. Determine whether the sequence is behind the table’s max id, and how to reset it safely.

    Open in Workspace →

  2. Module 2

    Connections and locks

    Diagnose "too many connections" and lock contention under load.

    PostgreSQL database errors hub

    Diagnostic commands run in order — each one narrows the fault

    1. Check connection usage against the limit 
      psql -c "SELECT count(*), (SELECT setting::int FROM pg_settings WHERE name='max_connections') AS max FROM pg_stat_activity;"

      Close to max means new connections will be refused — usually a pooler problem, not a database one.

    2. Find blocking locks 
      psql -c "SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid, blocked.query AS blocked_query 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;"

      Gives you the blocking PID directly. Investigate the blocker before killing anything.

    3. Find long-running and idle-in-transaction sessions 
      psql -c "SELECT pid, state, now()-xact_start AS xact_age, left(query,60) FROM pg_stat_activity WHERE state <> 'idle' ORDER BY xact_age DESC NULLS LAST LIMIT 20;"

      "idle in transaction" holds locks and blocks vacuum indefinitely — it is the single most common Postgres production problem.

  3. Module 3

    Run a database incident

    Triage a Postgres outage in the workspace and export the summary.

    Open the Troubleshooting Workspace

    Exercise

    The app is throwing database errors under load. Triage connections, locks, and slow queries; record findings and root cause.

    Open in Workspace →