Step 1: delete unused large rooms

Step 1.1: Find large rooms

SELECT r.name, s.room_id, s.current_state_events
    FROM room_stats_current s
    LEFT JOIN room_stats_state r USING (room_id)
    ORDER BY current_state_events DESC
    LIMIT 20;

SELECT rss.name, s.room_id, COUNT(s.room_id)
    FROM state_groups_state s
    LEFT JOIN room_stats_state rss USING (room_id)
    GROUP BY s.room_id, rss.name
    ORDER BY COUNT(s.room_id) DESC
    LIMIT 20;

https://matrix-org.github.io/synapse/latest/usage/administration/useful_sql_for_admins.html#show-top-20-larger-rooms-by-state-events-count

Step 1.2: Use Admin API Purge the unused large rooms

https://matrix-org.github.io/synapse/latest/admin_api/rooms.html#version-2-new-version

curl "http://localhost:8008/_synapse/admin/v1/rooms/${ROOMID}" -X DELETE -H 'Accept: application/json' -H 'Referer: http://localhost:8080/' -H "authorization: Bearer ${TOKEN}" --data '{ "purge": true, "message": "Sorry - kicking you out to clean up the database" }'

Step 1.3: Delete orphan state_groups_state and state_groups

After using the Admin API to purge the rooms, but only removes the data in the events table, and there are still a lot of related room data in the state_groups_state and state_groups tables (the main space occupation). This may be a synapse bug.

So you need to remove them manually.

https://github.com/matrix-org/synapse/issues/12821#issuecomment-1295773504

DELETE FROM
state_groups_state
WHERE
room_id IN (
    SELECT
    DISTINCT(state_groups.room_id) AS room_id_gone
    FROM
    state_groups
    LEFT JOIN events USING(room_id)
    WHERE
    events.room_id IS NULL
    GROUP BY
    room_id_gone
);

DELETE FROM
state_groups
WHERE
room_id IN (
    SELECT
    DISTINCT(state_groups.room_id) AS room_id_gone
    FROM
    state_groups
    LEFT JOIN events USING(room_id)
    WHERE
    events.room_id IS NULL
    GROUP BY
    room_id_gone
);

Step 2: Run synapse_auto_compressor

https://github.com/matrix-org/rust-synapse-compress-state

git clone https://github.com/matrix-org/rust-synapse-compress-state.git

cd rust-synapse-compress-state/
cargo build -r

cd synapse_auto_compressor/
cargo build -r

cd ../target/release/

./synapse_auto_compressor -p postgresql://synapse_user:[password]@[host]/synapse -c 1000 -n 10000000 | tee ~/synapse_auto_compressor.log

Step 3: Vacuum Database

REINDEX (VERBOSE) DATABASE synapse;
VACUUM FULL VERBOSE;

Before:

/images/2023/how-to-clean-up-the-synapse-database/01.thumbnail.png

After:

/images/2023/how-to-clean-up-the-synapse-database/02.thumbnail.png