wonder how much time postgres is actually wasting to print it all
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
@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
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 :)
Smol server part of the pixie.town infrastructure. Registration is approval-based, and will probably only accept people I know elsewhere or with good motivation.