VACUUM
usually does not return disk space to operating system, except in some special cases.
From the docs:
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. In contrast,VACUUM FULL
actively compacts tables by writing a complete new version of the table file with no dead space. This minimizes the size of the table, but can take a long time. It also requires extra disk space for the new copy of the table, until the operation completes.
The question is: how can this database state when one or more pages at the end of a table become entirely free
be achieved? This can be done via VACUUM FULL
, but I haven't got enough space to implement it. So are there any other possibilities?
Best Answer
To return space to the OS, use
VACUUM FULL
. While being at it, I suppose you runVACUUM FULL ANALYZE
. I quote the manual:Bold emphasis mine.
CLUSTER
achieves that, too, as a collateral effect.Plain
VACUUM
does not normally achieve your goal ("one or more pages at the end of a table entirely free"). It does not reorder rows and only prunes empty pages from the physical end of the file when the opportunity arises - like your quote from the manual instructs.You can get empty pages at the end of the physical file when you
INSERT
a batch of rows andDELETE
them before other tuples get appended. Or it can happen by coincidence if enough rows are deleted.There are also special settings that might prevent
VACUUM FULL
from reclaiming space. See: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:
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 unsignedint2
, and65535
is the maximum for that type (2^16 - 1
), so that's the safe upper bound.SQL Fiddle (reusing a simple table from a different case.)
Tools to measure row / table size:
Disk full
You need wiggle room on disk for any of these operations. There is also the community tool
pg_repack
as replacement forVACUUM FULL
/CLUSTER
. It avoids exclusive locks but needs free space to work with as well. The manual:As a last resort, you can run a dump/restore cycle. That removes all bloat from tables and indexes, too. Closely related question:
The answer over there is pretty radical. If your situation allows for it (no foreign keys or other references preventing row deletions), and no concurrent access to the table), you can just:
Dump the table to disk connecting from a remote computer with plenty of disk space (
-a
for--data-only
):From remote shell, dump table data:
In a pg session,
TRUNCATE
the table:From remote shell, restore to same table:
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?
Can you
VACUUM FULL
smaller tables first, one by one, thereby freeing up enough disk space?Can you run
REINDEX TABLE
orREINDEX 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.