Re: How do I know my table is bloated?

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