Drop indices concurrently on background updates (#18091)

Otherwise these can race with other long running queries and lock out
all other queries.

This caused problems in v1.22.0 as we added an index to `events` table
in #17948, but that got interrupted and so next time we ran the
background update we needed to delete the half-finished index. However,
that got blocked behind some long running queries and then locked other
queries out (stopping workers from even starting).

1 files changed, 2 insertions, 2 deletions

diff --git a/synapse/storage/background_updates.py b/synapse/storage/background_updates.py

index 34139f580d..a02b4cc9ce 100644
--- a/synapse/storage/background_updates.py
+++ b/synapse/storage/background_updates.py
@@ -789,7 +789,7 @@ class BackgroundUpdater:
                 # we may already have a half-built index. Let's just drop it
                 # before trying to create it again.
 
-                sql = "DROP INDEX IF EXISTS %s" % (index_name,)
+                sql = "DROP INDEX CONCURRENTLY IF EXISTS %s" % (index_name,)
                 logger.debug("[SQL] %s", sql)
                 c.execute(sql)
 
@@ -814,7 +814,7 @@ class BackgroundUpdater:
 
                 if replaces_index is not None:
                     # We drop the old index as the new index has now been created.
-                    sql = f"DROP INDEX IF EXISTS {replaces_index}"
+                    sql = f"DROP INDEX CONCURRENTLY IF EXISTS {replaces_index}"
                     logger.debug("[SQL] %s", sql)
                     c.execute(sql)
             finally: