Is `lock_timeout` necessary for a Postgres CREATE INDEX CONCURRENTLY

Question

When running CREATE INDEX CONCURRENTLY, can you lock up a table while the SHARE UPDATE EXCLUSIVE is being acquired? If it took 10 minutes to acquire the lock, would anyone be blocked from using the table during that time?

The point of running CONCURRENTLY is to safely add an index to an active table. But what I can't find a clear answer on is whether the initial lock being acquired can cause queries to queue up.

The documentation for a particular safe postgres migrations library mentions (https://github.com/doctolib/safe-pg-migrations#user-content-safe_add_remove_index):

If you still get lock timeout while adding / removing indexes, it might be for one of those reasons:

Long-running queries are active on the table. To create / remove an index, PG needs to wait for the queries that are actually running to finish before starting the index creation / removal. The blocking activity logger might help you to pinpoint the culprit queries.

A vacuum / autovacuum is running on the table, holding a ShareUpdateExclusiveLock, you are most likely out of luck for the current migration, but you may try to optimize your autovacuums settings.

But even that doesn't clearly tell me - if I don't set a timeout (so the timeout is 0), and I wait a long time for the lock to get acquired - will that cause other queries to wait until the lock is acquired or will the ADD INDEX be the only thing blocked during this time?

@AdrianKlaver yes, I have read that. Which specific part of that documentation answers my question? — jpcamara
– jpcamara, Commented Mar 16, 2022 at 14:57
Hey @AdrianKlaver, I think i'll wait for other more specific responses. But thanks for giving input. My specific question is in regards to how it can take time to even start building the index because of acquiring the SHARE UPDATE EXCLUSIVE lock. – The documentation you reference speaks at a high level about this impact. I am looking for someone ideally with real production experience, who has worked in a high load environment running concurrent indexes on active tables (10s of thousands of transactions per second) with no statement timeout applied. — jpcamara
– jpcamara, Commented Mar 16, 2022 at 15:41
@AdrianKlaver I do believe most general SO guidelines prefer you do not just reference articles, with no specific, quoted references called out. Two years from now those first two paragraphs could change, and so what you're specifically referring might be located somewhere else in that section. Something to keep in mind when commenting on future questions. — jpcamara
– jpcamara, Commented Mar 16, 2022 at 15:42

jjanes · Accepted Answer · 2022-03-16 17:31:22Z

3

It will block the things that ShareUpdateExclusive blocks. But not ordinary SELECT, INSERT, UPDATE, DELETE.

The most troubling thing it blocks might be ANALYZE. If the stats get too out of date and can't be repaired, your SELECTs might start choosing ridiculous plans which never finish, despite not being formally blocked.

It does have to transiently acquire stronger locks than ShareUpdateExclusive, but if it has to wait for them it does so in an indirect way which doesn't block others.

answered Mar 16, 2022 at 17:31

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Is `lock_timeout` necessary for a Postgres CREATE INDEX CONCURRENTLY

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related