Database hardware pros and cons
December 17, 2019

Pros and Cons of Key Open Source Databases

Open Source
Databases

Most Popular Open Source Databases

There are many open source database 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 options for the RDBMS space. Postgresql, MariaDB, and MySQL are the most popular databases available and we will be comparing them, their performance, benefits, and drawbacks of each type. 

Note: these open source databases 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:

  1. High availability – Referring to the database’s ability to be available in the event of a system failure.
  2. Firewall – Stops unwanted traffic from connecting to the database.
  3. Performance – Responsiveness, efficient utilization of resources, etc.
  4. Scalability – The ability to scale on demand.
  5. DR – Disaster recovery abilities, what will happen to a system that is destroyed.

 

Database Name

MariaDB

PostgreSQL

MySQL

Performance Feature

   

Load Balancing

YES

YES

NO

Replication

YES

YES

YES

Restore Session

YES

NO

NO

MultiMaster

YES

W/SOFTWARE

W/ SOFTWARE

Failover

YES

YES

YES

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.

 

Database Name

MariaDB

PostgreSQL

MySQL

Performance Feature

   

Table/Row Compression

YES

NO

YES

Log Compression

YES

YES

NO

Query Parallelization

NO

YES

NO

Query Result Caching

W/SOFTWARE

NO

NO

Sharding

W/SOFTWARE

W/SOFTWARE

NO

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.

Conclusion

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.

At OpenLogic, we'd love to help you in deciding on your open source database. Talk to an expert architect today!

CONNECT WITH AN EXPERT