Mastering PostgreSQL 9.6

Fighting table bloat

Table bloat is one of the most important issues when dealing with PostgreSQL. When you 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.

How can you figure out where table bloat is happening? Consider checking out the pg_stat_user_tables parameter:

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 will give you an impression of what is going on. You can also use pgstattuple as ...