MVCC garbage collection works by removing row versions that no supported transaction can see anymore. The difficult part is proving that a version is truly unreachable from every relevant snapshot before reclaiming its storage or associated index entries.
PostgreSQL calls the main maintenance process VACUUM; InnoDB uses purge machinery over undo history. The names and physical algorithms differ, but the responsibility is shared: historical state that once made concurrency possible must eventually stop consuming resources when no valid reader depends on it.
This guide focuses on that cleanup boundary. For the complete mechanism, use How MVCC Works. The companion guides explain snapshots and read views plus row versions.
The Direct Answer: Reclaim History Only After Its Last Reader Is Gone
Suppose camera-2 was available in version V1 and later reserved in V2. A current reader needs V2. An old snapshot can still need V1. Therefore V1 is obsolete for the latest state but not yet reclaimable.
Once every snapshot that could legally see V1 has finished, the database can mark that historical state safe for removal under its implementation rules. That transition—from invisible-to-new-readers to invisible-to-every-relevant-reader—is the central event in MVCC garbage collection.
PostgreSQL’s current routine vacuuming documentation explains that updated and deleted tuple versions eventually need removal, but tuples that may still be visible to older snapshots cannot be removed yet. Its VACUUM command documentation distinguishes ordinary vacuum from VACUUM FULL and other maintenance options.
Dead for the Latest Reader Does Not Mean Safe to Delete
This distinction is so important that it deserves its own vocabulary. A version can be logically dead for current reads because a newer committed version supersedes it. Yet it can remain historically live for an older snapshot.
If maintenance removes that version too early, the old snapshot can no longer reconstruct the database view it was promised. Correct cleanup therefore needs a cutoff tied to transaction visibility, not merely to the age of a file or the existence of a newer row.
PostgreSQL’s vacuum reporting uses concepts such as dead tuples that are not yet removable. This is not contradictory language. It means the tuple is dead for future state but still protected by an older visibility obligation.
Why Updates Create Cleanup Work
Every update can create a newer row version while leaving the older version physically present. A table whose logical row count stays constant can therefore accumulate substantial historical storage under a heavy update workload.
Imagine one counter row updated one million times. The application still has one logical counter. The storage engine has processed a million version transitions. Unless old history is reclaimed efficiently, physical work grows far faster than logical cardinality.
This is why update-heavy workloads should be monitored differently from append-only workloads. “Table has only ten thousand live rows” does not imply “table requires little maintenance.” Version churn matters.
Why Deletes Create Cleanup Work Too
A DELETE changes which snapshots should see the row, but it does not necessarily erase all historical bytes immediately. Older snapshots can still require the deleted row. Index entries can also need cleanup after the row becomes globally dead.
Therefore, deleting half a table can make current queries return half as many rows while the operating system sees little or no immediate reduction in file size. Logical absence and physical reclamation are separate events.
PostgreSQL’s ordinary VACUUM generally makes dead space reusable inside the relation rather than returning every freed page to the operating system. Its documentation notes that space can sometimes be truncated from the physical end of a relation, while VACUUM FULL rewrites the table more aggressively and is much more disruptive.
Ordinary VACUUM and VACUUM FULL Are Different Operations
Ordinary PostgreSQL VACUUM identifies removable tuples, updates visibility information and makes space available for reuse. It is designed for routine maintenance while the table remains in normal service.
VACUUM FULL rewrites the table into a new copy and takes a stronger lock. It can return more space to the operating system, but it needs additional temporary disk capacity and causes greater disruption.
This is an important operational lesson: “vacuum” is not one uniform event. A routine cleanup pass and a full rewrite have different lock, I/O, space and availability consequences. Diagnose the actual problem before choosing the most dramatic command.
Autovacuum Is a Scheduler, Not a Magic Guarantee
PostgreSQL includes autovacuum to schedule routine vacuum and analyze work. This reduces the need for manual maintenance, but it does not abolish capacity planning. If the workload generates dead tuples faster than maintenance can process them, the backlog can still grow.
Autovacuum also follows thresholds and per-table settings. A large table with a particular update pattern can need tuning different from a tiny rapidly changing table. The right observation is not “autovacuum is enabled,” but “is maintenance completing fast enough to keep dead history and transaction-age risk under control?”
Measure the queues and outcomes: dead tuples, oldest transaction age, vacuum frequency and duration, I/O impact, index cleanup, reclaimed pages and whether table growth stabilises after the workload returns to normal.
The Oldest Snapshot Can Define the Reclamation Boundary
Suppose transactions A, B and C hold snapshots at logical times 100, 140 and 180. The latest database state is 250. If version V can still be seen by A, it remains protected even though B, C and every new reader would choose a newer version.
This makes the oldest relevant reader disproportionately important. One long-running transaction can force the database to retain history generated by many newer transactions.
PostgreSQL documentation warns that long-running transactions can hold back VACUUM’s removable cutoff. The transaction need not be actively scanning the table at this moment; its open snapshot can be enough to preserve old versions.
A Read-Only Transaction Can Cause Write-Side Storage Pressure
This is one of the most useful counterintuitive lessons in MVCC. A read-only report can block no writers and still make the write-heavy part of the system retain more history.
Imagine a dashboard transaction opened in the morning and accidentally left uncommitted for six hours. During those six hours, another service updates thousands of rows repeatedly. The report session may use little CPU, but its snapshot can keep older versions from becoming reclaimable.
Operationally, monitor long-lived transactions as retention obligations, not only as active CPU consumers. “Idle in transaction” can be expensive precisely because the session is doing almost nothing while preventing the database from forgetting the past.
Cleanup Needs a Global Safety Proof, Not a Local Pairing Rule
Suppose V3 supersedes V2, and both appear on the same page. It can be tempting to delete V2 immediately because a newer version is adjacent. That is safe only if no supported snapshot can still need V2.
The proof depends on transaction visibility beyond that page. A reader elsewhere in the system can still hold a snapshot whose correct answer is V2. Local physical proximity does not establish global obsolescence.
This resembles LSM tombstone safety: maintenance must reason about surviving readers and surviving older data outside the local object being processed. Cleanup is a global semantic decision implemented through local physical work.
Index Cleanup Follows Row-Version Cleanup
An index can contain entries pointing to tuple versions that have become dead. Removing the heap version may therefore create corresponding index-maintenance work.
PostgreSQL’s current VACUUM documentation includes an INDEX_CLEANUP option and explains that index cleanup can sometimes be skipped when few dead tuples exist, while forcing or suppressing that work changes the maintenance trade-off.
The larger principle is that garbage collection spans all structures that represent the old logical state. A database is not cleaned merely because one heap tuple disappeared. Indexes, undo history, visibility maps and other metadata can have their own reclamation lifecycle.
Visibility Maps Make Some Reads and Maintenance Cheaper
PostgreSQL maintains a visibility map that can record pages whose tuples are known visible to all current and future transactions under relevant rules. That information helps vacuum skip some work and can enable index-only scans to avoid heap visibility checks for qualifying pages.
This is a good example of derived metadata accelerating a proof. The database has already established a page-level visibility property, so later operations can avoid repeating every tuple-level check.
Derived metadata itself must remain trustworthy. PostgreSQL exposes maintenance options for situations where the visibility map is suspected, but ordinary systems should rely on the engine’s normal protocol rather than manually altering internal files.
Freezing Handles the Transaction-ID Lifecycle
PostgreSQL’s traditional transaction IDs wrap around because the on-disk xid is 32 bits. Very old tuple visibility therefore cannot depend forever on remembering an ancient ordinary transaction ID as though identifiers were infinitely increasing.
Vacuum performs freezing work so old tuples can remain safely visible without being mistaken for transactions from a future wraparound cycle. PostgreSQL’s documentation treats anti-wraparound vacuum as a correctness requirement, not an optional space optimisation.
This is why a vacuum process can be urgent even when disk space looks fine. One maintenance job reclaims dead versions; another part of the same maintenance family protects transaction-ID interpretation over the lifetime of the database.
Transaction-ID Wraparound Is a Correctness Risk, Not Merely Bloat
If wraparound were ignored, very old transaction IDs could become ambiguous relative to newly reused identifiers. The system could no longer reliably decide which transactions happened before which others.
PostgreSQL therefore tracks transaction age and forces anti-wraparound maintenance when necessary. Its safeguards are designed to preserve visibility semantics even if ordinary tuning would prefer to postpone work.
For administrators, do not disable or indefinitely defer anti-wraparound maintenance to make a benchmark look smoother. A benchmark that wins by accumulating correctness debt is not measuring a sustainable database configuration.
InnoDB Purge Shows the Same Responsibility Through a Different Mechanism
InnoDB consistent reads reconstruct older versions using undo information. When old read views no longer require certain undo records, purge machinery can remove obsolete history according to InnoDB’s rules.
The physical shape differs from PostgreSQL’s dead heap tuples. Yet the high-level question is the same: what is the oldest historical state a current reader can still require?
When comparing engines, therefore, do not compare PostgreSQL dead-tuple count directly with an InnoDB undo metric as though they were the same unit. Compare the responsibility: retained history, oldest read view, cleanup throughput and the effect on foreground work.
Cleanup Bandwidth Competes With Foreground Work
Vacuum and purge consume CPU, storage reads, storage writes, cache capacity and sometimes lock resources. Running them too slowly lets debt accumulate. Running them too aggressively can hurt foreground latency.
This is a queueing problem. If the workload creates reclaimable history at an average rate of 40 MB per second while maintenance can process only 30 MB per second, the backlog grows by about 10 MB per second under that simplified accounting.
The numbers are illustrative, not benchmark claims. The model matters because it exposes the long-run condition: maintenance capacity must eventually meet or exceed the work generated, or the system needs admission control, workload changes or a different configuration.
Larger Tables Can Hide Cleanup Debt for Longer
A database can continue serving acceptable queries while dead history slowly accumulates. Free disk space acts like a buffer. That makes the problem easy to miss until table scans, index behaviour, cache pressure or storage headroom cross a threshold.
Capacity is therefore not only “how many live rows can fit?” It is also “how much temporary and historical state can exist during normal maintenance and unusual bursts?”
This same reasoning appeared in the LSM compaction cluster. Background maintenance creates debt whenever the system postpones physical reorganisation. MVCC vacuum debt and LSM compaction debt are different structures but share the operational need for sustainable cleanup throughput.
Why Ordinary VACUUM Often Does Not Shrink the File
When ordinary vacuum makes dead space reusable, future inserts or updates can consume that space. Returning every free region immediately to the operating system would require more invasive file reorganisation and can be counterproductive for tables that will continue changing.
Therefore, a stable table file size after vacuum can be healthy. The right question is whether reusable space is available internally and whether future growth stabilises, not whether the file timestamp or operating-system size changed dramatically after each maintenance run.
VACUUM FULL is appropriate only for specific cases where reclaiming file size justifies the rewrite and locking costs. It should not be a routine reflex for normal MVCC history.
Bulk DELETE and TRUNCATE Have Different Semantics
Deleting every row one by one creates ordinary row-deletion history and corresponding cleanup work. TRUNCATE is a stronger table-level operation that can discard the contents through a different mechanism and locking contract.
PostgreSQL documentation notes that TRUNCATE can be useful when an entire table is periodically emptied, but its MVCC behaviour differs from ordinary DELETE. Applications should not substitute it blindly when concurrent snapshots or foreign-key relationships matter.
This is another example of matching the operation to the business contract. “Make this table empty” can be implemented through mechanisms with very different concurrency and recovery semantics.
Maintenance Can Be Correct and Still Operationally Late
A vacuum worker can correctly identify removable tuples and still fail to keep up with the workload. Correctness and capacity are separate axes.
Look for signs such as rising dead-tuple counts, repeated emergency or anti-wraparound activity, increasing table size without equivalent live-data growth, longer cleanup durations and persistent old transactions.
A good diagnosis identifies whether the problem is insufficient maintenance service, too much version generation, a retained-snapshot blocker, an index-cleanup burden or a one-time bulk change. “Vacuum is slow” is only the symptom.
Long Transactions Can Be Correct and Still Be Expensive
A six-hour analytical transaction may genuinely require one consistent view. The answer is not automatically “kill every transaction older than five minutes.” The application requirement can be legitimate.
Instead, make the cost explicit. Could the report use an exported snapshot with bounded workers? Could it run on a replica whose retention model is appropriate? Could the application materialise a reporting dataset? Could the transaction release its snapshot earlier without violating the report’s meaning?
Engineering begins by respecting the required semantics, then finding the lowest-cost mechanism that satisfies them. Maintenance pressure is evidence to redesign the workflow, not permission to silently weaken correctness.
A Small Reclamation Laboratory
Use a disposable test table with one row. Start transaction A at a stable snapshot and read the row. In another session, update the row many times and commit each change.
Observe version or maintenance statistics before and after ordinary vacuum while A remains open. Then commit A and run maintenance again. The goal is to see how an old snapshot changes what the engine considers reclaimable.
Never infer production thresholds directly from a tiny lab. The experiment demonstrates the causal relationship between snapshot age and historical retention. Production behaviour depends on table size, indexes, autovacuum settings, transaction volume and hardware.
A Space-Reuse Laboratory
Populate a disposable table, delete or update many rows, run ordinary vacuum and record the table’s logical row count, database-reported relation size and later growth after inserting new rows.
You may see the operating-system-level file size stay similar while new rows reuse space internally. This is a useful counterexample to the assumption that successful garbage collection must always visibly shrink the file.
If you also test VACUUM FULL, record the stronger lock and rewrite behaviour. The two operations solve different physical-space problems even though both contain the word vacuum.
Do Not Manually Delete Database Files to “Clean Up Dead Tuples”
Database files participate in internal catalogs, page structures, transaction metadata and recovery protocols. A file that appears old or large to an administrator can still be part of the live relation.
Use supported maintenance commands and product-specific repair procedures. Filesystem cleanup that bypasses the engine can transform harmless bloat into corruption and data loss.
The same rule applied to LSM SSTables: the live database is defined by the engine’s metadata and lifecycle, not by a human guess based on filenames or modification times.
Diagnose Reclamation Pressure With Five Questions
- How fast is history being created? Updates, deletes, aborted work and transaction churn.
- How old is the oldest relevant snapshot or transaction? This can define the safe cutoff.
- How fast is maintenance completing? Measure actual vacuum or purge progress.
- Where is the unreclaimed space? Heap, indexes, undo, temporary rewrite files or pinned old relations.
- What guarantee requires the history to remain? Active transaction, backup, replica, temporal feature or accidental idle session?
These questions identify mechanism before remedy. Raising every threshold can postpone a visible symptom while increasing the amount of historical state the system must carry.
Frequently Asked Questions
Does VACUUM delete current rows?
Routine vacuum removes or recycles row versions that the engine has determined are no longer needed under its MVCC and maintenance rules. It is not meant to remove currently visible application rows.
Why does a deleted row still occupy space?
An older snapshot may still need the row, or cleanup may not have run yet. Even after cleanup, ordinary vacuum can make space reusable internally without shrinking the relation file immediately.
Can a SELECT cause table bloat?
A long-lived transaction snapshot can hold back reclamation of versions generated by other transactions. The SELECT itself does not create those versions, but its snapshot can require them to remain.
Is VACUUM FULL just a stronger normal vacuum?
It is a different, more invasive table rewrite with stronger locking and extra temporary space requirements. Routine maintenance should normally use ordinary vacuum unless a specific rewrite need justifies the cost.
Why does PostgreSQL care about transaction-ID age?
Traditional transaction IDs wrap. Freezing and anti-wraparound maintenance preserve correct visibility when the database has processed very large numbers of transactions.
Is vacuum the same as LSM compaction?
No. Both reclaim obsolete history, but they operate on different storage architectures and invariants. LSM compaction merges immutable sorted runs; MVCC vacuum or purge reclaims row-version history under transaction-visibility rules.
Sources and Scope
The main references are PostgreSQL’s current routine vacuuming, VACUUM command, MVCC introduction, visibility map and transaction ID documentation, plus MySQL InnoDB’s multi-versioning documentation. Queue rates, transaction numbers and camera versions in this article are teaching examples, not measured production results.
Final Synthesis: MVCC Needs Permission to Forget
Creating history is easy: every update can leave an older state behind. Reclaiming history is harder because the database must prove that no valid reader can still need it.
That is the heart of MVCC garbage collection. Track the oldest visibility obligation, retire versions only after that obligation disappears, keep maintenance throughput sustainable, and distinguish reusable internal space from operating-system file shrinkage. A healthy MVCC system does not merely remember the past. It knows exactly when the past is safe to forget.