Mastering PostgreSQL 9.6

Inspecting storage with pgstattuple

Sometimes it can happen that tables in PostgreSQL grow out of proportion. The technical term for a table that has grown too much is table bloat. The question arising now is: which tables have bloated and how much bloat is there? The pgstattuple extension will help to answer those questions:

test=# CREATE EXTENSION pgstattuple; CREATE EXTENSION 

The module again deploys a couple of functions. In the case of the pgstattuple extension, those functions return a row consisting of a composite type. Therefore, the function has to be called in the FROM clause to ensure a readable result:

test=# \x Expanded display is on. test=# SELECT * FROM pgstattuple('t_test'); -[ RECORD 1 ]------+-------- table_len | 3629056 ...