Matrix Synapse with PostgreSQL database

I just recently acquired a Raspberry Pi 4B with 8GB of RAM and want to migrate my Freedombox (Raspberry 3B+) to it. The idea is to use PostgreSQL and to keep the Matrix Synapse database all in RAM in order to improve performance as well as the SD card lifespan.

Now I did a quick research and the first step will be migrating from SQLite to PostgreSQL. Does anyone have experience with this or perhaps uses a Matrix/PostgreSQL configuration him/herself?

Thanks in advance!

1 Like

Okay, I found some useful material for migrating the SQLite database to Postgre. There is a actually tool for this in the matrix-synapse package called synapse_port_db. A detailed instruction can be found in their Github repository.

As to the migration from Raspberry 3B+ to Raspberry 4B 8GB I will try a manual migration of the LDAP directory in combination with my regular borg backups. I already installed Freedombox by using a fresh Raspberry Pi Debian image which already has corrected the RAM recognition bug. It seems to work as expected. I will post further updates if I succeed.

1 Like

Taken out of the Matrix-Synapse installation doc:

Using PostgreSQL

By default Synapse uses an SQLite database and in doing so trades performance for convenience. Almost all installations should opt to use PostgreSQL instead. Advantages include:

  • significant performance improvements due to the superior threading and caching model, smarter query optimiser
  • allowing the DB to be run on separate hardware

For information on how to install and use PostgreSQL in Synapse, please see Using Postgres

SQLite is only acceptable for testing purposes. SQLite should not be used in a production server. Synapse will perform poorly when using SQLite, especially when participating in large rooms.

1 Like

@jonny, would you be able to post any comparison of how Matrix Synapse is performing under the two databases for a typical FreedomBox workload? If there is a significant impact, we can consider switching to PostgreSQL and deploy an automatic migration for all users.

@sunil, yes I was planning to do that in any case - albeit my conclusions will be anecdotal.

I suppose that the impact will be higher the more users and rooms are hosted on a server and the more connections to federated servers exist. In my case there are currrently 6 users and some more rooms - no outward connections - so I don’t expect the performance to jump extraordinarily. I do expect it to become more “snappy” though.

My intention is to maintain the performance level even with more users on my FBX and expected federation to other servers in the near future.

I think the question wheather to migrate automatically depends on the hardware the Fbx is running on. If it is a SBC with 1GB RAM or less it wouldn’t make much sense in my opinion. But I guess adding an upgrade option to the Matrix config page and let the users decide themselves would be an idea worth following…

1 Like

While Matrix Synapse may not be have been optimized for sqlite, sqlite is a very capable database (despite the perception to the contrary). In the near future we may expect people to have 2 GB of RAM and our decision may change easily then.

Also, no matter the suitable database, we should not give the option to the users. Imagine, if we can’t decide what the best DB for the task is, what chance does a non-technical user have to be able to decide.

I agree, SQLite has made strong progress in the last 10 years and I also see it as suitable for small and/or standalone Matrix setups – that’s to say as a standard setting. Nonetheless I find the idea of a “migration at the push of a button” worth exploring because:

  • there are quite some tech-savvy Fbx users who know how to use it
  • the educational factor of Free Software in general and the possibility for non-tech people to “tinker” with this feature and observe the effects - without needing the technical expertise
  • the option can be communicated as “advanced setting” and explained accordingly. The average user doesn’t even have to notice it at the first glance.
  • the implementation seems quite fuss-free

So much for now…

1 Like

Okay, it took me a while to get the time to finish and test this but now I can confirm my previous assumption. The Matrix-Synapse experience definitely feels snappier now and even though the speed did not increase all too much in terms of “objective measurability” (I guess) I’m happy that I did it. It’s just more fun when messages are delivered almost instantly.

Another thing to consider here is that one has to setup the PostgreSQL DB backup manually because the backup app does not cover it - which is not much of a fuss though.