Re: Vacuum (table performance)

"Claudio Lapidus" <clapidus(at)hotmail(dot)com> writes:

> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>>
>>"Claudio Lapidus" <clapidus(at)hotmail(dot)com> writes:
>> > ... we are seeing increasing execution times, not for the
>> > function but for the vacuum itself.
>>
>>Does a REINDEX of the table fix it?
>>
>
> Hmm, I'm looking at the documentation and it says that REINDEX
> acquires an exclusive lock on the table. Does this mean that during
> the reindex operation the table is unavailable for read/write by other
> processes?
>
> An alternative suggested right there is to drop and recreate an index,
> where -it says- CREATE INDEX would get a write lock on the table. Does
> this mean that during the create index operation the whole table is
> unavailable for write by other processes?
>
> thanks again
> cl.

At the very least you are going to need a write lock with the existing
implementation of PostgreSQL. Unfortunately, indexes with the current
version of PostgreSQL grow and grow indefinitely. Tables with a lot
of turnover are especially problematic. However, with the new release
that will be entering beta "real soon now" this will no longer be the
case. Tom Lane has fixed the indexing code so that a vacuum will
clean out the index as well as the tablespace.

My guess is that Mr. Lane is fishing for a little bit of "beta
testing" :).

Jason