[ADMIN] How do I know my table is bloated?
Hi, everyone. 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?
Re: [ADMIN] How do I know my table is bloated?
From: Rodrigo Barboza [mailto:rodrigombu...@gmail.com] Sent: Thursday, May 30, 2013 2:50 PM To: Igor Neyman Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] How do I know my table is bloated? Well, maybe I am. But I am worried because I know that there are some tables that do lots of updates and delete. As this concept is new for me, I am trying to be prepared to detect a situation like this. -- It all depends on pattern of your inserts/updates/deletes. If your index accumulates lots of almost (but not completely) empty pages with just few entries left, than - yes, REINDEX is your friend. b.t.w., this concept is not unique to Postgres, it's just a nature of B-tree indexes. Igor -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] How do I know my table is bloated?
On Thu, May 30, 2013 at 3:55 PM, Igor Neyman iney...@perceptron.com wrote: From: Rodrigo Barboza [mailto:rodrigombu...@gmail.com] Sent: Thursday, May 30, 2013 2:50 PM To: Igor Neyman Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] How do I know my table is bloated? Well, maybe I am. But I am worried because I know that there are some tables that do lots of updates and delete. As this concept is new for me, I am trying to be prepared to detect a situation like this. -- It all depends on pattern of your inserts/updates/deletes. If your index accumulates lots of almost (but not completely) empty pages with just few entries left, than - yes, REINDEX is your friend. b.t.w., this concept is not unique to Postgres, it's just a nature of B-tree indexes. Igor I see... I guess the only way to know is if I suspect that size of my db is growing unexpected. But it is good to know that a b-tree concept concept. Because I came from a very old mysql installation (that is way postgres is new for me) and I am trying to keep my postgres the most stable possible.
Re: [ADMIN] How do I know my table is bloated?
On Thu, May 30, 2013 at 6:05 AM, Rodrigo Barboza rodrigombu...@gmail.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...@gmail.com -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] How do I know my table is bloated?
On Fri, May 31, 2013 at 2:06 AM, Sergey Konoplev gray...@gmail.com wrote: On Thu, May 30, 2013 at 6:05 AM, Rodrigo Barboza rodrigombu...@gmail.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...@gmail.com Thanks, guys!
