Skip to content
AITroveRead. Build. Understand.
Reading list
Make this comfortable

Vacuum horizon: find transactions holding old row versions alive

Last updated: 5 Oct 20266 min read
tutorial
AdvancedBy AITrove Editorial

PostgreSQL keeps old row versions while a transaction may still need to see them. A long transaction, an idle session left inside a transaction, or a replication slot can prevent cleanup from advancing. Autovacuum may run yet fail to reclaim the rows the workload expects. A forced table rewrite can use substantial disk and strong locks; it is not the first response to a growing table.

Operational decision

A payment ledger receives frequent status updates and its table size rises. Inspect pg_stat_activity for old transactions and idle-in-transaction sessions, pg_stat_all_tables for dead tuples and vacuum activity, and replication-slot positions for retained history. The SQL reads only session and table statistics; exact visibility depends on database privileges. Correlate growth with row churn and query latency. Find the owner of an old session before terminating it, since it may contain an unfinished business operation. Fix the application transaction scope or timeout in a test environment, then watch the vacuum horizon advance and dead tuple estimates fall under normal autovacuum. If disk pressure is already acute, use a documented capacity and maintenance plan; do not run VACUUM FULL on the hot ledger merely because it promises a smaller file. Track transaction age and freeze risk as well as bytes, because write availability can be threatened long before an operator notices a table-size chart.

sql
SELECT pid, state, now() - xact_start AS transaction_age, wait_event_type
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start LIMIT 20;
SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_all_tables
WHERE relname = 'ledger_entries';

Cost and verification

Longer snapshots can improve an analytical report but make row cleanup and storage more expensive. More frequent vacuum work uses I/O, while too little maintenance allows bloat and freeze pressure to build. A single dead-tuple estimate is approximate; compare trend, transaction blockers, and query behavior. Resolving a blocker does not instantly return disk space to the operating system, so size headroom and verify the steady-state growth rate.

Common Mistakes

  • Do not run VACUUM FULL as the first response to a hot-table size increase.
  • Do not terminate an old transaction without identifying its owner and effect.
  • Do not assume an autovacuum start time means cleanup is advancing.

Connected lessons

Practice and check

devops
operations
Storage details