Reclaiming space in synapse postgresql database
Follow the fat elephant
I have received an alert from Grafana – that my synapse directory is almost full, which was kinda strange as I have given 100GB partition to it just a couple of weeks ago.. So I have put a hat, picked up some cider and something to smoke and went to the adventure.
From the old times I knew that postgresql database size can be reduced using vacuumdb. Entered the container and boom – after 15 or so minutes it has finished and reclaimed 100MB of space.. Hmmm... Interesting – which table eats the space. Google, link
SELECT relname AS "relation", pg_size_pretty ( pg_total_relation_size (C .oid) ) AS "total_size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C .relnamespace) WHERE nspname NOT IN ( 'pg_catalog', 'information_schema' ) AND C .relkind <> 'i' AND nspname !~ '^pg_toast' ORDER BY pg_total_relation_size (C .oid) DESC LIMIT 5;
relation | total_size --------------------+------------ state_groups_state | 65 GB event_json | 1197 MB event_edges | 619 MB events | 595 MB event_auth | 528 MB
Alright!!! Google: stategroupsstate, link and found a compression tool.
git clone, crap a short docker-compose.yml and build the tool.
root@instance-20211112-2005:/opt/synapse-compress-state# cat docker-compose.yaml --- version: "3.5" services: synapse-compress: build: context: rust-synapse-compress-state/ command: synapse_auto_compressor -p postgresql://user:pass@dbhost/dbname -c 500 -n 100 networks: - synapse networks: synapse: name: synapse
let's crap some more:
root@instance-20211112-2005:/opt/synapse# cat /opt/synapse-compress-state/run.sh #!/bin/bash cd /opt/synapse-compress-state/ docker-compose up
put it into crontab:
@daily /opt/synapse-compress-state/run.sh > /dev/null
later googled more and found some smarter people than me: shrink synapse database and that really helped, especially reindexing.