Which database management system should I be using?
There are so many options - MySQL, PostgreSQL, etc. Which one is best for me? How are they different? How do I make sure I can back up my data?
Typically, the most common systems we see used are MySQL, MariaDB, SQLite, and PostgreSQL, and we have documentation on some other database systems as well. While they seem similar, there are different use cases for each. Here are some considerations when choosing a database application:
MySQL is the most popular relational database management system in use today. It is meant to be a fast, multi-user database server that is free and easy to use regardless of the amount of data it is handling. MySQL supports data access from multiple users and multiple applications.
MariaDB is a spin-off of MySQL and was designed by some of the original MySQL developers as a drop-in replacement. It is very similar to MySQL in operation with an emphasis on adding new features and reliability.
PostgreSQL is a more advanced database system that supports a more elaborate structure and more data types than other DBs and is object-relational. It uses SQL the same way that the other common database systems do, but has more advanced features with an emphasis on security.
SQLite is self-contained, reliable, and meant to be small and fast. While it won't handle a large amount of data well, it is file-based and embedded as opposed to having a separate server process. This allows you to keep your data locally stored.
When comparing database applications, some common concerns are whether the database will be able to handle the load requirements, backing up data from the database, and redundancy.
Typically, a PostgreSQL user has a need for particular data type support as opposed to processing power. SQLite is limited in storage size due to its nature - it stores all data in a single disk file. Generally speaking, MariaDB or MySQL would be an optimal all-purpose choice if there is not a specific need for unsupported data types or local data storage.
Backing Up the Database
There are differences in the way MySQL/MariaDB, SQLite, and PostgreSQL are backed up due to their differences in structure. For MySQL or MariaDB, mysqldump is a great solution. PostgreSQL has its own backup capability. SQLite also has a command for backing up.
PostgreSQL automatically retains data over two databases in its system, so if one database goes down the data will still remain in the other. MySQL/MariaDB databases can be linked to a secondary redundant array via database replication. For SQLite, you would need to use something like Raft Consensus Protocol.