Обсуждение: Reindex - Is this necessary after a vacuum?

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

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

Markus Benne <thing@m-bass.com> 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