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.

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?

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. :)

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.

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.