On Wed, Jul 24, 2013 at 11:50 AM, pg noob <pgnube@xxxxxxxxx> wrote: > In PostgreSQL 8.4... > > I am wondering if autovacuum will periodically rebuild indexes? It doesn't rebuild indexes, it marks empty index pages for reuse. > If not, how advisable is it to reindex periodically? Here described the recommendations and explanations for 8.4 http://www.postgresql.org/docs/8.4/static/routine-reindex.html > After the problem was discovered we found that a reindex would fail because > there were > duplicate ID values. > > Our thought is that if we had a scheduled cron job that would periodically > do a reindex this > corruption might have been caught sooner by detecting the reindex failure. Periodical reindex is a very dubious technique to monitor database corruption. Honestly, I have never heard of any standard or recommended practice of doing it. However, there is a tool that pretends to do so (https://github.com/tvondra/pg_check), but I do not now what state it is currently and if it is production ready. > If a reindex is something that should be done frequently as part of regular > maintenance > why isn't there a mode of autovacuum that does this automatically? Or maybe > there is and > I just don't know about it..? It is not necessary to reindex to be a part of regular maintenance. The main goal of autovacuum is to effectively reuse space and to update statistics. If autovacuum is configured properly reindex is not required. However, if you have some high/bulk-update/delete operations autovacuum might not manage with bloat, and in this case you can use this tool pgcompactor (https://code.google.com/p/pgtoolkit/) in conjunction with pgstattuple extension or pg_repack (https://github.com/reorg/pg_repack). -- 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@xxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
