PostgreSQL vs MySQL

PostgreSQL and MySQL are both mature, free relational databases that can run almost any OLTP workload, so the choice is rarely decided by a feature checklist. It is decided by a handful of architectural choices that change behaviour under load: how each serves a connection, where a row physically lives, what an UPDATE writes, which isolation level you get by default, and how changes reach replicas. This page compares those choices, with numbers, so you can pick one and defend the pick.

Picture it first: two land-record offices

Two district offices keep land records.

Office P shelves each record wherever there is a free slot, and every catalogue (by owner, by survey number, by village) gives the exact shelf and slot. When a record changes, the clerk writes a fresh copy into a new slot, stamps the old one “superseded”, and adds a card to every catalogue, because every catalogue must now point at the new slot. A weekly sweeper removes superseded copies and stale cards. Each visitor gets a personal clerk.

Office M shelves records in survey-number order. The other catalogues give the survey number, not a shelf, so finding a record by owner takes two look-ups. When a record changes, the clerk corrects it in place, first copying the old line onto a slip in a back drawer for anyone who still needs it. Only catalogues whose field changed get new cards. A team of clerks in one room serves visitors in turn.

Map it back. Office P is PostgreSQL: heap tables, indexes holding a physical location (the ctid), a new row version per update, VACUUM as the sweeper, a backend process per connection. Office M is MySQL’s InnoDB engine: a clustered index on the primary key, secondary indexes holding the key, in-place updates with an undo log, purge as the sweeper, a thread per connection. Neither office is wrong; they are fast at different things.

Where they come from

PostgreSQL descends from Berkeley’s Postgres project (its licence still carries a 1994 University of California copyright) and ships under the PostgreSQL License, a permissive licence similar to BSD or MIT. The PostgreSQL Global Development Group, a community rather than one vendor, develops it. MySQL is open source under the GPL, and Oracle, which owns it, also sells commercial licences and an Enterprise Edition with extras such as the thread pool. MariaDB is a GPLv2 fork made by MySQL’s original developers; it has drifted (it supports INSERT … RETURNING, MySQL 8.4 does not), so don’t treat the two as interchangeable.

On this page “MySQL” means MySQL 8.4 with InnoDB, its default storage engine, and “PostgreSQL” means version 18. The deeper story of PostgreSQL itself lives in PostgreSQL; this page is about the comparison.

One process or one thread per connection

PostgreSQL uses what its documentation calls a process per user model. A supervisor process, the postmaster, listens on the port and spawns a new backend process for every connection. That backend parses, plans and executes every query for that one client, and talks to the other backends through shared memory and semaphores.

MySQL runs as one mysqld process and, by default, executes each client connection on its own thread, with a thread cache so new connections can reuse old threads. Its manual lists what goes wrong as connections grow: thousands of thread stacks make CPU caches useless, context switching climbs, and too many concurrent transactions fight over InnoDB’s internal mutexes. The commercial Enterprise Thread Pool caps how many threads run at once.

What this costs in memory:

• MySQL. The Vitess documentation puts the overhead of one MySQL connection at 256 KB to almost 3 MB, depending on the release. Uber’s 2016 post says it ran some MySQL instances close to 10,000 concurrent connections.
• PostgreSQL. Each backend has its own private memory, and query memory comes on top. work_mem (4 MB by default) applies per sort or hash operation, and hash operations may use work_mem × hash_mem_multiplier (2.0 by default), so 8 MB. Take 300 connections, each running a query with two hash joins and one sort: 300 × (2 × 8 + 4) MB = 300 × 20 MB = 6,000 MB, about 6 GB, in the worst case.

The consequence is the same in both, but sharper for PostgreSQL: a 16-core server runs 16 queries at any instant, so thousands of connections only add queueing, memory and context switches. Put a pooler such as PgBouncer in transaction mode (or your cloud’s proxy) in front of PostgreSQL and keep active backends near the core count; MySQL tolerates direct connections for longer, but the same arithmetic wins eventually. Connection pooling covers sizing with Little’s law.

Where rows live: heap vs clustered index

This is the difference that explains most of the others.

PostgreSQL stores a table as a heap: an array of 8 kB pages, with each row version placed wherever there is room. A row version’s address is its ctid, a page number plus a slot number such as (7,4). Every index, including the primary key, maps a key to a ctid. The primary key is just one more index.

InnoDB stores a table as a B+tree on the primary key, the clustered index. Its leaf pages (16 KB by default) hold the whole rows, in key order. If you declare no primary key, InnoDB uses the first UNIQUE index whose columns are all NOT NULL, and failing that a hidden 6-byte row ID. Every secondary index record stores the indexed columns plus the primary key, and InnoDB uses that key to find the row.

Four consequences follow.

1. Primary-key choice matters far more in InnoDB. Rows are stored in key order, so the key decides where every insert lands. MySQL’s manual says sequential inserts leave pages about 15/16 full, but random inserts leave them anywhere from 1/2 to 15/16 full. A random UUIDv4 key scatters inserts across the whole tree: each insert needs a random leaf page in memory, and splits leave half-empty pages behind. If pages end up around 70% full instead of 94% (an assumption inside that range), the same rows need 0.94 ÷ 0.70 ≈ 1.34× the pages, and the hot working set becomes the whole table.

The key is also copied into every secondary index. A 16-byte binary UUID instead of an 8-byte BIGINT, on a 500-million-row table with 4 secondary indexes, costs 500,000,000 × 4 × 8 bytes = 16 GB extra; a 36-character string costs far more. In PostgreSQL a random key scatters only the primary-key index. The fix in both is a time-ordered key: a BIGINT identity or UUIDv7 (PostgreSQL 18 has a built-in uuidv7()).

2. Secondary lookups cost two descents in InnoDB. Markus Winand calls this the clustered index penalty: any WHERE clause served by a secondary index pays it, UPDATE and DELETE included. PostgreSQL pays one descent plus one heap page.

3. Covering indexes behave differently. InnoDB secondary indexes already contain the primary key, so SELECT id FROM users WHERE email = ? never touches the clustered index. PostgreSQL can add columns with INCLUDE and do index-only scans, but it can skip the heap only for pages its visibility map marks all-visible, which VACUUM maintains. On a busy table recently changed pages aren’t marked, so “index-only” scans quietly visit the heap.

4. Range scans by primary key favour InnoDB. With a key like (customer_id, order_id), all of one customer’s orders sit side by side, one range read. PostgreSQL’s CLUSTER command sorts a table once, but new and updated rows are not kept in order. The trade-offs of both layouts are in database indexes and composite index design.

What an UPDATE writes

Both engines use MVCC so readers don’t block writers, but they keep old versions in different places, and that changes what one UPDATE costs.

• PostgreSQL never overwrites a row. An update writes a complete new tuple, sets xmax on the old one, and, because the new tuple has a new ctid, adds an entry to every index. The exception is a HOT (heap-only tuple) update: if no indexed column changed and the page has room, the new tuple stays on the same page and no index is touched. Dead tuples stay until VACUUM, usually autovacuum, reclaims them (vacuum and bloat).
• InnoDB writes the old values to an undo log and changes the row in place in the clustered index. A secondary index changes only if one of its columns changed: the old entry is delete-marked and a new one inserted. Background purge threads later discard undo and delete-marked entries that no snapshot needs.

So PostgreSQL makes the writer pay (more pages and WAL per update), then pays again in VACUUM; InnoDB makes old readers pay, rebuilding versions from undo, and lets the history list grow when purge falls behind. In PostgreSQL, avoid indexing columns that change constantly and lower a hot table’s fillfactor to leave room for HOT updates. In InnoDB, keep transactions short so purge keeps up.

The Uber data point

The most quoted comparison is Uber’s 2016 post on moving from PostgreSQL to MySQL. On PostgreSQL 9.2 they reported write amplification (one changed field wrote a new tuple plus an entry in every index, all of it WAL-logged), replication volume that strained cross-data-centre links, a replica corruption bug in some 9.2 releases, hot-standby conflicts that paused replay or cancelled queries, and upgrades that needed downtime because physical replication can’t cross major versions.

Read it as one workload’s verdict, not a ranking. Markus Winand’s reply notes the post never mentions HOT updates, so Uber’s hot updates must have changed an indexed column; that InnoDB pays the clustered index penalty on every secondary lookup instead; and that the workload looked like a key-value store, which is how Uber used MySQL, under its own Schemaless layer. Some pain has since eased: built-in logical replication, added after that post, crosses major versions, and hot_standby_feedback trades replica cancellations for some bloat on the primary. The lesson worth keeping: count writes per update on your hottest table. Reads and writes has more storage-engine war stories.

Isolation defaults, and a timeline where they diverge

The two engines start at different levels, and the same level name means different things.

• PostgreSQL defaults to Read Committed: each statement sees a fresh snapshot. Its Repeatable Read is snapshot isolation that aborts a transaction which tries to update a row changed since its snapshot. Its Serializable is Serializable Snapshot Isolation (SSI): it tracks reads with predicate locks that never block, and aborts one transaction when a dangerous pattern appears.
• InnoDB defaults to Repeatable Read: plain SELECTs read a snapshot taken at the first read, but UPDATE, DELETE and locking reads act on the latest committed rows and lock the index range they scan, using gap and next-key locks to block inserts into it. Its Serializable is the same plus locking: with autocommit off, plain SELECTs become SELECT … FOR SHARE.

How each level behaves in practice is in transaction isolation levels in practice, and SSI versus locking in serializable isolation. Here is one divergence that surprises people porting code. A clinic table slots(doctor_id, slot_hour) has an index on both columns, and doctor 7 has slots at 10 and 12:

-- T1: lock doctor 7's late-morning slots before changing them
BEGIN;
SELECT * FROM slots WHERE doctor_id = 7 AND slot_hour BETWEEN 10 AND 12 FOR UPDATE;

-- T2, meanwhile
INSERT INTO slots (doctor_id, slot_hour) VALUES (7, 11);

Neither is a bug. InnoDB’s gap locks protect “no new rows in this range” rules for free, but they also make inserts wait on range scans and are a classic source of deadlocks. PostgreSQL never blocks the insert, so a rule like “at most three slots per doctor per morning” needs a constraint, an explicit lock on a parent row, or SERIALIZABLE with retries (phantom read).

Replication and high availability

PostgreSQL ships its write-ahead log. With physical streaming replication, a standby replays WAL as it is generated and becomes a byte-for-byte copy; it is asynchronous by default, with lag typically under a second, and needs the same major version on the same platform. Logical replication is publish-and-subscribe: a subscriber copies a snapshot of the published tables, then receives row changes. It crosses major versions and platforms, but carries no DDL or sequence values. Core PostgreSQL doesn’t detect a failed primary or promote a standby; its documentation leaves that to external tools, in practice a cluster manager or a managed service.

MySQL replicates through the binary log, row-based by default. GTIDs (source_uuid:transaction_id) give every transaction an identity across the topology, so a replica applies each one at most once and can switch sources without hunting for file positions. Semi-synchronous replication makes the source wait until a replica has the transaction. Group Replication goes further: a Paxos-based layer makes a majority agree on each transaction’s order before it commits, and in single-primary mode elects a new primary automatically. InnoDB Cluster packages it with MySQL Shell and MySQL Router.

In practice, PostgreSQL’s physical replica is exact but chatty and tied to one version. MySQL’s row-based binlog is logical and version-tolerant (upgrade replicas one at a time, then promote one), and easy for change data capture tools to read. Lag and failover traps are in data replication.

Where each one shines

PostgreSQL

• Rich types. jsonb with GIN indexes that answer containment queries (@>) on any key; arrays; and range types with exclusion constraints, which make “no two bookings of one room may overlap” a database rule.
• More ways to index. Partial and expression indexes, plus GIN, GiST and BRIN index types.
• Transactional DDL. Schema changes roll back with the transaction, so a failed migration leaves nothing half-done.
• Extensions. PostGIS for geospatial data, pgvector for vector similarity search, Citus for distributing tables, TimescaleDB for time series. Teams often pick PostgreSQL for one of these alone.
• RETURNING on INSERT, UPDATE and DELETE; since version 18 it can return old and new values.

MySQL

• Instant and online DDL. In 8.4, adding or dropping a column defaults to ALGORITHM=INSTANT, a metadata-only change; secondary indexes build while writes continue.
• A proven sharding layer. Vitess shards MySQL behind a proxy that speaks the MySQL protocol. It served all of YouTube’s database traffic for over five years, and Slack, Square and JD.com run it (sharding a relational database).
• JSON through generated columns. The JSON type isn’t indexed directly: you index a generated column or functional key part, or use a multi-valued index for arrays.
• Constraints have caught up. CHECK constraints are enforced since 8.0.16; earlier versions parsed and ignored them.
• Cheap updates on heavily indexed tables, and replication most operators find simple to run.

The same task in both dialects

An upsert that adds to a wallet balance and returns the new value:

-- PostgreSQL: name the conflict target; RETURNING gives the result in one round trip
INSERT INTO wallet (user_id, balance) VALUES (42, 100)
ON CONFLICT (user_id) DO UPDATE
  SET balance = wallet.balance + EXCLUDED.balance
RETURNING balance;

-- MySQL 8.4: fires on ANY unique key; no RETURNING, so read it back
INSERT INTO wallet (user_id, balance) VALUES (42, 100) AS new
ON DUPLICATE KEY UPDATE balance = balance + new.balance;
SELECT balance FROM wallet WHERE user_id = 42;

PostgreSQL guarantees an atomic insert-or-update even under high concurrency. MySQL’s version reacts to a duplicate in any unique index, so its manual advises avoiding it on tables with several unique keys. The older VALUES(col) form is deprecated in favour of the row alias shown.

Indexing a JSON attribute, and keeping a partial index small:

-- PostgreSQL: one GIN index serves containment queries on any key
CREATE INDEX orders_attrs_gin ON orders USING GIN (attrs);
SELECT id FROM orders WHERE attrs @> '{"payment": "upi"}';

-- PostgreSQL: index only the 2% of orders still open
CREATE INDEX orders_open ON orders (restaurant_id) WHERE status <> 'DELIVERED';

-- MySQL: extract one path into a virtual generated column, then index it
ALTER TABLE orders ADD COLUMN payment VARCHAR(16) AS (attrs->>'$.payment');
CREATE INDEX orders_payment ON orders (payment);
SELECT id FROM orders WHERE payment = 'upi';

MySQL has no partial indexes; its CREATE INDEX has no WHERE clause. And a double-booking rule that only PostgreSQL can state declaratively:

CREATE EXTENSION btree_gist;
CREATE TABLE room_booking (
  room_id int,
  during  tstzrange,
  EXCLUDE USING gist (room_id WITH =, during WITH &&)
);

Schema changes at scale

Both engines can change big tables without downtime, by different routes. Online schema and data migrations covers the expand-and-contract process around them.

PostgreSQL. CREATE INDEX CONCURRENTLY builds an index without blocking writes. It scans the table twice, waits for transactions that could use the index, can’t run inside a transaction block, and on failure leaves an INVALID index that still costs writes, so you drop it and retry. Adding a nullable column, or one with a non-volatile default, only changes the catalogue. The catch is the lock: most ALTER TABLE forms take an ACCESS EXCLUSIVE lock, and while one waits behind a long-running query, later queries on that table queue behind it, so set a short lock_timeout and retry. Because DDL is transactional, a whole migration can sit inside BEGIN … COMMIT.

MySQL. Each ALTER TABLE runs as INSTANT, INPLACE or COPY, and you can demand one (ALGORITHM=INSTANT, LOCK=NONE) so it fails fast instead of silently copying the table. Each instant column change adds a row version, and once a table has 64 (in 8.4), instant adds and drops are refused until it is rebuilt. Changing a column’s type still needs COPY, which blocks writes; that is where gh-ost comes in. It builds a “ghost” copy, copies rows in chunks, applies ongoing changes by reading the binary log instead of using triggers, can throttle or pause, and lets you postpone the final swap. MySQL DDL is atomic (all or nothing) but not transactional: each DDL statement implicitly commits the open transaction.

Side by side

How to choose

Most applications would be fine on either, so choose on what actually differs:

1. Team and tooling first. A database your on-call engineers can tune, back up and fail over beats a theoretically better one; a new service inherits existing runbooks.
2. Data model and queries. Geospatial search (PostGIS), embeddings next to relational data (pgvector), document-style jsonb queries, range constraints or partial indexes point to PostgreSQL. Real analytics belongs in a warehouse either way.
3. Write shape. Hot tables with many secondary indexes and constant updates to indexed columns favour InnoDB. First check whether dropping an index or tuning fillfactor would make PostgreSQL’s updates HOT.
4. Scale-out plan. If you expect to shard, Vitess has carried MySQL at YouTube and Slack scale; Citus is the PostgreSQL option, and both families have wire-compatible distributed SQL databases. Most systems never get there (database scaling).
5. Operations. Both run as managed services: Amazon RDS and Aurora offer MySQL- and PostgreSQL-compatible engines, and Cloud SQL runs MySQL, PostgreSQL and SQL Server. Managed failover removes PostgreSQL’s biggest do-it-yourself gap.

Going deeper: how each one fails in production

PostgreSQL

• Connection storms. A deploy or retry storm opens thousands of connections, each a process. Pool before you need to.
• Autovacuum falling behind. Dead tuples pile up, indexes bloat, scans slow. One forgotten open transaction stops clean-up database-wide, and in the extreme the 32-bit transaction counter heads for wraparound (vacuum and bloat).
• Replica query cancellations. Long reports on a standby conflict with WAL replay and are cancelled after max_standby_streaming_delay; hot_standby_feedback stops that by delaying clean-up on the primary.

MySQL

• Purge lag. Long transactions keep undo alive; the history list grows and old readers walk longer chains (MVCC).
• Gap-lock deadlocks. Range scans under Repeatable Read lock gaps, so inserts into nearby keys deadlock more than teams expect. Some switch to Read Committed, which drops most gap locking, and accept phantoms.
• Random primary keys. UUIDv4 keys fragment the clustered index, and fixing it later means rebuilding the table.
• Silent loss of semi-sync. If no replica acknowledges in time, the source falls back to asynchronous replication (data replication).

Long transactions, missing indexes and N+1 queries hurt both. Most “PostgreSQL vs MySQL” incidents are really schema, query and transaction-length incidents.

Cheat sheet

How to say it in the interview

“Both are mature OLTP databases, so I’d start from what the team can operate. The differences that matter are architectural. PostgreSQL forks a process per connection, so I’d put PgBouncer in front. It stores rows in a heap with every index pointing at a physical location, so an update writes a new row version and, unless it’s HOT, a new entry in every index; VACUUM cleans up later. InnoDB clusters rows on the primary key and updates in place with an undo log, which is cheaper for update-heavy tables with many indexes, but secondary lookups walk two trees and the key should be short and sequential. Defaults differ too: Read Committed versus Repeatable Read with gap locks. Here I’d choose PostgreSQL for jsonb and PostGIS, on a managed service with a pooler.”

Why did Uber move from PostgreSQL to MySQL, and does that mean MySQL is faster? Their update-heavy tables had many indexes, so each update rewrote every index and the WAL that replicated it; they also hit a replica bug and painful upgrades on PostgreSQL 9.2. That is one workload. HOT updates, logical replication and fewer indexes address much of it, and InnoDB pays elsewhere, on every secondary lookup.

Why is a UUIDv4 primary key worse in MySQL than in PostgreSQL? InnoDB stores rows in key order, so random keys land on random leaf pages, split them and leave them half-empty, and every secondary index carries the wide key. In PostgreSQL only the primary-key index suffers. In both, use a time-ordered key such as UUIDv7 or a BIGINT identity.

Same Repeatable Read, same behaviour? No. PostgreSQL’s is snapshot isolation that aborts on a concurrent update and takes no gap locks. InnoDB’s reads a snapshot but writes against the latest version and locks gaps, so inserts can block and read-then-write code can lose updates.

How would you run a big schema change in each? PostgreSQL: CREATE INDEX CONCURRENTLY, additive columns that don’t rewrite the table, and a short lock_timeout. MySQL: demand ALGORITHM=INSTANT or INPLACE with LOCK=NONE, and use gh-ost for changes that would copy the table.

Which would you choose if we expect to shard? MySQL with Vitess if the team knows MySQL: it has carried YouTube’s and Slack’s MySQL traffic. With PostgreSQL I’d look at Citus or a PostgreSQL-compatible distributed SQL database, after checking whether partitioning, replicas and caching postpone sharding for years.

Where to go next

• PostgreSQL: PostgreSQL itself, its features and operations.
• MVCC: xmin and xmax, undo and read views, in full.
• Vacuum and bloat: autovacuum tuning and wraparound.
• Connection pooling: PgBouncer modes and pool sizing.
• Transaction isolation levels in practice: what each level really does in both engines.
• Data replication: sync vs async, lag and failover traps.
• Sharding a relational database: Vitess, Citus and application-level sharding.
• Online schema and data migrations: expand, migrate, contract.
• Database architectures: how a database server is built, from one box to many.

References and further reading

1. PostgreSQL documentation: How Connections Are Establishedpostgresql.orgThe process-per-user model: the postmaster spawns one backend process per connection, and backends share memory and semaphores.
2. MySQL 8.4 Reference Manual: MySQL Enterprise Thread Pooldev.mysql.comThe default one-thread-per-connection model and the three problems (cache thrash, context switches, contention) the commercial thread pool addresses.
3. MySQL 8.4 Reference Manual: Clustered and Secondary Indexesdev.mysql.comThe primary key as clustered index, the fallback to a hidden 6-byte row ID, and secondary records that carry the primary key.
4. MySQL 8.4 Reference Manual: The Physical Structure of an InnoDB Indexdev.mysql.com16 KB pages, and pages about 15/16 full after sequential inserts versus 1/2 to 15/16 full after random ones.
5. PostgreSQL documentation: Transaction Isolationpostgresql.orgRead Committed as the default, and Serializable implemented as Serializable Snapshot Isolation with non-blocking predicate locks.
6. MySQL 8.4 Reference Manual: Transaction Isolation Levelsdev.mysql.comRepeatable Read as the InnoDB default, gap and next-key locks on scanned ranges, and Serializable turning plain SELECTs into locking reads.
7. MySQL 8.4 Reference Manual: Online DDL Operationsdev.mysql.comINSTANT as the default for adding columns, the 64 row-version limit, in-place index builds with concurrent DML, and type changes that need COPY.
8. Why Uber Engineering Switched from Postgres to MySQL (Uber, 2016)uber.comWrite amplification, WAL replication volume, a replica corruption bug, hot-standby conflicts, upgrade pain and connection limits on PostgreSQL 9.2.
9. On Uber’s Choice of Databases (Markus Winand, 2016)use-the-index-luke.comThe counter-reading: HOT updates, the clustered index penalty on secondary lookups, and why the post describes one update-heavy workload.
10. Vitess documentation: What is Vitessvitess.ioMySQL sharding through VTGate and VTTablet, its YouTube history and users, and per-connection MySQL memory of 256 KB to almost 3 MB.