Thread: Rebuild indexes

Hi Everyone,

I've a kind of less inserts/mostly updates table,
which we vacuum every half-hour.

here is the output of vacuum analyze

INFO:  --Relation public.accounts--
INFO:  Index accounts_u1: Pages 1498; Tuples 515:
Deleted 179.
    CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  Index accounts_u2: Pages 2227; Tuples 515:
Deleted 179.
    CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO:  Index accounts_u3: Pages 246; Tuples 515:
Deleted 179.
    CPU 0.00s/0.00u sec elapsed 0.00 sec.

however its indexes keeps growing on and on. After
surfing the manuals for a while, i came to know that
vacuum doesn't clears up dead tuples caused by
updates. so i then decided to do reindex online, but
that makes exclusive lock on table which would prevent

writing on to tables.

finally i'm at a point where i decided to do index
swapping.

for e.g.

1. create index accounts_u1_swap,accounts_u2_swap and
accounts_u3_swap in addition to the original indexes

2. analyze table to update stats, so that the table
knows about new indexes.

3. drop original indexes

4. i wish i had a rename index command to rename _swap
to its original index name. now create indexes with
original name

5. follow #2  and #3 (now drop _swap indexes)

Is there a better way to do this. comments are
appreciated.

thanks
-Shankar

__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

Shankar K <shan0075@yahoo.com> writes:
> ... so i then decided to do reindex online, but
> that makes exclusive lock on table which would prevent
> writing on to tables.

So does CREATE INDEX, so it's not clear what you're buying with
all these pushups.

> 2. analyze table to update stats, so that the table
> knows about new indexes.

You do not need to ANALYZE to get the system to notice new indexes.

> 4. i wish i had a rename index command to rename _swap
> to its original index name.

You can rename indexes as if they were tables.

            regards, tom lane

Shankar,

> Is there a better way to do this. comments are
> appreciated.

No.  This is one of the major features in 7.4; FSM and VACUUM will manage
indexes as well.   Until then, we all suffer ....

BTW, the REINDEX command is transaction-safe.   So if your database has "lull"
periods, you can run it without worrying that any updates will get turned
back.

--
-Josh Berkus
 Aglio Database Solutions
 San Francisco