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)

Item MariaDB
10.4.12
MySQL
8.0.20
PostgreSQL
12.2
Queries per sec. 7148 4757 5317
Transaction per sec. 357 238 265
Total queries 429040 285516 319284
Read queries 300328 199864 223552
Write queries 85808 57101 63776
Other queries 42904 28551 31956
Ignored errors 0 0 38

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)

Item MariaDB
10.4.8
MySQL
8.0.17
PostgreSQL
12.2
Queries per sec. 7540 6727 5732
Transaction per sec. 377 336 286
Total queries 452396 403757 344111
Read queries 316680 282632 240926
Write queries 90477 80750 68750
Other queries 45239 40375 34435
Ignored errors 1 1 34

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

MySQL 10110
PostgreSQL 4418
MariaDB 494

 
Indeed Japan

MySQL 10308
PostgreSQL 6173
MariaDB 336

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.


What is VPS?

  1. CPUs of VPS : How to judge whether it is good or not

OS & Virtual Environment

  1. How to switch to AlmaLinux from CentOS(RHE’s clone)
  2. How to upgrade to CentOS 8 from CentOS 7 and its merit
  3. Benchmark of performance degradation by Docker's overhead with knowhow of installation&usage of docker

Database

  1. MariaDB vs MySQL vs PostgreSQL: Flowchart to choose best RDB

Programming Language

  1. How to install PHP8