--- title: "PostgreSQL Internals - Module 7: VACUUM, Autovacuum & Bloat" description: "Inside PostgreSQL VACUUM: dead tuple collection, OldestXmin watermark, autovacuum triggers, XID freeze mechanics, bloat diagnosis, and pg_repack vs VACUUM FULL." author: "Samba Siva" published: 2026-05-12T15:49:29.821Z updated: 2026-11-05T07:57:20.739Z tags: - "PostgreSQL" - "Technical" - "PGInternals" url: "https://sambasivareddy.in/blog/postgresql-internals-module-7-vacuum-autovacuum-bloat" --- # PostgreSQL Internals - Module 7: VACUUM, Autovacuum & Bloat In the previous blog, we studied about the Index in the PostgreSQL, how they will be picked to generate a query plan, different types of indexes available in PostgreSQL. And we ended the blog with possible chance of `bloat` in indexes when the frequent deletes happen. In this blog, we'll explore how VACUUM cleans dead tuples and removes bloat, how it prevents XID wraparound, and how autovacuum - PostgreSQL's background maintenance engine decides when and how aggressively to act. * * * ## Part 1: Why VACUUM Exists? Every write operation in PostgreSQL creates a debt that must eventually be paid. For example: ```markdown INSERT → writes 1 new tuple → debt: none (yet) UPDATE → writes 1 new tuple version → marks old tuple dead (xmax set) → writes 1 new index entry per index (assume N indexes) → marks old index entries dead → debt: 1 dead heap tuple + N dead index entries DELETE → marks tuple dead (xmax set) → marks index entries dead → debt: 1 dead heap tuple + N dead index entries ``` This debt accumulates on every page. And Dead tuples cause: - Waste space: pages fill with unreachable data - Slow scans: every scan reads and discards dead tuples - Block index-only scans - `all_visible` bit cannot be set while dead tuples exists - Risk wraparound - old `xmin` values must be frozen before XID space/range exhausts And VACUUM is the debt collector, whose responsibility to clear all the debt being created, which will discuss in the next part in clear. * * * ## Part 2: What VACUUM Actually Does? VACUUM on a table is **not a single operation**. It is a sequence of precise steps ### Step 1: Acquire ShareUpdateExclusiveLock ```sql VACUUM orders; ``` VACUUM acquires `ShareUpdateExclusiveLock` - the weakest table lock. This: - Allows concurrent `SELECT` , `INSERT` , `UPDATE` and `DELETE` - Block concurrent `VACUUM` - only one VACUUM/table at a time - Blocks DDL statements on the table like `ALTER TABLE` and `DROP TABLE` This is why standard VACUUM does not block normal operations. `VACUUM FULL` is different altogether, it acquires `AccessExclusiveLock` and blocks everything. ### Step 2: Compute OldestXmin Before touching any page, VACUUM computes `OldestXmin` - the oldest transaction ID that any active transaction could need to see: ``` OldestXmin = min( all active transaction XIDs, all open cursor XIDs, all prepared transaction XIDs, all replication slot restart_lsn XIDs ) ``` Any tuple with `xmax < OldestXmin` is dead to everyone i.e., no active transaction can ever see it again. These are safe to remove. ### Step 3: Scan the heap - page by page VACUUM reads every heap page sequentially. For each page, it performs the following checks: ``` 1. Read page into shared buffers 2. Check visibility map: if all_visible bit SET → SKIP this page entirely i.e., no dead tuples possible, no work to do here if all_frozen bit SET → SKIP for freeze pass too 3. For each tuple on page: a. Check xmin/xmax against OldestXmin b. If dead (xmax < OldestXmin AND xmax is COMMITTED) add to dead_tuples[] array (stored in maintenance_work_mem) c. If live but xmin old enough for freezing mark for freeze (set HEAP_XMIN_FROZEN bit) WAL - log the change 4. If any tuples frozen: WAL - log a FREEZE record ``` The `dead_tuples[]` array in `maintenance_work_mem` accumulates the `TIDs` of dead tuples. When it fills up, VACUUM must flush it by cleaning indexes before it can continue scanning the heap; this is why `maintenance_work_mem` matters for VACUUM performance. ### Step 4: Clean Indexes For each index on the table, VACUUM calls `ambulkdelete()` : ```markdown For B-Tree: Scan index leaf page For each index entry whose TID is in dead_tuples[]: mark the index entry as dead (LP_DEAD flag) Return count This is O(indexSize), not O(deadTuples) complexity Why? VACUUM must scan the entire index to find dead entries ``` This is why a table with many indexes is expensive to VACUUM. Each index gets a full scan. ### Step 5: Heap Cleanup After index cleanup, VACUUM makes a **second pass** (first is to mark dead tuples) over the heap pages that had dead tuples: ``` For each page with dead tuples: 1. Remove dead tuple data 2. Update ItemId flags: LP_DEAD → LP_UNUSED 3. Compact the page (shift live tuples toward the end) 4. Update pd_lower (free space pointer moves back) 5. Update FSM: report new free space to free space map 6. If ALL tuples on page are visible to everyone: set all_visible bit in visibility map 7. If ALL tuples on page are frozen: set all_frozen bit in visibility map 8. WAL-log all changes ``` ### Step 6: Update Statistics Once all the above steps done, it's time for the VACUUM to update the stats in `pg_class` , `pg_stat_user_tables` and Truncate trailing empty pages if possible. ```markdown 1. Update pg_class: relpages ← new page count estimate reltuples ← new live tuple count estimate relallvisible ← pages with all_visible bit set 2. Update pg_stat_user_tables: n_dead_tup ← reset to 0 last_vacuum ← now() ``` * * * ## Part 3: Freezing - Preventing XID Wraparound XID is `32`bit. After `~4` billion transactions, it wraps around i.e., transaction IDs restart from 3 (XIDs 0–2 are reserved for system use). Without intervention, old tuples with small `xmin` values would appear to be **_"in the future"_** - invisible to everyone and causes database corruption. ### The freeze threshold Following parameters control when tuples are frozen: - `vacuum_freeze_min_age` - A tuple is eligible for freezing when its `xmin` is this many transactions old. VACUUM won't freeze newer tuples. - Default: `50M` XIDs - `vacuum_freeze_table_age` - When the table's `relfrozenxid` is this old, VACUUM performs an aggressive freeze scan - visits every page regardless of the visibility map. - Default: `150M` XIDs - `autovacuum_freeze_max_age` - When a table's relfrozenxid reaches this age, autovacuum is forced to run on it - even if it's otherwise inactive. This is the safety net. If it triggers, you have a problem. - Default: `200M` XIDs ### What freezing does? ```sql -- Before freeze; -- t_xmin = 9582, t_infomask = 0x0100 (HEAP_XMIN_COMITTED) VACUUM FREEZE orders; -- After freeze -- t_xmin = 9582 (UNCHANGED - PG 14+ preserves original XID) -- t_infomask = 0x0300 (HEAP_XMIN_COMMITTED | HEAP_XMIN_FROZEN) -- added frozen bit (0x0200) ``` Once frozen bit is set, `XID` value becomes irrelevant and never be checked again for freezing or vacuuming. ### Monitor wraparound proximity ```sql -- Most critical query for PostgreSQL health: SELECT datname, age(datfrozenxid) AS xid_age, 2000000000 - age(datfrozenxid) AS xids_remaining, round(100.0 * age(datfrozenxid) / 2000000000, 2) AS pct_toward_wraparound FROM pg_database ORDER BY age(datfrozenxid) DESC; -- Per-table view: SELECT schemaname, relname, age(relfrozenxid) AS table_xid_age, pg_size_pretty(pg_total_relation_size(relid)) AS size FROM pg_stat_user_tables ORDER BY age(relfrozenxid) DESC LIMIT 20; -- ⚠️ WARNING thresholds: -- age > 1,500,000,000 → URGENT: manual VACUUM FREEZE immediately -- age > 1,800,000,000 → CRITICAL: PostgreSQL will shut down soon -- age > 2,100,000,000 → PostgreSQL refuses new transactions ``` * * * ## Part 4: Autovacuum - The Background Maintenance Engine Manual `VACUUM` is not practical at scale. Autovacuum runs in the background automatically. Understanding its decision algorithm is essential for tuning it. ### Autovacuum Architecture ```markdown postmaster └── autovacuum launcher (1 process, always running) └── autovacuum worker (up to autovacuum_max_workers, default 3) └── one worker per database, one table at a time ``` The launcher wakes up every `autovacuum_naptime` seconds (default: 1 minute), checks `pg_stat_user_tables` for tables needing work, and forks workers. ### The Vacuum trigger formula A table is eligible for autovacuum when: ```markdown n_dead_tup > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × reltuples Default: n_dead_tup > 50 + 0.02 × reltuples = dead tuples exceed 50 + 2% of live tuples Example: Table with 10,000,000 rows: threshold = 50 + 0.02 × 10,000,000 = 200,050 dead tuples → autovacuum triggers after 200K dead tuples accumulate Table with 1,000 rows: threshold = 50 + 0.02 × 1,000 = 70 dead tuples → triggers after just 70 dead tuples ``` ### The Analyze trigger formula ```markdown n_mod_since_analyze > autovacuum_analyze_threshold + autovacuum_analyze_scale_factor × reltuples Default: n_mod_since_analyze > 50 + 0.1 × reltuples = modifications exceed 50 + 10% of live tuples ``` ### Cost-based Throttling - why autovacuum seems slow Autovacuum is intentionally throttled to avoid I/O saturation: ```markdown autovacuum_vacuum_cost_delay = 2ms (pause between cost limit hits) autovacuum_vacuum_cost_limit = 200 (cost units before pausing) Cost per operation: vacuum_cost_page_hit = 1 (page found in shared_buffers) vacuum_cost_page_miss = 10 (page read from disk) vacuum_cost_page_dirty = 20 (page dirtied by VACUUM) Example: 100% cache miss workload 200 cost limit / 10 cost per miss = 20 pages per 2ms burst = 20 pages × 8KB = 160KB per 2ms = ~80 MB/s maximum VACUUM throughput For a 100GB table: 100GB / 80MB/s ≈ 21 minutes minimum ``` > This is by design — autovacuum should not compete with your production workload. But if it can't keep up with your write rate, you need to tune cost limits and scale factors per table: ```sql -- Per-table autovacuum tuning (overrides global settings): ALTER TABLE orders SET ( autovacuum_vacuum_scale_factor = 0.01, -- trigger at 1% instead of 2% autovacuum_vacuum_cost_delay = 0, -- no throttling for this table autovacuum_vacuum_cost_limit = 1000 -- higher cost limit ); -- For large, frequently-updated tables: ALTER TABLE events SET ( autovacuum_vacuum_scale_factor = 0.005, -- 0.5% autovacuum_vacuum_threshold = 1000, autovacuum_analyze_scale_factor = 0.02 ); ``` * * * ## Part 5: Table Bloat - Diagnosing the Problem `Bloat` is the gap between the **logical size of your data and the physical size of the files** on disk. It comes in two forms. ### Heap Bloat ```sql -- Quick bloat estimate using pgstattuple: CREATE EXTENSION pgstattuple; SELECT * FROM pgstattuple('orders'); -- table_len = 1073741824 (1 GB physical size) -- tuple_count = 5000000 (live rows) -- tuple_len = 600000000 (bytes used by live tuples) -- tuple_percent = 55.9 (% of pages with live data) -- dead_tuple_count = 800000 (dead rows) -- dead_tuple_len = 96000000 (bytes used by dead tuples) -- dead_tuple_percent = 8.9 (% of pages with dead data) -- free_space = 377741824 (free bytes in pages) -- free_percent = 35.2 ← 35% of the table is wasted! -- Full bloat scan (slow but accurate): SELECT pg_size_pretty(table_len) AS total, pg_size_pretty(tuple_len) AS live_data, pg_size_pretty(dead_tuple_len) AS dead_data, pg_size_pretty(free_space) AS free, round(dead_tuple_percent::numeric, 1) AS dead_pct, round(free_percent::numeric, 1) AS free_pct FROM pgstattuple('orders'); ``` ### Index Bloat ```sql -- Index bloat via pgstattuple: SELECT * FROM pgstatindex('idx_orders_status'); -- index_size = 209715200 (200 MB) -- leaf_pages = 24000 -- empty_pages = 1200 ← pages with no live entries -- deleted_pages = 3400 ← pages fully reclaimed -- avg_leaf_density = 68.4 ← B-tree pages 68% full (100% = no bloat) -- leaf_fragmentation = 12.3 ← % of leaf pages out of logical order -- Bloat estimate without pgstattuple (from catalog stats): SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size, round(100.0 * pg_relation_size(indexrelid) / nullif(pg_total_relation_size(relid), 0), 1) AS pct_of_table, idx_scan, idx_tup_read, idx_tup_fetch FROM pg_stat_user_indexes WHERE relname = 'orders' ORDER BY pg_relation_size(indexrelid) DESC; ``` Once you've confirmed severe bloat with the queries above, standard VACUUM won't be enough, it can only reclaim space within existing pages, not shrink the file itself. * * * ## Part 6: VACUUM FULL vs pg\_repack When bloat is severe, standard VACUUM cannot help - it reclaims space within pages but cannot return pages to the OS or compact the file. For that you need a table rewrite. ### VACUUM FULL ```sql VACUUM FULL orders; ``` **What it does:** - Acquires `AccessExclusiveLock` - blocks all reads and writes - Creates a brand new heap file - Copies all live tuples into the new file (compact) - Rebuilds all indexes from scratch - Drops the old file - Releases lock - Vacuum full also reset `relfrozenxid` to the current XID - so after a VACUUM FULL, the table's wraparound age resets to 0 **Pros**: Table is fully compacted; all bloat is eliminated. **Cons**: - Complete table lock for the entire duration - duration = proportional to table size - 100GB table = potentially hours of downtime ### pg\_repack - zero-downtime alternative ```sql -- Install CREATE EXTENSION pg_repack; -- Repack a table online pg_repack -d mydb -t orders -- Repack only indexes (even faster) pg_repack -d mydb -t orders --only-indexes ``` How **pg\_repack** works without locking: 1. Create a new empty table (shadow table) 2. Copy all live tuples to shadow table (no lock, reads allowed) 3. Create a trigger on original table: log all changes to a delta table 4. Apply delta changes to shadow table (catch up with live writes) 5. Repeat step 4 until delta is small (milliseconds of lag) 6. Acquire brief `AccessExclusiveLock` (milliseconds only) 7. Apply final delta, swap table OIDs, drop original 8. Release lock **Downtime = milliseconds** (just the final `OID` swap), not hours. ``` VACUUM FULL: simple, built-in, requires maintenance window pg_repack: complex, extension required, truly online ``` * * * ## Part 7: Reading VACUUM VERBOSE Output ``` VACUUM (VERBOSE, ANALYZE) orders; ``` ```markdown INFO: vacuuming "public.orders" INFO: scanned index "orders_pkey" to remove 84000 row versions -- index cleanup pass DETAIL: CPU: user: 0.42s, system: 0.08s, elapsed: 1.23s INFO: scanned index "idx_orders_status" to remove 84000 row versions DETAIL: CPU: user: 0.38s, system: 0.05s, elapsed: 1.01s INFO: "orders": removed 84000 row versions in 7200 pages -- heap cleanup DETAIL: CPU: user: 0.12s, system: 0.04s, elapsed: 0.18s INFO: index "orders_pkey" now contains 5000000 row versions in 14000 pages DETAIL: 84000 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. INFO: "orders": found 84000 removable, 5000000 nonremovable row versions in 62800 out of 62800 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 1234567 There were 12000 unused item identifiers. -- reusable slots Skipped 0 pages due to buffer pins, 0 frozen pages. 0 pages are entirely empty. CPU: user: 1.24s, system: 0.23s, elapsed: 4.87s INFO: analyzing "public.orders" INFO: "orders": scanned 30000 of 62800 pages, containing 2400000 live rows and 0 dead rows; 30000 rows in sample, 5000000 estimated total rows ``` After running VACUUM VERBOSE, these are the numbers that tell you whether VACUUM did its job: ```markdown 84000 removable row versions → dead tuples successfully collected 0 cannot be removed yet → blocked by long-running transaction Skipped N frozen pages → visibility map working (those pages skipped) 0 pages entirely empty → no file truncation possible (no trailing empty pages) 14000 pages in pkey index → index is growing ``` * * * ## Conclusion In this module, we explored how VACUUM and autovacuum work under the hood, starting from: 1. Why VACUUM exists? every `UPDATE` and `DELETE` leaves dead tuples and dead index entries behind due to MVCC's append-only write model, and without VACUUM the table grows forever, index-only scans stop working, and XID wraparound eventually corrupts the entire database. 2. The exact VACUUM sequence: computing `OldestXmin` as the reclaim watermark, scanning heap pages while skipping `all_visible` ones, collecting dead TIDs into `maintenance_work_mem`, cleaning indexes via `ambulkdelete()`, compacting pages, updating the FSM, and setting visibility map bits. 3. How autovacuum decides when to act: the trigger formula, cost-based throttling parameters, and how to tune aggressiveness per table using `ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = ...)` for large high-churn tables. 4. How XID freezing prevents wraparound: setting the `HEAP_XMIN_FROZEN` infomask bit in PostgreSQL 14+, the three age thresholds that control when freezing happens, and how to monitor proximity to the 2.1 billion XID danger zone using `age(datfrozenxid)`. 5. When to use `VACUUM FULL` versus `pg_repack` : full table lock for hours versus online shadow-table rewrite with milliseconds of downtime. * * * ## What's Next → Module 8: Concurrency & Locking We've now seen that VACUUM itself needs locks to run, that long-running transactions block `OldestXmin` from advancing, and that `VACUUM FULL` holds `AccessExclusiveLock` for its entire duration. Module 8 goes into the locking system itself; the full lock hierarchy from `AccessShareLock` to `AccessExclusiveLock`, how row-level locks work through `xmax` and `MultiXactId` rather than a lock table, deadlock detection, advisory locks, and the `pg_locks` queries that diagnose blocking in production.