RE: [HACKERS] Index recreation in vacuum

> -----Original Message-----
> From: Bruce Momjian [mailto:pgman(at)candle(dot)pha(dot)pa(dot)us]
> > >
> > > Maybe I am all wrong here. Maybe most of the advantage of
> rename() are
> > > meaningless with reindex using during vacuum, which is the most
> > > important use of reindex.
> > >
> > > 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.
>
> OK, how about making a copy of the heap table before starting vacuum,
> moving all the tuples in that copy, create new index, and then move the
> new heap and indexes over the old version. We already have an exclusive
> lock on the table. That would be 100% reliable, with the disadvantage
> of using 2x the disk space. Seems like a big win.
>

I heard from someone that old vacuum had been like so.
Probably 2x disk space for big tables was a big disadvantage.

In addition,rename(),unlink(),mv aren't preferable for transaction
control as far as I see. We couldn't avoid inconsistency using
those OS functions.
We have to wait the change of relation file naming if copying
vacuum is needed.
Under the spec we need not rename(),mv etc.

Regards.

Hiroshi Inoue
Inoue(at)tpf(dot)co(dot)jp