VACUUM FULL is a rewrite, not a vacuum

VACUUM FULL writes a new copy of a table containing only its live rows, rebuilds every index on it, then swaps the new files in and drops the old ones. Routine vacuum, by contrast, edits the existing files in place and returns nothing to the filesystem. Sharing a keyword makes them sound like the same operation at two intensities. They have different locks, different disk requirements, different durations and different side effects, and only one of them is safe to schedule.

The three costs nobody mentions until afterwards

It takes an ACCESS EXCLUSIVE lock for the whole rewrite, which blocks reads as well as writes, and on a busy table that lock request also stops every query that arrives behind it. The wait is the whole duration of the copy, not a moment at either end.

It needs free space for a second copy of the table and its indexes before it releases the first. Running it on the largest relation in a cluster that is already short of disk is the classic way to turn a space problem into an outage.

And it resets work that routine vacuum had already done. The visibility map is not carried across the rewrite, so every page is marked as needing a visit again, and the index-only scans that depended on that map start fetching from the heap until something vacuums the table.

Watching the visibility map go back to zero

relallvisible in the catalog counts the pages currently marked all-visible. A routine vacuum sets it; the rewrite discards it.

CREATE TABLE invoice (id int PRIMARY KEY, amount numeric);
INSERT INTO invoice SELECT g, g * 1.5 FROM generate_series(1, 50000) g;
VACUUM (ANALYZE) invoice;
SELECT relpages, relallvisible FROM pg_class WHERE relname = 'invoice';
VACUUM FULL invoice;
ANALYZE invoice;
SELECT relpages, relallvisible FROM pg_class WHERE relname = 'invoice';

CREATE TABLE
INSERT 0 50000
VACUUM
 relpages | relallvisible 
----------+---------------
      271 |           271
(1 row)

VACUUM
ANALYZE
 relpages | relallvisible 
----------+---------------
      271 |             0
(1 row)

Every page was all-visible, and after a rewrite of a table with no dead rows in it none of them are. The ANALYZE in between is there to show that it does not bring the bits back; only a vacuum sets them. The page count did not move either, because there was nothing to reclaim. That is the whole shape of the trap: the operation cost an exclusive lock and a full copy, recovered no space, and left the table worse for reads than it started.

When a rewrite is still the right call

It is the right call when a table really has grown far past what its live rows need and the cause has already been dealt with, and when the exclusive lock can be scheduled rather than discovered. Two alternatives take briefer locks, and the trade-offs between them, along with how to tell measured bloat from estimated bloat before committing to any of this, are in autovacuum and table bloat. Follow any rewrite with a routine vacuum so the map comes back.