pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedReindex - Is this necessary after a vacuum?
3+ messages / 3 participants
[nested] [flat]
* Reindex - Is this necessary after a vacuum?
@ 2005-10-25 13:56 Markus Benne <thing@m-bass.com>
2005-10-25 14:16 ` Re: Reindex - Is this necessary after a vacuum? Richard Huxton <dev@archonet.com>
2005-10-25 14:27 ` Re: Reindex - Is this necessary after a vacuum? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 3+ messages in thread
From: Markus Benne @ 2005-10-25 13:56 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Reindex - Is this necessary after a vacuum?
2005-10-25 13:56 Reindex - Is this necessary after a vacuum? Markus Benne <thing@m-bass.com>
@ 2005-10-25 14:16 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 3+ messages in thread
From: Richard Huxton @ 2005-10-25 14:16 UTC (permalink / raw)
To: markus@m-bass.com; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Reindex - Is this necessary after a vacuum?
2005-10-25 13:56 Reindex - Is this necessary after a vacuum? Markus Benne <thing@m-bass.com>
@ 2005-10-25 14:27 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2005-10-25 14:27 UTC (permalink / raw)
To: markus@m-bass.com; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-10-25 14:27 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-25 13:56 Reindex - Is this necessary after a vacuum? Markus Benne <thing@m-bass.com>
2005-10-25 14:16 ` Richard Huxton <dev@archonet.com>
2005-10-25 14:27 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox