Fragmentation/Vacuum, Analyze, Re-Index
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
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:
Show quoted text
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
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
Show quoted text
Thanks
Deepak
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: