MySQL vs Postgres in 2026, in depth: storage, MVCC, locking, replication and the release lines that matter now

The release lines in October 2026

MySQL now follows a long-term-support and innovation model. MySQL 8.0 reached end of life on 30 April 2026, so anything still on it is running without security fixes. The supported LTS lines are 8.4, from April 2024, and 9.7, released in April 2026 with several replication observability and Group Replication features moved from Enterprise into Community. Innovation releases sit between LTS lines and have switched to calendar versioning, starting with 26.7 in July 2026. Production fleets should normally track an LTS line.

PostgreSQL ships one major version a year and supports each for five years. PostgreSQL 18, from September 2025, is the current major release; it added an asynchronous I/O subsystem, a built-in uuidv7() function, virtual generated columns as the default kind, OAuth authentication and B-tree skip scan. PostgreSQL 19 was at its fourth beta in late September 2026 with a release candidate expected next, so plan upgrades around its final release notes, not beta announcements.

Storage: a clustered tree versus a heap

InnoDB stores every table as a B+tree keyed on the primary key, and the leaf pages hold the rows themselves. Secondary indexes store the indexed columns plus the primary key, so a lookup by email walks the email tree, then the primary key tree. Postgres stores rows in an unordered heap and every index, primary or not, points at a physical tuple location.

The consequences are practical. In InnoDB, primary key choice decides physical layout: sequential keys append to the right edge of the tree, while random keys such as UUID version 4 insert all over it, splitting pages and multiplying write I/O. Range scans on the primary key read contiguous pages, which suits time-ordered or tenant-prefixed keys. Wide primary keys also bloat every secondary index, because each entry carries a copy.

In Postgres, primary keys are just a unique index, but updates are more expensive: an update writes a new tuple version, and unless the change qualifies as a heap-only tuple, meaning no indexed column changed and the page has room, every index on the table gets a new entry too. Tables with many indexes and frequently updated columns pay this write amplification. Leaving free space with a lower fillfactor raises the share of heap-only updates.

MVCC: undo log purge versus vacuum

Both engines give readers a consistent snapshot without blocking writers, but they keep history in different places. InnoDB updates the row in place and writes the previous version into the undo log; a reader needing the old version reconstructs it from undo, and a background purge discards undo once no snapshot needs it. Postgres leaves the old tuple in the heap and marks it dead once invisible to every snapshot; VACUUM, usually run by autovacuum, reclaims that space and maintains the visibility map that index-only scans depend on.

The shared enemy is the long-running transaction. In MySQL it blocks purge, the history list length climbs and every read of a hot row walks a longer undo chain. In Postgres it stops vacuum from removing dead tuples, so tables and indexes bloat. Postgres adds a hazard of its own: transaction ids are 32 bits, so tables must be frozen before the counter wraps, and a vacuum that is blocked for long enough eventually forces protective measures. The mechanics are covered in the MVCC article and the tuning in the vacuum and bloat article.

Isolation and locking defaults

The defaults differ, and code ported between them changes behaviour silently. MySQL InnoDB defaults to REPEATABLE READ; Postgres defaults to READ COMMITTED. In MySQL, locking reads and writes under REPEATABLE READ take next-key locks, which lock index records and the gaps between them to prevent phantoms. That is correct but surprising, because two transactions working on different keys can still deadlock through the gaps:

-- MySQL, default REPEATABLE READ. orders has an index on (customer_id).
-- Session A
START TRANSACTION;
SELECT * FROM orders WHERE customer_id = 42 FOR UPDATE;   -- next-key locks the range
-- Session B
START TRANSACTION;
SELECT * FROM orders WHERE customer_id = 43 FOR UPDATE;
-- Session A
INSERT INTO orders (customer_id, total) VALUES (43, 10);   -- waits on B's gap lock
-- Session B
INSERT INTO orders (customer_id, total) VALUES (42, 10);   -- waits on A: deadlock, one is rolled back

InnoDB detects the cycle and rolls one transaction back, so the application must retry. Many teams switch MySQL to READ COMMITTED, which disables most gap locking, once they have checked that their code does not rely on repeatable reads.

Postgres REPEATABLE READ is snapshot isolation: no gap locks, but a transaction that tries to update a row changed since its snapshot fails with a serialization error. SERIALIZABLE adds serializable snapshot isolation, detecting dangerous read-write patterns and aborting one transaction. Either way, the application must retry on SQLSTATE 40001:

import psycopg
from psycopg import errors

def transfer(conn, src, dst, amount, attempts=5):
    for _ in range(attempts):
        try:
            with conn.transaction():
                conn.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
                bal = conn.execute("SELECT balance FROM accounts WHERE id = %s",
                                   (src,)).fetchone()[0]
                if bal < amount:
                    raise ValueError("insufficient funds")
                conn.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s", (amount, src))
                conn.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s", (amount, dst))
            return
        except errors.SerializationFailure:
            continue          # SQLSTATE 40001: safe to retry the whole transaction
    raise RuntimeError("gave up after retries")

Replication and high availability

MySQL's row-based binlog is a single logical change stream that replicas, change data capture tools and cross-version upgrades all consume, which makes topologies with many replicas and blue-green major upgrades straightforward. Postgres physical replication copies byte-identical pages, which is simple and exact but ties the standby to the primary's major version; major upgrades use pg_upgrade or logical replication into a new cluster. High availability is a product decision in Postgres, since core ships no failover manager. The replication article compares the general trade-offs.

Schema changes

Postgres runs most DDL inside transactions, so a migration that adds a table, a column and a constraint either fully applies or fully rolls back. Adding a column with a constant default has been a metadata-only change since version 11, and indexes can be built without blocking writes using CREATE INDEX CONCURRENTLY, which cannot run inside a transaction block.

MySQL DDL commits implicitly, so a failed multi-step migration leaves partial state. In exchange, InnoDB's online DDL is broad: since 8.0.29, ALGORITHM=INSTANT adds or drops columns at any position as a metadata change, and many other operations run in place without blocking writes. For table rebuilds on big tables, gh-ost and pt-online-schema-change remain the standard tools.

-- PostgreSQL: DDL is transactional; bound the lock wait so a long query cannot queue everyone
SET lock_timeout = '3s';
BEGIN;
ALTER TABLE orders ADD COLUMN channel text NOT NULL DEFAULT 'web';  -- metadata-only since PG 11
COMMIT;
-- CONCURRENTLY cannot run inside a transaction block, so it is its own step
CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_channel_idx ON orders (channel);

-- MySQL 8.0.29 and later: instant add column, metadata-only, any position
ALTER TABLE orders ADD COLUMN channel VARCHAR(16) NOT NULL DEFAULT 'web', ALGORITHM=INSTANT;
-- operations that rebuild the table: use ALGORITHM=INPLACE, LOCK=NONE, or gh-ost / pt-osc

Both share one trap: DDL needs a brief exclusive lock, and while it waits behind a long query, every new query queues behind the DDL. Set a lock timeout, lock_timeout in Postgres or lock_wait_timeout in MySQL, and retry.

Features that still differ

Postgres's extension model is its largest structural advantage: geospatial, time-series and vector search run inside the database you already operate. MySQL's advantages are operational: a simpler thread-based server, a replication stream many tools understand, and very predictable performance for primary-key-centric access patterns.

Connections

MySQL serves each connection with a thread, which is cheap enough that thousands of mostly idle connections are workable. Postgres forks a backend process per connection, with a larger memory footprint and costlier connection setup, so most production Postgres sits behind a pooler such as PgBouncer in transaction mode, which breaks session state such as SET, SQL-level PREPARE and session advisory locks unless you plan for it; recent PgBouncer versions can track protocol-level prepared statements, so check yours. The connection pooling article covers sizing.

Worked example: a random primary key

A team stores 50 million events a day keyed on UUID version 4. On MySQL, inserts land on random leaf pages of the clustered tree, so the working set is the whole tree; once it exceeds the buffer pool, each insert costs a random read and page splits leave pages half full. The fix is a time-ordered key: generate UUID version 7 in the application and store it as BINARY(16), or use an auto-increment key and keep the UUID as a unique secondary column.

On Postgres the heap appends regardless of key, so the table is unaffected, but the primary key index suffers the same random-insert pattern. Postgres 18's uuidv7() fixes that in the database. The lesson generalises: InnoDB punishes random keys in the table itself, Postgres only in the index.

Failure modes

What to do next

1. If you run MySQL 8.0, plan the move to 8.4 or 9.7 LTS now; it has been without security fixes since 30 April 2026.
2. List your top ten queries and classify them as primary-key lookups, secondary lookups, range scans or analytics; that profile, not a feature list, should drive the choice.
3. Check primary keys for randomness and move hot tables to time-ordered keys.
4. Decide isolation explicitly: READ COMMITTED on MySQL unless you need more, and retry loops for SQLSTATE 40001 on Postgres.
5. Alert on the long-transaction signals: history list length on MySQL, oldest transaction age and dead tuples on Postgres.
6. Choose your high-availability story before go-live: InnoDB Cluster or a managed service for MySQL, Patroni or a managed service for Postgres.