VACUUM
VACUUM is the collector for the garbage MVCC leaves behind: it removes dead tuples and marks their space reusable, but reusable inside the file. What it almost never does is make the file smaller. This lesson proves both halves.
What VACUUM actually does to a page
Three updates leave three dead tuples on the page (the bloat lesson showed the chain).
A> UPDATE counters SET n = 1 WHERE id = 1;
UPDATE 1
A> UPDATE counters SET n = 2 WHERE id = 1;
UPDATE 1
A> UPDATE counters SET n = 3 WHERE id = 1 RETURNING xmin, n;
xmin | n
------+---
1001 | 3
(1 row)
A> VACUUM counters;
VACUUMAfter VACUUM: slot 1 is a redirect to the live version, slots 2–3 are unused (reusable), only slot 4 still holds a tuple. The corpses are gone.
A> SELECT lp, lp_flags, t_xmin, t_xmax, t_ctid
FROM heap_page_items(get_raw_page('counters', 0)) ORDER BY lp;
lp | lp_flags | t_xmin | t_xmax | t_ctid
----+----------+--------+--------+--------
1 | 2 | | |
2 | 0 | | |
3 | 0 | | |
4 | 1 | 1001 | 0 | (0,4)
(4 rows)The freed space is immediately reusable: the next INSERT lands in slot 2.
A> INSERT INTO counters VALUES (2, 0) RETURNING ctid, id;
ctid | id
-------+----
(0,2) | 2
(1 row)Same story at file level — 1000 rows in 5 pages, doubled to 9 by an UPDATE of every row.
A> SELECT (pg_relation_size('bloat') / 8192)::int AS pages;
pages
-------
5
(1 row)
A> UPDATE bloat SET filler = 'y';
UPDATE 1000
A> SELECT (pg_relation_size('bloat') / 8192)::int AS pages;
pages
-------
9
(1 row)
A> VACUUM bloat;
VACUUMVACUUM cleaned 1000 dead tuples — and the file is still 9 pages. The space is free inside the file.
A> SELECT (pg_relation_size('bloat') / 8192)::int AS pages;
pages
-------
9
(1 row)
A> VACUUM FULL bloat;
VACUUMVACUUM FULL rewrites the table from scratch — back to 5 pages. The price: it holds ACCESS EXCLUSIVE for the whole rewrite.
A> SELECT (pg_relation_size('bloat') / 8192)::int AS pages;
pages
-------
5
(1 row)Verified against PostgreSQL 18.4 · Run it yourself · Scenario source
Reading the after picture (lp_flags values aren't in the manual — the pageinspect docs point at src/include/storage/itemid.h): 1 is a normal tuple, 2 is a redirect — the chain's entry point now jumps straight to the live version — and 0 is unused, free for the taking. The very next INSERT proves the point by landing in slot 2, previously a corpse. (The redirect is a leftover of a HOT update chain: updates that don't touch indexed columns link versions within the page, and index entries keep pointing at the root slot.)
Why your table didn't shrink
The manual says it plainly: the standard form of VACUUM "removes dead row versions in tables and indexes and marks the space available for future reuse. However, it will not return the space to the operating system, except in the special case where one or more pages at the end of a table become entirely free and an exclusive table lock can be easily obtained". VACUUM FULL is the real shrink — it "actively compacts tables by writing a complete new version of the table file with no dead space" — but it holds ACCESS EXCLUSIVE for the whole rewrite: a full outage on that table, plus double the disk while it runs. On a big production table that's rarely acceptable; steady autovacuum that never lets bloat build up beats heroic compaction.
So the division of labor is the thing to remember: VACUUM reclaims for reuse, VACUUM FULL reclaims for the OS. Day to day you want the former, running continuously via autovacuum, and you reserve VACUUM FULL for a genuine one-off disaster whose outage you can schedule. There's no good reason to switch autovacuum off — it takes only a weak SHARE UPDATE EXCLUSIVE lock and runs happily alongside reads and writes, and every "autovacuum is killing us" story ends with a table that needed it more, not less. A table that's bloated but recycling in place is perfectly healthy: new versions reuse the freed slots and the size plateaus. Size that climbs without limit means vacuum can't keep up — the first thing to check is a long transaction pinning the horizon, which is the next lesson.