How PostgreSQL Vacuum Actually Works

People talk about PostgreSQL vacuum as if it were a janitor that comes by later and tidies up a mess. That description is not wrong, but it hides the important part. Vacuum is not cosmetic maintenance. It is one of the core mechanisms that makes PostgreSQL's version of MVCC sustainable. Without it, updates would leave old row versions behind forever, indexes would accumulate dead pointers, visibility checks would stay expensive, and eventually the cluster would hit transaction ID wraparound protection and start refusing writes.

The confusing part is that vacuum does several jobs at once, and those jobs operate at different layers of the storage engine. Some of it is about reclaiming space. Some of it is about telling future vacuums which pages can be skipped. Some of it is about marking ancient tuple XIDs as permanently safe so a 32 bit counter can keep cycling without making old rows look like they were inserted in the future. Some of it is about keeping indexes from dragging around references to tuple versions nobody can ever see again.

That is why operators often misread symptoms. A table file keeps its size after a large delete, so they conclude vacuum did nothing. Autovacuum is running, but bloat still grows, so they conclude autovacuum is broken. An anti-wraparound vacuum appears in pg_stat_activity and starts fighting with DDL, so they conclude PostgreSQL picked a bad time to do maintenance. In reality the system is following quite strict rules about snapshot visibility, page layout, XID age, and lock safety.

This article is about those rules. We will stay close to PostgreSQL's actual storage model, to the PostgreSQL 18 manuals, and to the current implementation in src/backend/access/heap/vacuumlazy.c, which is where concurrent plain vacuum lives today. The goal is to explain what happens between DELETE FROM orders WHERE ... and the later moment when that space becomes reusable, why it does not always become reusable immediately, and why a cluster with no dead rows can still need vacuum urgently.

Vacuum Starts With Heap Pages And Tuple Headers

PostgreSQL stores tables as arrays of fixed-size pages, usually 8 kB each. The page format is documented in PostgreSQL 18 section 66.6, and the basic shape matters because vacuum works at page granularity, not at some abstract row list.

A heap page contains:

• a 24 byte page header
• an array of item identifiers, one per tuple slot
• free space in the middle
• the tuple bodies themselves packed from the end of the page backwards

The page header contains pd_lsn, free space offsets, and pd_prune_xid, which is a hint about whether pruning might pay off. The item identifier array matters just as much. PostgreSQL keeps the slot numbers stable even when tuple bodies move inside the page, because external references such as ctid point to a page number plus item identifier index, not to a byte offset.

The tuple header carries the visibility history vacuum cares about. In the documentation's HeapTupleHeaderData, the key fields are these:

typedef struct HeapTupleHeaderData {
    TransactionId t_xmin;   /* inserting transaction */
    TransactionId t_xmax;   /* deleting or updating transaction */
    ItemPointerData t_ctid; /* self or newer tuple version */
    uint16 t_infomask2;
    uint16 t_infomask;
    uint8  t_hoff;
} HeapTupleHeaderData;

t_xmin says who created the tuple version. t_xmax says who deleted it, or who replaced it with a newer version during an update. t_ctid points either to the tuple itself or to the next version in the update chain. The infomask bits cache visibility facts such as whether the inserting transaction committed or whether the tuple is frozen.

That means a PostgreSQL row is not just application data. It is application data plus a small visibility ledger attached to the front. Vacuum reads and rewrites that ledger all the time.

A concrete example helps. Suppose a row starts life like this:

page 42, slot 7
xmin = 810
xmax = 0
ctid = (42,7)
status = live

Then a later transaction with XID 842 updates the row. PostgreSQL does not overwrite the old tuple in place. It creates a new tuple version, perhaps on the same page, perhaps somewhere else, and turns the old version into a historical record:

old version: xmin = 810, xmax = 842, ctid = (42,11)
new version: xmin = 842, xmax = 0,   ctid = (42,11)

Readers with an old snapshot may still need the version created by 810. Readers with a new snapshot should see the version created by 842. Vacuum's first question is therefore not "is this old?" but "can any valid snapshot still need this?"

That is the first mental shift worth keeping. Vacuum is constrained by visibility semantics before it is constrained by free space or IO.

Updates And Deletes Leave History Behind On Purpose

DELETE in PostgreSQL does not erase bytes from the heap page. It marks a tuple version as no longer live for future snapshots by filling in xmax. UPDATE is internally a delete plus insert, so it leaves the old version behind and appends a new one. This is the price PostgreSQL pays for readers that do not block writers and writers that do not block readers.

If transaction 900 starts before transaction 905 updates a row, transaction 900 must still be able to read the older visible version. If PostgreSQL physically removed the old bytes as soon as 905 committed, snapshot consistency would break.

That is why vacuum is necessary at all. MVCC produces garbage as a normal side effect of concurrency.

The storage consequences are easy to underestimate:

1. Heap space is consumed by dead tuples. A busy table can end up with far more historical versions than live rows.
2. Indexes can point at dead tuples. If the update touched indexed columns, PostgreSQL usually adds new index entries for the new version and keeps the old ones until vacuum proves they are no longer needed.
3. Visibility checks stay expensive without metadata. If the system does not know a page is visible to everyone, index-only scans cannot trust the index alone.
4. XID age keeps increasing even on static rows. Old tuples must eventually be frozen so wraparound logic cannot misclassify them.

This is why a table that receives constant churn can look healthy at the SQL level and sick at the storage level. COUNT(*) might be stable at 50 million rows while the heap and indexes keep growing because every logical row has had six physical incarnations in the last week.

The next mental trap is file size. Plain vacuum is designed to make dead space reusable inside the same relation. Most of the time it does not shrink the file and hand blocks back to the operating system. If a 100 GB table in Dublin loses 40 GB of obsolete row versions, plain vacuum may leave it as a 100 GB file that now contains 40 GB of reusable holes. For future inserts and updates, that can be perfectly fine. For people looking only at du -sh, it looks like failure.

A good shorthand is this:

That table captures a lot of operational confusion. Many incidents start with someone expecting the first row and the second row to be the same thing.

Vacuum Is A Three-Phase Storage Pass, Not One Sweep

As of PostgreSQL 18, the opening comment block in src/backend/access/heap/vacuumlazy.c describes concurrent plain vacuum in three main phases.

Phase I: scan the heap. Vacuum walks heap pages, prunes and freezes tuples where it can, and stores dead tuple TIDs in an in-memory TID store.

Phase II: vacuum the indexes. Using the collected dead TIDs, vacuum tells each index access method to remove obsolete index entries.

Phase III: vacuum the heap. Vacuum goes back to the heap pages referenced in the TID store and reaps the dead line pointers, making that space reusable.

That might sound odd at first. Why not remove dead heap tuples the moment they are found?

Because index safety comes first. If vacuum removed a heap tuple slot before the indexes forgot about it, an index lookup could land on a pointer that now refers to unrelated data or an already-reused slot. PostgreSQL therefore keeps dead item identifiers around until the index cleanup phase has removed references to them. Only then is it safe to reap the dead heap items.

This is also why memory matters. Vacuum must store dead tuple IDs somewhere between the heap scan and index cleanup. It uses at most maintenance_work_mem or autovacuum_work_mem for that dead TID store. If the store fills up before the whole relation has been scanned, vacuum pauses the heap scan, runs index cleanup and heap cleanup for the current batch, empties the store, and then resumes scanning.

So even on one table, plain vacuum can alternate between scanning and cleanup in chunks. It is not always one full sequential pass followed by one full index pass.

The heap scan itself is selective. Vacuum uses the visibility map to skip pages when safe. On a normal vacuum, pages already known to be all-visible can often be skipped, because there should be nothing to clean there. On an aggressive anti-wraparound vacuum, many more pages must be visited because freezing old XIDs matters even if there are no dead tuples.

There are also lock subtleties. Plain vacuum takes a SHARE UPDATE EXCLUSIVE lock on the relation, which allows ordinary SELECT, INSERT, UPDATE, and DELETE to continue. That is why autovacuum is viable on live systems. But within the heap scan, PostgreSQL still needs short-lived page-level cleanup locks when it wants to prune a page. If a non-aggressive vacuum cannot get the cleanup lock immediately, it may skip some pruning and come back later on another run.

At the end, plain vacuum may try to truncate empty pages from the tail of the table file. This is one of the few ways plain vacuum can return space to the OS, but it requires a stronger lock momentarily, which is why some operators disable truncation for contentious tables.

The implementation details line up closely with pg_stat_progress_vacuum, where you can literally watch phases such as scanning heap, vacuuming indexes, vacuuming heap, and truncating heap appear for a live run.

Page Pruning And HOT Mean Some Cleanup Happens Before Vacuum

A common oversimplification is "vacuum removes old versions". Sometimes it does. Sometimes ordinary page access already did part of the work.

PostgreSQL has a mechanism called page pruning. When a backend visits a heap page and sees that pruning is likely to pay off, it can compact tuple space and clean up update chains on that page as part of normal operation. This is not the same as full relation vacuum, and it does not replace vacuum, but it reduces how much work vacuum later has to do.

This interacts with HOT, heap-only tuples, documented in PostgreSQL section 66.7. HOT is possible when an update does not change any indexed column and there is enough free space on the same heap page for the new version. In that case PostgreSQL can append a new tuple version without creating new index entries for every update. Indexes continue to point at the original line pointer, and the heap follows the chain to the current live version.

That changes the economics of churn dramatically.

Without HOT:

• update creates a new heap tuple
• update often creates new index entries
• old heap tuple and old index entries wait for vacuum

With HOT:

• update creates a new heap tuple on the same page
• index entries do not multiply for each update
• intermediate tuple versions can often be removed during normal page pruning

If you are wondering why fillfactor exists, this is one of the answers. Leaving some room on each page makes same-page updates more likely, which makes HOT more likely, which reduces index churn and future vacuum work.

Still, HOT is not a get-out-of-vacuum-free card. Vacuum is still needed to:

• reclaim dead line pointers that pruning could not fully remove yet
• advance freezing state and relfrozenxid
• maintain visibility map bits
• process pages that were never visited by a backend that happened to prune them
• clean dead index tuples for non-HOT updates and deletes

The practical lesson is that heavily updated tables behave very differently depending on whether the updates are HOT-friendly. Two tables with the same row count and the same update rate can impose very different vacuum costs if one table updates indexed columns all day and the other only flips a non-indexed status field.

Visibility Map And Free Space Map Turn Vacuum Into Future Speed

Vacuum is not only about removing dead rows. It also leaves behind metadata that speeds up future work.

Every heap relation has two side structures that matter here:

• the visibility map or VM
• the free space map or FSM

The visibility map stores two bits per heap page, in a separate _vm fork. PostgreSQL section 66.4 describes them.

• all-visible means every tuple on the page is visible to all current and future transactions until the page is modified again
• all-frozen means every tuple is frozen, so even aggressive anti-wraparound vacuum can skip the page

These bits are conservative. Vacuum sets them only when it is sure the condition is true. Any data modification on the page clears them.

The all-visible bit is useful twice.

First, future vacuums can often skip that page.

Second, index-only scans can trust the index without fetching the heap tuple for visibility checking. PostgreSQL indexes do not store tuple visibility directly, so a normal index scan has to visit the heap to make sure the tuple is visible to the current snapshot. If the VM says the whole page is all-visible, that heap fetch can be skipped.

That means vacuum can improve read performance even on tables where space reuse is not urgent.

The free space map plays a different role. It is an approximate directory of which pages have space for future inserts or updates. After vacuum frees room inside a page, it updates the FSM so later writers can find that page instead of extending the relation immediately.

Think of the two maps as answering different questions:

• VM: "Can future readers and vacuums skip this page?"
• FSM: "Can future writers fit something here?"

You can see why both matter on a mature OLTP table in Paris that is constantly flipping order status, timestamps, and fulfilment metadata. VM helps later readers and later vacuums avoid work. FSM helps later writers reuse holes created by older versions instead of growing the table again.

This is also why a plain vacuum that appears to have reclaimed no OS-visible space may still have improved the system substantially. It may have:

• made thousands of pages reusable for future writes
• turned large regions all-visible for index-only scans
• pushed old pages toward all-frozen so anti-wraparound work gets smaller later

Those are real wins, just not wins that show up as a smaller file on disk.

Freezing Solves The 32 Bit XID Problem

Vacuum's least intuitive job is freeze management.

PostgreSQL transaction IDs are 32 bit counters. They wrap. The cluster therefore cannot treat xmin = 10 as eternally older than xmin = 4,000,000,000. After enough transactions, modulo arithmetic makes old values ambiguous. PostgreSQL solves this by freezing sufficiently old tuples so they no longer rely on ordinary XID comparison.

The PostgreSQL 18 routine vacuuming documentation puts it plainly: every table in every database must be vacuumed often enough to prevent transaction ID wraparound, otherwise old rows could appear to be inserted in the future and become invisibly lost from normal queries.

The special permanent marker is FrozenTransactionId. In pre-9.4 releases PostgreSQL literally replaced xmin with that magic XID. In modern PostgreSQL it typically preserves the original xmin and sets a frozen flag bit, which is more useful for forensics and introspection. Either way, the visibility rule becomes "this tuple is older than every normal transaction".

That is why a table with almost no updates can still need vacuum. If it contains old tuple versions that have never been frozen, their XID age keeps advancing whether or not the data changes.

PostgreSQL tracks this with relfrozenxid in pg_class and datfrozenxid in pg_database. The docs even suggest queries like these:

SELECT c.oid::regclass AS table_name,
       greatest(age(c.relfrozenxid), age(t.relfrozenxid)) AS age
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm');
 
SELECT datname, age(datfrozenxid)
FROM pg_database;

When a table's age crosses the configured thresholds, vacuum becomes aggressive. That means page skipping rules tighten because the mission is no longer only dead-row cleanup. The mission is to inspect enough unfrozen tuples to advance relfrozenxid safely.

This is also where all-frozen VM bits matter. A page already known to contain only frozen tuples can be skipped even in aggressive mode. A page that is merely all-visible may still need to be scanned so old XIDs can be frozen.

If you ignore this long enough, PostgreSQL eventually stops being polite. First it emits warnings along the lines of "database must be vacuumed within N transactions". Later, with only a small safety margin left, it refuses commands that would assign new XIDs. At that point vacuum is not housekeeping. It is an outage-prevention path.

That urgency is why anti-wraparound autovacuums are harder to cancel. PostgreSQL would rather annoy you with maintenance pressure than let the cluster walk into a visibility catastrophe.

Autovacuum Uses Formulas, Not Feelings

Autovacuum is often described as a background vacuum service, but it is more specific than that. PostgreSQL runs an autovacuum launcher process that starts worker processes across databases, and each worker decides whether a table qualifies for vacuum or analyze based on thresholds derived from table statistics.

For dead tuple cleanup, the default vacuum trigger is:

vacuum threshold =
    min(autovacuum_vacuum_max_threshold,
        autovacuum_vacuum_threshold
        + autovacuum_vacuum_scale_factor * reltuples)

So for a table with 10 million estimated rows, the default base threshold of 50 and default scale factor of 0.2 produce a threshold of about 2,000,050 dead tuples unless the max threshold caps it.

For insert-heavy tables, PostgreSQL also has an insert trigger:

vacuum insert threshold =
    autovacuum_vacuum_insert_threshold
    + autovacuum_vacuum_insert_scale_factor
      * reltuples
      * percent_of_table_not_frozen

That clause about the unfrozen portion matters. Insert-only tables still need vacuum sometimes so pages can become all-visible and eventually all-frozen.

Analyze has its own threshold, based on inserted, updated, and deleted rows since the last statistics refresh.

Wraparound protection is a separate override. Even if a table has few dead rows and would never qualify under the ordinary formula, PostgreSQL forces vacuum once relfrozenxid gets too old relative to autovacuum_freeze_max_age. That forced vacuum happens even if autovacuum is otherwise nominally disabled.

Two practical consequences follow.

First, defaults are often too relaxed for very large hot tables. A scale factor of 0.2 means a 500 million row table can accumulate 100 million dead tuples before ordinary autovacuum feels pressure. On a churny event table in Madrid, that is usually far too late.

Second, defaults can be too aggressive for tiny but very hot tables if each vacuum is expensive because of indexes or lock churn. The right settings are workload-dependent, which is why PostgreSQL exposes per-table storage parameters such as:

ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.02,
    autovacuum_analyze_scale_factor = 0.01,
    autovacuum_vacuum_cost_limit = 2000
);

Cost-based delay is the next lever. Vacuum consumes IO, so PostgreSQL charges work units to a cost balance and sleeps once the balance exceeds a limit. This is how background vacuum avoids flattening foreground latency. The downside is obvious: if the system is already behind, sleeping more can mean it never catches up. A table in Stockholm that gets 40,000 updates per second may need higher cost limits or more workers, not gentler sleeping.

The right way to think about autovacuum is not "on or off". It is "what event rate, table size, and freeze age should trigger what amount of background work?"

Vacuum Falls Behind For Predictable Reasons

When a PostgreSQL system suffers vacuum trouble, the root cause is usually one of a short list of blockers.

Long-Running Transactions

Vacuum can remove a dead tuple only when no active snapshot can still see it. A transaction that started hours ago pins the global visibility horizon. Even if the application forgot about it, vacuum has to respect it.

This is why an innocent-looking idle in transaction session can inflate storage across the cluster. It does not need to touch the bloated table directly. If it holds an old snapshot, dead tuples newer than that snapshot cannot be reclaimed.

Useful first check:

SELECT pid,
       now() - xact_start AS xact_age,
       state,
       backend_xmin,
       query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

If backend_xmin is ancient, vacuum is working with one hand tied behind its back.

Prepared Transactions

Two-phase commit keeps prepared transactions alive across client disconnects. Their snapshots and XIDs can also pin cleanup horizons. If an application server in Frankfurt prepares and forgets, vacuum pays for the forgetfulness.

Check pg_prepared_xacts when bloat makes no sense.

Replication Slots And Hot Standby Feedback

Logical replication slots and physical slots can pin xmin or catalog_xmin so WAL retention and tuple cleanup cannot advance past what a downstream consumer still might need. Hot standby feedback can do something similar on behalf of read queries running on replicas. This avoids canceling replica queries, but it pushes bloat pressure back onto the primary.

This tradeoff is common in analytics-heavy setups: keeping long reports alive on a read replica can mean more dead tuples piling up on the write primary.

Conflicting Locks And DDL Churn

Plain vacuum's table-level lock is compatible with ordinary DML, but it conflicts with some schema-changing commands. Autovacuum is usually interrupted rather than blocking those commands, except when the run is there to prevent wraparound. A migration framework that constantly acquires conflicting locks can starve routine vacuum work.

IO Budget Too Small For Churn Rate

Sometimes there is no exotic blocker. The table just produces dead tuples faster than workers can process them. This shows up when n_dead_tup and table size climb steadily even though autovacuum runs often. The fix is more worker capacity, lower thresholds, friendlier schema for HOT, lower fillfactor, or less churn in the application. Often it is some combination.

These causes are boring, which is good. It means vacuum incidents are usually diagnosable, not mystical.

Plain Vacuum Reuses Space, But Bloat Is Still Real

A relation can be badly bloated even if vacuum is technically functioning.

Why? Because internal reuse is not the same as dense packing.

Suppose a table grew to 200 GB while processing a year-end import in Milan. Later, 120 GB of rows were deleted. Plain vacuum can mark those dead regions reusable, but the file may still remain near 200 GB if the free space is scattered throughout the relation. Future writes can reuse it, but sequential scans still walk a large file and the OS still sees a large allocation.

Indexes have a similar story. Vacuum can remove dead index tuples, but it does not rebuild index page layout into its tightest possible form. After enough churn, an index can remain larger and less cache-friendly than a freshly built one even though dead entries are being cleaned.

This is why operators need different tools for different goals:

• keep up with MVCC churn: plain VACUUM
• refresh planner statistics: ANALYZE or VACUUM ANALYZE
• physically shrink a heap: VACUUM FULL, CLUSTER, or a rewriting ALTER TABLE
• physically rebuild a bloated index: REINDEX

VACUUM FULL is the most famous shrink tool, but it is expensive because it rewrites the table into a new file and takes an ACCESS EXCLUSIVE lock. That blocks ordinary reads and writes. It also temporarily needs extra disk space for the new copy.

The PostgreSQL docs are clear on the strategic lesson: routine maintenance should aim to use plain vacuum often enough that VACUUM FULL becomes rare. When you do need VACUUM FULL, the problem is usually not "vacuum failed today". The problem is "storage churn exceeded maintenance policy for a long time".

One nuance worth knowing is INDEX_CLEANUP. PostgreSQL can skip index vacuuming when there are very few dead tuples because the cost of visiting every index may outweigh the benefit. This is normally sensible. But if you are diagnosing lingering dead line pointers or index bloat on a sensitive table, forcing VACUUM (INDEX_CLEANUP ON) can help make behaviour more predictable for a maintenance window.

Another nuance is truncation. Plain vacuum may truncate empty tail pages, but only if it can get the stronger lock that truncation requires. If a hot table never offers that window, tail-space return may not happen even when the end of the relation is empty.

Index Cleanup, TOAST, And Failsafe Paths Matter More Than People Expect

The word "vacuum" makes people picture heap tuples, but a serious PostgreSQL maintenance plan has to think about three extra pieces: indexes, TOAST tables, and the anti-wraparound failsafe.

Start with indexes. Heap cleanup is often not the expensive part. If a table has eight secondary indexes, every non-HOT update can leave eight families of dead pointers behind. The heap pass might discover dead tuple versions quickly, but phase II still has to ask each index access method to walk its structure and delete obsolete entries. On a large OLTP table in Amsterdam with several overlapping indexes added over the years, the real cost of vacuum may be the index forest, not the heap.

This is why schema design and vacuum behaviour are linked. An index you barely use in queries still has to absorb inserts, updates, and deletes, and vacuum still has to clean it. The storage engine does not care that the index was created for one dashboard five quarters ago. If the relation churns, that forgotten index is now part of the maintenance bill.

You can often see this in pg_stat_progress_vacuum. The heap scan advances briskly, then the process sits in vacuuming indexes for far longer than expected. That is not a bug. It is vacuum paying back write amplification from the index set you chose.

TOAST is the second extra piece. Large values such as long JSON documents or large text fields may spill into a separate TOAST table. From vacuum's point of view, that means one logical application table can have two storage problems:

• dead rows in the main heap
• dead chunks in the TOAST relation

Plain vacuum processes the corresponding TOAST table by default because churn in wide columns can create substantial dead storage there too. A row update that changes one large toasted value may leave behind obsolete TOAST chunks even if the main heap tuple count looks modest. This is one reason some tables surprise operators with a much larger total footprint than the base heap alone suggests.

There is also a subtle effect on freezing and wraparound queries. When you inspect relfrozenxid age for a user table, you should remember its TOAST table has its own age and its own maintenance history. The PostgreSQL docs use greatest(age(c.relfrozenxid), age(t.relfrozenxid)) for exactly this reason.

The third extra piece is the failsafe path. PostgreSQL would prefer every vacuum to do the full nice version of maintenance: collect dead TIDs, clean indexes carefully, reap heap items, update metadata, maybe truncate tail pages. But near wraparound danger, elegance becomes optional and survival becomes mandatory.

The SQL reference for VACUUM documents this explicitly. If wraparound pressure becomes too high, the failsafe mechanism can skip index vacuuming even when you asked for INDEX_CLEANUP ON. That sounds alarming until you think about the tradeoff. Near XID exhaustion, an untidy index is vastly preferable to a cluster that stops assigning new XIDs. PostgreSQL therefore chooses the work that advances the freeze horizon fastest.

This matters operationally because it explains a class of confusing observations:

• an anti-wraparound autovacuum appears
• it finishes faster than expected
• some index bloat remains
• the cluster stops screaming about wraparound anyway

That is not inconsistency. That is PostgreSQL making a priority decision under pressure.

Parallel vacuum fits into the same picture. Plain vacuum can use parallel workers for index phases when there are enough eligible indexes and parallel maintenance workers available. This can be a real win on large relations with multiple big B-tree indexes, because phase II and the associated cleanup work dominate wall-clock time. But parallel vacuum is not magic. It does not parallelise the heap scan itself, and it does not help if the real blocker is an old snapshot pinning removal or if the table has only one significant index.

The useful way to frame all of this is that vacuum is not one algorithm but a bundle of related maintenance tasks sharing one visit to a relation:

• heap visibility cleanup
• index entry cleanup
• free space accounting
• visibility-map maintenance
• freeze advancement
• optional TOAST processing
• optional truncation

Different tables pay for different parts of that bundle. A narrow append-mostly event table might pay mostly for freezing and VM maintenance. A heavily updated customer table with six indexes might pay mostly for index cleanup. A document table with large toasted payloads might quietly spend much of its maintenance budget in the TOAST relation.

Once you see vacuum in those terms, tuning gets less mystical. You stop asking "why is vacuum slow?" as if there were one answer, and start asking which part of the maintenance bundle is expensive on this relation and why.

Read Vacuum Like An Operator, Not Like A Mystery Process

The fastest way to get better at vacuum is to read what PostgreSQL already tells you.

A manual verbose run can be surprisingly informative:

VACUUM (VERBOSE, ANALYZE) orders;

The output includes counts of pages scanned, tuples removed, pages skipped due to the visibility map, index cleanup work, and freezing progress. For autovacuum, similar details can be emitted through log_autovacuum_min_duration.

Live progress is available through pg_stat_progress_vacuum:

SELECT pid,
       relid::regclass AS relation,
       phase,
       heap_blks_total,
       heap_blks_scanned,
       heap_blks_vacuumed,
       index_vacuum_count,
       num_dead_tuples,
       max_dead_tuples
FROM pg_stat_progress_vacuum;

A few interpretations are worth memorising.

• If phase stays in scanning heap forever while num_dead_tuples approaches max_dead_tuples, the worker may be chunking because dead TID memory fills repeatedly.
• If phase reaches vacuuming indexes and sits there, your cost is in index cleanup, not heap scanning.
• If aggressive vacuums read many pages with few dead tuples removed, the work is probably about freezing, not bloat.
• If ordinary autovacuums keep appearing on the same table but n_dead_tup stays high, look for pinned horizons or insufficient worker budget.

The statistics views are approximate, especially n_dead_tup, but the trend is useful. Pair them with storage metrics and application behaviour.

A practical diagnostic bundle for one table often looks like this:

SELECT relname,
       n_live_tup,
       n_dead_tup,
       vacuum_count,
       autovacuum_count,
       last_vacuum,
       last_autovacuum,
       last_analyze,
       last_autoanalyze
FROM pg_stat_user_tables
WHERE relname = 'orders';
 
SELECT pg_size_pretty(pg_relation_size('orders')) AS heap,
       pg_size_pretty(pg_indexes_size('orders')) AS indexes,
       pg_size_pretty(pg_total_relation_size('orders')) AS total;

Then compare that with activity that could pin cleanup:

SELECT slot_name, active, xmin, catalog_xmin
FROM pg_replication_slots;

and with transaction age from pg_stat_activity.

This is enough to answer most real questions:

• Is vacuum running?
• Is it keeping up?
• Is it reclaiming internal space but not shrinking files?
• Is freeze age the real driver?
• Is a snapshot horizon pinned?
• Are indexes the expensive part?

Without that discipline, teams often jump straight to VACUUM FULL, which is sometimes necessary but often just the loudest tool available.

What Vacuum Actually Promises

The best way to end the confusion is to state the promise precisely.

Vacuum does not promise that a table gets smaller after deletes.

Vacuum does not promise that every obsolete tuple disappears on the first pass.

Vacuum does not promise that autovacuum can outrun any workload with any settings.

What it does promise, when the system is healthy, is this:

• dead tuple versions that no valid snapshot can still see will be reclaimed for reuse
• obsolete index entries will be removed often enough that heap slots can be safely reaped
• pages known to be universally visible will be marked so future vacuums and some queries can skip work
• ancient tuple XIDs will be frozen before wraparound turns them toxic
• the table will stay operational under MVCC instead of drowning in its own history

That is a much more interesting job than sweeping the floor.

PostgreSQL's storage engine depends on the idea that history can exist for a while and then be retired safely. WAL makes dirty pages crash-safe. MVCC makes concurrent reads possible. Vacuum closes the loop by proving which history is now disposable and which old metadata must be preserved forever as frozen fact.

If you keep that loop in your head, vacuum stops looking like a background chore and starts looking like what it really is: the retirement system for PostgreSQL's row-version economy. Without it, every update is a debt that never gets settled.