To return space to the OS, use
VACUUM FULL. While being at it, I suppose you run
VACUUM FULL ANALYZE. I quote the manual:
Selects "full" vacuum, which can reclaim more space, but takes much
longer and exclusively locks the table. This method also 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. Usually this
should only be used when a significant amount of space needs to be
reclaimed from within the table.
Bold emphasis mine.
CLUSTER achieves that, too, as a collateral effect.
You cannot achieve the goal (
one or more pages at the end of a table entirely free) with plain
VACUUM. It does not reorder rows and only prunes empty pages from the end of the physical the disk if the opportunity arises - like your quote from the manual instructs.
You can get empty pages at the rear when you
INSERT a batch of rows and
DELETE them before other tuples get appended. Or it can happen by chance, if you delete a lot of rows (including those appended last).
Prepare empty pages at the end of a table for testing
The system column
ctid represents the physical position of a row. You need to understand that column:
We can work with that and prepare a table by deleting all rows from the last page:
DELETE FROM tbl t
SELECT (split_part(ctid::text, ',', 1) || ',0)')::tid AS min_tid
, (split_part(ctid::text, ',', 1) || ',65535)')::tid AS max_tid
ORDER BY ctid DESC
WHERE t.ctid BETWEEN d.min_tid AND d.max_tid;
Now, the last page is empty. This ignores concurrent writes. Either you are the only one writing to that table or you need to to take a write lock to avoid interference.
The query is optimized to identify qualifying rows quickly. The second number of a
tid is the tuple index stored as unsigned
65535 is the maximum for that type (
2^16 - 1), so that's the safe upper bound.
SQL Fiddle reusing a simple table from another occasion.
Tools to measure row / table size:
You need wiggle room on your disk for any of these operations. There is also the community tool
pg_repack as replacement for
VACUUM FULL /
CLUSTER. It avoids exclusive locks, but that, too, needs free space on disk. Quoting the manual
Requires free disk space twice as large as the target table(s) and indexes.
As a last resort, you can run a dump/restore cycle. That removes all bloat from tables and indexes, too. There is a closely related question here on dba.SE:
The answer over there is pretty radical. If your situation allows for it (no foreign keys or other references preventing that you delete rows), you can just:
Dump the table to disk connecting from a remote computer with plenty of disk space (
pg_dump -h <host_name> -p <port> -t mytbl -a mydb > db_mytbl.sql
TRUNCATE the table in the DB:
INSERT original rows to same table from the remote connection.
psql -h <host_name> -p <port> mydb -f db_mytbl.sql
It is now free of any dead rows or bloat.
But maybe you can have that simpler?
Can you make enough space on disk by deleting (moving) unrelated files?
VACUUM FULL smaller tables first, one by one, thereby freeing up enough disk space?
Can you run
REINDEX TABLE or
REINDEX INDEX to free disk space from bloated indexes?
Whatever you do, don't be rash. If in doubt, backup everything to a secure location first.