2 files changed, 10 insertions, 2 deletions
diff --git a/synapse/storage/databases/main/sliding_sync.py b/synapse/storage/databases/main/sliding_sync.py
index a287fd2a3f..6a62b11d1e 100644
--- a/synapse/storage/databases/main/sliding_sync.py
+++ b/synapse/storage/databases/main/sliding_sync.py
@@ -68,6 +68,14 @@ class SlidingSyncStore(SQLBaseStore):
             columns=("membership_event_id",),
         )
 
+        self.db_pool.updates.register_background_index_update(
+            update_name="sliding_sync_membership_snapshots_user_id_stream_ordering",
+            index_name="sliding_sync_membership_snapshots_user_id_stream_ordering",
+            table="sliding_sync_membership_snapshots",
+            columns=("user_id", "event_stream_ordering"),
+            replaces_index="sliding_sync_membership_snapshots_user_id",
+        )
+
     async def get_latest_bump_stamp_for_room(
         self,
         room_id: str,
diff --git a/synapse/storage/schema/main/delta/92/03_ss_membership_snapshot_idx.sql b/synapse/storage/schema/main/delta/92/04_ss_membership_snapshot_idx.sql
index c694203f95..6f5b7cb06e 100644
--- a/synapse/storage/schema/main/delta/92/03_ss_membership_snapshot_idx.sql
+++ b/synapse/storage/schema/main/delta/92/04_ss_membership_snapshot_idx.sql
@@ -12,5 +12,5 @@
 -- <https://www.gnu.org/licenses/agpl-3.0.html>.
 
 -- So we can fetch all rooms for a given user sorted by stream order
-DROP INDEX IF EXISTS sliding_sync_membership_snapshots_user_id;
-CREATE INDEX IF NOT EXISTS sliding_sync_membership_snapshots_user_id ON sliding_sync_membership_snapshots(user_id, event_stream_ordering);
+INSERT INTO background_updates (ordering, update_name, progress_json) VALUES
+  (9204, 'sliding_sync_membership_snapshots_user_id_stream_ordering', '{}');