Re: [HACKERS] Index recreation in vacuum

> > > In fact, for REINDEX cases where you don't have a lock on the entire
> > > table as you do in vacuum, you could reindex the table with a simple
> > > read-lock on the base table and index, and move the new index into place
> > > with the users seeing no change. Only people traversing the index
> > > during the change would have a problem. You just need an exclusive
> > > access on the index for the duration of the rename() so no one is
> > > traversing the index during the rename().
> > >
> > > Destroying the index and recreating opens a large time span that there
> > > is no index, and you have to jury-rig something so people don't try to
> > > use the index. With rename() you just put the new index in place with
> > > one operation. Just don't let people traverse the index during the
> > > change. The pointers to the heap tuples is the same in both indexes.
> > >
> > > In fact, with WAL, we will allow multiple physical files for the same
> > > table by appending the table oid to the file name. In this case, the
> > > old index could be deleted by rename, and people would continue to use
> > > the old index until they deleted the open file pointers. Not sure how
> > > this works in practice because new tuples would not be inserted into the
> > > old copy of the index.
> >
> > 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.

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