Reindex - Is this necessary after a vacuum?

Поиск
Список
Период
Сортировка
Искать

Reindex - Is this necessary after a vacuum?

От:
Markus Benne <thing@m-bass.com>
Дата:
We are reindexing frequently, and I'm wondering if
this is really necessary, given that it appears to
take an exclusive lock on the table.

Our table in question is vacuumed every 4 minutes, and
we are reindexing after each one.

I'm not a fan of locking this table that frequently,
even if it is only for 5 - 10 seconds depending on
load.

The vacuum is a standard vacuum.  Nightly we do a
vacuum analyze.

Thanks for any tips,
...Markus

Re: Reindex - Is this necessary after a vacuum?

От:
Richard Huxton <dev@archonet.com>
Дата:
Markus Benne wrote:
> We are reindexing frequently, and I'm wondering if
> this is really necessary, given that it appears to
> take an exclusive lock on the table.
> 
> Our table in question is vacuumed every 4 minutes, and
> we are reindexing after each one.
> 
> I'm not a fan of locking this table that frequently,
> even if it is only for 5 - 10 seconds depending on
> load.
> 
> The vacuum is a standard vacuum.  Nightly we do a
> vacuum analyze.

At most I'd do a nightly reindex. And in fact, I'd probably drop the 
index, full vacuum, recreate index.

But you only need to reindex at all if you have a specific problem with 
the index bloating. Are you seeing this?

-- 
   Richard Huxton
   Archonet Ltd

Re: Reindex - Is this necessary after a vacuum?

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Markus Benne  writes:
> Our table in question is vacuumed every 4 minutes, and
> we are reindexing after each one.

That's pretty silly.  You might need a reindex once in awhile, but
not every time you vacuum.

The draft 8.1 docs contain some discussion of possible reasons for
periodic reindexing:
http://developer.postgresql.org/docs/postgres/routine-reindex.html
but none of these reasons justify once-per-vacuum reindexes.

			regards, tom lane
FAQ