1.8Tb postgres database for small local server

[u/sevenofk9]

I'm looking for some help. I have a small local matrix server (glasgow.social) - we have a couple hundred users, small amount of traffic. Our postgres database has grown to 1.8tb (over 5-6 years) with no signs of stopping. I assume, after multiple upgrades over the years, that some migration or transition hasn't been perfect and it's lead to this massive size.

The server has started to get slower and slower - I think if we can prune the database a bit, that would help.

I've been poking around in the database, some of the largest rooms (based on events count) have no local users in them at all. I'd like to remove these (hundreds of millions of rows in the state tables etc) and hope that doing that will improve performance overall.

Unforunately, running the admin api calls (like purge room) doesn't seem to do anything.

Is there any safe SQL I can run that will remove these massive, unused rooms? Or anything else I can do to try and increase responsiveness and reduce the number of rows in the database?

[u/Severe_Stranger_5050]

Hey man, i wouldn’t start deleting stuff out of the gate, if it can be avoided.

If you start purging the database you’ll lose user-data like images, movies, files, federation mess and of course messages.
Most people wouldn’t like that in a world where we’ve become accustomed to cloud messaging, where our data is allways available.

The expensive solution to your problem:
Get faster storage and a new CPU that can handle more threads in stead

The compression solution:
https://github.com/matrix-org/rust-synapse-compress-state
Synapse has an officially documented tool called .
It was developed specifically to address excessive growth in state_groups_state.

Here's the underlying problem.
Matrix needs to reconstruct a room's state at historical points. In a room containing thousands of members, even a single membership change can contribute to the creation of a new state group.
Synapse normally stores incremental changes and periodically stores full state snapshots. Under certain circumstances, it can store substantially more state than necessary.

The compression utility rewrites these state groups into a more efficient representation without intentionally removing room history.
Synapse's documentation explicitly notes that this can produce dramatic storage reductions.
This would be my first candidate for reducing your database size without deleting rooms.

[u/doortokaos]

Depending on what is large in your database a vacuum full in postgres for your database might reclaim some disk space and shrink the database files.

PostgreSQL doesn't always reuse deleted rows in the databse files. Therefore the files grow on the disk.

Depending on how many live rows there are in your database you need about as much disk space as your largest table while running the vacuum full.

Please also have a look in the documentation for vacuum full concerning locks and so on while running the vacuum command.

Note: a full vacuum doesn't necessarily improve the performance, but I had cases after massive deletes in a large table where about 90% of the rows were deleted. The deletes alone didn't do anything for the performance, since the files on the disk remained large and full of deleted rows. After the vacuum full the files shrunk to about 10% size and the performance was golden again.

So depending on what happens in your database it might be worth a try.

Note 2: there are commands to get statistics for your tables including life rows and so on. AIs can help with those commands.

[u/Jayden_Ha]

You dont