We discussed in Chapter 1, Cluster Management Fundamentals, that there is no separate UNDO storage in PostgreSQL. PostgreSQL stores UNDO records as multiple versions within the same table. UNDO records or dead tuples may be cleaned by autovacuum or manual vacuum, but space may not be reclaimed by the operating system unless the empty pages are at the end of the heap.
Untuned vacuum settings may lead to further damage. For example, a query may have to scan 100 pages instead of 10 pages due to bloat. In this case, the only choice we are left with is to rebuild the table. This can be performed using VACUUM FULL; however, it would put an exclusive lock on the table and does not allow reads and writes on the table. You may always want to avoid this in a production database. For this reason, we have an extension named pg_repack, which can do a great job in performing an online rebuild of database objects such as tables and indexes...
