step 1: generate a list of the rooms with most rows in state_groups_state:
    
sudo -u postgres psql -d synapse -c "select room_id, count(*) from state_groups_state group by room_id order by count(*) DESC LIMIT 100" | tee /root/large-rooms.txt

step 2: decide which rooms you want to "bonk"

step 3: call /root/kick-users-and-temp-delete-room for each room. Here is the redacted content of that script:


#!/bin/bash

roomid="$1"

[ -z "$roomid" ] && exit 1

echo "getting list of users in $roomid..."

curl -sS "localhost:8008/_synapse/admin/v1/rooms/$roomid/members?access_token=xxxxxxxxxxxxxx" | jq -r '.members[]' | grep 'cyberia.club'


echo "deleting $roomid..."

curl -H "Content-Type: application/json" -X DELETE "localhost:8008/_synapse/admin/v2/rooms/$roomid?access_token=xxxxxxxxxxxxxx" \
	--data '{ "block": false, "force_purge": true, "purge": true, "message": "This room is being cleaned, stand by..." }'

echo ''
echo 'GET /_synapse/admin/v2/rooms/$roomid/delete_status : '

curl -sS "localhost:8008/_synapse/admin/v2/rooms/$roomid/delete_status?access_token=xxxxxxxxxxxxxx"




step 4: poll /root/get-delete-room-status for each room until all the statuses are "completed".
Here is the redacted content of that script:

#!/bin/bash

roomid="$1"

[ -z "$roomid" ] && exit 1

echo ''
echo 'GET /_synapse/admin/v2/rooms/$roomid/delete_status : '

curl -sS "localhost:8008/_synapse/admin/v2/rooms/$roomid/delete_status?access_token=xxxxxxxxxxxxxx"


step 5:  Get all the state group ids for the deleted rooms: 
NOTE: in this example it selects for 2 rooms. you can just keep spamming OR statements to get all the rooms. 

sudo -u postgres psql -d synapse -c " SELECT id from state_groups where room_id = '"'!'"tgbPIxPlvGiwpsGJuu:matrix.org' OR  room_id = '"'!'"REMyNDGslqLUQnGoxg:matrix.org'; " > /tmp/stategroups1.txt

step 5 and a half:  manually edit the tmp/stategroups1.txt file to remove any sort of header and footer from the postgres output:

nano /tmp/stategroups1.txt

step 6:  sort the state group IDs and convert them to state_groups_state DELETE statements: 

cat /tmp/stategroups1.txt | sort | sed -E 's/([0-9]+)/DELETE FROM state_groups_state where state_group = \1;/' > /tmp/stategroups2.sql

step 7:  run all the DELETEs:

sudo -u postgres psql -d synapse -f /tmp/stategroups2.sql

step 8: For each room, Delete from the other state group related tables that are not as row-dense as state_groups_state

sudo -u postgres psql -d synapse -c " DELETE FROM state_group_edges where state_group in (SELECT id from state_groups where room_id = '"'!'"YTvKGNlinIzlkMTVRl:matrix.org'); "
sudo -u postgres psql -d synapse -c " DELETE FROM event_to_state_groups where state_group in (SELECT id from state_groups where room_id = '"'!'"YTvKGNlinIzlkMTVRl:matrix.org'); "

step 9: delete from the state groups  table for each room: 

sudo -u postgres psql -d synapse -c " DELETE FROM  state_groups where room_id = '"'!'"YTvKGNlinIzlkMTVRl:matrix.org'; "