Infra

MariaDB vs MySQL vs PostgreSQL: Flow chart to choose best database

 

Which RDB should I use, MariaDB, MySQL or PostgreSQL?

We often encounter articles listing what is good and what is bad but there is no conclusion for readers about which one you should use.
So I provide flowchart by which you can choose suitable RDB(Relational DataBase) for you.
In this article, I compare 3 major OSS RDBs(MariaDB, MySQL and PostgreSQL)

I will show you background of flowchart in addition to benchmark result later.

Flowchart of choosing suitable RDB for you

Q1. Your service will be created based on WordPress
Yes => MariaDB
No => Q2.

Q2. Do you want to develop mobile app or app using GIS?
Yes => PostgreSQL
No => Q3.

Q3. Do you need more than 64 columns with index in 1 table?
Yes => PostgreSQL
No => MariaDB

Background of Flow chart

Reason of choosing MariaDB anyway instead of MySQL

– Oracle purchased MySQL, which created concern and most of Linux distributors are now adopting MariaDB.
– Original developer of MySQL is developing MariaDB. Oracle has its own RDB Oracle, so it is hard to commit fully to MySQL’s success in terms of Oracle. As result, MariaDB has more functions now compared to MySQL
– MariaDB provided you better performance in default configuration and in AWS’s RDS’s setting based on the benchmark result in May/2020 compared to MySQL.

So if you wonder whether you should adopt MariaDB or MySQL, MariaDB is better choice for you.


Reason of choosing MariaDB over PostgreSQL

– WordPress supports only MariaDB and MySQL, which means WordPress doesn’t work with PostgreSQL
– Market share of WordPress as web sites exceeded 36% and it is still growing. Even if you won’t contruct the system only by WordPress, infrastructure for WordPress is always required
– If you have MariaDB system for WordPress, maintaining PostgreSQL and MariaDB cost you more in therms of system and human resource
– MariaDB(MySQL) had better DB management tool like WorkBench and A5:SQL Mk-2 compared to PosgreSQL
– MariaDB is basically compatible with MySQL and there are many engineers who have the experience of using MySQL
– MariaDB provided you better performance in default configuration and in AWS’s RDS’s setting based on the benchmark result in May/2020 compared to PostgreSQL


Reason of choosing PostgreSQL over MariaDB

– PostgreSQL supports Native JSON type (jsonb), so speed of accessing to Json’s data is far faster than MariaDB(MySQL) thogh MariaDB is providing Virtual Column whose performance is not enough
– 3rd Party software which provides REST API(PostgREST), Software which provides GraphQL API(Graphile) help you to develop software which use JSON, which means you can develop mobile app and SPA(Single Page Application) with less effort.
PostGIS helps you to develop application with GIS
– PostgreSQL has more function than MariaDB
– Error criteria is stricter than MariaDB, so you can avoid problem caused by unexpected hading of data.
– MariaDB(MySQL) can have only 64 key index per table. PostgreSQL doesn’t have such limitation.

Comparison by benchmark for RDB’s performance

Comparison using latest version with default setting

Tested: May/2020
Machine: 1 server = Linode 1CPU/1GB RAM/SSD
Test condition: 8 concurrent access in 60 seconds using sysbench. I used latest stable version of each RDB with default settings of them.

In this benchmark, the result was like this
mariadb(ver10.4.12) > postgresql(ver12.2) > mysql(ver8.0.20)

ItemMariaDB
10.4.12
MySQL
8.0.20
PostgreSQL
12.2
Queries per sec.714847575317
Transaction per sec.357238265
Total queries429040285516319284
Read queries300328199864223552
Write queries858085710163776
Other queries429042855131956
Ignored errors0038

Comparison using AWS RDS’s latest version

AWS RDS is managed database, so by using it, you can compare RDB with tuned status.
Version of AWS RDS can be a little bit older than official latest version.
Machine: EC2(1CPU/1GB) + RDS(1CPU/1GB)

In this benchmark, result is like this.
mariadb(ver10.4.8) > mysql(ver8.0.17) > postgresql(ver12.2)

ItemMariaDB
10.4.8
MySQL
8.0.17
PostgreSQL
12.2
Queries per sec.754067275732
Transaction per sec.377336286
Total queries452396403757344111
Read queries316680282632240926
Write queries904778075068750
Other queries452394037534435
Ignored errors1134

Summary

Function is important for choosing RDB but if you don’t find any appealing difference, then adopting MariaDB looks attractive in terms of performance.

Status of usage in real business

Number of job positions in 05/2020

Indeed USA

MySQL10110
PostgreSQL4418
MariaDB494

 
Indeed Japan

MySQL10308
PostgreSQL6173
MariaDB336

I know the performance and functions are not all but it is a little bit surprise that MariaDB is behind so much in terms of usage compared to MySQL and PostgreSQL though its performance is good.
Anyway switching RDB is risky and can cost you, so switching is not easy if you don’t encounter problems.