Re: [HACKERS] Index recreation in vacuum

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

If we don't do that and vacuum fails, what state is the table left in?
If we don't update the index for every tuple, the index is invalid in a
vacuum failure. rename() is not going to help us here. It keeps the
old index around, but the index is invalid anyway, right?

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