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.

How to do it...

To identify the bloat of an object, we have to use the pgstattuple extension, otherwise we have to follow the approach that is mentioned at: http://www.databasesoup.com/2014/10/new-table-bloat-query.html:

postgres=# CREATE EXTENSION...