Pros and Cons of Key Open Source Databases
There are many open source database software solutions or DBMS (DataBase Management Systems). Types include relational database, relational object, sql, no-sql, new-sql, xml databases, and several more. This blog will cover open source database software options for the RDBMS space.
Most Popular Open Source Databases
Postgresql, MariaDB, and MySQL are the most popular databases available. Below, we will be comparing them, their performance, benefits, and drawbacks of each type.
Note: these open source database software comparisons are not all “apples to apples,” as some of these software packages provide features that others do not.
Some items we will compare are listed below:
- High availability – Referring to the database’s ability to be available in the event of a system failure.
- Firewall – Stops unwanted traffic from connecting to the database.
- Performance – Responsiveness, efficient utilization of resources, etc.
- Scalability – The ability to scale on demand.
- DR – Disaster recovery abilities, what will happen to a system that is destroyed.
Open Source Database Comparison
Here's a quick comparison table of open source databases and performance features. Keep reading for a full comparison of capabilities.
Database Name | MariaDB | PostgreSQL | MySQL |
|---|---|---|---|
Load Balancing | Yes | Yes | No |
Replication | Yes | Yes | Yes |
Restore Session | Yes | No | No |
MultiMaster | Yes | With software | With software |
Failover | Yes | Yes | Yes |
Table/Row Compression | Yes | No | Yes |
Log Compression | Yes | Yes | No |
Query Parallelization | No | Yes | No |
Query Result Caching | With software | No | No |
Sharding | With software | With software | No |
MySQL, PostgreSQL, MariaDB Pros and Cons
Compare the MySQL, PostgreSQL, and MariaDB pros and cons. For instance, MariaDB's biggest pro is scalability.
High Availability
High availability involves a few different software functions. This means that users can be balanced over multiple nodes, and failover will occur if one of the nodes crashes. Replication occurs between nodes, and it is easy to set up in most popular open source databases. There are many guides available on setting up replication in PostgreSQL and MariaDB readily available. Replication ensures data security in the event of corruption.
Features Explained
Restore session is a feature that reconnects a user session if it is disconnected during the process of a query/transaction, seamlessly,
Multi-Master means that more than one node in the cluster can act as a master node. The master node normally accepts connections. In most multi-master setups, only one node is “writable”, which prevents data inconsistency errors, while the other nodes act as a master serving “read-only” queries.
PostgreSQL has a multi-master setup that is simple to get running. There are also options for multiple writeable nodes using 3rd party software like Crunchy. MariaDB has an embedded multi-master setup, but MySQL requires 3rd party software to enable this feature. MySQL is the only one of the three that does not have this functionality “out of the box”.
Failover is crucial when maintaining the high availability of a resource. This means at least 2 servers are running in a configuration that will allow connections to each server to be moved to the other server in the event of a failure of one of the nodes.
Firewall and Security
Security is put in place to prevent data breaches, unauthorized access, and access control. This will allow only certain people to access the database. All three of these database solutions offer TLS/SSL encryption. This encrypts data that is sent to and front the database, making it unreadable to someone sniffing network data.
MySQL and MariaDB both offer encrypted backups out of the box, but you can encrypt and database backup that is created (it can be a file, and any file can be encrypted, or the drive containing backups can be encrypted.)
There are many authentication mechanisms available on all three of these systems. PAM, LDAP, Kerberos, PAM, and more are available on all of them. PostgreSQL offers SASL auth. MariaDB supports LDAP through PAM.
As for individual access control, MySQL, MariaDB, and PostgreSQL offer GRANT/REVOKE of privileges for individual users and roles. Each of these databases has a form of firewall.
Performance
One of the key aspects of performance is the ability to take advantage of multi-threading. Unfortunately, PostgreSQL is not built with a multi-threaded architecture. MySQL and MariaDB are. This is not to say that PostgreSQL cannot spin up multiple processes to handle loads, it can, it is just not as efficient as MySQL and MariaDB at utilizing multiple threads.
Scalability
MariaDB has the proxy to utilize multiple purpose-built storage engines that will allow for the best scaling of our three choices. PostgreSQL does scale, but not as well as MariaDB in this aspect. While spinning up multiple database instances of any of these software solutions is an option, MariaDB is designed to take the most advantage of this.
Disaster Recovery
PostgreSQL, MariaDB, and MySQL all support standbys with replication and full/incremental backups for recovery and have a backup / restore tools. This means you can have off-site instances of any of these types of databases. Any of them will be optimal for a DR situation if the instances are configured properly, with proper architecture, but MariaDB offers Point-in-Time recovery with the ability to roll back specific transactions (in the event of corruption.) This might make MariaDB a little more disaster-proof.
Get Support For Open Source Database Software
Google can provide an abundance of information on all three of these open source database software packages as they are all extremely popular. When working with support teams to support these products, most teams will have exposure to these open source databases, most professional support engineers work with all three of them daily.
If you are trying to determine what open source database to use in your professional environment, based on features alone, I would recommend PostgreSQL or MariaDB. Before choosing an open source database, make sure you understand it's licensing and latest trends.
For more information on these open source databases considerations, check out the 2019 open source support report. And learn more about OpenLogic database support.
At OpenLogic, we'd love to help you in deciding on your open source database. Talk to an expert architect today!