Transition from RocksDB to Sqlite / FoundationDB

[rudihorn]

As part of the change from version 0.2 to 0.3, the internal storage engine of RocksDB was replaced by either Sqlite or FoundationDB. I would like to understand this change, as it would also help me to make the better choice for my setup. Maybe for a bit of context, the website description has changed from:

Stalwart JMAP stores all metadata and settings in RocksDB, a high performance embedded database for key-value data. RocksDB is used in production systems at various web-scale enterprises including Facebook, Yahoo! and LinkedIn. It has also been adpoted as storage backend by popular SQL and NoSQL databases such as Apache Cassandra, CockroachDB and MySQL. Even though RocksDB can be fully trusted with your information, e-mail messages are stored separately from metadata, directly in the file system to allow quick recovery in case of catastrophic failure.

to

Stalwart JMAP stores all metadata and settings in SQLite or in FoundationDB, a high performance distributed database. E-mail messages are stored separately from metadata, directly in the file system using Maildir or alternatively on the S3-storage of your choice.

Now at least on paper, the RocksDB seemed appealing to me because it: 1) required very little setup but 2) still promised a solid backend for data.

Now we are given two choices of engine. SQLite sounds like it is a lot less scalable, and my immediate (probably historically influenced) reaction was that this is not the ideal choice. It sounds like it is the less ideal option.

The other choice is FoundationDB. This seems to be more complex to setup out of the box. From what I can tell it uses a server-client model. The *.deb packages did not install out of the box on my system, making it feel a little less mature. However it would be the more scalable solution.

Looking at the docs, FoundationDB seems to provide (probably good) full text search. Was this the main motivation for the switch? Did RocksDB also handle the FTS search in the previous versions? If so I'm assuming this is also where Sqlite is most likely going to perform worse? Does sqlite still support all search capabilities, or is the text search as good? Does FoundationDB scale down well on small setups, or does it prefer to allocate minimum amounts of resource regardless of the load / amount of data (like Exchange does...)?

I don't want this to head into a deep discussion, but it would be nice to have a better understanding oy what is going on.

[mdecimus]

The primary reason for the switch from RocksDB to either SQLite or FoundationDB was the limited options offered by RocksDB for easily scaling and backing up the data. The previous version of Stalwart JMAP Server employed its own clustering module and replicated data using a custom implementation of the Raft protocol. Over time, it became clear that this approach was complex to maintain and challenging to test for correctness. Thus, the decision was made to delegate replication responsibilities to the database, hence the introduction FoundationDB.

FoundationDB was chosen for its robust capabilities as a distributed key-value store. It's designed to scale very efficiently, which makes it a strong choice for larger, enterprise-level deployments.

On the other hand, SQLite may seem less scalable on paper, but its real-world performance is quite impressive. It's deployed in millions of applications and has been extensively tested. SQLite can comfortably support thousands of users, which makes it suitable for many standard use cases. For data replication, there are solutions like Litestream that can work in tandem with SQLite to ensure your data remains safe and readily available.

[rudihorn]

Thanks for taking time to explain this 👍

Have you compared the FTS between FoundationDB / SQLite as I assume different engines provide fairly different results? If not, then I guess that is fine and probably not necessarily worth spending your time on.

[mdecimus]

Stalwart Mail Server handles full text search internally, it does not rely on the FTS capabilities of either FoundationDB or SQLite. This was done mainly because the indexed data will be stored encrypted in a future release.

[rudihorn]

That is very helpful, thank you very much again!