HF 6c031ca0fa add ability to clean state of a specific room to purge script 2024-01-14 00:30:48 +01:00
.. add state_groups_state sql by room 2024-01-13 17:55:09 +01:00 add ability to clean state of a specific room to purge script 2024-01-14 00:30:48 +01:00

Clean up postgres synapse database

Shell script that cleans up the matrix postgresql database:

  • removes push notifications that aren't needed
  • kicks out inactive users from rooms
  • cleans up states with synapse_auto_compressor
  • deletes messages that are older than 14 days from rooms


  1. Set SQL credentials, URL and homeserver to local matrix in the script
  2. build synapse_auto_compressor and set its path in the script
  3. make sure that the bridge did start at least once (it creates rooms and adds an admin user that we need)
  4. make sure that you do NOT have a Message Retention Policy set, because this script does it for you, however, media_retention is still needed. This script will not delete any media.
  5. add it as a cron job, like:
0 2,8,14,23 * * * root /etc/matrix-synapse/
12 11 * * 0 root /etc/matrix-synapse/ reset

The "reset" argument is for resetting the synapse_auto_compressor, it shouldn't be run often, but might come in hany if the compressor ends up in a weird state:

/etc/matrix-synapse/ reset

Further resources

Useful commands

check currently active queries:

SELECT pid, query, NOW() - query_start AS elapsed FROM pg_stat_activity WHERE query != '<IDLE>';

check events of room sorted by time:

select content, type, received_ts from events where room_id = '!' and type = '' order by topological_ordering limit 100;

largest room by state_groups_state

SELECT s.room_id, COUNT(s.room_id)
  FROM state_groups_state s
  GROUP BY s.room_id 
  ORDER BY COUNT(s.room_id) DESC
  LIMIT 20;

show tables by size:

SELECT nspname || '.' || 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 20;

show complexity of room:

SELECT COUNT(*)/500.0 FROM current_state_events WHERE room_id='...';