Skip to content

CONCEPT Cited by 1 source

VACUUM FULL (Postgres)

VACUUM FULL is Postgres's compaction mechanism for tables that have accumulated too many dead row versions under MVCC. It purges versions old enough that no running transaction can still need them, then rewrites the entire table to reclaim the space.

Why it's needed

Postgres's MVCC design (see concepts/xmin-xmax-row-versioning) creates a new row version on every UPDATE rather than overwriting in place. Over time a heavily-updated table accumulates thousands of duplicated row versions — dead tuples — that serve no live transaction. From Ben Dicken's PlanetScale explainer (Source: sources/2026-02-22-planetscale-what-is-a-database-transaction): "Over time, we can end up with thousands of duplicate rows that are no longer needed."

Dead tuples still occupy disk pages, slow down sequential scans, and dilute cache locality. Left alone they turn a working set that fits in RAM into one that doesn't.

What it does

When VACUUM FULL runs it:

  1. Identifies obsolete row versions — any version whose xmax is older than the MVCC horizon (the oldest transaction ID still running and able to need old data).
  2. Purges them.
  3. Compacts the table — rewrites the remaining live tuples into a fresh, densely-packed heap file, reclaiming the unused disk space to the OS.

The result: no gaps, no dead tuples, minimum disk footprint.

Cost

VACUUM FULL takes an ACCESS EXCLUSIVE lock on the target table for the duration of the rewrite, blocking all reads and writes. It also requires enough free disk space to hold a second copy of the table while rewriting. In practice this makes it an offline-style maintenance operation on production, typically scheduled or triggered only when the lighter-weight incremental autovacuum has fallen behind and bloat is already pathological.

Autovacuum vs. VACUUM FULL

  • autovacuum / plain VACUUM — incremental, runs in the background, does not rewrite the table. Marks dead-tuple space as reusable for future inserts but does not return space to the OS.
  • VACUUM FULL — the heavy hammer: takes an exclusive lock, rewrites the whole table, returns space to the OS.

Routine operation relies on autovacuum. VACUUM FULL is a rare recovery operation for when bloat has grown past the point of graceful handling.

Contrast with MySQL

MySQL does not need an equivalent compaction pass: because updates overwrite in place and old values live only in the undo log (see concepts/mysql-undo-log), there is no accumulation of dead tuples on the main heap to vacuum. The undo log itself is naturally reclaimed when no transaction needs it.

Seen in

Last updated · 517 distilled / 1,221 read