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