PostgreSQL VS MySQL Performance Comparison

Last updated: November 9 2024

Table of contents

PostgreSQL VS MySQL performance

In this article, I conduct original research into the performance of MySQL and PostgreSQL using Reserva, a custom benchmarking tool I created to simulate a high volume digital payments system.

PostgreSQL is 474% faster than MySQL in my most recent tests. Both systems are mature, modern, and performant databases, but I would recommend using PostgreSQL unless you have a specific reason for using MySQL.

Reserva

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:

  1. DB is queried to authenticate and authorize the user who is requesting payment.
  2. DB is queried to get information about the card used and account to be debited.
  3. DB is queried to authenticate and authorize the user who will approve or deny the payment request.
  4. DB is queried to update both account balances and record the payment.

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.

Benchmark setup

Let’s talk about the hardware, database versions, and configurations that were used to conduct this test.

Hardware

Benchmark hardware

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 😃.

CPUs

NUC CPUs

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!

RAM / Disks

RAM and disks

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.

Networking

Networking equipment

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!

Comparable cloud instances

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.

Databases

Now, let’s discuss the databases used and how they were configured.

PostgreSQL config

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 system RAM for shared_buffers, so I used the following minimal postgresql.conf file:

listen_addresses = '*'

shared_buffers = 16GB

MySQL config

I used the mysql:9.1.0-oraclelinux9 Docker image to deploy MySQL. After quite a few tests, I found that the only setting that actually improved performance was setting innodb_buffer_pool_size to the recommended size of 75% of system RAM. As such, I used the following configuration file:

[mysqld]

innodb_buffer_pool_size = 48G

Populating data

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.

The test

I ran Reserva with the following settings for both databases:

Aggregation query

At the end of the test, I executed the following queries to show how many payments were made. The results of those queries are visualized below.

PostgreSQL query

SELECT
    DATE_TRUNC('minute', CREATED_AT) AS MINUTE,
    COUNT(*) AS NUMBER_OF_TRANSFERS
FROM
    TRANSFERS
GROUP BY
    MINUTE
ORDER BY
    MINUTE;

MySQL query

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;

Results

Fastest open-source databases Reserva benchmark

As you can see, PostgreSQL far outperformed MySQL. In fact, I was surprised how much faster it was than MySQL.

Of course, the tests of both databases 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 PostgreSQL’s dominance? I guess that will play out in the near future as I perform more tests with it.

Here is another way of viewing the test results, where I’ve simply summed the amount of payments made over the last 60 minutes of the test, and divided by 60 to put the results in a per second format:

PostgreSQL vs MySQL payments per second

Conclusion

PostgreSQL left MySQL in the dust. It is actually surprising how much faster it was is than MySQL, considering that some major tech organizations like Uber have written blog posts expressing their preference for MySQL, for performance reasons.

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.