Four commands, four different jobs. Measured on PostgreSQL 17.10:
VACUUM t;— marks dead row versions reusable inside the table. Takes aSHARE UPDATE EXCLUSIVElock, so reads and writes keep working. Does not collect planner statistics.ANALYZE t;— collects the column statistics the query planner uses. Reclaims nothing. Same lock.VACUUM ANALYZE t;— both in one pass, and the one to run by hand.VACUUM FULL t;— rewrites the table into a new file and returns space to the OS. Takes anACCESS EXCLUSIVElock, so nothing can touch the table while it runs, and needs free disk space roughly the size of the table.
If you remember one thing: on a default install you probably need none of them. Autovacuum is on out of the box and, in the test below, cleaned a scratch table 37 seconds after it went dirty, unprompted.
Table of Contents
VACUUM reuses space; VACUUM FULL returns it
A 300,000-row table with every second row deleted, so the dead tuples are spread through the file and cannot simply be chopped off the end:
=== after deleting every 2nd row (before any VACUUM) ===
heap | total | filenode | n_live_tup | n_dead_tup
-------+-------+----------+------------+------------
69 MB | 75 MB | 16398 | 150000 | 150000
(1 row)
=== after plain VACUUM ===
VACUUM
heap | total | filenode | n_dead_tup
-------+-------+----------+------------
69 MB | 75 MB | 16398 | 0
(1 row)
=== after VACUUM FULL ===
VACUUM
heap | total | filenode | n_dead_tup
-------+-------+----------+------------
34 MB | 38 MB | 16405 | 0
(1 row)Plain VACUUM cleared all 150,000 dead tuples and the file stayed at 69 MB. That space is a free list, not waste. On a separate table, inserting 300,000 fresh rows after a vacuum consumed the reclaimed space and the file did not grow at all: 138 MB before, 138 MB after, 600,000 rows.
Note the filenode column. VACUUM leaves it alone; VACUUM FULL changes it (16398 → 16405), because it is a rewrite. That is the mechanism behind the disk-space warning: the PostgreSQL 17 manual says VACUUM FULL “requires extra disk space, since it writes a new copy of the table and doesn’t release the old copy until the operation is complete.” Both copies exist at once, so a table filling 60% of its volume cannot be VACUUM FULLed on that volume.
One nuance the usual advice skips: plain VACUUM can shrink the file when the dead space sits at the end. Deleting the top half of a 300,000-row table and vacuuming took it from 69 MB to 34 MB — trailing empty pages get truncated.
The locks, demonstrated rather than asserted
With one session holding an open transaction that has read the table (an AccessShareLock), a second session tried each command with a 3-second lock_timeout:
=== plain VACUUM while a reader holds ACCESS SHARE ===
VACUUM
=== VACUUM FULL while a reader holds ACCESS SHARE ===
ERROR: canceling statement due to lock timeout
=== ANALYZE while a reader holds ACCESS SHARE ===
ANALYZESampling pg_locks from a third session while each ran on a 489 MB table confirms the modes: AccessExclusiveLock for VACUUM FULL, ShareUpdateExclusiveLock for plain VACUUM and for ANALYZE. One idle-in-transaction session is enough to stall VACUUM FULL indefinitely, which is why it belongs in a maintenance window and plain VACUUM does not.
Three corrections to the earlier version of this page
“VACUUM plays a vital role in updating data statistics for the optimizer” — no. That is ANALYZE’s job. On a fresh 100,000-row table, a bare VACUUM left pg_stats completely empty and last_analyze NULL; VACUUM ANALYZE then populated it:
=== after bare VACUUM (no ANALYZE) ===
VACUUM
reltuples | relpages
-----------+----------
100000 | 443
(1 row)
rows_in_pg_stats
------------------
0
(1 row)
=== after VACUUM ANALYZE ===
VACUUM
rows_in_pg_stats
------------------
2
(1 row)
attname | n_distinct | most_common_vals
---------+------------+------------------------
grp | 7 | {g3,g1,g0,g4,g5,g6,g2}
(1 row)VACUUM does refresh pg_class.reltuples and relpages (−1 → 100000), which the planner reads. It does not build the value distributions that drive join and index choices. Running VACUUM and expecting better plans will not work.
“You need to either be a superuser or the owner of the database” — not since PostgreSQL 16. A role that is neither superuser nor database owner was refused, then granted only the MAINTAIN privilege on one table and succeeded:
--- as hw_vac_user, VACUUM without MAINTAIN:
WARNING: permission denied to vacuum "hw_vac_stats", skipping it
VACUUM
--- grant MAINTAIN, retry:
GRANT
VACUUM
--- did it actually vacuum?
vacuum_count before=3 after=4Worth knowing that the refusal is a warning, not an error — VACUUM exits 0 having silently skipped the table. A cron job vacuuming as an under-privileged role looks like it is working.
Parallel vacuum is not configured with the parallel_workers table setting. The earlier text said the feature is “enabled and configured by setting parallel_workers at the table level”. On a table with three indexes the worker count was identical whether that parameter was unset or set to 4 — launched 2 parallel vacuum workers for index vacuuming (planned: 2) either way. It tracked the index count, and happened unasked. Parallelism covers the index vacuum phases only, needs at least two indexes, and is bounded by max_parallel_maintenance_workers and the command’s own PARALLEL n option. It is also unavailable where the page implied it would help most:
=== VACUUM (FULL, PARALLEL 2) ===
ERROR: VACUUM FULL cannot be performed in parallelAutovacuum already does this
The shipped defaults on 17.10, read from pg_settings rather than recalled: autovacuum on, autovacuum_vacuum_threshold 50, autovacuum_vacuum_scale_factor 0.2, autovacuum_analyze_threshold 50, autovacuum_analyze_scale_factor 0.1, autovacuum_naptime 60, autovacuum_max_workers 3. A table declared with WITH (autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 50), dirtied with a 5,000-row UPDATE and then left alone:
autovacuum fired after 37s: 2026-08-10 13:10:11.412763-05
last_autovacuum | autovacuum_count | n_dead_tup
-------------------------------+------------------+------------
2026-08-10 13:10:11.412763-05 | 1 | 0Two repeat runs fired at 73 and 88 seconds; the delay varies with the 60-second naptime.
Manual VACUUM earns its place after a bulk load or a mass delete, when you do not want to wait a naptime for the cleanup and statistics to catch up. Routine scheduled VACUUM FULL is close to always wrong: it blocks the application, and if autovacuum is keeping up there is nothing for it to reclaim. When the bloat is in an index rather than the table, REINDEX CONCURRENTLY rebuilds it without the exclusive lock — a narrower tool for the narrower problem.
If you are checking whether it is keeping up, note that VACUUM FULL does not update last_vacuum or vacuum_count — confirmed again here, both unchanged across a full rewrite. Finding when vacuum and analyze last ran covers that view and its other traps.
Reproducing this
verify-optimize-tables-vacuum-analyze.sh builds a temporary cluster on port 55511, checks every claim above, and deletes the cluster on exit. It touches no existing database. On 17.10 it reports 26 passed, 0 failed.
Check this yourself. Every command and every block of output on this page is reproduced by /verify/optimize-tables-vacuum-analyze.sh. Download it and run it: it creates its own scratch files, prints one line per claim, cleans up after itself, and exits non-zero if any claim here turns out to be wrong. If it disagrees with this page, the page is wrong.
