VACUUM FULL takes an exclusive lock

VACUUM FULL takes an exclusive lock
Type: incident simulation · Topic: Locks & Transactions · Level: L3 · Duration: 10–15 min
Launch: ride postgres start stage-02/10-vacuum-full-lock

POSTMORTEM (root cause · how it was found · the fix · lesson)
Root cause: a maintenance job took an ACCESS EXCLUSIVE lock on a hot table (the
lock a VACUUM FULL rewrite holds for its whole duration). That lock conflicts
with everything — even plain SELECTs — so all access to the table blocked until
it finished.

How it was found: pg_stat_activity showed a maintenance session holding the
table; pg_locks showed mode = 'AccessExclusiveLock' on orders, with ordinary
queries queued behind it.

The fix: SELECT pg_terminate_backend(<maintenance_pid>);
Ending the maintenance session releases the exclusive lock and the app queries
resume.

Lesson: VACUUM FULL rewrites the whole table under an ACCESS EXCLUSIVE lock — it
is not a routine operation. Use ordinary VACUUM (autovacuum, or tuned settings)
to reclaim bloat online, pg_repack for an online rewrite, or schedule VACUUM
FULL in a maintenance window. Don't run it on a hot table during business hours,
and don't kill the innocent app queries that are merely waiting.

INVESTIGATION HINTS (the staged path to diagnose and fix)
1. All access to orders is blocked, including reads. That points at an ACCESS EXCLUSIVE lock — a heavy maintenance operation, not a normal query.
2. Find it: pg_stat_activity shows a maintenance session holding the table; pg_locks shows mode = 'AccessExclusiveLock' on orders. A plain VACUUM doesn't do this — VACUUM FULL (a full table rewrite) does.
3. Terminate the maintenance backend to release the lock. The safe alternatives to VACUUM FULL are ordinary VACUUM / autovacuum tuning / pg_repack in a maintenance window — never on a hot table mid-day.