State groups relation schema

[sargon]

We have several Homeservers whose state_groups_state table is filling up the database server. One of the servers I take care of has reached ~180GB in table size, see below

 public | state_events       | table | ttt_matrix | 165 MB | 
 public | state_group_edges  | table | ttt_matrix | 77 MB  | 
 public | state_groups       | table | ttt_matrix | 80 MB  | 
 public | state_groups_state | table | ttt_matrix | 182 GB | 

That size has nearly doubled since I last reported the size in #3364. To my knowledge there have been bugs contributing to this growth, e.g. #6566.

So to the question in need: Can someone of the core devteam please provide a relational schema or point me to the correct documentation, if I am blind, of the state tables. So I can forge/propose a query to cleanup the mess - even if we have to turn off the homeserver during that operation, but we have to get rid of this garbage data - sooner or later.

[mirsal]

Maybe have a look at this tool: https://github.com/matrix-org/rust-synapse-compress-state

[rubo77]

I started createing a visualization here: #934 (comment)

[sargon]

Maybe have a look at this tool: https://github.com/matrix-org/rust-synapse-compress-state

Yep, that tool is well known. But that only compresses referenced data - I run that periodically.

If there would be documentation about the relations of the tables in the database, we as a community would be able to find bugs in the database handling. And especially we maintainers of old matrix server instances would be able to cleanup our database. I have several large datasets in that giant state_groups_state table which have a room_id which is not reverenced in the rooms table.
But there is no written knowledge, so that I can decide that removing those data is safe.

[sargon]

Continuing in this process I had a look at the purge process, re-implemented it by rewriting all delete queries into select equivalents. And build a script around that, I had uploaded the script and the corresponding results into a gist

https://gist.github.com/sargon/445ed23a471db609a165a816f2be6ce8

Looking at that data, its seems like there is a lot of data where entries in state_groups relate to events that does not exist in the database anymore, and throse entries relate to impressive set of entries in state_groups_state.

But since my understanding of the database concept of synapse is limited, I am not able to decide if that data is garbage or if it still of some value.

[rihardsk]

I'd also very much like this to get resolved or at least have instructions to clean up the db. I have a single user HS. The state_groups_state table is 28 GB in size

           relation           | total_size 
------------------------------+------------
 public.state_groups_state    | 28 GB      
 public.event_json            | 771 MB     
 public.received_transactions | 599 MB     
 public.events                | 273 MB     
 public.event_edges           | 266 MB     

I've measured this even after leaving all of the big rooms that i had previously joined and running the synapse_janitor.sql script. (There was a single large room that i couldn't leave because of an error with duplicate entries in some table which is likely due to using UTF-8 collate settings and is related to #6696 (comment). I haven't resolved this yet).

[richvdh]

As a general rule, I would encourage people who want to understand the deepest darkest secrets of the database schema to drop by #synapse-dev:matrix.org and ask questions. It's unlikely we're going to prioritise writing documentation of the schema any time soon.

The particular question here isn't as simple as you might think; to answer it briefly:

We need to be able to relatively quickly calculate the state of a room at any point in that room's history. In other words, we need to know the state of the room at each event in that room. This is done as follows:

A sequence of events where the state is the same are grouped together into a state_group; the mapping is recorded in event_to_state_groups. (Technically speaking, since a state event usually changes the state in the room, we are recording the state of the room after the given event id: which is to say, to a handwavey simplification, the first event in a state group is normally a state event, and others in the same state group are normally non-state-events.)

state_groups records, for each state group, the id of the room that we're looking at, and also the id of the first event in that group. (I'm not sure if that event id is used much in practice.)

Now, if we stored all the room state for each state_group, that would be a huge amount of data. Instead, for each state group, we normally store the difference between the state in that group and some other state group, and only occasionally (every 100 state changes or so) record the full state.

So, most state groups have an entry in state_group_edges (don't ask me why it's not a column in state_groups) which records the previous state group in the room, and state_groups_state records the differences in state since that previous state group.

A full state group just records the event id for each piece of state in the room at that point.

If anybody wants to write that up and submit it as a PR, feel free. I'm unlikely to be able to spend the time in polishing it myself and I don't think it should be a priority for the core team.

[richvdh]

(again: if you have further questions on the database schema, please bring them to #synapse-dev.)

[richvdh]

I've copied most of that stuff to https://github.com/matrix-org/synapse/wiki/State-Groups.