From PostgreSQL wiki
VACUUM vs VACUUM FULL
VACUUM command and associated autovacuum process are PostgreSQL's way of controlling MVCC bloat. The
VACUUM command has two main forms of interest - ordinary
VACUUM FULL. These two commands are actually quite different and should not be confused.
VACUUM scans a table, marking tuples that are no longer needed as free space so that they can be overwritten by newly inserted or updated data. See Introduction to VACUUM, ANALYZE, EXPLAIN, and COUNT and the PostgreSQL documentation on MVCC for a detailed explanation of this. Note that you should rarely need to use the
VACUUM command directly on a modern PostgreSQL database, as autovacuum should take care of it for you if properly set up.
VACUUM FULL, unlike
VACUUM, touches data that has not been deleted. On pre-9.x versions of Postgres, it moves data into spaces earlier in the file that have been freed. Once it's created a free space at the end of the file, it truncates the file so that the OS knows that space is free and may be reused for other things. Moving in-use data around this way can have adverse side-effects, including taking heavy weight locks, increased i/o, and adding index bloat. On older systems, there are better ways to free space if you need to, and better ways to optimize tables (see below) so you should essentially never use
VACUUM FULL on a pre-9.x system. Even on 9.x and above, the system is designed with the goal that you should never be running
VACUUM FULL regularly.
For clarity, 9.0 changes VACUUM FULL. As covered in the documentation, the VACUUM FULL implementation has been changed to one that's similar to using CLUSTER in older versions. This gives a slightly different set of trade-offs from the older VACUUM FULL described here. While the potential to make the database slower via index bloating had been removed by this change, it's still something you may want to avoid doing, due to the locking and general performance overhead of a VACUUM FULL.
When to use
VACUUM FULL and when not to
Many people, either based on misguided advice on the 'net or on the assumption that it must be "better", periodically run
VACUUM FULL on their tables. This is generally not recommended and in some cases can make your database slower, not faster.
VACUUM FULL is only needed when you have a table that is mostly dead rows - ie, the vast majority of its contents have been deleted. It should not be used for table optimization or periodic maintenance, as it's generally counterproductive. In most cases the freed space will be promptly re-allocated, possibly increasing file-system-level fragmentation and requiring file system space allocations that're slower than just re-using existing free space within a table.
When you run
VACUUM FULL on a table, that table is locked for the duration of the operation, so nothing else can work with the table.
VACUUM FULL is much slower than a normal VACUUM, so the table may be unavailable for a while.
More importantly, on pre-9.0 systems, while
VACUUM FULL compacts the table, it does not compact the indexes - and in fact may increase their size, thus slowing them down, causing more disk I/O when the indexes are used, and increasing the amount of memory they require. A
REINDEX may be required after
VACUUM FULL on PostgreSQL versions older than 9.0. See the main documentation's notes on VACUUM vs VACUUM FULL.
What to use instead
If you shouldn't use
VACUUM FULL, what should you be using?
If autovacuum is running frequently enough and aggressively enough, your tables should never grow ("bloat") due to unreclaimed dead rows, so you should never need to return "dead" space to the OS.
Autovacuum continues to improve dramatically with every PostgreSQL version, and is a very good reason to make sure you are running the latest version. For example, with 8.4 the free space map is now managed automatically, removing a no-longer-necessary tuning parameter and eliminating a major source of table bloat.
If autovacuum isn't doing enough to keep your tables and indexes bloat-free, tune it, don't supplement it with manual vacuuming and reindexing. You may need to increase your free space map settings (pre-8.4), tune autovacuum to run more frequently, and/or tell autovacuum to vacuum certain frequently-updated tables more aggressively than others.
Unless you need to return space to the OS so that other tables or other parts of the system can use that space, you should use
VACUUM instead of
If you need to manually
VACUUM your tables at any time other than when running major admin or update tasks that rewrite large parts of your tables, you probably don't have autovacuum set up well enough.
If you're trying to "optimise" your tables by packing them down and removing table bloat that's accumulated due to (say) insufficiently aggressive autovacuuming, or you're trying to return dead space in a table to the operating system,
CLUSTER is usually your best bet. It runs a lot faster than
VACUUM FULL and will compact and optimise the indexes as well as the table its self. However, you will need enough free space to hold all the in-use data from the table while
The problem with
CLUSTER is that it reorders the table following an index. If the table is not already approximately in that index' order, this will take a long time because it will have to do a scattered read of the table pages over and over as it looks for each tuple. A faster alternative is to request a full table rewrite without requiring a particular order. PostgreSQL versions prior to 9.0 do not offer any direct way to invoke this operation; however, you can use the following workaround. Choose any table column, and use
ALTER TABLE to change its type to the same type. This is obviously going to cause no logical change to the table, but the server will have to rewrite the table, getting rid of dead tuples while at it.
For example, assuming the
an_integer_column is of type
ALTER TABLE your_table ALTER an_integer_column SET DATA TYPE integer;
This trick will not work in PostgreSQL versions 9.1 or later, as it detects that the change in data type is degenerate and so no rewrite is necessary.
Sometimes it can be faster to use a
SELECT ... INTO command to copy data from a bloated table into a new table, then re-create the indexes and finally rename the tables to replace the old one with the new one. It's rarely worth doing this instead of using
CLUSTER, though, as
CLUSTER does almost the same thing automatically and can rebuild indexes in parallel. The main reason you may want to use
SELECT ... INTO instead of
CLUSTER is if you don't want to sort the table.
If you've been using
VACUUM FULL to free space from a table that's periodically completely emptied using
DELETE FROM tablename; (without a
WHERE clause), you can use
TRUNCATE TABLE to replace those two steps with one much, much faster one.
DELETE FROM tablename; VACUUM FULL tablename;
TRUNCATE TABLE tablename;
Please make sure to read the caveats in the notes on the
TRUNCATE TABLE documentation. If
TRUNCATE TABLE isn't suitable for your needs, you can use
DELETE followed by
DELETE on a table that is the target of foreign key references, consider adding an index to the referencing columns. That will allow checks for foreign key enforcement to avoid a sequential scan on the referencing table, making
DELETE from the referenced table vastly faster.
Recovering from index bloat caused by
If you have indexes badly bloated by regular use of
VACUUM FULL, your best bet is usually going to be to use
CLUSTER to rewrite the table and rebuild the indexes.
If you can't afford to have the table locked for that long, you can rebuild each index individually while queries continue to run on the table. PostgreSQL unfortunately doesn't have a
REINDEX CONCURRENTLY command, but it can be simulated with appropriate use of CREATE INDEX ... CONCURRENTLY, ALTER INDEX ... RENAME and DROP INDEX to create new indexes, swap the old and new ones by renaming, and drop the old indexes. Note that since you can't drop some indexes, such as primary keys, this may not be a possible cleanup technique for all of them.
So, why does
VACUUM FULL exist?
The reasons are mostly historical ones. In particular, it was more useful before the availability of a fully MVCC-safe
CLUSTER, and severe table bloat was more common before autovacuum was included in mainline and enabled by default.
There is one remaining use for
VACUUM FULL that there often isn't a better alternative for, though - recovering from disk-full situations.
VACUUM FULL requires much less free space than
CLUSTER to compact a table, so it can be useful if you've (almost) completely run out of space.
VACUUM FULL proposed for removal from PostgreSQL 8.5 (later renamed to PostgreSQL 9.0), and that article gives some additional support for the suggestions given here. The change proposed there was implemented. The few cases where the old VACUUM FULL could be helpful, such as recovering from low-disk space situations, did not outweigh the issues of the old code--which included an incompatibility with the new replication features in the 9.0 release.
Originally by --Ringerc 03:48, 26 November 2009 (UTC)