autovacuum and reindex

Joao Miguel Ferreira wrote:

Hello all,

I'd like to know if the autovacuum feature also deals with automatically
reindexing my indexes.

It doesn't.

I know Pg8 know comes with a more eficient management of indexes, but I
also read in the manuals that it's still good practice to routine
reindex de most critical (in terms of speed) indexes.

Could someone please tell me if autovacuum does or doesnt take care of
reindexing, or if reindexing is or isn't important in pg8.

Reindexing isn't as important as it used to be before 7.4, but it is
still needed in certain cases. I think the recommendation is to avoid
it if you can, and apply only to those cases where you demonstrably need
it. I've seen cases on which people was doing REINDEX when they
actually needed more frequent vacuuming, so just because queries are
faster after a reindex it doesn't mean that it's the cure to the
problem.

In any case I doubt vacuum is ever going to deal with REINDEX, because
that needs exclusive locks on the table which is not something that
autovac wants to deal with. Moreover, tables needing reindexing are
probably those most contended, so it makes even less sense to be locking
them for any nontrivial length of time.

--
Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J
"Puedes vivir solo una vez, pero si lo haces bien, una vez es suficiente"