Postgres vs MySQL under load: reads, writes, updates and one open transaction

The usual answer to “Postgres or MySQL?” is that it doesn’t matter much for a normal web app. I tested that claim with one Go shop service in front of PostgreSQL 18 and MySQL 9.7 LTS, on identical machines, and raised the request rate step by step until each database failed.

The scope decides how far these numbers travel: one database machine with 2 dedicated vCPUs and 4 GB of memory, no replicas, no connection pooler, read committed on both sides. Code, k6 script and setup scripts are in huseyinbabal/db-wars.

Results

A rate counts as held when p95 stays under 50 ms and under 1% of requests fail. The table gives the highest rate held, in requests per second. Rounds with 3 runs show the median, with every run listed so the spread is visible.

+ means Postgres was still holding at the last step of that test, so its real limit is higher than the number shown.

Two things to keep in mind. Each number is a step on a ladder, so precision below the step size isn’t real. And the new orders round is the noisy one: one Postgres run stopped at 500 while the other two held 1,397, and MySQL moved between 992 and 1,197. I’d call that round close and the others not.

The setup

Both stacks were loaded in parallel inside a single DigitalOcean region, with matching CPU models on the two database machines. The app and load machines are much bigger than the database on purpose, so the database is the first thing to run out.

Each round starts from fresh data: 5 million orders and 100,000 products, with the same schema and the same SQL on both sides.

The settings that decide the result:

The cache sizes look unequal and they are meant to. InnoDB reads with O_DIRECT, so it gets no help from the OS page cache and the buffer pool is all it has. Postgres leans on the page cache, and the documented starting point for shared_buffers is a quarter of memory. Both sides acknowledge a write only after it reaches the disk, and neither keeps a replication log, since there is no replica.

The load is a k6 ramping arrival rate: a fixed number of requests per second that goes up every 30 seconds. The step size depends on the round, from 200 for new orders to 1,000 for reads.

Why reads cost InnoDB more

The read mix covers four endpoints: single product lookup, category listing (20 per page), single order lookup, and the newest orders of one customer. CPU headroom at the 6,000 rps step: roughly 20% idle on the MySQL server, roughly 60% on the Postgres one.

The difference comes from what a secondary index stores:

• PostgreSQL: the table is a heap, and every index entry points straight at a row’s physical location (its ctid). One index lookup, one heap fetch.
• InnoDB: the table is the primary key B-tree. Entries in a secondary index hold the row’s primary key, not its location, so reaching each matching row costs another descent from the root of the clustered index.

For a category page of 20 products that means 20 extra B-tree descents on MySQL. In the CPU profile of the MySQL machine, B-tree search was the top entry.

You can watch the counters on your own database. On Postgres, buffer hits per query:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, name, category, price_cents, stock FROM products WHERE category = 42 AND id > 0 ORDER BY id LIMIT 20;

On MySQL, the handler counters before and after the same query:

FLUSH STATUS;
SELECT id, name, category, price_cents, stock FROM products WHERE category = 42 AND id > 0 ORDER BY id LIMIT 20;
SHOW SESSION STATUS LIKE 'Handler_read%';

The two counters don’t measure the same thing, so compare each against itself as the data or the query changes, not against each other.

When the data no longer fits in memory (20 million orders), the gap stayed: about 10,000 against 4,000. Turning MySQL’s performance schema off didn’t move its 7,000 limit.

Updates and HOT

Postgres is often described as weak at updates, because every update writes a new row version. In this test it held more than MySQL on both update rounds, and the reason is HOT (heap-only tuple) updates.

When an update changes no indexed column and the new version fits on the same page, Postgres doesn’t touch the indexes at all. The old index entries still lead to the new version through a short chain on the page. In the round with no index on the updated column, 94% of updates were HOT. In the round where the column was indexed, 0% were, and Postgres dropped from about 7,900 to 5,500 while MySQL stayed near 4,000.

To see your own ratio:

SELECT relname, n_tup_upd, n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0), 1) AS hot_pct
FROM pg_stat_user_tables
ORDER BY n_tup_upd DESC
LIMIT 10;

Disk writes went the other way from what I expected: at 3,000 updates a second on the unindexed column, MySQL wrote about 54 MB/s to disk and Postgres about 16 MB/s.

A lower fillfactor leaves room on each page for new versions and raises the HOT ratio on update-heavy tables. I didn’t change it in this test.

One open transaction

Short rounds can’t show the cost of old row versions, because cleanup keeps up for a few minutes. So I ran 2,000 updates a second on the indexed column for longer: once for 30 minutes as is, and once for 25 minutes with a transaction held open on each database.

In the plain 30 minute run, Postgres indexes grew from 522 MB to 735 MB and the MySQL tablespace from 646 MB to 705 MB. Postgres still had the lower response time for most of the run.

In the second run, one session on each database started a repeatable read transaction, read one product, and then slept inside the transaction for longer than the round lasted. On Postgres:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM products WHERE id = 1;
SELECT pg_sleep(1700);

On MySQL the same thing was START TRANSACTION WITH CONSISTENT SNAPSHOT, the same read, and SELECT SLEEP(1700).

Vacuum can’t remove any row version that snapshot might still see, so on Postgres:

• n_dead_tup on the orders table climbed to roughly 3 million,
• the table grew from 383 MB to 614 MB and its indexes to 912 MB,
• p95 averaged about 325 ms over the last few minutes.

MySQL keeps old versions in its undo log instead of the table. Undo grew from 67 MB to over 1 GB, but the table stayed compact and p95 stayed near 20 ms. No other round in the test went to MySQL.

On Postgres, these two queries find the problem before it costs you:

-- sessions holding a transaction open, oldest first
SELECT pid, state, now() - xact_start AS open_for, left(query, 60) AS last_query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start
LIMIT 5;

-- dead rows waiting for vacuum
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 5;

In an app, the usual culprit is a session that is idle in a transaction (a request that began a transaction and never committed). Postgres can end those for you:

ALTER DATABASE shop SET idle_in_transaction_session_timeout = '60s';

That timeout would not have stopped the session in this test, because it was busy sleeping rather than idle. For a transaction that stays active, transaction_timeout (PostgreSQL 17 and later) puts a cap on the whole transaction.

On MySQL, the same pressure shows up as a growing history list length:

SELECT count FROM information_schema.innodb_metrics WHERE name = 'trx_rseg_history_len';

The machines

A faster or slower CPU on one side could explain all of this, so after the main rounds I swapped the two database machines and ran reads again. The result was 14,868+ against 6,969, with Postgres ahead as before.

What this doesn’t cover

One small machine, these versions, these settings. No replicas, no failover, no connection pooler, no large write bursts against a cold cache, and no measurement of what a team already knows how to run. Any of those can change the choice. The open transaction result is the one I’d act on first: if an app can leave a transaction idle, Postgres needs a timeout or monitoring for it.

Sources

• db-wars: the app, k6 script and setup scripts
• PostgreSQL: heap-only tuples (HOT)
• PostgreSQL: routine vacuuming
• PostgreSQL: idle_in_transaction_session_timeout and transaction_timeout
• PostgreSQL: resource consumption, shared_buffers
• MySQL: clustered and secondary indexes
• MySQL: InnoDB multi-versioning and purge
• MySQL: innodb_flush_method
• k6: ramping arrival rate executor