Обсуждение: How do I know my table is bloated?

From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Rodrigo Barboza
Sent: Thursday, May 30, 2013 9:06 AM
To: pgsql-admin@postgresql.org
Subject: [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
tableis bloated. 
But how do I know when a table is bloated?


You could use PGSTATTUPLE extension, which you could read about here:

http://www.postgresql.org/docs/9.2/static/pgstattuple.html

Regards,
Igor Neyman

From: Rodrigo Barboza [mailto:rodrigombufrj@gmail.com]
Sent: Thursday, May 30, 2013 10:42 AM
To: Igor Neyman
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] How do I know my table is bloated?




On Thu, May 30, 2013 at 11:16 AM, Igor Neyman <ineyman@perceptron.com> wrote:


From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Rodrigo Barboza
Sent: Thursday, May 30, 2013 9:06 AM
To: pgsql-admin@postgresql.org
Subject: [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
tableis bloated. 
But how do I know when a table is bloated?

You could use PGSTATTUPLE extension, which you could read about here:

http://www.postgresql.org/docs/9.2/static/pgstattuple.html

Regards,
Igor Neyman


--Does auto-vaccum do the reindex process for us?

No, autovacuum and reindexing are 2 completely different processes.

Igor Neyman

From: Rodrigo Barboza [mailto:rodrigombufrj@gmail.com]
Sent: Thursday, May 30, 2013 11:10 AM
To: Igor Neyman
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] How do I know my table is bloated?

Well, so I need to do reindex frequentely in database? Is that right?
Or should I use cluster, as David said?
How frequently should I do this operation?

---------------------

Not necessarily.
If there is no table or index bloat, which you should be able to check first, - why bother?

Normally autovacuum does pretty good job even with default configuration parameters.
If not, you could start with adjusting autovacuum parameters.
And reindex only as a last resort, when your index size grows "uncontrollably".

Igor Neyman

From: Rodrigo Barboza [mailto:rodrigombufrj@gmail.com]
Sent: Thursday, May 30, 2013 12:04 PM
To: Igor Neyman
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] How do I know my table is bloated?

I have some tables that I do a lot of updates, deletes and inserts.
So I am worried that my cluster can grow up to a huge size...
The best option would be to create a scheduled process to check if it is bloated and if so, reindex?

--------------------------

Depends on whether it's table bloat or index bloat.
But first, you try to minimize bloat by tuning autovacuum.

Igor Neyman

From: Rodrigo Barboza [mailto:rodrigombufrj@gmail.com]
Sent: Thursday, May 30, 2013 2:24 PM
To: Igor Neyman
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] How do I know my table is bloated?


I am using the defualt values for autovaccum. 
How do you suggest to tune the autovacuum?
If the problem is index bloat, autovaccum won't be a solution, am I right?

----------------------------------------------------------

Rodrigo,

I think you are putting "a cart in front of the horse" (so to speak).
Did you verify that you have bloated indexes?  Under normal conditions it shouldn't happen.
From docs (http://www.postgresql.org/docs/9.2/static/routine-reindex.html):
"B-tree index pages that have become completely empty are reclaimed for re-use. However, there is still a possibility
ofinefficient use of space: if all but a few index keys on a page have been deleted, the page remains allocated.
Therefore,a usage pattern in which most, but not all, keys in each range are eventually deleted will see poor use of
space."

So, yes, if index is really gets bloated than reindexing fixes this problem.

Igor Neyman

From: Rodrigo Barboza [mailto:rodrigombufrj@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

On Thu, May 30, 2013 at 6:05 AM, Rodrigo Barboza
<rodrigombufrj@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.ru@gmail.com