Add PostgreSQL as backend storage!

PostgreSQL is a powerful database engine.

added database enhancement help wanted labels

added no-priority label

Is there some justification for this other than it's what Synapse uses? Are you expecting a performance gain of some kind?

assigned to @ehuff700

Taking a look at this. @tcpipuk I don't think performance is at the center of this request. In fact if support for this feature would be implemented as a key-value store per Conduwuit's existing implementation, the performance would absolutely be worse than implementations made for performance like rocksdb.

Support for this feature would moreso provide value for users running conduwuit in volatile environments where state isn't persisted across restarts (e.g microservices) and the user desires state to be stored in a separate service (e.g sql-based DB like postgres, persistent redis/dragonfly, etc).

Blocked until #268 (closed) is resolved

added blocked label

After a lot of discussions and thoughts, I don't think we want postgresql support.

conduwuit does not intend to be a Synapse replacement, so implementing every single Synapse feature is not ideal. We want to have general feature parity, but postgresql is not on that list.

A finely tuned RocksDB like conduwuit's will outclass postgresql in every single way, no matter what. Upstream's RocksDB setup is poor (huge I/O amplification, way too many files due to basically zero compaction, no write coalesces, no individual tuning based on random or sequential read/writes, etc) which is why some may think RocksDB is slow and postgresql is the "solution" when it's absolutely not. conduwuit intends to be high performance, zero latency and easy to deploy and setup at the same time. There is far more we can take RocksDB (and our general database code) too, just our current database code is somewhat limiting (way too many needless allocations everywhere, non-async aware database code, need to reduce unnecessary database queries, etc).

Additionally, postgresql support would be a huge maintenance burden no matter which direction we go. Whether it's forming a proper database schema and creating and maintaining a million SQL queries, which I do not want to do and as you can tell Synapse struggles heavily with the maintenance burden of the million SQL queries scattered everywhere in the codebase, or spending a ton of effort refactoring KvTree database code just to hack postgresql as a very inefficient key-value store that will perform terribly and serve little to no value other than "fake supporting" a relational database.

Every application is different and there are different application requirements. Throwing a relational database onto everything, just because it's popular or whatever, is not what we should be doing.

And finally, we're not going to support postgresql just because upstream has an interest in doing it. If upstream ever decides to support postgresql (which they are really severely underestimating based on our own research into this already), we'll support a way to migrate to RocksDB similar to what conduit_toolbox does.

closed