Conclusion
The table below shows an overview of how well the database systems scored for each of the criteria. Totals are calculated by taking the sum of the scores multiplied by the respective weight factor.
| Criterion | Weight | PostgreSQL | MySQL | MongoDB | Redis | MariaDB | Cassandra |
|---|---|---|---|---|---|---|---|
| Data integrity | 5 | 5 | 5 | 4 | 2 | 5 | 2 |
| Handling necessary data | 5 | 5 | 5 | 5 | 2 | 5 | 5 |
| JavaScript support | 5 | 4 | 4 | 5 | 3 | 4 | 3 |
| Performance | 3 | 4 | 3 | 3 | 5 | 3 | 3 |
| Query support | 3 | 5 | 4 | 4 | 2 | 4 | 3 |
| Scalability | 2 | 2 | 2 | 4 | 3 | 2 | 5 |
| Total | 101 | 95 | 99 | 62 | 95 | 78 |
Four of the six databases end very close together at the top of these results. This includes the three relational databases (PostgreSQL, MySQL and MariaDB), which are similar in their architecture, but the NoSQL database MongoDB also scores high.
The choice between a relational database and MongoDB comes down to a trade-off between data robustness and scalability. MongoDB is generally recommended for specific purposes, such as fast prototyping, unstructured data and massive scales1. On the other hand, it can be challenging to design data relations and join different data types, which makes relational databases more suitable in most scenarios: “If you don’t know what database to choose, then choose a relational database (MySQL or PostgreSQL)”2. Because the data for the Photo Gallery is naturally relational — items like photos, albums and users are all linked — for me, these qualities outweigh the importance of scaling in this project.
The differences between PostgreSQL, MySQL and MariaDB comes more down to the details in advanced features and implementation3, of which the impact to the application should in most cases be limited. For the Photo Gallery, I will continue with PostgreSQL, as it scores a bit better on performance and features, and because it fits better with my current set-up.
Even though I will be developing the application with support for just one database now, due to the similar architecture, it could be possible to add support for additional (relational) database systems in the future. In fact, this is not uncommon amongst other self-hosted applications, such as NextCloud4.