My HS has accumulated thousands of unreferenced state groups

[matrixbot]

This issue has been migrated from #3364.

---

... which are filling up my disk :(

---

To check if you are also affected, run this query:

select count(*) from state_groups sg
    left join event_to_state_groups esg on esg.state_group=sg.id
    left join state_group_edges e on e.prev_state_group=sg.id
where esg.state_group is null and e.prev_state_group is null;

if you see numbers in the thousands, then it is this issue. Otherwise, you're not affected by this issue.

[n-peugnet]

Is Erik Johnston's tool still the best way to get rid of the unreferenced state groups? Because they indeed are filling up my disk, and the repo has not been touched for 2 years.

[martinvonwittich]

Is Erik Johnston's tool still the best way to get rid of the unreferenced state groups? Because they indeed are filling up my disk, and the repo has not been touched for 2 years.

I would also like to know what's currently the best way to deal with this.

Our unreferenced state_groups don't number in the thousands...

matrix=# select count(*) from state_groups sg
    left join event_to_state_groups esg on esg.state_group=sg.id
    left join state_group_edges e on e.prev_state_group=sg.id
where esg.state_group is null and e.prev_state_group is null;
  count   
----------
 32228549
(1 Zeile)

matrix=# select count(*) from state_groups;
  count   
----------
 33246015
(1 Zeile)

matrix=# SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_relation_size(C.oid)) AS "size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  ORDER BY pg_relation_size(C.oid) DESC
  LIMIT 20;
                   relation                    |  size   
-----------------------------------------------+---------
 public.state_groups_state                     | 7480 MB
 public.event_json                             | 7035 MB
 public.state_groups                           | 4600 MB
 public.state_groups_state_type_idx            | 4142 MB
 public.event_search                           | 1666 MB
 public.events                                 | 1508 MB
 public.state_group_edges                      | 1445 MB
 public.state_group_edges_unique_idx           | 1040 MB
 public.push_rules_stream                      | 862 MB
 public.event_edges                            | 849 MB
 public.state_groups_pkey                      | 712 MB
 public.event_edges_event_id_prev_event_id_idx | 677 MB
 public.events_order_room                      | 540 MB
 public.events_room_stream                     | 490 MB
 public.events_jump_to_date_idx                | 490 MB
 public.push_rules_stream_user_stream_id       | 473 MB
 public.event_to_state_groups                  | 460 MB
 public.event_json_event_id_key                | 378 MB
 public.events_event_id_key                    | 378 MB
 public.event_to_state_groups_event_id_key     | 378 MB
(20 Zeilen)