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