SEO

All,

A couple of questions regarding REINDEX command:

Running PostgreSQL 7.4.2 on Solaris.

1) When is it necessary to run REINDEX or drop/create
an index? All I could really find in the docs is:

"In some situations it is worthwhile to rebuild
indexes periodically with the REINDEX command. (There
is also contrib/reindexdb which can reindex an entire
database.) However, PostgreSQL 7.4 has substantially
reduced the need for this activity compared to earlier
releases."

What are these situations? We have a database with
some large tables. Currently we reindex (actually
drop/create) nightly. But as the tables have grown
this has become prohibitively time-consuming.
According to the above comment it may not be necessary
at all.

2) If reindexing is necessary, how can this be done in
a non-obtrusive way in a production environment. Our
database is being updated constantly. REINDEX locks
client apps out while in progress. Same with "CREATE
INDEX" when we drop/create. The table can have over
10 million row. Recreating the indexes seems to take
hours. This is too long to lock the client apps out.
Is there any other solution?

thanks,

Bill



__________________________________
Do you Yahoo!?
Make Yahoo! your home page
http://www.yahoo.com/r/hs

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Bill,

> 1) When is it necessary to run REINDEX or drop/create
> an index? All I could really find in the docs is:

If you need to VACUUM FULL, you need to REINDEX as well. For example, if you
drop millions of rows from a table.

> 2) If reindexing is necessary, how can this be done in
> a non-obtrusive way in a production environment. Our
> database is being updated constantly. REINDEX locks
> client apps out while in progress. Same with "CREATE
> INDEX" when we drop/create. The table can have over
> 10 million row. Recreating the indexes seems to take
> hours. This is too long to lock the client apps out.
> Is there any other solution?

Better to up your max_fsm_pages and do regular VACUUMs regularly and
frequently so that you don't have to REINDEX at all.


--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

On Mon, Apr 18, 2005 at 12:21:42 -0700,
Bill Chandler <billybobc1210@yahoo.com> wrote:
>
> Running PostgreSQL 7.4.2 on Solaris.
>
> 1) When is it necessary to run REINDEX or drop/create
> an index? All I could really find in the docs is:
>
> "In some situations it is worthwhile to rebuild
> indexes periodically with the REINDEX command. (There
> is also contrib/reindexdb which can reindex an entire
> database.) However, PostgreSQL 7.4 has substantially
> reduced the need for this activity compared to earlier
> releases."

In pathologic cases it is possible to have a lot of empty space on a lot
of your index pages. Reindexing would change that to a smaller number.
In earlier versions, I think it was possible to have completely empty
pages and this happened for patterns of use (new values monotonically
increasing, oldest values deleted first) that were actually seen in
practice.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Josh Berkus <josh@agliodbs.com> writes:
>> 1) When is it necessary to run REINDEX or drop/create
>> an index? All I could really find in the docs is:

> If you need to VACUUM FULL, you need to REINDEX as well. For example, if you
> drop millions of rows from a table.

That's probably a pretty good rule of thumb. It's worth noting that
VACUUM FULL tends to actively bloat indexes, not reduce them in size,
because it has to create new index entries for the rows it moves before
it can delete the old ones. So if a VACUUM FULL moves many rows you
are likely to see the indexes get bigger not smaller.

> Better to up your max_fsm_pages and do regular VACUUMs regularly and
> frequently so that you don't have to REINDEX at all.

Yes, definitely. Also consider using CLUSTER rather than VACUUM FULL
when you need to clean up after massive deletions from a table. It's
not any less intrusive in terms of locking, but it's often faster and it
avoids the index bloat problem (since it effectively does a REINDEX).

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq