Thread: 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