Applications self hosted on The Edge will use PostgreSQL as a relational database engine when possible.
Applications self hosted on The Edge will use other relational database engines, such as MariaDb and SQLite, when PostgreSQL would be unsupported by the application or for any other practical reason, e.g. imagine that our team would stumble and fall because of blocking issues related to the usage of PostgreSQL, while other engines work well.
More often than not applications self hosted on The Edge have a mandatory dependency and require a relational database management server in order to work properly. The ones which seem to be supported in most cases are PostgreSQL, MariaDb/ MySQL and SQLite.
To facilitate potential future automation of the installation and upgrade of applications on The Edge the support of many database servers seems impractical. We would like to stick to a single server as much as possible. The consistent approach will also help administrators of The Edge with installing and maintaining applications manually.
In order to make a decision, we need to consider the following questions.
Is the database server
The matrix of the possible options is below. The used scale for points is 1 - 5, where 1 point is the least and 5 points are the highest score.
| Criterium | SQLite | MariaDb | PostgreSQL |
|---|---|---|---|
| App support | 4 | 4 | 5 |
| Reliable storage | 2 | 4 | 5 |
| Easy to administer | 1 | 5 | 5 |
| Performance | 1 | 3 | 4 |
| Easy to upgrade server | 1 | 5 | 3 |
| Backup | 1 | 5 | 5 |
| Here to stay in 5 years | 5 | 5 | 5 |
| Total score | 15 | 31 | 32 |
Eventually the total scores of MariaDb and PostgreSQL are very close. Therefore, the wider app support of PostgreSQL and its better performance and reliability become the most important criteria for the final decision.
Thus PostgreSQL will be the relational database server for applications self hosted on the Edge.
wiki.js will be dropping support of MariaDb/MySQL with their new version. There might be others, though none come to mind right now.
PostgreSQL is a very robust storage and it is known for lack of data corruption in general. MariaDb can corrupt data every now and then. SQLite is mostly used for getting applications running quickly and being a reliable storage for large data sets is not exactly one of its strengths.
"Easy" means that it is well documented and there are means to run any common scenario from a command line tool. This is instrumental for further automating manual work related to database server management and maintenance.
PostgreSQL is known for better performance with write-heavy applications. MariaDb sometimes performs better with read-heavy applications. However any complex queries requiring support of foreign keys, i.e. relying on the relational aspect of the database, is still slower with MariaDb.
The performance of SQLite is probably the best with small data sets. However it is not meant to be a production ready solution.
MariaDb is the easiest solution as there is literally no manual upgrade work required. PostgreSQL has its own distinct upgrade flow for upgrades to major versions, which makes things a bit more challenging for automation. There is no other way to manage the upgrade of SQLite but bumping the version of the library as it is used as an embedded database.
Both MariaDb and PostgreSQL provide built-in features for exporting to and importing data from SQL text files. SQLite does not provide any tools due to its embedded nature and more narrow scope.
There is no good reason to assume that any of these projects would go away in five years. They will certainly evolve and chances are high that they will be still among the leading open source solutions for managing relational data.