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.
Commen in the Fediverse @saint@river.group.lt