Add background job to clear unreferenced state groups (#18154)

Fixes #18150 

### Pull Request Checklist

<!-- Please read
https://element-hq.github.io/synapse/latest/development/contributing_guide.html
before submitting your pull request -->

* [X] Pull request is based on the develop branch
* [x] Pull request includes a [changelog
file](https://element-hq.github.io/synapse/latest/development/contributing_guide.html#changelog).
The entry should:
- Be a short description of your change which makes sense to users.
"Fixed a bug that prevented receiving messages from other servers."
instead of "Moved X method from `EventStore` to `EventWorkerStore`.".
  - Use markdown where necessary, mostly for `code blocks`.
  - End with either a period (.) or an exclamation mark (!).
  - Start with a capital letter.
- Feel free to credit yourself, by adding a sentence "Contributed by
@github_username." or "Contributed by [Your Name]." to the end of the
entry.
* [X] [Code
style](https://element-hq.github.io/synapse/latest/code_style.html) is
correct
(run the
[linters](https://element-hq.github.io/synapse/latest/development/contributing_guide.html#run-the-linters))

---------

Co-authored-by: Erik Johnston <erikj@element.io>

2 files changed, 17 insertions, 0 deletions

diff --git a/synapse/storage/schema/__init__.py b/synapse/storage/schema/__init__.py

index 49e648a92f..c90c2c6051 100644
--- a/synapse/storage/schema/__init__.py
+++ b/synapse/storage/schema/__init__.py
@@ -158,6 +158,7 @@ Changes in SCHEMA_VERSION = 88
 
 Changes in SCHEMA_VERSION = 89
     - Add `state_groups_pending_deletion` and `state_groups_persisting` tables.
+    - Add background update to delete unreferenced state groups.
 """
 
 
diff --git a/synapse/storage/schema/state/delta/89/02_delete_unreferenced_state_groups.sql b/synapse/storage/schema/state/delta/89/02_delete_unreferenced_state_groups.sql
new file mode 100644

index 0000000000..184dc8564c
--- /dev/null
+++ b/synapse/storage/schema/state/delta/89/02_delete_unreferenced_state_groups.sql
@@ -0,0 +1,16 @@
+--
+-- 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>.
+
+-- Add a background update to delete any unreferenced state groups
+INSERT INTO background_updates (ordering, update_name, progress_json) VALUES
+  (8902, 'delete_unreferenced_state_groups_bg_update', '{}');