Add index to sliding sync membership snapshot table, to fix a performance issue. (#18074)

To address a performance problem due to the foreign key on the same
column.

cc @erikjohnston

---------

Signed-off-by: Olivier 'reivilibre <oliverw@matrix.org>

1 files changed, 15 insertions, 0 deletions

diff --git a/synapse/storage/schema/main/delta/89/01_sliding_sync_membership_snapshot_index.sql b/synapse/storage/schema/main/delta/89/01_sliding_sync_membership_snapshot_index.sql
new file mode 100644

index 0000000000..7799cffdce
--- /dev/null
+++ b/synapse/storage/schema/main/delta/89/01_sliding_sync_membership_snapshot_index.sql
@@ -0,0 +1,15 @@
+--
+-- This file is licensed under the Affero General Public License (AGPL) version 3.
+--
+-- Copyright (C) 2025 New Vector, Ltd
+--
+-- This program is free software: you can redistribute it and/or modify
+-- it under the terms of the GNU Affero General Public License as
+-- published by the Free Software Foundation, either version 3 of the
+-- License, or (at your option) any later version.
+--
+-- See the GNU Affero General Public License for more details:
+-- <https://www.gnu.org/licenses/agpl-3.0.html>.
+
+INSERT INTO background_updates (ordering, update_name, progress_json) VALUES
+    (8901, 'sliding_sync_membership_snapshots_membership_event_id_idx', '{}');