Allowing multiple DDL commands to run simultaneously

We use the word CONCURRENTLY to describe DDL that executes without
preventing select, insert, update or delete against a table. That is
not the topic discussed here.

I've been asked if we could consider allowing more types of DDL to run
at the same time as each other. Specifically, that all/most of the DDL
commands that currently take ShareUpdateExclusive lock should be able
to run side-by-side with each other, i.e. any of the following could
run together

VACUUM (without FULL), ANALYZE, CREATE/DROP INDEX CONCURRENTLY, CREATE
STATISTICS, ALTER TABLE VALIDATE, ALTER TABLE(options etc).

So you would be able to run a VACUUM while an ALTER TABLE VALIDATE was
running, or run an ANALYZE while running CREATE INDEX CONCURRENTLY.

The main blocker to this is that we hold ShareUpdateExclusiveLock on
each command until the end of the transaction.

Obviously there are places we need locks, such as the part of VACUUM
that reads the indexes does need a lock for the duration of the index
scan. The proposal here is to minimize the duration of those locks,
e.g. make VACUUM lock each index in turn and then drop the lock before
moving to the next index, so a DROP INDEX CONCURRENTLY would only be
blocked by a VACUUM if it was scanning that particular index at the
time of the command, and only for the duration of that scan. No doubt
other cases exist.

Removing such locks from the VACUUM/ANALYZE path would prevent autovac
cancelling itself when other forms of DDL are executed.

Proposal would be to add a new lock mode "ShareUpdate", which does not
conflict with itself and yet conflicts with "ShareUpdateExclusive" or
higher. (Hence, it is a strong lock type). DDL would take a
ShareUpdateLock on the table, then during critical portions of
commands it would take a ShareUpdateExclusiveLock and then release it
again before commit.

We would still want to prevent two VACUUMs or two ANALYZEs from
running concurrently, so we would still need a lock mechanism to
prevent that. I'll leave that implementation point open for now since
it could lead to sidetracking away from the main idea.

Implementation would be to introduce the new infrastructure, then make
it work for the VACUUM/CREATE INDEX CONCURRENTLY case, then work
through other commands one by one.

Thoughts?

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