PostgreSQL vs. MySQL: Beyond Basic Query Performance

A row you just updated in Postgres still has two copies on disk, and MySQL's equivalent update never does

Run UPDATE accounts SET balance = balance - 100 WHERE id = 42 in PostgreSQL and the old row doesn't get touched. A brand-new tuple is written elsewhere on the page, the old one gets marked dead, and unless every index on that table happens to avoid the changed column, every index entry pointing at that row now needs a second entry too. Run the same statement against InnoDB and the row is modified in place, with the previous value shunted into an undo log segment that only gets consulted if someone else's transaction still needs to see the old value. Same SQL, same ACID guarantee, completely different physical operation. That difference — not syntax, not the query planner, not "which one is faster" — is what actually separates these two engines at scale, and it explains almost every operational surprise that shows up once a table crosses a few hundred million rows.

This isn't a feature checklist. Both databases have JSON support, both do window functions, both replicate. The interesting comparison is underneath that: how each one decides what a "row" is, how it finds one, and what it does when a hundred clients try to touch the same one at once. That's what determines whether a schema that ran fine at 10 million rows falls over at 500 million, and why the fix looks completely different depending on which engine is under it.

PostgreSQL keeps every row version; InnoDB keeps one and a diff

PostgreSQL's concurrency model is built entirely around never overwriting a row in place. The documentation calls this a "multiversion model": every SQL statement sees a snapshot of the database in a state consistent with some earlier point in time, and PostgreSQL maintains that illusion by keeping multiple physical versions of a row alive simultaneously. Every tuple carries two hidden system columns, xmin and xmax. xmin is the ID of the transaction that created this version; xmax is the ID of the transaction that expired it — zero if the row is still current. A SELECT running inside transaction 5000 walks the heap and, for each tuple, checks whether xmin is a transaction that committed before its snapshot was taken and whether xmax is either zero or belongs to a transaction that hadn't committed yet. That's the whole visibility check, run per tuple, on every scan.

The consequence: UPDATE in PostgreSQL is really INSERT a new tuple plus mark the old one's xmax. The old version physically remains on disk, taking up exactly the space it always did, until VACUUM decides no running transaction can possibly still need it and reclaims the space for reuse. This is why a table that logically holds 2 million rows but gets updated constantly can occupy the disk footprint of 20 million — the condition PostgreSQL engineers just call bloat, and it's arguably the single most common cause of "this table used to be fast" tickets on any long-running Postgres instance.

InnoDB solves the same problem — readers shouldn't block writers, writers shouldn't block readers — with the opposite physical strategy. The current row is stored once, in place, inside the clustered index leaf page. When a transaction modifies it, InnoDB writes the previous version of the row into an undo log record before applying the change, and the row itself carries two hidden fields: DB_TRX_ID, the ID of the transaction that last touched it, and DB_ROLL_PTR, a pointer to that undo record. A transaction that needs an older version doesn't scan alternate heap tuples; it follows the rollback pointer chain backward, reconstructing the version it's entitled to see one undo record at a time, as documented in MariaDB's InnoDB internals materials on the write path. Committed changes update the row in place; the undo record just sits there until the InnoDB purge thread confirms no active read view still needs it, at which point the undo space gets reused. There's no separate old copy of the row cluttering the primary data pages — the "history" lives off to the side in undo tablespaces, capped at 128 rollback segments each.

This single design decision cascades into almost every practical difference between the two engines.

Why an update in PostgreSQL can touch every index on the table

Because a PostgreSQL UPDATE creates a whole new tuple at a new physical location, every index that references the row's location has to learn about that new location — otherwise an index scan following an old pointer would land on a dead tuple. PostgreSQL's documentation on Heap-Only Tuples (HOT) is explicit about the cost this creates and the fix: if the update doesn't touch any column referenced by any index on the table, and the target page has enough free space left, PostgreSQL writes the new version on the same page and links it to the old one via the tuple's t_ctid field, without touching a single index. The index entry keeps pointing at the original slot; a reader following it walks the in-page HOT chain to the current version.

The two conditions that make HOT possible are narrow. First, the update must not modify any indexed column — not the primary key, not a unique index, not a partial index predicate, not an expression index input, not even a column pulled in via an INCLUDE clause on a covering index. Touch any of those and the optimization is off entirely for that update, full stop, even if nine other indexes on the table were unaffected. Second, the new tuple has to physically fit on the same page as the old one, which is why tables that expect heavy HOT traffic are commonly built with a lower fillfactor — deliberately leaving slack space on each page at creation time so later updates have somewhere to land without spilling to a new page.

Get HOT right and updates on that table are cheap and don't inflate index size. Get it wrong — say, by indexing a last_login timestamp that gets touched on every request — and every single update now writes new entries into every index on the table, which is exactly the kind of design mistake that looks fine in a proof-of-concept and becomes a write-amplification problem at production volume. There's an active proposal on the PostgreSQL wiki, Partial HOT, to relax this all-or-nothing behavior so an update only has to touch the indexes whose columns actually changed, but as of PostgreSQL 18 it remains a design document, not shipped behavior.

InnoDB doesn't have an equivalent problem to solve because it never needed one. The clustered index leaf page is the row storage — updating a non-key column just rewrites bytes in that page, in place, and secondary indexes, which store the indexed columns plus the primary key value rather than a physical location, don't need to change at all unless the update touches a column they actually index. The trade-off shows up somewhere else instead: InnoDB's change buffer. When a secondary index needs a new entry and its page isn't currently in the buffer pool, InnoDB doesn't do a synchronous disk read just to insert one record — it stashes the change in the change buffer and merges it into the real index page later, during a background operation or the next time that page happens to be read into memory anyway. This exists specifically because secondary index inserts on a busy table would otherwise generate random I/O proportional to insert volume, and unique secondary indexes can't use it at all because uniqueness has to be checked against the real page immediately.

Locking: PostgreSQL barely locks; InnoDB locks by default and gap-locks on top of that

PostgreSQL's MVCC model is designed so that readers and writers structurally cannot block each other — a SELECT never waits on an UPDATE, because it's reading an older, still-consistent snapshot rather than contending for the current row. The PostgreSQL documentation states this as a guarantee, not an optimization: "reading never blocks writing and writing never blocks reading," maintained even at the strictest isolation level through Serializable Snapshot Isolation. Two transactions writing the same row still block each other — MVCC solves reader/writer contention, not writer/writer contention — but the common case of one process reading while another writes elsewhere in the table never queues.

InnoDB reads under its default REPEATABLE READ isolation are also non-locking consistent reads, served from the undo-log-reconstructed snapshot exactly like Postgres. Where InnoDB diverges sharply is what happens on writes, and specifically on range operations, because of a mechanism called next-key locking. MySQL's own reference manual defines it precisely: a next-key lock is a record lock on an index entry combined with a gap lock on the space immediately preceding that entry. The purpose is preventing phantom reads — stopping a second transaction from inserting a new row into a range another transaction has already scanned under REPEATABLE READ. The mechanism is triggered specifically by range scans, index scans on non-unique indexes, and locking reads (SELECT ... FOR UPDATE), and MySQL's documentation is direct about the effect: if one transaction holds a shared or exclusive lock on record R, another transaction cannot insert a new record into the gap immediately preceding R in index order — not just modify R, but insert anywhere in the gap before it.

This has a concrete operational bite that shows up constantly in high-throughput InnoDB deployments: a SELECT ... FOR UPDATE or a ranged UPDATE/DELETE doesn't just lock the rows it touches. It locks the gaps around them too, which means concurrent inserts into that key range block even though they're not touching any row the first transaction actually cares about. A batch job doing DELETE FROM orders WHERE created_at < '2024-01-01' under REPEATABLE READ will next-key lock across that entire range, and a concurrent insert of a new order with an old backdated timestamp — rare, but not impossible — queues behind it. Engineers running InnoDB at scale learn to either drop to READ COMMITTED, where gap locking is largely disabled for non-foreign-key checks, or explicitly design around it, because next-key locking is the default, not an edge case.

PostgreSQL has no equivalent gap-locking concept because it doesn't need one — phantom prevention under Serializable falls out of snapshot comparison (Serializable Snapshot Isolation detects the conflict pattern and aborts one transaction rather than blocking either one preemptively), not out of locking empty space between index entries. The trade-off is symmetric, not a free win for Postgres: SSI achieves this by aborting transactions with a serialization failure that the application has to retry, whereas InnoDB's approach blocks rather than aborts. Which failure mode is preferable depends entirely on whether the application layer already has retry logic for transient errors — a lot of application code that assumes MySQL-style blocking behavior will not gracefully handle a Postgres serialization failure the first time it hits one in production.

JSONB and GIN: PostgreSQL indexes into the document; MySQL still indexes around it

PostgreSQL's jsonb type stores JSON in a decomposed binary format specifically so it can be indexed at the key/value level, not just matched as a blob of text. The mechanism is the GIN index — Generalized Inverted Index — and the PostgreSQL documentation's description of its internal structure is the part that actually matters for capacity planning: a GIN index is a B-tree built over keys, where each leaf tuple contains either a small posting list of heap pointers (row IDs where that key occurs) stored directly alongside the key, or, once that list grows past a size threshold, a pointer to a separate posting tree of heap pointers. Each distinct key value is stored exactly once no matter how many rows contain it, which is what makes GIN dramatically more compact than a naive index-every-occurrence approach would be for data where a small number of keys recur across millions of rows — a common shape for JSONB documents sharing a schema.

There are two built-in JSONB operator classes and the choice between them is a real design decision, not a default to accept blindly: jsonb_ops, the default, indexes every key and every value, supporting the containment operator @>, existence operators ?, ?|, ?&, and JSON path queries; jsonb_path_ops indexes only @> and JSON path operators but produces smaller indexes with better performance for the queries it does support, according to PostgreSQL's own documentation, because it hashes the entire path-to-value sequence into a single index entry rather than indexing keys and values separately. A schema doing nothing but containment queries against JSONB columns — WHERE metadata @> '{"status": "active"}' — should default to jsonb_path_ops and only fall back to the full operator class if it later needs key-existence checks.

GIN's write-side cost is real and documented plainly: inserting or updating one row can require inserting into the index once per extracted key, which is inherently slower than a single B-tree insert on a scalar column. PostgreSQL mitigates this with fastupdate, which routes new entries into an unsorted pending list rather than the main structure and defers the expensive part to autovacuum, autoanalyze, or whenever that pending list crosses gin_pending_list_limit — but the tips section of the GIN documentation is candid that a sufficiently large bulk load is still faster done by dropping the index and rebuilding it afterward than by inserting through it live.

MySQL added a native JSON type in 5.7 and it validates and stores JSON in an optimized binary form, but it does not have an equivalent inverted-index structure over document contents. Indexing into a JSON document in MySQL means creating a generated column that extracts a specific path with JSON_EXTRACT or the ->> operator, then putting a conventional B+ tree index on that generated column — which works, and works well, for a known, fixed set of paths queried predictably, but doesn't give a general containment or full-document search capability the way GIN does. A workload that needs to query arbitrary keys inside variably-shaped JSON documents at index speed is a workload GIN was purpose-built for and MySQL's generated-column approach only partially covers.

Heap-organized tables versus clustered indexes: the other half of the storage story

The comparison so far has centered on concurrency, but the physical table layout underneath both engines drives a second, independent set of trade-offs, and it's the one that decides how a primary key lookup and a secondary index lookup actually cost out.

PostgreSQL tables are heap-organized. Rows live in heap pages in no particular order related to any index, and every index — including the primary key — is a separate B-tree structure whose leaf entries point to a heap location via a tuple ID (the page number and slot on that page). A primary key lookup in PostgreSQL is therefore always a two-step operation: traverse the B-tree to find the TID, then fetch the actual page to read the row. There's no way to avoid that second hop for the primary key specifically; it costs the same as any other index.

InnoDB tables are index-organized around the primary key: the clustered index's leaf pages are the row data, stored in primary key order. MySQL's own manual is unambiguous about this and about the consequence: "accessing a row through the clustered index is fast because the index search leads directly to the page that contains the row data," compared to storage organizations — meaning PostgreSQL's — that keep an index separate from the rows it points to. A primary key lookup in InnoDB is one B-tree traversal, full stop, with the row sitting right there in the leaf.

The cost shows up on the other end: InnoDB secondary indexes don't store a page location, they store the primary key value. Every secondary index lookup in InnoDB requires a second traversal — of the clustered index, using the primary key it just retrieved — to get the actual row, unless every column the query needs is already present in the secondary index itself (a "covering index," which skips the second lookup entirely). This has a specific, well-documented design consequence that MySQL's manual flags directly: "if the primary key is long, the secondary indexes use more space," because every secondary index leaf entry carries a full copy of the primary key. A table with a wide, multi-column, or UUID-based primary key doesn't just cost more space in the clustered index — it inflates every single secondary index on that table, which is why InnoDB schemas favor short, monotonic primary keys (an auto-increment integer being the canonical choice) far more aggressively than PostgreSQL schemas need to, since PostgreSQL's TID-based secondary indexes don't carry that penalty.

Clustering by primary key also means InnoDB page splits behave differently under load than PostgreSQL's heap inserts do. InnoDB reserves roughly 1/16 of each new index page for future inserts specifically to delay splits, but a table with a genuinely random primary key — a UUID being the classic offender — forces inserts into random locations across the clustered index rather than appending at the end, which triggers page splits constantly and fragments the clustered index over time in a way sequential keys don't. PostgreSQL heap inserts, by contrast, just go into whatever page has free space per the free space map, with no ordering constraint to violate, though this comes at the cost described earlier — the heap has no intrinsic locality benefit for range scans on the primary key the way InnoDB's clustered order provides for free.

What each architecture actually breaks under at high scale

None of the above is abstract once a table is large enough that these behaviors compound instead of staying background noise.

PostgreSQL's failure mode at scale is bloat and the operational discipline required to prevent it. Dead tuples don't shrink the table file — VACUUM marks their space reusable, it doesn't return it to the OS — and if autovacuum falls behind a write-heavy table (undersized autovacuum_vacuum_scale_factor, long-running transactions holding back the oldest visible snapshot, or a table simply growing faster than the vacuum workers assigned to it), the table and every index on it bloats well past its live data size, degrading every sequential scan and defeating index-only scans that depend on the visibility map. PostgreSQL 17 specifically targeted this: its release notes cite work to let vacuum "more efficiently remove and freeze tuples," a direct acknowledgment that vacuum throughput at scale had been a known bottleneck worth engineering effort in a major release as recent as September 2024. The other hard limit is transaction ID wraparound — every transaction consumes an XID, XIDs are finite, and a table whose oldest unfrozen tuple isn't dealt with in time faces PostgreSQL forcing an aggressive, blocking vacuum to avoid data loss. This is avoidable with normal autovacuum tuning, but it's a failure mode with no InnoDB equivalent, because InnoDB's transaction IDs and undo-based history don't create the same freezing requirement.

InnoDB's failure mode at scale is different in character: long-running transactions don't bloat the primary table, they bloat the undo log, since old row versions have to stay in undo segments until the oldest active read view no longer needs them — visible operationally as a growing history list length, and it can degrade performance well before it becomes a correctness issue. The other classic InnoDB scale problem is exactly the gap-locking behavior described earlier: as concurrent write volume rises, next-key locks on range operations create contention that has no PostgreSQL analog, because PostgreSQL simply doesn't lock gaps between rows. A migration from InnoDB to Postgres often "fixes" contention that was really a REPEATABLE READ/next-key-locking artifact and gets misattributed to Postgres being faster in general, when the honest read is narrower: Postgres removed a specific lock type that the workload was tripping over, and the same workload rewritten to use READ COMMITTED on InnoDB would likely have shown a similar improvement.

Choosing between them on the basis of "which one performs better" is asking the wrong question — both are extremely fast relational engines that have run enormous production workloads for two decades. The right question is which failure mode the operations team is better equipped to watch for and tune around: vacuum scheduling and bloat monitoring, or undo log growth and lock-wait contention under REPEATABLE READ. That's an infrastructure and team-capability decision, not a benchmark result, and it's usually decided the first time either failure mode actually happens in production, whichever system that turns out to be.