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?
OS & Virtual Environment
- How to switch to AlmaLinux from CentOS(RHE’s clone)
- How to upgrade to CentOS 8 from CentOS 7 and its merit
- Benchmark of performance degradation by Docker's overhead with knowhow of installation&usage of docker
Database
Programming Language