`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)

[alan7000]

Hello, I see that since the migration issue from Matrix to Element HQ, there has been no further activity, even though the problem still exists.
Am I the only one still experiencing this issue with orphaned entries in the relevant tables?

[miguelrdev]

I still experience this issue. Check out my state_groups_state table:

relation | total_size ----------------------------------------------+------------ public.state_groups_state | 178 GB

My server is only a few users and I left all the rooms, did a bunch of purging and it still balloons like crazy.