Last updated: October 31 2024
PostgreSQL, MySQL, and MariaDB are the most popular open-source relational databases. This article compares the performance of these systems using Reserva, a custom benchmarking tool I created to simulate a high volume digital payments system.
My tests show that PostgreSQL is the fastest of the bunch.
In my opinion, the performance difference between PostgreSQL and MariaDB is close enough that other factors should be more important when deciding between them. However, MySQL is so much slower than the other two that I would only use it if I had no other choice.
Reserva is a system I made to simulate a streamlined, high volume payments processing system. It is written in Go and supports arbitrary amounts of concurrency.
On start, Reserva loads all users and authentication tokens into memory, then attempts to make as many funds transfers as possible in a loop. Each funds transfer requires 4 round trips to the database and touches 6 tables. The workflow is as follows:
Reserva allows you to select whether or not you want deletes to be part of the workload. The results on this page do include deletes as part of the workload.
Let’s talk about the hardware, database versions, and configurations that were used to conduct this test.
I plan on running a lot of database benchmarks in the near future, and constantly paying for cloud services was starting to get expensive. So, I decided to purchase a few mini PCs and a networking switch to simulate a modern cloud environment.
Also, I like buying computer stuff 😃.
The mini PCs were 11th gen Intel NUCS. Specifically, they are:
I put the database servers on the computer with the i7 processor, because Reserva is less CPU intensive than the database programs it’s designed to test.
Both chips have 4 hyperthreaded cores for a total of 8 threads. This makes them comparable to an 8 vCPU VM that you might rent in the cloud. They also have a TDP of 28 watts, which I measured during load. In fact, these computers are well cooled and sometimes even surpass 28 watts!
I purchased high quality RAM and SSDs to get the most out of these mini PCs. They are both outfitted with:
It is well known that databases can become bottlenecked by slow disks, so I paid particular attention to my choice of SSD. The Samsung 990 Pro is, as far as I’m aware, one of the fastest PCIe 4 disks available on the market.
I initially conducted these tests using my M1 MacBook Air connected to one of the mini PCs via WiFi. However, I noticed right away how much that relatively poor connection slowed down the benchmark results. Over WiFi 6 with a strong connection, PostgreSQL was only able to complete 300-500 transfers per second. This was 30X slower than I eventually achieved!
So, I decided to go all in and buy another mini PC, plus:
This brought my network latency, as measured with ping, from several milliseconds down to around 0.65 milliseconds. This imperceptibly small difference in latency made a huge difference in my end test results. So, pay attention to networking, folks!
Here are a couple of comparable database servers with 8 vCPUs and 64 GB of RAM from AWS and Azure:
Before storage, it would cost about $846.72 per month to rent a similar machine on AWS.
Despite their size, these are powerful computers with resources that resemble a busy, production quality system.
Now, let’s discuss the databases used and how they were configured.
I deployed the postgres:17.0-bookworm
Docker image on the i7 mini PC.
I spent a whole day configuring performance parameters and found that, for this particular use case, the only one that made any real difference was shared_buffers
.
The PostgreSQL docs recommend a value of around 25% of total syste RAM for shared_buffers
, so I used the following minimal postgresql.conf
file:
listen_addresses = '*'
shared_buffers = 16GB
Once the PostgreSQL test was complete, I deployed the mariadb:11.5.2-noble
Docker image on the i7 mini PC.
Again, I found that the only performance parameter than made an appreciable difference in performance was innodb_buffer_pool_size
. A good starting value for that parameter is 50%-75% of total system RAM, so I used the following mariadb.cnf
file:
[mariadb]
innodb_buffer_pool_size = 48G
I used the mysql:9.1.0-oraclelinux9
Docker image to deploy MySQL. Because MySQL and MariaDB share most of their configuration parameters, I decided to use the exact same config parameters:
[mysqld]
innodb_buffer_pool_size = 48G
For each database, I ran a DB preparation SQL scripts to create:
You can see the SQL setup scripts, along with the rest of the code, on Reserva’s Github page.
I ran Reserva with the following settings for both databases:
At the end of the test, I executed the following queries to show how many payments were made over the course of the hour. The results of those queries are visualized below.
SELECT
DATE_TRUNC('minute', CREATED_AT) AS MINUTE,
COUNT(*) AS NUMBER_OF_TRANSFERS
FROM
TRANSFERS
GROUP BY
MINUTE
ORDER BY
MINUTE;
SELECT
hour(CREATED_AT) AS hour_created_at,
minute(CREATED_AT) AS minute_created_at,
COUNT(*) AS NUMBER_OF_TRANSFERS
FROM
transfers
GROUP BY
hour_created_at, minute_created_at
ORDER BY
hour_created_at, minute_created_at;
As you can see, PostgreSQL and MariaDB far outperformed MySQL. In fact, I was surprised how much faster MariaDB was than MySQL - especially considering that they both use the InnoDB storage engine.
Something that struck me is how consistently PostgreSQL performed in this particular round of benchmarks. I’ve tested PostgreSQL before but haven’t ever observed such consistent performance before.
All three databases in this comparison used an identical version of Reserva, but I am constantly making changes to the program to improve its ability to test databases. Maybe some change I made resulted in this consistency? I guess that will play out in the near future as I perform more tests with it.
Another thing worth noting is the degradation of MariaDB’s performance over the course of the hour. I don’t have any convincing explanation of why this happened to MariaDB, but didn’t happen to either of the other databases. I will investigate this issue in a future test, hopefully over a longer period of time.
Here is another way of viewing the test results, where I’ve simply summed the amount of payments made over the last 30 minutes of the test, and divided by the number of seconds in 30 minutes:
PostgreSQL and MariaDB both performed quite well, and left MySQL in the dust. It is actually surprising how much faster MariaDB is than MySQL, considering they are compatible with each other.
I would use PostgreSQL for new projects if that option was available to me. For any existing workload that uses MySQL, you might see a large performance bump by switching to MariaDB.