> I saw some people talking about the reindex command and I read in the docs > the one reason to use reindex is when a table is bloated. > But how do I know when a table is bloated?
Take a look at the pgcompactor tool https://code.google.com/p/pgtoolkit/. This will find out what tables and indexes in your cluster are bloated and softly (without heavy locks) remove the bloat. Before using it setup the pgstattuple extension in all the databases you age going to check for bloat. Some usage examples are below.
To get bloat statistics for all the cluster do:
pgcompactor --all --reindex --verbose info --dry-run
To remove bloat from all the cluster:
pgcompactor --all --reindex --verbose info
For a specific table use:
pgcompactor --table tablename --reindex --verbose info --dry-run
You can also specify --dbname, --schema, --exclude-<dbname/schema/table>, etc.
For all the options:
pgcompactor --man
-- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA