Why deleted space stays in the file
The common complaint that VACUUM fails to make a table smaller rests on a wrong assumption about what the command is for. Cleanup does not normally hand disk space back to the operating system, and the reason lies in how PostgreSQL decides which row versions are visible.
Tuple visibility and the hidden columns
Every row carries a set of hidden system columns holding transaction IDs: xmin, xmax, cmin and cmax. PostgreSQL uses these to determine, often but not always, whether a given transaction may see a row. A minimal demonstration table with a single column, loaded through two separate transactions of three rows each, shows the split clearly: the first three rows carry one transaction number and the remaining rows another.
The behaviour of UPDATE exposes the mechanics. Changing a row does not modify it in place — the CTID, the physical location of the row on disk, moves, because PostgreSQL copies the row and the old position disappears. A second UPDATE copies it again. Keeping the previous version is not optional: without it ROLLBACK could not work. The consequence, visible on inspection, is a stretch of dead rows that must eventually be removed.
What VACUUM actually reclaims
COMMIT cannot be allowed to kill dead rows either, which is why cleanup happens asynchronously whenever VACUUM runs. The command hunts for row versions no transaction can see anymore, wherever they sit inside the data file. The space it frees is space PostgreSQL itself may reuse — not space that leaves the file.
There is no filesystem operation that lets a 1 GB data file return "the middle of the file" to the operating system simply because that region is empty. Instead, PostgreSQL records the freed space internally and allocates it again later.
The truncation exception
A narrow case does shrink the file. If every row from a certain position onward is dead, VACUUM can truncate the table. A table that has just been emptied by DELETE illustrates this: its size is retained until VACUUM runs, because cleanup is asynchronous, and then the trailing block of dead rows is cut away.
On large tables, though, some rows near the end of the data file almost always remain alive under normal circumstances. Counting on VACUUM to shrink a table is therefore a bad bet.
When the table itself is the problem
Table bloat — tables growing out of proportion to their contents — can be addressed with VACUUM FULL, at the cost of a table lock that is often unacceptable in a business critical environment. pg_squeeze exists as an alternative for reorganizing large tables without that extensive locking.
