pg-checkup
Workspace
HomeReports
GitHub
Run on GitHub
Learn
Start hereToo many connectionsSlow queriesLocks and blocked queriesVacuum and bloatTransaction ID wraparoundReplication lag and slotsDisk full and WAL growthOut of memoryHigh CPU and I/OStale connectionsWhen to scale
Help
File a ticketRequest a featurePricing
PrivacyTerms
Docs/Locks and blocked queries
Sign inNew checkupC

Locks and blocked queries

Symptom: queries hang, a migration never finishes, or the app times out while CPU is idle.

Confirm

select pid, pg_blocking_pids(pid) as blocked_by, wait_event_type, wait_event,
       now() - query_start as waiting, left(query, 80) as query
from pg_stat_activity where cardinality(pg_blocking_pids(pid)) > 0;

Follow blocked_by to the root. The root is very often a session that is idle in transaction:

select pid, usename, now() - xact_start as open_for, state, left(query, 80)
from pg_stat_activity where state like 'idle in transaction%' order by xact_start;

Fix now

select pg_cancel_backend(<pid>);     -- cancels the running query, keeps the session
select pg_terminate_backend(<pid>);  -- ends the session and rolls back its transaction

Why one slow query stalls everything

An ALTER TABLE that waits behind a long-running read makes every later query on that table wait behind the ALTER. Always give schema changes a short leash:

set lock_timeout = '5s';

Safer migrations

  • create index concurrently, never a plain create index, on a live table.
  • Add constraints as not valid, then validate constraint in a second step (it takes a light lock).
  • Adding a column with a constant default is instant on PostgreSQL 11+.

Deadlocks

Postgres aborts one side after deadlock_timeout (1 s). Fix the cause: touch rows and tables in the same order everywhere, and keep transactions short. Set log_lock_waits = on to see who waited on whom. For job queues use select ... for update skip locked.

Prevent

alter role app_user set idle_in_transaction_session_timeout = '60s';

PreviousSlow queriesNextVacuum and bloat