A Blog

So, synapse is a neat tool, but it sucks at managing storage space. The built-in automatic room history purges works, but it doesn't delete the state data. If a matrix room gets spammed or just too large, you can't remove the state data without kicking all the users out of the room, deleting the data, then letting them rejoin. This can be mitigated with the rust compressor tool for synapse, which apparently has amazing squishing power, but I went a different route. Here are my notes from the process.

This is mostly based on forest's notes and this script.

Purge Remote Media

• get a timestamp in unix seconds: date +%s000 --date "10 days ago"
  • everything before this date will be purged
• curl -k -X POST "http://localhost:8008/_synapse/admin/v1/purge_media_cache?before_ts=$your-timestamp-here&access_token=$your-access-token"

Purge Big Rooms

• use some forest scripts

Purge Rooms from Database

• get a list of the biggest rooms
  • docker compose exec postgresql psql -U synapse -d synapse -c "select room_id, count(*) from state_groups_state group by room_id order by count(*) DESC LIMIT 100"
  • run this inside the synapse directory
  • copy biggest rooms to a text file somewhere
• use kick-user-purge-room script in snippets/matrix/kick-user-purge-room for each room id (must be wrapped in single quotes)
  • ./kick-user-purge-room '!some-room-id'

Purge State Group IDs

• Get all the state group ids from the deleted rooms
  • docker compose exec postgresql psql -U synapse -d synapse -c " SELECT id from state_groups where room_id = '"'!'"some-room-id' OR room_id = '"'!'"some-room-id'; " > stategroups1.txt
  • docker compose exec postgresql psql -U synapse -d synapse -c " SELECT id from state_groups where room_id = '"'!'"some-room-id' OR room_id = '"'!'"some-room-id'; " > stategroups2.txt
• remove header and footer from the stategroups files with a text editor
• sort the state group ids and convert them to postgres delete statements
  • cat ./stategroups1.txt | sort | sed -E 's/([0-9]+)/DELETE FROM state_groups_state where state_group = \1;/' > stategroups1.sql
  • cat ./stategroups2.txt | sort | sed -E 's/([0-9]+)/DELETE FROM state_groups_state where state_group = \1;/' > stategroups2.sql
• move the files into the docker container sudo cp ./stategroups{1,2}.sql ./postgresdata/
• delete the state group state!
  • docker compose exec postgresql psql -U synapse -d synapse -f /var/lib/postgresql/data/stategroups1.sql
  • docker compose exec postgresql psql -U synapse -d synapse -f /var/lib/postgresql/data/stategroups2.sql

Cleanup

• delete from other state group related tables
  • docker compose exec postgresql psql -U synapse -d synapse -c "DELETE FROM state_group_edges where state_group in (SELECT id from state_groups where room_id = '"'!'"some-room-id'); "
  • docker compose exec postgresql psql -U synapse -d synapse -c "DELETE FROM event_to_state_groups where state_group in (SELECT id from state_groups where room_id = '"'!'"some-room-id'); "
  • docker compose exec postgresql psql -U synapse -d synapse -c "DELETE FROM state_groups where room_id = '"'!'"some-room-id'; "

Remove table and index bloat

• commands from this excellent article on cleaning up synapse bloat
  • I didn't use the rust compressor tool because I used forest's notes to delete the state of the massive rooms instead
• reindex the synapse database
  • docker compose exec postgresql psql -U synapse -d synapse -c "REINDEX (VERBOSE) DATABASE synapse";
• vaccum up the table bloat
  • docker compose exec postgresql psql -U synapse -d synapse -c "VACUUM FULL VERBOSE";

DONE!