Possibly solve synapse DB huge size issues caused by unreferenced state groups #478
Description
Issue : database growing crazy when using mautrix bridges.
More information : https://wiki.chatons.org/doku.php/services/messagerie_instantanee/matrix#nettoyer_les_evenements_orphelins
Solution
clean unreferenced state groups.
Provide a build of https://github.com/erikjohnston/synapse-find-unreferenced-state-groups
in the yunohost package
And clean-up cron task if app service registration file containing mautrix is present:
`
cd /etc/matrix-synapse/clean-unreferenced
systemctl stop synapse
# generate the list of orphan event
#SYNAPSE HAS TO BE STOPPED OTHERWISE IMPORTANT EVENTS MAY BE ERASED
#see erikjohnston/synapse-find-unreferenced-state-groups#8
rust-synapse-find-unreferenced-state-groups -p "postgresql://$DBUSER:$PGPASSWORD@localhost/$DBNAME" -o "/etc/matrix-synapse/clean-unreferenced/unreferenced.csv"
systemctl restart synapse
su -c /usr/bin/psql postgres
#size before cleanup
postgres=# SELECT pg_size_pretty( pg_database_size( 'synapse' ) );
synapse=# \c synapse
synapse=# CREATE TEMPORARY TABLE unreffed(id BIGINT PRIMARY KEY);
synapse=# COPY unreffed FROM '/home/user/unreferenced.csv' WITH (FORMAT 'csv');
synapse=# DELETE FROM state_groups_state WHERE state_group IN (SELECT id FROM unreffed);
synapse=# DELETE FROM state_group_edges WHERE state_group IN (SELECT id FROM unreffed);
synapse=# DELETE FROM state_groups WHERE id IN (SELECT id FROM unreffed);
synapse=# REINDEX (verbose) DATABASE synapse;
synapse=# VACUUM;
#size after cleanup
synapse=# SELECT pg_size_pretty( pg_database_size( 'synapse' ) );
`
If it's the first time the db is cleaned, then space freed in the DB should be released on disk:
synapse=# VACUUM FULL;
Note that this could also be implemented in each mautrix_ynh package rather than in synapse. But as it concerns cleaning the synapse DB, I guess it's better here.