You cannot select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
HF f8b77b5982 add complexity sql command 3 months ago
.. add complexity sql command 3 months ago add delete argument to purge script to manually delete a room 3 months ago

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;

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='...';