`state_groups` (& `state_groups_state` & `state_group_edges`) are not fully purged alongside the rooms

[matrixbot]

This issue has been migrated from #12821.

---

While investigating my database size, I found that state_groups_state and state_groups hold many stale entries that reference old rooms that have long since been removed from rooms and all related events tables.

It appears these are not purged alongside other tables, particularly via the DELETE _synapse/admin/v1/rooms/{room_id} API (with purge: true)

Version information

• Version: Synapse 1.57.1

• Install method: Docker image

• Platform: Linux (Debian)