Re: How do I know my table is bloated?

On Thu, May 30, 2013 at 6:05 AM, Rodrigo Barboza
<rodrigombufrj(at)gmail(dot)com> wrote:
> 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

Profile: http://www.linkedin.com/in/grayhemp
Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
Skype: gray-hemp
Jabber: gray(dot)ru(at)gmail(dot)com