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: state_groups_state, 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.