Unblock vacuum held back by a long transaction
VACUUM cannot remove dead rows newer than the oldest running transaction's snapshot. One idle-in-transaction session can pin cleanup cluster-wide, here 200,000 dead rows stay 'not yet removable' until it ends.
Problem
What you're actually looking at
The symptom as it shows up on a real server.
VACUUM only removes row versions that are invisible to every running transaction. A single long-lived transaction, even one sitting idle in a transaction block, holds back the removable cutoff (xmin), so dead tuples pile up until it commits or rolls back.
Meridian's courier_positions has 200,000 dead rows, but a nightly report job left a session idle in transaction. VACUUM runs but reports the dead rows as 'not yet removable'.
Simple terms
VACUUM can only remove a dead row once no running transaction could still need to see it. A single old transaction that keeps a snapshot open, for example one that ran under REPEATABLE READ and is now sitting "idle in transaction", holds that cutoff back for the whole database. So dead rows pile up and VACUUM reports them as "not yet removable" until that session finally commits or rolls back. (A plain READ COMMITTED session that is merely idle does not pin anything, because it drops its snapshot the moment each statement ends.) Finding and ending the snapshot-holding session lets cleanup catch up.
Full runbook for this incident
- The full identify checklist, the exact signals that tell you it's this incident
- Every diagnostic query; lab output is attached only to the steps we actually captured
- The resolution path and the pitfalls that make it worse
- Mitigation steps to stop it recurring, plus a verify-you're-done query
Card required. Cancel before day 7 and you are not charged.
More in this category
Other Vacuum & bloat runbooks
Neighbouring incidents that share the same diagnostic surface.
Connected
How this connects to the rest of the library
A live view of this page's real cross-references, what explains it, what fixes it, what to tune, and where to go next. Every link is an authored relationship, not a guess.
Need the full procedure?
Pro runbooks finish the incident path
Free runbooks teach the shape. Pro opens the full step transcript, edge cases, and prevention depth.