Horton

Re: [DOC] Document concurrent index builds waiting on each other

Alvaro Herrera <alvherre@2ndquadrant.com>

View thread View on postgr.es
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: James Coleman <jtc331@gmail.com>
Cc: Bruce Momjian <bruce@momjian.us>, pgsql-hackers <pgsql-hackers@postgresql.org>
Date: 2019-09-29T15:27:09Z
Lists: pgsql-hackers 
On 2019-Sep-28, James Coleman wrote:

> I believe caveats like this are worth calling out rather than
> expecting users to have to understand the implementation details an
> work out the implications on their own.

I agree.

> I read Alvaro as referring to the fact that the docs already call out
> the following:
> 
> > Regular index builds permit other regular index builds on the same
> > table to occur simultaneously, but only one concurrent index build
> > can occur on a table at a time.

Yeah, that's what I was understanding.

BTW I think there's an approach that could alleviate part of this
problem, at least some of the time: whenever CIC runs for an index
that's not on expression and not partial, we could set the
PROC_IN_VACUUM flag.  That would cause it to get ignored by other
processes for snapshot purposes (including CIC itself), as well as by
vacuum.  I need to take some time to research the safety of this, but
intuitively it seems safe.

Even further, I think we could also do it for regular CREATE INDEX
(under the same conditions) provided that it's not run in a transaction
block.  But that requires even more research/proof.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Commits

  1. Call out vacuum considerations in create index docs

  2. Document concurrent indexes waiting on each other

  3. snapshot scalability: Don't compute global horizons while building snapshots.

  4. Remove PROC_IN_ANALYZE and derived flags

  5. Improve performance of get_actual_variable_range with recently-dead tuples.