In PostgreSQL, what does VACUUM FULL do that plain VACUUM does not, and what does it cost while it runs?
answer
- space back to the OS, or not
- a brand-new copy of the table
- every index rebuilt too
- the strongest table lock there is
- free disk for the copy
basics
~20 sPlain VACUUM makes dead-row space reusable inside the table's existing files while reads and writes continue. VACUUM FULL writes a compacted new copy of the table and rebuilds its indexes, returning space to the OS, but blocks all access and needs extra disk.
solid answer
~50 sPlain `VACUUM` removes dead row versions and records the freed space in the free space map, so later inserts and updates in the same table reuse it. It takes only a `SHARE UPDATE EXCLUSIVE` lock, so queries keep running, and it gives space back to the operating system only when pages at the very end of the table become empty. `VACUUM FULL` instead copies every row that is live, or still visible to an open snapshot, into a brand-new file, rebuilds every index, and deletes the old files at commit. That shrinks the table to its minimum, but it holds an `ACCESS EXCLUSIVE` lock for the whole run, so even `SELECT` waits, and it needs free disk for the new table and indexes until it finishes. Autovacuum never runs it: it is a one-off repair after an event such as a mass update or delete.
code
sql · 6 lines-- Run outside a transaction block, in a maintenance window
SELECT pg_size_pretty(pg_total_relation_size('payroll_ledger')) AS total_before;
VACUUM (FULL, VERBOSE) payroll_ledger;
SELECT pg_size_pretty(pg_total_relation_size('payroll_ledger')) AS total_after;go deeper
Recall the split: plain VACUUM makes space reusable without blocking anyone, while VACUUM FULL rewrites the table to shrink it and locks everyone out while it runs.
Explain the mechanics: a new file holding only rows still needed, every index rebuilt, files swapped at commit, and why that needs both an ACCESS EXCLUSIVE lock and free disk for the copy.
Show judgment: when a one-off VACUUM FULL in a window is fine, why it fails to shrink while an old snapshot is open, and when to reach for an online rewrite instead.
Treat a table that needs VACUUM FULL as a symptom: churn outpaced vacuum, so the real decision is workload, fillfactor or autovacuum policy that keeps rewrites rare.
## Two commands with different goals PostgreSQL never overwrites a row in place: an `UPDATE` or `DELETE` leaves the old row version behind as a **dead tuple** until vacuum removes it. Plain `VACUUM` and `VACUUM FULL` both deal with dead tuples, but they solve different problems. - **Plain `VACUUM`** removes dead tuples, records the freed space in the table's **free space map** and normally leaves the file the same size, so later inserts and updates in that table reuse the space. It runs alongside normal traffic. - **`VACUUM FULL`** builds a new, compacted copy of the table and swaps it in. It is the one that makes the table physically smaller and hands the freed bytes back to the operating system. How plain `VACUUM` and autovacuum are scheduled and tuned is a separate subject; the point here is what `VACUUM FULL` adds and what it costs. | | Plain `VACUUM` | `VACUUM FULL` | |---|---|---| | Table lock | `SHARE UPDATE EXCLUSIVE`: reads and writes continue | `ACCESS EXCLUSIVE`: even `SELECT` waits | | Freed space | Reused inside the table; only trailing empty pages are truncated | Returned to the operating system | | Indexes | Dead entries removed, index files keep their size | Every index rebuilt from scratch | | Extra disk | Negligible | Room for the new table and its indexes until commit | | Progress view | `pg_stat_progress_vacuum` | `pg_stat_progress_cluster` | | Run by autovacuum | Yes | Never | ## What VACUUM FULL does, step by step 1. It requests an **`ACCESS EXCLUSIVE`** lock, which conflicts with every other lock mode, so it first waits for every running query on the table to finish. 2. It creates a new, empty heap file and copies into it, in physical order, every tuple that is live or still visible to some open snapshot. Tuples are frozen as they are copied, which is why the `FREEZE` option is redundant with `FULL`. 3. The table's TOAST data is rewritten the same way. 4. It swaps the new files in under the same table OID and **rebuilds every index** on the table. 5. At commit the old files are deleted and the space goes back to the operating system. With `VERBOSE`, the summary line reports how many row versions were `removable` and `nonremovable`, plus how many `dead row versions cannot be removed yet`. ## The costs - **The table is unavailable.** Reads and writes queue for the whole run, which on a large table can be hours. A `VACUUM FULL` that is still waiting for its lock also holds up the queries that arrive after it. - **Disk space.** The old files cannot be released until the new table and indexes are complete; the manual says to expect extra space roughly equal to the table's size. On a nearly full volume, `VACUUM FULL` can fail at the moment you most need it. - **I/O and WAL.** The whole table is read and written and every index rebuilt. With the default `wal_level = replica` the new files are WAL-logged, so `pg_wal`, the WAL archive and every standby absorb the rewrite too. - **Open snapshots limit the shrink.** A tuple deleted after the oldest open transaction began is still visible to it and must be copied, so a long-running transaction can leave the new table nearly as big as the old one. - **No transaction block.** Like every form of `VACUUM`, it cannot run inside `BEGIN ... COMMIT`. ## When it is the right tool `VACUUM FULL` is a one-off repair, not maintenance. Autovacuum never issues it, and the manual recommends frequent plain vacuums over occasional full ones for heavily updated tables: a table under constant churn simply grows back to its steady-state size, so rewriting it buys nothing. It earns its place when: - a one-off event, such as a backfill that rewrote every row of a payroll ledger or a purge of old pay periods, left far more free space than normal traffic will reuse; - the table is small enough, or the maintenance window long enough, for an exclusive lock to be acceptable; - you have measured the bloat first, for example with the `pgstattuple` extension, rather than guessing from the file size. When the table must stay readable and writable during the rewrite, core PostgreSQL has no online form of it: `CLUSTER` takes the same `ACCESS EXCLUSIVE` lock. The usual answer is the third-party `pg_repack` extension, which needs that lock only briefly at the start and at the end.
- Why can VACUUM FULL in PostgreSQL finish and leave the table barely smaller than before?Because it must copy every tuple that some open snapshot can still see. Rows deleted or superseded after the oldest running transaction started are only recently dead, so they go into the new file; `VACUUM (FULL, VERBOSE)` reports them as `dead row versions cannot be removed yet`. End the long transaction or idle-in-transaction session first, then rerun; otherwise you pay for the lock and the rewrite and get little back.
- Does plain VACUUM ever give disk space back to the operating system in PostgreSQL?Yes, but only from the end of the table. If the last pages become completely empty, `VACUUM` truncates them, taking an `ACCESS EXCLUSIVE` lock for the truncation; the `TRUNCATE` option of `VACUUM` and the `vacuum_truncate` table storage parameter can switch that off. Free space in the middle of the file stays inside the table for reuse.
Plain VACUUM is like crossing out settled lines in a ledger notebook so new entries can be written in their place: the notebook keeps all its pages. VACUUM FULL copies only the lines still in use into a new, thinner notebook and throws the old one away, and nobody may read or write the ledger while the copying is under way.
- VACUUM FULL runs online and only slows queries down a little
- Plain VACUUM shrinks the table file right after rows are deleted
- Autovacuum switches to VACUUM FULL by itself when bloat gets high
- VACUUM FULL compacts the file in place, so it needs no spare disk
- Scheduling VACUUM FULL nightly on busy tables keeps them healthy