PostgreSQL 10 High Performance - Third Edition

Full vacuum

In versions of PostgreSQL before 9.0, the VACUUM FULL command, which is never executed by autovacuum, takes a more aggressive approach to space reuse. It compacts tables by moving rows to the earliest page they can be placed onto. This makes the one situation where vacuum can release disk space, when it's all at the end of the table, very likely. If you have any dead rows in your table, VACUUM FULL will relocate them one at a time to an earlier part of the table, and then shrink it accordingly once the end is all empty space.

There are two major downsides to doing that. The first is that it's very time and resource intensive. VACUUM FULL is likely to take a very long time if your table is larger, and during that time it will have ...