RE: [HACKERS] Index recreation in vacuum

> -----Original Message-----
> From: Bruce Momjian [mailto:pgman(at)candle(dot)pha(dot)pa(dot)us]
>
> > > I don't know how people estimate this disadvantage.
> >
> > That's why I was recommending rename(). The actual window of
> > vunerability goes from perhaps hours to fractions of a second.
> >
> > In fact, if I understand this right, you could make the vulerability
> > zero by just performing the rename as one operation.
> >
> > 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.

Regards.

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