Add background job to clear unreferenced state groups

[erikjohnston]

After fixing #9406 and #17937, we still have a bunch of unreferenced state groups in the DB which point to the full state, causing lots of unnecessary DB usage. We should add a background job to go and delete unreferenced state groups.

Note that we can still have new unreferenced state groups, just that when we purge history we won't de-delta the state group entries and instead delete them.

A one-off background job to delete unreferenced state groups would:

1. Record the current max state group
2. Each run would look at the next N state groups (in ascending order) via state_groups table, and check if there are any unreferenced ones. Note that we need to check both event_to_state_groups and state_group_edges tables.
3. Call _find_unreferenced_groups on any unreferenced state groups to get others that can be deleted if the given state group is deleted.
4. Call mark_state_groups_as_pending_deletion to schedule them for deletion

I'm also wondering if instead of having this as a one off job we do this periodically to catch new unreferenced state groups.

[devonh]

While implementing this I noticed that when the deletion task runs, it doesn't clean up the state_groups_pending_deletion table. It leaves the entries in there.

We'll need to address that as well, otherwise that table will grow endlessly.

[devonh]

Also - should we be cleaning up any state_group_edges that are dangling after the deletion as well?

[devonh]

@erikjohnston thoughts on cleaning up state_groups_pending_deletion & state_group_edges tables? ^

[pmaier1]

Any idea which Synapse release we would be targeting for this? (re https://github.com/element-hq/backend-internal/issues/75#issuecomment-2653987772)

[erikjohnston]

@erikjohnston thoughts on cleaning up state_groups_pending_deletion & state_group_edges tables? ^

Err, yes they should be cleaned up too

[devonh]

State Group table cleanup PR: #18165