Mastering PostgreSQL 12 - Third Edition

Inspecting storage with pgstattuple

Sometimes, it may be the case that tables in PostgreSQL grow out of proportion. The technical term for a table that has grown too much is table bloat. The question that arises now is: Which tables have bloated and how much bloat is there? The pgstattuple extension will help us answer those questions:

test=# CREATE EXTENSION pgstattuple;CREATE EXTENSION

As we stated previously, the module 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=# \xExpanded display is on.test=# SELECT * FROM pgstattuple('t_test');-[ RECORD 1 ]--------------------+-------------- ...