Re: REINDEXing database-wide daily

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 3/30/2010 4:32 AM, Gnanakumar wrote:
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.



I could be way off base here, so I hope others will confirm/deny this: I think the more often you run vacuum, the less you notice it. If you wait for too long then vacuum will have to work harder and you'll notice a speed decrease. But many small vacuums which dont have as much work to do, you wont notice.

It could be, and I'm guessing again, because your database grew from 3 to 30 gig (if I recall the numbers right), REINDEX had lots of affect. But if vacuum can keep up with space reuse, REINDEX may not be needed. (maybe a few weeks or once a month).

-Andy



--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux