Thread: Concurrency issue with DROP INDEX CONCURRENTLY
Hello, I have an interesting concurrency issue with DROP INDEX CONCURRENTLY that I can summarize with this test scenario: /**************************************************/ — suppose we have this table and index create table test(x int); create index idx1 on test(x); — now suppose with the database “live” and the above table super busy (lots of queries on the table using index idx1), Idecide to make the index unique create unique index concurrently idx2 on test(x); — runs fine drop index concurrently idx1; — took 3 hours to finish, since the table is super busy /**************************************************/ Taking 3 hours to drop the index is not surprising (lots of queries on the table using idx1). What surprises me is the dropindex causes havoc with concurrency on the table, causing queries to pile up. Once the drop index finishes, everything goes back to normal. I thought by using the CONCURRENTLY option, the drop index is “safe” from concurrency issues for the underlying table, butin the above scenario it doesn’t appear to be “safe”. I am trying to formulate a theory to explain this. Any ideas? Regards, Kiriakos
On 2/9/23 07:45, Kiriakos Georgiou wrote: > Hello, > > I have an interesting concurrency issue with DROP INDEX CONCURRENTLY that I can summarize with this test scenario: > > /**************************************************/ > > — suppose we have this table and index > create table test(x int); > create index idx1 on test(x); > > — now suppose with the database “live” and the above table super busy (lots of queries on the table using index idx1),I decide to make the index unique > create unique index concurrently idx2 on test(x); — runs fine > drop index concurrently idx1; — took 3 hours to finish, since the table is super busy > > /**************************************************/ > > Taking 3 hours to drop the index is not surprising (lots of queries on the table using idx1). What surprises me is thedrop index causes havoc with concurrency on the table, causing queries to pile up. > Once the drop index finishes, everything goes back to normal. > > I thought by using the CONCURRENTLY option, the drop index is “safe” from concurrency issues for the underlying table,but in the above scenario it doesn’t appear to be “safe”. > > I am trying to formulate a theory to explain this. Any ideas? 1)From here: https://www.postgresql.org/docs/current/sql-dropindex.html "With this option, the command instead waits until conflicting transactions have completed." 2) Probably too late for this case, but info from https://www.postgresql.org/docs/current/view-pg-locks.html and https://www.postgresql.org/docs/15/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW would be useful. > > Regards, > Kiriakos > -- Adrian Klaver adrian.klaver@aklaver.com
My apologies - there is no issue with DROP INDEX CONCURRENTLY. It’s just brain fade on my part (I dropped the existing index before creating the new UNIQUE index, causing TPS on this tableto go to zero *facepalm*). Regards, Kiriakos > On Feb 9, 2023, at 10:45 AM, Kiriakos Georgiou <kg.postgresql@olympiakos.com> wrote: > > Hello, > > I have an interesting concurrency issue with DROP INDEX CONCURRENTLY that I can summarize with this test scenario: > > /**************************************************/ > > — suppose we have this table and index > create table test(x int); > create index idx1 on test(x); > > — now suppose with the database “live” and the above table super busy (lots of queries on the table using index idx1),I decide to make the index unique > create unique index concurrently idx2 on test(x); — runs fine > drop index concurrently idx1; — took 3 hours to finish, since the table is super busy > > /**************************************************/ > > Taking 3 hours to drop the index is not surprising (lots of queries on the table using idx1). What surprises me is thedrop index causes havoc with concurrency on the table, causing queries to pile up. > Once the drop index finishes, everything goes back to normal. > > I thought by using the CONCURRENTLY option, the drop index is “safe” from concurrency issues for the underlying table,but in the above scenario it doesn’t appear to be “safe”. > > I am trying to formulate a theory to explain this. Any ideas? > > Regards, > Kiriakos
On Thu, 2023-02-09 at 10:45 -0500, Kiriakos Georgiou wrote: > I have an interesting concurrency issue with DROP INDEX CONCURRENTLY that I > can summarize with this test scenario: > > /**************************************************/ > > — suppose we have this table and index > create table test(x int); > create index idx1 on test(x); > > — now suppose with the database “live” and the above table super busy (lots > of queries on the table using index idx1), I decide to make the index unique > create unique index concurrently idx2 on test(x); — runs fine > drop index concurrently idx1; — took 3 hours to finish, since the table is super busy > > /**************************************************/ > > Taking 3 hours to drop the index is not surprising (lots of queries on the table > using idx1). What surprises me is the drop index causes havoc with concurrency > on the table, causing queries to pile up. > Once the drop index finishes, everything goes back to normal. > > I thought by using the CONCURRENTLY option, the drop index is “safe” from concurrency > issues for the underlying table, but in the above scenario it doesn’t appear to be “safe”. DROP INDEX CONCURRENTLY also locks the table, but it waits until it finds a time when it can get the lock right away. Once it has the lock, it finishes the task. In the time when the table is locked, concurrent statements are blocked. This should not take a long time, but perhaps that is enough to cause the havoc you observe. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com