Mastering PostgreSQL 12 - Third Edition

Fighting table bloat

Table bloat is one of the most important issues when dealing with PostgreSQL. When we are facing bad performance, it is always a good idea to figure out whether there are objects that need a lot more space than they are supposed to have.

How can we figure out where table bloat is happening? Check out the pg_stat_user_tables view:

test=# \d pg_stat_user_tables   View "pg_catalog.pg_stat_user_tables" Column            | Type             | Modifiers-------------------+------------------+-----------  relid             | oid              | schemaname        | name             | relname           | name             | ... n_live_tup        | bigint           | n_dead_tup        | bigint           |

The n_live_tup and n_dead_tup fields give us an impression of what is going on, and we can also use pgstattuple.

What can we do if there is serious table ...