Re: concurrent index builds unneeded lock?

On Sun, Jul 12, 2009 at 4:42 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> I'm kind of wondering how big the use case for that really is.
> AFAICT the point of a concurrent build is to (re)build an index
> without incurring too much performance penalty for foreground
> query processing.  So how often are you really going to want
> to fire off several of them in parallel?  If you can afford to
> saturate your machine with indexing work, you could use plain
> index builds.

I don't really see those as comparable cases. Firing off multiple
concurrent index builds only requires lots of available I/O
throughput; using plain index builds requires a maintenance window
where any updates to the table is shut down.

Being able to run multiple concurrent index builds just means being
able to roll out a schema change more quickly. It doesn't let you do
anything that was impossible before.

Another thing that's annoyed me about our current support for
concurrent index builds is that you can't run multiple concurrent
builds on the same table. Since they all take the strangely named
ShareUpdateExclusiveLock you can only run one at a time. Fixing that
would require introducing a new, uh, ShareUpdateSharedLock(?) which
conflicts with the vacuum lock but not itself. It didn't seem worth
introducing a new lock type at the time but with syncscanning and the
evidence people are actually doing this I'm starting to wonder.

--
greg
http://mit.edu/~gsstark/resume.pdf