Postgres vs MySQL performance: which is faster, and when? · DpkDB
Postgres vs MySQL performance: which is faster, and when?
Explains by mechanism where InnoDB or PostgreSQL does less work for lookups, updates, joins, connections and replication, then shows how to benchmark your own workload with pgbench and sysbench.
Neither PostgreSQL nor MySQL is faster in general. MySQL with InnoDB has a structural advantage on primary-key lookups, primary-key range scans and frequent updates to rows in tables with many indexes. PostgreSQL has a structural advantage on complex joins, large aggregations and anything that benefits from parallel query or specialised indexes. For a typical web application with sensible indexes, the gap between the two is smaller than the gap between a tuned and an untuned installation of either, so choose on features and team knowledge, then benchmark your own queries before believing anyone's numbers.
This article explains each difference by mechanism, for PostgreSQL 18 and MySQL 8.4 with InnoDB, and ends with how to run a comparison that means something. It contains no benchmark figures, because figures from someone else's hardware and schema do not transfer to yours. For the feature comparison, see PostgreSQL vs MySQL: the differences that matter.
Where each engine has the advantage
Workload
Structural advantage
Why
Lookup by primary key
MySQL
The row is in the leaf of the primary key B+tree
Range scan in primary key order
MySQL
Rows are physically stored in that order
Lookup by secondary index
Roughly even
InnoDB descends two B+trees; PostgreSQL descends one and fetches a heap page
Update of a non-indexed column
Even to MySQL
In place in InnoDB; PostgreSQL can use a HOT update if the page has room
Update on a table with many indexes
MySQL
PostgreSQL may write a new entry in every index
Bulk insert with a random primary key
PostgreSQL
Heap inserts append; InnoDB splits clustered index pages
A reference comparison of PostgreSQL and MySQL: storage and MVCC, isolation defaults, DDL, data types, indexes, replication, licensing and tooling, with a decision summary.
Queries needing GIN, GiST, BRIN or partial indexes
PostgreSQL
MySQL has no equivalent index types
"Structural advantage" means the engine does less work for that operation, not that the difference is visible in your application.
Primary key lookups and range scans
InnoDB stores each table as a B+tree on the primary key with complete rows in the leaf pages. A lookup by primary key descends one tree and is finished. A range scan on a primary key of (account_id, order_id) reads neighbouring rows from the same few pages.
PostgreSQL stores rows in a heap in no guaranteed order, and the primary key is an ordinary B-tree that points into it. A lookup descends the index and then reads the heap page. A range scan reads the index entries in order, but the heap rows they point to may be spread across many pages. On a table that is only ever appended to, heap order follows insertion order, so the penalty is small. On a table whose rows are updated often, it grows. CLUSTER rewrites a table in index order, but the documentation describes it as a one-time operation that is not maintained afterwards.
PostgreSQL avoids the heap visit with an index-only scan when the index contains every column the query needs. This depends on the visibility map: if a page has been modified since the last vacuum, the heap is visited anyway to check row visibility. On a table with constant writes, index-only scans deliver less than their name suggests, which is also the reason behind Why is SELECT COUNT(*) so slow in PostgreSQL?.
The clustered design has its own cost: a wide primary key enlarges every secondary index and a random one splits pages. See InnoDB and the clustered primary key.
Secondary index lookups
In InnoDB a secondary index entry holds the indexed columns and the primary key value. Fetching other columns means a second descent through the clustered index for every matching row. Covering the query avoids that, and the primary key columns are always present in the index.
In PostgreSQL all indexes are equal. Each entry points directly at a heap location, so there is one index descent and one heap fetch per row. When many rows match, PostgreSQL can switch to a bitmap scan, which collects the matching locations, sorts them by page and reads the heap in physical order. It can also combine several indexes on the same table by ANDing and ORing their bitmaps. MySQL has an index merge access method for a similar purpose and supports index condition pushdown to filter rows inside the storage engine before the clustered lookup.
InnoDB wins when the secondary index covers the query. PostgreSQL wins when many rows match and are fetched through a bitmap scan.
The cost of an UPDATE
This is the largest mechanical difference for write-heavy applications.
PostgreSQL never overwrites a row. An UPDATE writes a full new row version to the heap. If the update changes no indexed column and there is free space on the same page, the new version is a heap-only tuple (HOT) and no index is touched. If either condition fails, PostgreSQL inserts a new entry into every index on the table, including indexes on columns that did not change, because the row has a new physical location.
You can measure how often you get the cheap path:
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
WHERE n_tup_upd >0ORDERBY n_tup_upd DESC
LIMIT 20;
A low percentage on a heavily updated table means either an index on a column that changes often, or pages with no free space. Dropping an unneeded index or lowering the table's fillfactor fixes the second and sometimes the first.
InnoDB updates the row in place in the clustered index and writes the old column values to an undo log. Only the secondary indexes whose columns changed are modified: the old entry is delete-marked and a new one inserted. Changes to secondary index pages that are not in memory can be deferred through the change buffer and merged later.
The undo approach moves cost to readers. A transaction that needs an older version of a row has to follow the undo chain to rebuild it, and a row changed many times since the reader's snapshot makes that chain long.
Both engines also protect against partially written pages after a crash, at a write cost. PostgreSQL writes the full content of a page to the WAL the first time the page changes after a checkpoint (full_page_writes). InnoDB writes pages to the doublewrite buffer before writing them to their final location.
Bloat, vacuum and purge lag
Old row versions have to be removed in both systems, and in both a long-running transaction prevents it.
In PostgreSQL the old versions are in the table and its indexes. Autovacuum reclaims them. When it cannot keep up, or when an open transaction holds back the cleanup horizon, tables and indexes grow, and every scan reads dead rows. How PostgreSQL MVCC and VACUUM actually work has the monitoring queries.
In InnoDB the old versions are in undo logs, cleaned by purge threads. A long transaction makes the history list grow, visible as History list length in SHOW ENGINE INNODB STATUS. Consistent reads slow down as undo chains lengthen, and delete-marked index entries accumulate until purge reaches them. The table itself does not bloat in the same way from updates, although deletes and page splits still leave fragmented space.
PostgreSQL's cleanup needs per-table tuning on large, busy tables. InnoDB's needs less attention in normal operation, until a forgotten transaction has been open for hours.
Connection cost and pooling
PostgreSQL starts an operating system process for each connection. MySQL uses a thread per connection and caches threads for reuse. Opening a connection is more expensive in PostgreSQL, and a large number of open connections consumes more memory and scheduling overhead.
An application that opens a connection per request, or a serverless platform that creates many short-lived clients, will show MySQL as faster unless PostgreSQL is run behind a pooler. With PgBouncer in transaction pooling mode, many client connections share a small number of server connections and the difference largely disappears. Transaction pooling restricts session features such as SET, LISTEN and session-level advisory locks, and protocol-level prepared statements need max_prepared_statements set to a non-zero value. See How do I fix "FATAL: sorry, too many clients already" in PostgreSQL?.
A benchmark that compares the two at thousands of direct connections measures connection architecture, not query execution.
Joins and the query planner
PostgreSQL's planner chooses among nested loop, merge and hash joins, and can run sequential scans, B-tree index scans, bitmap heap scans, hash joins and aggregates across parallel worker processes. It supports extended statistics on correlated columns and just-in-time compilation of expressions for long-running queries. It has no optimizer hints in core, so a bad plan is fixed with better statistics, a different index or a rewritten query.
MySQL executes joins with nested loops and, since 8.0.18, hash joins, which it uses for equi-joins when no index applies. It has no merge join. A query runs on a single thread, with one narrow exception: innodb_parallel_read_threads parallelises clustered index reads for operations such as CHECK TABLE, and the manual notes that it does not apply to secondary index scans. MySQL supports histograms on column values, and it does have optimizer hints and index hints, which give an operator a direct way to correct a bad plan.
For typical OLTP queries, which join a handful of rows through indexes, both planners produce the same nested loop plan and performance is equivalent. The gap opens with queries that join large sets, use several subqueries or aggregate millions of rows. PostgreSQL has more ways to execute these and can use several cores for one query. Each engine provides EXPLAIN ANALYZE to compare estimated and actual row counts. For PostgreSQL, see EXPLAIN ANALYZE in PostgreSQL: reading plans without guessing, and for the cases where an index is ignored, Why is my query still slow when the column has an index?.
Analytics queries
Both are row stores built for transactional work. Within that limit PostgreSQL is the better choice for reporting queries on the primary database: parallel scans and aggregates, hash aggregation, BRIN indexes for very large tables that are naturally ordered by time, materialized views for precomputed results, and an asynchronous I/O subsystem in version 18 that speeds up sequential and bitmap heap scans.
MySQL runs the same queries on one thread per query. Once analytics queries compete with transactional traffic on either engine, the answer is a read replica and eventually a columnar system.
Write-heavy workloads
For inserts, a PostgreSQL heap accepts rows wherever there is space regardless of key order, so only the indexes pay for a random key. InnoDB pays in the table itself when the primary key is random. With a sequential primary key InnoDB inserts are efficient and stay compact.
For durability, compare like with like. PostgreSQL flushes the WAL at commit unless synchronous_commit is relaxed. MySQL flushes the InnoDB redo log according to innodb_flush_log_at_trx_commit and the binary log according to sync_binlog, and binary logging is enabled by default in 8.4, so a fully durable MySQL commit syncs two logs. Group commit amortises this under concurrency on both sides.
For sustained writes, the long-run behaviour matters more than the peak: PostgreSQL write throughput dips after each checkpoint as full-page images inflate the WAL, and autovacuum competes for I/O. InnoDB has to keep flushing dirty pages and purging undo fast enough to stay ahead.
Replication lag
PostgreSQL physical replication replays the WAL on the standby in a single process. Replay is cheap per change because it applies page-level modifications, but a standby that serves read queries has a conflict to manage: replaying a change that removes rows a running query still needs either delays replay (max_standby_streaming_delay) or cancels the query. Turning on hot_standby_feedback avoids the cancellations by holding back cleanup on the primary, which can cause bloat there.
MySQL replicas re-execute row changes from the binary log, with multiple applier threads by default (replica_parallel_workers is 4 in 8.4) while preserving commit order. Lag usually comes from one very large transaction, from DDL, or from tables without a primary key, where each row change needs a scan on the replica. Why is my MySQL replica lagging behind the primary? covers these.
How to benchmark your own workload
Use the standard tools to learn the method, then replace their workload with yours.
pgbench ships with PostgreSQL. Its default script is loosely based on TPC-B and is update-heavy on small rows.
# Create a dataset. Scale 1000 creates 100,000,000 rows in pgbench_accounts.
pgbench -i -s 1000 bench
# 30 minutes, 32 clients, 8 driver threads, prepared statements,# progress every 10 seconds, per-statement latency at the end
pgbench -c 32 -j 8 -T 1800 -P 10 -M prepared -r bench
# Fixed arrival rate: report latency at 2,000 transactions per second
pgbench -c 32 -j 8 -T 1800 -P 10 -M prepared --rate=2000 --latency-limit=100 bench
# Your own queries
pgbench -c 32 -j 8 -T 1800 -P 10 -M prepared -f my_workload.sql bench
sysbench runs the same Lua workloads against both engines, which makes it the usual tool for cross-engine tests. Its OLTP scripts are dominated by primary-key access.
# MySQL
sysbench oltp_read_write --db-driver=mysql \
--mysql-host=10.0.0.5 --mysql-user=sbtest --mysql-password=secret --mysql-db=sbtest \
--tables=10 --table-size=10000000 prepare
sysbench oltp_read_write --db-driver=mysql \
--mysql-host=10.0.0.5 --mysql-user=sbtest --mysql-password=secret --mysql-db=sbtest \
--tables=10 --table-size=10000000 \
--threads=32 --time=1800 --report-interval=10 --percentile=99 run
# PostgreSQL (sysbench must be built with --with-pgsql)
sysbench oltp_read_write --db-driver=pgsql \
--pgsql-host=10.0.0.6 --pgsql-user=sbtest --pgsql-password=secret --pgsql-db=sbtest \
--tables=10 --table-size=10000000 prepare
sysbench oltp_read_write --db-driver=pgsql \
--pgsql-host=10.0.0.6 --pgsql-user=sbtest --pgsql-password=secret --pgsql-db=sbtest \
--tables=10 --table-size=10000000 \
--threads=32 --time=1800 --report-interval=10 --percentile=99 run
Rules that make the result worth having:
Tune both. The default shared_buffers in PostgreSQL and the default innodb_buffer_pool_size in MySQL are both 128MB. A test on defaults measures the defaults.
Match durability. Decide on full durability or not, and set synchronous_commit, innodb_flush_log_at_trx_commit and sync_binlog to match.
Use a realistic data size. A dataset that fits in memory hides all I/O behaviour. Size it in the same proportion to RAM as production.
Run long enough. Checkpoints, autovacuum and purge only show up after tens of minutes. Discard the warm-up period.
Run the client on a separate machine, and use the same connection strategy (pooler or not) you will use in production.
Report latency percentiles at a fixed rate, not only peak throughput. Peak throughput with every core saturated says little about response time at your normal load.
Use your schema and queries. A script of your ten most frequent statements with realistic parameters is worth more than any standard workload.
Why published benchmarks mislead
The workload favours one design. sysbench OLTP is mostly primary-key reads and writes, which suits a clustered index. Analytic suites with large joins suit PostgreSQL's planner. Neither resembles your application.
One side is untuned. The author knows one database well and runs the other close to defaults.
Durability or connection handling is mismatched. One side syncs every commit, or PostgreSQL is run with hundreds of direct connections and no pooler.
The run is short. Vacuum, checkpoints, purge lag and fragmentation have not started to matter.
Versions are old. Results from MySQL 5.7 predate hash joins.
The publisher sells one of them.
Checklist
Identify your dominant operations: primary-key access, secondary index lookups, updates on indexed tables, large joins or aggregations.
Match them to the table at the top. If nothing stands out, performance is not the deciding factor.
On PostgreSQL, check the HOT update ratio and autovacuum behaviour on your busiest tables, and put a pooler in front.
On MySQL, use a narrow sequential primary key, make hot secondary indexes covering, and watch the history list length.
Benchmark with your own queries, tuned configuration, matched durability, realistic data size and runs of at least 30 minutes.
Compare latency percentiles at your expected request rate, not maximum throughput.
Raising max_connections to 1000 usually makes things worse. Learn why Postgres connections are expensive, where they're leaking and how PgBouncer fixes it for good.
