Re: Support for REINDEX CONCURRENTLY

On 3 October 2012 02:14, Michael Paquier <michael(dot)paquier(at)gmail(dot)com> wrote:

> Well, I spent some spare time working on the implementation of REINDEX
> CONCURRENTLY.

Thanks

> The following restrictions are applied.
> - REINDEX [ DATABASE | SYSTEM ] cannot be run concurrently.

Fair enough

> - indexes for exclusion constraints cannot be reindexed concurrently.
> - toast relations are reindexed non-concurrently when table reindex is done
> and that this table has toast relations

Those restrictions are important ones to resolve since they prevent
the CONCURRENTLY word from being true in a large proportion of cases.

We need to be clear that the remainder of this can be done in user
space already, so the proposal doesn't move us forwards very far,
except in terms of packaging. IMHO this needs to be more than just
moving a useful script into core.

> Here is a description of what happens when reorganizing an index
> concurrently

There are four waits for every index, again similar to what is
possible in user space.

When we refactor that, I would like to break things down into N
discrete steps, if possible. Each time we hit a wait barrier, a
top-level process would be able to switch to another task to avoid
waiting. This would then allow us to proceed more quickly through the
task. I would admit that is a later optimisation, but it would be
useful to have the innards refactored to allow for that more easily
later. I'd accept Not yet, if doing that becomes a problem in short
term.

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