PostgreSQL Vacuum Analyze vs VACUUM FULL: Locks and Stats

Four commands, four different jobs. Measured on PostgreSQL 17.10:VACUUM t;— marks dead row versions reusableinsidethe table. Takes aSHARE UPDATE EXCLUSIVElock, so reads and writes keep working. Doesnotcollect 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 ContentsVACUUM reuses space; VACUUM FULL returns itThe locks, demonstrated rather than assertedStatistics, permissions and parallel vacuumAutovacuum already does thisVACUUM reuses space; VACUUM FULL returns itA 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)PlainVACUUMcleared 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 thefilenodecolumn.VACUUMleaves it alone;VACUUM FULLchanges 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 beVACUUM FULLed on that volume.One nuance the usual advice skips: plainVACUUMcanshrink 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 assertedWith one session holding an open transaction that has read the table (anAccessShareLock), a second session tried each command with a 3-secondlock_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 ANALYZESamplingpg_locksfrom a third session while each ran on a 489 MB table confirms the modes:AccessExclusiveLockforVACUUM FULL,ShareUpdateExclusiveLockfor plainVACUUMand forANALYZE. One idle-in-transaction session is enough to stallVACUUM FULLindefinitely, which is why it belongs in a maintenance window and plainVACUUMdoes not.Statistics, permissions and parallel vacuumVACUUM does not collect planner statistics; that is ANALYZE’s job.On a fresh 100,000-row table, a bareVACUUMleftpg_statscompletely empty andlast_analyzeNULL;VACUUM ANALYZEthen 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 refreshpg_class.reltuplesandrelpages(−1 → 100000), which the planner reads. It does not build the value distributions that drive join and index choices. RunningVACUUMand expecting better plans will not work.Since PostgreSQL 16, you do not need to be a superuser or the owner of the database to vacuum a table.A role that is neither superuser nor database owner was refused, then granted only theMAINTAINprivilege 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 before3 after4Worth knowing that the refusal is awarning, not an error —VACUUMexits 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 theparallel_workerstable setting.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 bymax_parallel_maintenance_workersand the command’s ownPARALLEL noption. It is also unavailable forVACUUM FULL: VACUUM (FULL, PARALLEL 2) ERROR: VACUUM FULL cannot be performed in parallelAutovacuum already does thisThe shipped defaults on 17.10, read frompg_settingsrather 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 withWITH (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.Which command reclaims space, which one a reader blocks, who may runVACUUMand when autovacuum fires are all asserted by optimize-tables-vacuum-analyze.sh against a PostgreSQL server it sets up on port 55511 and tears down when it finishes, connecting to nothing else.ManualVACUUMearns 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 scheduledVACUUM FULLis 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 itiskeeping up, note thatVACUUM FULLdoes not updatelast_vacuumorvacuum_count— confirmed again here, both unchanged across a full rewrite. Finding when vacuum and analyze last ran covers that view and its other traps.