Why VACUUM Exists: Dead Tuple Reclamation and Visibility Maintenance

Learning outcomes

ServiceHub has an update-heavy work-order feed. The team notices that the table file is larger than the number of currently visible rows would suggest, and someone proposes a nightly VACUUM FULL. This lesson starts from the actual PostgreSQL mechanism: Multi-Version Concurrency Control (MVCC) leaves obsolete tuple versions behind until they are no longer needed; ordinary VACUUM turns eligible dead space into reusable space, maintains visibility metadata, and protects transaction-ID health. That is a different job from rewriting a table to make its operating-system file smaller.

1. Create a disposable high-churn ServiceHub table

The lab deliberately uses a separate app.ch09_vacuum_lab table so maintenance experiments cannot damage earlier course objects. Run the setup as a role that owns the table or has the required maintenance privileges.

DROP TABLE IF EXISTS app.ch09_vacuum_lab;CREATE TABLE app.ch09_vacuum_lab (    work_order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,    customer_id integer NOT NULL,    status text NOT NULL CHECK (status IN ('queued','assigned','done','cancelled')),    payload text NOT NULL,    updated_at timestamptz NOT NULL DEFAULT clock_timestamp());CREATE INDEX ch09_vacuum_status_id_idx    ON app.ch09_vacuum_lab(status, work_order_id);INSERT INTO app.ch09_vacuum_lab(customer_id, status, payload)SELECT (g % 500) + 1,       CASE WHEN g % 5 = 0 THEN 'done' ELSE 'queued' END,       repeat(md5(g::text), 4)FROM generate_series(1, 20000) AS g;ANALYZE app.ch09_vacuum_lab;

ANALYZE samples the table and refreshes planner statistics; it does not remove dead tuples. Record both estimated row-state counters and physical sizes before the churn.

SELECT relname, n_live_tup, n_dead_tup,       last_vacuum, vacuum_count,       last_autovacuum, autovacuum_count,       last_analyze, analyze_countFROM pg_stat_user_tablesWHERE relid = 'app.ch09_vacuum_lab'::regclass;SELECT pg_size_pretty(pg_relation_size('app.ch09_vacuum_lab')) AS heap,       pg_size_pretty(pg_indexes_size('app.ch09_vacuum_lab')) AS indexes,       pg_size_pretty(pg_total_relation_size('app.ch09_vacuum_lab')) AS total;

n_live_tup and n_dead_tup are estimates maintained by the statistics system, not a byte-accurate heap inventory. Size functions answer a different question: how much relation storage is currently allocated.

2. Manufacture obsolete versions safely

Updates create new tuple versions; deletes make the old version logically dead after the deleting transaction commits and once no older snapshot still needs it. We intentionally perform both.

UPDATE app.ch09_vacuum_labSET status = 'assigned',    updated_at = clock_timestamp()WHERE work_order_id % 3 = 0;DELETE FROM app.ch09_vacuum_labWHERE work_order_id % 4 = 0;SELECT pg_stat_clear_snapshot();SELECT n_live_tup, n_dead_tup, n_tup_upd, n_tup_delFROM pg_stat_user_tablesWHERE relid = 'app.ch09_vacuum_lab'::regclass;

Exact counter values can lag because statistics are accumulated asynchronously and may be cached within your session. The invariant is the direction of change: SQL sees fewer current rows, while obsolete versions can remain physically present until cleanup is possible.

SELECT count(*) AS visible_rowsFROM app.ch09_vacuum_lab;SELECT pg_size_pretty(pg_relation_size('app.ch09_vacuum_lab')) AS heap_after_churn;

3. Run ordinary VACUUM and interpret the evidence

VACUUM (ANALYZE) combines tuple cleanup/visibility work with planner-statistics refresh. It is online relative to ordinary reads and writes, although it still takes lightweight relation locks and consumes I/O/CPU.

VACUUM (ANALYZE, VERBOSE) app.ch09_vacuum_lab;SELECT pg_stat_clear_snapshot();SELECT n_live_tup, n_dead_tup,       last_vacuum, vacuum_count,       last_analyze, analyze_countFROM pg_stat_user_tablesWHERE relid = 'app.ch09_vacuum_lab'::regclass;SELECT pg_size_pretty(pg_relation_size('app.ch09_vacuum_lab')) AS heap_after_vacuum;

After a successful vacuum, the dead-tuple estimate commonly falls and the manual-vacuum counters/timestamps advance. The heap file may stay near the same size because pages are now reusable inside the relation. PostgreSQL can sometimes truncate completely empty pages from the physical tail, so “VACUUM never shrinks a file” is also too absolute.

4. Visibility map and index-only scans

The visibility map tracks heap pages that are all-visible (and separately all-frozen). An index-only scan can return indexed columns without visiting the heap only when PostgreSQL can establish that the referenced heap page is all-visible. VACUUM is an important producer/maintainer of those bits.

CREATE EXTENSION IF NOT EXISTS pg_visibility;SELECT *FROM pg_visibility_map_summary('app.ch09_vacuum_lab'::regclass);

pg_visibility is a supplied extension; installing it may require elevated privileges. The mandatory mental model does not depend on it, but the summary makes otherwise-hidden visibility metadata observable.

BEGIN;SET LOCAL enable_seqscan = off;EXPLAIN (ANALYZE, BUFFERS)SELECT work_order_id, statusFROM app.ch09_vacuum_labWHERE status = 'done'ORDER BY work_order_idLIMIT 100;ROLLBACK;

The lab-only planner toggle helps expose the index-only mechanism; it is not a production tuning recommendation. If the plan contains Index Only Scan, inspect Heap Fetches. Fewer heap fetches after vacuum can be consistent with more all-visible pages, but a single plan is not a universal performance guarantee.

5. When VACUUM cannot reclaim a version yet

VACUUM is constrained by visibility horizons. A long-running transaction can keep an old snapshot alive, so tuple versions that are dead to newer transactions may still be potentially visible to that older snapshot. This is why “autovacuum ran” and “all obsolete versions became reusable” are not synonymous.

SELECT pid, usename, state, xact_start, backend_xmin, wait_event_type, wait_event, queryFROM pg_stat_activityWHERE datname = current_database()ORDER BY xact_start NULLS LAST;SELECT pid, relid::regclass, phase,       heap_blks_total, heap_blks_scanned, heap_blks_vacuumedFROM pg_stat_progress_vacuum;

backend_xmin is evidence that a backend contributes to a snapshot horizon, while xact_start helps identify transactions that have remained open. Do not terminate sessions merely because they are old: first identify application ownership, correctness impact, and whether that backend actually explains the maintenance blockage.

6. Wrong approach: “VACUUM FULL every night”

VACUUM FULL rewrites a table into a new compact file and requires an ACCESS EXCLUSIVE lock. That can be appropriate for exceptional space-reclamation cases, but it is not a substitute for healthy autovacuum.

SELECT n_live_tup, n_dead_tup,       last_autovacuum, autovacuum_countFROM pg_stat_user_tablesWHERE relid = 'app.ch09_vacuum_lab'::regclass;SELECT pg_size_pretty(pg_relation_size('app.ch09_vacuum_lab')),       pg_size_pretty(pg_indexes_size('app.ch09_vacuum_lab'));

The repaired workflow asks: is there reusable space, is the table actually growing despite reuse, is autovacuum keeping up, are long transactions blocking cleanup, and is returning disk to the OS genuinely required? Chapter 09 Lesson 5 will compare rewrite choices after those questions are answered.

7. Production judgment and cleanup

Routine vacuum health should be monitored using a combination of dead/live estimates, last vacuum/autovacuum times, table growth, long-running transactions, visibility/index-only behavior when relevant, and autovacuum logs/progress during incidents. No single counter is a bloat percentage. Do not schedule VACUUM FULL merely because a heap file does not shrink after deletes.

DROP TABLE IF EXISTS app.ch09_vacuum_lab;

Authoritative references

These mechanisms are version-sensitive. Use the documentation for the PostgreSQL major you actually operate.

• PostgreSQL 18 — Routine Vacuuming
• PostgreSQL 18 — VACUUM
• PostgreSQL 18 — pg_stat_all_tables / monitoring statistics
• PostgreSQL 18 — pg_visibility