PostgreSQL High Performance Cookbook

Dealing with bloating tables and indexes

In this recipe, we will be discussing how to deal with bloats using PostgreSQL's garbage collector processes.

Getting ready

We all know that PostgreSQL's storage implementation is based on MVCC. As a result of MVCC, PostgreSQL needs to reclaim the dead space/bloats from the physical storage, using its garbage collector processes called vacuum or autovacuum. If we do not reclaim these dead rows, then the table or index will keep growing until the disk space gets full. In a worst case scenario, a single live row in a table can cause the disk space outage. We will discuss these garbage collector processes in more detail in the next recipe, but for now let's find out which tables or indexes have more dead space. ...