Re: [HACKERS] Index recreation in vacuum

> > Let's look at index using during vacuum. Right now, how does vacuum
> > handle indexes when it moves a tuple? Does it do each index update as
> > it moves a tuple? Is that why it is so slow?
> >
>
> Yes,I believe so. It's necessary to keep consistency between heap
> table and indexes even in case of abort/crash.
> As far as I see,it has been a big charge for vacuum.

In fact, maybe we just need to look at the ability to recreate the
entire table/index in one big function. We could do a sequential scan
of the table and if we find > X number of rows that are expired, we
decide to do a full recreate of the table with all new indexes vs.
doing a vacuum. This seems to be the core of what the REINDEX function
is doing anyway.

In fact, I wonder if we should enable a % parameter to VACUUM, so vacuum
does something only of X% of the disk space will be saved by the vacuum.
Currently if someone deletes the first row of a able, every row is moved
to save a few bytes of disk space. That is certainly a waste, and
telling people they have to vacuum every night is probably a waste in
most cases too, but we don't give administrators the ability to control
when a vacuum is a good idea.

We could get ALTER TABLE DROP COLUMN working too by recreating the table
without the dropped column.

--
Bruce Momjian | http://www.op.net/~candle
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026