PostgreSQL Index Locking – What Type of Locks Are Needed When Creating a Postgres Index Concurrently?

indexlockingpostgresql

I know that when an index is being created concurrently, only a SHARE ShareUpdateExclusiveLock lock is needed.

But is there a more strict type of lock needed at the very start of the process for a small amount of time? Or is only a SHARE ShareUpdateExclusiveLock lock needed for the entire operation?

I'm asking because I'm wondering what types of timeouts are advantageous to set when creating an index concurrently.

Best Answer

Well, when documentation is not enough it is possible to looking directly in code. I will give links to already released PostgreSQL 13.1 (REL_13_1 tag) in order to avoid possible line number changes in future.

I probably will not describe the entire code path of the command from the client. By grammar we know that we need IndexStmt command node - same grammar for both concurrently and non-concurrently create index. Everything useful will start in ProcessUtilitySlow

And here, in comment to case T_IndexStmt, can be found direct answer for question:

/*
 * .... To avoid lock upgrade hazards, it's
 * important that we take the strongest lock that will
 * eventually be needed here, so the lockmode calculation
 * needs to match what DefineIndex() does.
 */
lockmode = stmt->concurrent ? ShareUpdateExclusiveLock
    : ShareLock;

In general PostgreSQL will do not upgrade lock during command execution. And will take the strongest lock that will eventually be needed. For CREATE INDEX CONCURRENTLY it's ShareUpdateExclusiveLock. Not a SHARE lock as mentioned in question.

But let's continue reading:

  • in case of partitioned table find_all_inheritors will acquire same lockmode on inheritors.
  • in transformIndexStmt can be found some locking modes, but NoLock and AccessShareLock - they are weaker than ShareUpdateExclusiveLock
  • in DefineIndex we see another reminder:
     * To avoid lock upgrade hazards, that lock should be at least
     * as strong as the one we take here.

And decide to take againg ShareUpdateExclusiveLock for concurrently build. This part of the code is noticeably long (still not as large as, for example, planner), but readable.

I am missing something? Probably. So let's build PostgreSQL with -DLOCK_DEBUG option to debug locking behavior and see that happens:

./configure  --prefix=/home/melkij/tmp/pgdev/inst --enable-cassert --enable-debug CFLAGS="-ggdb -Og -g3 -fno-omit-frame-pointer -DLOCK_DEBUG" --enable-tap-tests
make -sj 4
make install
initdb ...
pg_ctl start ...
# then psql with follow commands
create table foo as select generate_series(1,1000) as id;
set trace_locks = true;
create index concurrently on foo (id);
select oid, relname, relkind from pg_class where oid in (16387,16393);
#  oid  |  relname   | relkind 
#-------+------------+---------
# 16387 | foo        | r
# 16393 | foo_id_idx | i

trace_locks will log many things, but we are interested in:

[vxid:3/62 txid:0] [CREATE INDEX] LOG:  LockAcquire: lock [12664,16387] ShareUpdateExclusiveLock
[vxid:3/62 txid:0] [CREATE INDEX] LOG:  LockAcquire: lock [12664,16387] ShareUpdateExclusiveLock
[vxid:3/62 txid:0] [CREATE INDEX] LOG:  LockAcquire: lock [12664,16393] AccessExclusiveLock
[vxid:3/62 txid:495] [CREATE INDEX] LOG:  LockAcquire: lock [12664,16387] ShareUpdateExclusiveLock
[vxid:3/63 txid:0] [CREATE INDEX] LOG:  LockAcquire: lock [12664,16387] ShareUpdateExclusiveLock
[vxid:3/63 txid:0] [CREATE INDEX] LOG:  LockAcquire: lock [12664,16393] RowExclusiveLock
[vxid:3/64 txid:0] [CREATE INDEX] LOG:  LockAcquire: lock [12664,16387] ShareUpdateExclusiveLock
[vxid:3/64 txid:0] [CREATE INDEX] LOG:  LockAcquire: lock [12664,16393] RowExclusiveLock
[vxid:3/64 txid:0] [CREATE INDEX] LOG:  LockAcquire: lock [12664,16393] ExclusiveLock
[vxid:3/64 txid:0] [CREATE INDEX] LOG:  LockAcquire: lock [12664,16393] ExclusiveLock

lock [12664,16387] here means database OID = 12664, pg_class OID = 16387 (such rows are logged from LockAcquireExtended).

So, we actually acquire only ShareUpdateExclusiveLock on table itself. OID=16393 with several other locks is the index that this command built. Although heavy locking levels are mentioned for this object, it will not interfere with other queries. Create index concurrently command is specially designed to work safely while running normal application queries.

Related Question