Re: REINDEX on large DB vs. DROP INDEX/CREATE INDEX

On 1/24/08 12:48 PM, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Wes <wespvp(at)msg(dot)bt(dot)com> writes:
>> I'm running 8.1.4. Assume I have exclusive access to the DB.
>
> You really ought to update to 8.1.something-newer, but I digress.

I was planning on upgrading to 8.x at the same time as this reindex - just
do a dump/reload, but...

I guess I've been asleep at the wheel and didn't realize 8.1.11 was out.
Since that wouldn't require a DB reload, I guess that would be highly
recommended?

>> 1. Is there any advantage to doing the DROP/CREATE over just doing a REINDEX
>> DATABASE.
>
> No, not if you don't mind exclusive locks. DROP together with CREATE
> INDEX CONCURRENTLY might be nicer if you were trying to do this without
> completely shutting down the DB, but if you aren't running normal
> operations then just use REINDEX.

I was thinking there was some clean up that didn't happen with REINDEX,
related to disk space allocation maybe? Perhaps this was just on older 7.x
versions - something I vaguely recall back under 7.x when I was having some
corruption issues.

>> 3. With a REINDEX DATABASE, how can I monitor progress?
>
> It should give you a NOTICE after each table.

Is there anything that shows up in ps for each index it is working on?

> BTW, what have you got maintenance_work_mem set to?

It is currently set to 983025. Not sure where I got that strange number
from. It's a 2 GB machine. I've been trying to get more, but when it runs
fine day to day, it's kind of hard to justify. Lots of disks, not so much
memory.

I guess I should also turn off fsync for the duration.

Wes