ah, done!

biggest room on pixie.town went from 47519774 rows to 1205630

after compressing a bunch more, and a REINDEX + Vacuum full overnight, i reclaimed 50GB of actual storage space

@haskal veeery brrrrr

@f0x :O how?

@dumpsterqueer with https://github.com/matrix-org/rust-synapse-compress-state
basically optimizing a Matrix DAG, takes a looot of processing (and ram and storage) while running though, that compression took more than an hour (running from my desktop so doesn't affect operation too much)

@f0x ooh thanks...! So you dumped the DB onto your desktop from the server, did the processing there, and moved it back?

@dumpsterqueer ah no, db is still on the server, connecting remotely through ssh tunnel (over LAN here). So it fetches all the rows, into memory (4gb+) then crunching happens, no way the matrix server would survive that additional load

@f0x ahhh right okay! That's clear. Neato, might try it on my synapse too for fun

@dumpsterqueer you can run
SELECT room_id, count(*) AS count
FROM state_groups_state
GROUP BY room_id
ORDER BY count DESC;

to get an idea for state groups per room, and then start compression for the biggest ones (only really makes sense if the numbers are big tho)

@f0x cool. Right now there's only like 5 or 6 people using the ondergrond matrix and only one or two public rooms with not many messages yet. But when that changes it's nice to know this. Bookmarking for future use :)