Hosting Operations

Check PostgreSQL Lock Waits

Writes are stuck and you need to see whether sessions are waiting on locks.

Command

psql -X -c "select pid, wait_event_type, wait_event, state, left(query, 80) as query from pg_stat_activity where wait_event_type is not null order by pid;"

What changed

Nothing changes. PostgreSQL prints sessions with wait events.

Danger

safe

When to use it

Use when requests hang, migrations stall, or writes stop moving.

When not to use it

Do not use this alone to decide which backend to terminate.

Undo or recovery

No undo needed because this command is read-only.

Expected output

Rows showing waiting PIDs, wait event type, wait event, state, and query prefix.

demo script

Disposable terminal steps

psql -X -c "select pid, wait_event_type, wait_event, state, left(query, 80) as query from pg_stat_activity where wait_event_type is not null order by pid;"
psql -X -c "select wait_event_type, count(*) from pg_stat_activity where wait_event_type is not null group by wait_event_type;"

Terminal transcript Demo JSON

simulated output

What it looks like

disposable vessel

::fixture-ready::
$ psql -X -c "select pid, wait_event_type, wait_event, state, left(query, 80) as query from pg_stat_activity where wait_event_type is not null order by pid;"
 pid | wait_event_type |  wait_event  | state  |              query
-----+-----------------+--------------+--------+----------------------------------
 518 | Lock            | relation     | active | alter table orders add column
 522 | IO              | DataFileRead | active | select * from reports
(2 rows)
::exit-code::0
$ psql -X -c "select wait_event_type, count(*) from pg_stat_activity where wait_event_type is not null group by wait_event_type;"
 wait_event_type | count
-----------------+-------
 Lock            |     1
 IO              |     1
(2 rows)
::exit-code::0

YouTube Short

Check for lock waits.

A stuck database may be waiting behind one lock. Query wait events before you restart PostgreSQL.

experiments

A/B tests to run

Metric: short_completion_rate

A: The outage was a queue, not a crash.

B: Check Postgres wait events before restart.