What Is Database Vacuuming? Why Postgres Needs It

Vacuuming is a maintenance process, most closely associated with PostgreSQL, that reclaims storage occupied by rows a database no longer needs but hasn’t physically removed yet. It exists because of how many databases handle updates and deletes internally: rather than modifying a row in place, they often leave the old version behind and mark it obsolete, which means “delete” doesn’t actually free space until something comes along and cleans up afterward. That something is the vacuum process.

Why rows don’t just disappear

Postgres uses a concurrency model called MVCC — multiversion concurrency control — where updating a row doesn’t overwrite it in place. Instead, Postgres writes an entirely new row version and marks the old one as no longer current. This is what lets concurrent transactions each see a consistent snapshot of the data without blocking each other: a long-running read can keep seeing an old row version even while a writer creates a new one. It’s a deliberate tradeoff, not an oversight, and it’s part of what makes the isolation guarantees discussed in our database isolation levels guide possible without heavy locking.

The cost of that tradeoff is that old row versions — Postgres calls them “dead tuples” — pile up as normal operations happen. A DELETE doesn’t reclaim space; it just marks a row dead. An UPDATE is effectively a delete of the old version plus an insert of a new one. Left unaddressed, a table’s on-disk size keeps growing even if its actual row count stays flat, a condition generally called table bloat.

What vacuum actually does

The vacuum process scans a table, identifies dead tuples that no active transaction could possibly still need to see, and marks that space as reusable by future inserts and updates on the same table. Critically, standard vacuum doesn’t shrink the file on disk in most cases — it makes freed space available for reuse within the table’s existing file, rather than returning it to the operating system. A separate, more disruptive operation (VACUUM FULL) rewrites the table entirely to actually shrink the file, at the cost of an exclusive lock for the duration.

Vacuum also updates statistics used by the query planner and, critically, prevents a specific and more serious failure mode described below.

Autovacuum: doing this automatically

Modern Postgres runs an autovacuum background process by default, watching tables for enough dead-tuple activity to trigger a vacuum without manual intervention. For most workloads, autovacuum with reasonable tuning is sufficient and manual vacuuming is unnecessary — the failure mode people actually run into is autovacuum being effectively disabled or unable to keep up, whether through misconfigured thresholds, a table so large and high-churn that vacuum can’t complete before more bloat accumulates, or a long-running transaction holding back the point up to which dead tuples can be safely reclaimed.

That last case is a common, avoidable trap: a transaction left open for hours (an idle connection that started a transaction and never committed, for instance) prevents vacuum from cleaning up any row versions that transaction might still be able to see, no matter how old they are, letting bloat accumulate across the whole table in the meantime.

The more serious reason vacuum exists: transaction ID wraparound

Beyond reclaiming space, vacuum performs a function that isn’t optional to skip indefinitely: Postgres identifies row versions using a transaction ID counter that is finite and does eventually wrap around. Vacuum “freezes” old row versions, marking them as visible to all transactions regardless of ID comparison, which is what allows the counter to safely wrap without confusing old and new data. If autovacuum is disabled or unable to keep up for long enough that the transaction ID counter gets dangerously close to wraparound, Postgres will forcibly shut down further writes to protect data integrity — a rare but serious operational incident, and one of the few good reasons to actively monitor vacuum activity in production rather than assuming autovacuum has it fully handled.

Vacuuming and write-ahead logging

Vacuum’s activity, like any other write to the database, goes through the same write-ahead log mechanism Postgres uses for durability generally — it’s not a special out-of-band process from the storage engine’s point of view, just another source of writes that get logged before being applied.

Does every database need this?

Not identically. SQLite has its own VACUUM command, but it works differently — rebuilding the database file to reclaim space rather than Postgres’s incremental in-place reuse — and doesn’t carry the transaction ID wraparound concern, since SQLite doesn’t use the same MVCC transaction ID scheme. MySQL’s default InnoDB storage engine handles old row versions differently as well, via its undo log and purge threads rather than a directly analogous vacuum process. The underlying issue — reclaiming space left behind by updates and deletes under an MVCC model — is common across many databases; the specific mechanism and its failure modes are not. See PostgreSQL vs MySQL for more on where the two diverge architecturally.

The takeaway

Vacuuming reclaims space left behind by Postgres’s MVCC model, where updates and deletes leave old row versions in place rather than removing them immediately. Autovacuum handles this automatically for most workloads, but long-running transactions and misconfigured thresholds can let bloat accumulate, and letting the underlying transaction ID counter approach wraparound is a genuine (if rare) production risk rather than just a performance nuisance. It’s not a Postgres-specific quirk to route around — it’s the maintenance cost of the concurrency model that makes Postgres’s isolation guarantees possible in the first place.