Re: Support for REINDEX CONCURRENTLY

On 2013-01-25 13:48:50 +0900, Michael Paquier wrote:
> All the comments are addressed in version 8 attached, except for the
> hashtable part, which requires some heavy changes.
> 
> On Thu, Jan 24, 2013 at 3:41 AM, Andres Freund <andres@2ndquadrant.com>wrote:
> 
> > On 2013-01-15 18:16:59 +0900, Michael Paquier wrote:
> > > Code path used by REINDEX concurrently permits to
> > > create an index in parallel of an existing one and not a completely new
> > > index. Shouldn't this work for indexes used by exclusion indexes also?
> >
> > But that fact might safe things. I don't immediately see any reason that
> > adding a
> > if (!indisvalid)
> >    return;
> > to check_exclusion_constraint wouldn't be sufficient if there's another
> > index with an equivalent definition.
> >
> Indeed, this might be enough as for CREATE INDEX CONCURRENTLY this code
> path cannot be taken and only indexes created concurrently can be invalid.
> Hence I am adding that in the patch with a comment explaining why.

I don't really know anything about those mechanics, so some input from
somebody who does would be very much appreciated.

> > > > > +     /*
> > > > > +      * Phase 2 of REINDEX CONCURRENTLY
> > > > > +      */
> > > > > +
> > > > > +     /* Get the first element of concurrent index list */
> > > > > +     lc2 = list_head(concurrentIndexIds);
> > > > > +
> > > > > +     foreach(lc, indexIds)
> > > > > +     {
> > > > > +             WaitForVirtualLocks(*heapLockTag, ShareLock);
> > > >
> > > > Why do we have to do the WaitForVirtualLocks here? Shouldn't we do this
> > > > once for all relations after each phase? Otherwise the waiting time will
> > > > really start to hit when you do this on a somewhat busy server.
> > > >
> > > Each new index is built and set as ready in a separate single
> > transaction,
> > > so doesn't it make sense to wait for the parent relation each time. It is
> > > possible to wait for a parent relation only once during this phase but in
> > > this case all the indexes of the same relation need to be set as ready in
> > > the same transaction. So here the choice is either to wait for the same
> > > relation multiple times for a single index or wait once for a parent
> > > relation but we build all the concurrent indexes within the same
> > > transaction. Choice 1 makes the code clearer and more robust to my mind
> > as
> > > the phase 2 is done clearly for each index separately. Thoughts?
> >
> > As far as I understand that code its purpose is to enforce that all
> > potential users have an up2date definition available. For that we
> > acquire a lock on all virtualxids of users using that table thus waiting
> > for them to finish.
> > Consider the scenario where you have a workload where most transactions
> > are fairly long (say 10min) and use the same tables (a,b)/indexes(a_1,
> > a_2, b_1, b_2). With the current strategy you will do:
> >
> > WaitForVirtualLocks(a_1) -- wait up to 10min
> > index_build(a_1)
> > WaitForVirtualLocks(a_2) -- wait up to 10min
> > index_build(a_2)
> >
> ...
> >
> > So instead of waiting up 10 minutes for that phase you have to wait up
> > to 40.
> >
> This is necessary if you want to process each index entry in a different
> transaction as WaitForVirtualLocks needs to wait for the locks held on the
> parent table. If you want to fo this wait once per transaction, the
> solution would be to group the index builds in the same transaction for all
> the indexes of the relation. One index per transaction looks more solid in
> this case if there is a failure during a process only one index will be
> incorrectly built.

I cannot really follow you here. The reason why we need to wait here is
*only* to make sure that nobody still has the old list of indexes
around (which probably could even be relaxed for reindex concurrently,
but thats a separate optimization).
So if we wait for all relevant transactions to end before starting phase
2 proper, we are fine, independent of how many indexes we build in a
single transaction.

> Also, when you run a REINDEX CONCURRENTLY, you should
> not need to worry about the time it takes. The point is that this operation
> is done in background and that the tables are still accessible during this
> time.

I don't think that arguments holds that much water. Having open
transactions for too long *does* incur a rather noticeable overhead. And
you definitely do want such operations to finish as quickly as possible,
even if its just because you can go home only afterwards ;)

Really, imagine doing this too 100 indexes on a system where
transactions regularly take 30 minutes (only needs one at a time). Minus
the actual build-time thats very approx 4h against like half a month.

> > Btw, seing that we have an indisvalid check the toast table's index, do
> > we have any way to cleanup such a dead index? I don't think its allowed
> > to drop the index of a toast table. I.e. we possibly need to relax that
> > check for invalid indexes :/.
> >
> For the time being, no I don't think so, except by doing a manual cleanup
> and remove the invalid pg_class entry in catalogs. One way to do thath
> cleanly could be to have autovacuum remove the invalid toast indexes
> automatically, but it is not dedicated to that and this is another
> discussion.

Hm. Don't think thats acceptable :/

As I mentioned somewhere else, I don't see how to do an concurrent build
of the toast index at all, given there is exactly one index hardcoded in
tuptoaster.c so the second index won't get updated before the switch has
been made.

Haven't yet looked at the new patch - do you plan to provide an updated
version addressing some of the remaining issues soon? Don't want to
review this if you nearly have the next version available.

Greetings,

Andres
-- Andres Freund                       http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training &
Services