VACUUM
PostgreSQL 18 documents standard VACUUM as reclaiming obsolete versions for reuse. It can return wholly empty trailing heap pages when the required exclusive lock is available. VACUUM FULL instead rewrites the relation to compact it. Neither observation here promises an exact size for arbitrary tables.
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)At file level this 1000-row heap grows from 5 to 9 pages after UPDATE.
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;
VACUUMStandard VACUUM completes and the asserted file size remains 9 pages. This size observation does not count reclaimed tuples or reusable bytes.
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.6 · Run it yourself · Scenario source
The asserted counters page has a redirect at slot 1, unused slots 2 and 3, and a live tuple at slot 4. The next INSERT asserts ctid (0,2), showing reuse in this setup. pageinspect exposes those fields; the PostgreSQL 18 item-id definitions define flags 0 (unused), 1 (normal) and 2 (redirect). HOT requires sufficient page space and no changed indexed columns except summarizing indexes; this is not a rule that every non-indexed UPDATE makes the same chain layout.
Why your table didn't shrink
In bloat the asserted size is nine pages after standard VACUUM and five after VACUUM FULL. The nine-page result alone does not count reclaimed tuples or measure all reusable space. The counters observation demonstrates reclamation separately.
VACUUM FULL requires ACCESS EXCLUSIVE and extra storage for the new copy until the rewrite finishes. Ordinary reads and writes from other transactions cannot acquire their table locks while that lock is held. Exact extra storage, rewrite duration and production acceptability depend on the relation and workload; no “double disk” benchmark is established here.
Standard VACUUM uses SHARE UPDATE EXCLUSIVE, compatible with ordinary table reads and writes but conflicting with some maintenance and DDL. Its optional trailing-page truncation can require ACCESS EXCLUSIVE. Autovacuum performs standard vacuuming, not VACUUM FULL. Regular maintenance can promote reuse, but neither stable table size nor zero blocking is a universal guarantee. Check old snapshots and other retention conditions when cleanup cannot reclaim the needed versions.