3 ms·

I have wondered why Synapse, the most feature-complete Matrix homeserver, so vehemently recommends against use of SQLite as it's backing db. They say that the p

by ripdog 4y ago

I have wondered why Synapse, the most feature-complete Matrix homeserver, so vehemently recommends against use of SQLite as it's backing db. They say that the performance is insufficient and it's only appropiate for testing purposes. That would make sense if you assume that Synapse is only going to be used in instances with hundreds/thousands of users, but plenty of people host their own instances for themselves only. Surely SQLite would be plenty for single-user instances, or family instances?

Arathorn 4y ago
The problem is that even a single user matrix server can be very resource intensive if that user joins big rooms with thousands of users spread over thousands of servers. Synapse is very database heavy, so the parallelism in Postgres helps a lot - plus some of the hot DB paths have special cased queries for Postgres to use some of its more obscure features that Sqlite lacks. Finally, we don’t dogfood or optimise Synapse with Sqlite, so there’s a risk of perf regressions.
- ripdog 4y ago
  Thank you, Matthew! This would be fantastic information to include in the Synapse documentation, for those of us being seduced by the operational simplicity of SQLite, and posts like this. :)
  - irskep 4y ago
    Adding my voice to this - I was actually looking at Synapse a few days ago and was wondering why SQLite was not recommended. This would have answered a question I didn't know the answer to until just now.
- srcreigh 4y ago
  Which obscure Postgres optimization features does SQLite lack? Can you share any extra info about the table layouts or queries which are slower in SQLite vs Postgres? In particular which postgres-specific optimizations have been made?
chillfox 4y ago
I guess it depends upon how SQLite is configured and what storage it's deployed on. If it's setup with WAL mode and on SSD storage then it should handle thousands of writes a second fairly easily.

by ripdog 4y ago

Arathorn 4y ago

The problem is that even a single user matrix server can be very resource intensive if that user joins big rooms with thousands of users spread over thousands of servers. Synapse is very database heavy, so the parallelism in Postgres helps a lot - plus some of the hot DB paths have special cased queries for Postgres to use some of its more obscure features that Sqlite lacks. Finally, we don’t dogfood or optimise Synapse with Sqlite, so there’s a risk of perf regressions.

ripdog 4y ago
Thank you, Matthew! This would be fantastic information to include in the Synapse documentation, for those of us being seduced by the operational simplicity of SQLite, and posts like this. :)
- irskep 4y ago
  Adding my voice to this - I was actually looking at Synapse a few days ago and was wondering why SQLite was not recommended. This would have answered a question I didn't know the answer to until just now.
srcreigh 4y ago
Which obscure Postgres optimization features does SQLite lack? Can you share any extra info about the table layouts or queries which are slower in SQLite vs Postgres? In particular which postgres-specific optimizations have been made?

chillfox 4y ago

I guess it depends upon how SQLite is configured and what storage it's deployed on. If it's setup with WAL mode and on SSD storage then it should handle thousands of writes a second fairly easily.