Postgres Pro
Downloads
Home   >   mailing lists

Thread: Fragmentation/Vacuum, Analyze, Re-Index

Fragmentation/Vacuum, Analyze, Re-Index

From
DM
Date:
Hello All,

How to identify if a table requires full vacuum? How to identify when to do re-index on an existing index of a table?

Is there any tool for the above?

Thanks
Deepak Murthy


Re: Fragmentation/Vacuum, Analyze, Re-Index

From
DM
Date:
Is there any script/tool to identify if the table requires full vacuum? or to re-index an existing index table?

Thanks
Deepak

On Fri, Jan 22, 2010 at 12:11 AM, DM <dm.aeqa@gmail.com> wrote:
Hello All,

How to identify if a table requires full vacuum? How to identify when to do re-index on an existing index of a table?

Is there any tool for the above?

Thanks
Deepak Murthy



Re: Fragmentation/Vacuum, Analyze, Re-Index

From
Richard Neill
Date:
DM wrote:
> Is there any script/tool to identify if the table requires full vacuum?
> or to re-index an existing index table?
>

Don't know if there is a script to specifically do this, though you may
find this query a useful one:

SELECT relname, reltuples, relpages FROM pg_class ORDER BY relpages DESC;


(it shows what's currently using most of the disk).


In general though, you should never use "VACUUM FULL". The best bet is
to tune autovacuum to be more aggressive, and then occasionally run CLUSTER.

Best wishes,

Richard



> Thanks
> Deepak
>

Re: Fragmentation/Vacuum, Analyze, Re-Index

From
Reid Thompson
Date:
On 1/22/2010 2:27 PM, Richard Neill wrote:
> DM wrote:
>> Is there any script/tool to identify if the table requires full
>> vacuum? or to re-index an existing index table?

http://pgsql.tapoueh.org/site/html/news/20080131.bloat.html

The bucardo project has released its nagios plugins for PostgreSQL and we can extract from them this nice view
in order to check for table and index bloat into our PostgreSQL databases: