REINDEXing database-wide daily

From: "Gnanakumar" <gnanam(at)zoniac(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: REINDEXing database-wide daily
Date: 2010-03-30 09:32:23
Message-ID: 00f801cacfeb$e8b8f4a0$ba2adde0$@com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi,

We're using PostgreSQL 8.2.

I have a question in connection to this question posted by me earlier:

http://archives.postgresql.org/pgsql-performance/2010-03/msg00343.php

In our application, DML operations (INSERT/UPDATE/DELETE) are heavily
performed in a day.

I also read about pg_autovacuum & REINDEX at:

http://www.postgresql.org/docs/8.2/interactive/routine-vacuuming.html

http://www.postgresql.org/docs/8.2/static/sql-reindex.html

I do not want to run pg_autovacuum daemon on a busy hour.

In case, if I can afford to take my database offline at low-usage time and
perform REINDEX database-wide manually/linux cron, to boost up index
performance, what is the community answer/suggestion on the following:

1. Is it a good idea to perform this on a daily basis?

2. Any implications of doing this on a daily basis?

3. Is there a way to find out bloated indexes?

4. Any other maintenance command, like ANALYZE, that has to be executed
before/after REINDEX?

5. Is there a way to find out when REINDEX was last run on an
INDEX/TABLE/DATABASE?

NOTE: I've also seen from my past experience that REINDEX database-wide
greatly improves performance of the application.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Matthew Wakeling 2010-03-30 09:34:55 Re: Performance regarding LIKE searches
Previous Message Josh Berkus 2010-03-30 06:18:27 Re: why does swap not recover?