MySQL/InnoDB against PostgreSQL: clustered keys, gap locks, DDL commits and undo logs

Same SQL, different engine: InnoDB keeps rows in key order, locks the gaps it scans, commits on DDL and rebuilds old versions from undo logs.

Before you read: predict

ExamHall's grading service keeps results (id, candidate_id, score, needs_review) with an index on score, and the same table exists on a MySQL 8.4 server and on a PostgreSQL 17 server, each server at its default settings. The same two sessions run on each. Session A: BEGIN; UPDATE results SET needs_review = true WHERE score BETWEEN 50 AND 60; and stays open. Session B, a candidate finishing: INSERT INTO results (candidate_id, score) VALUES (77, 55);. No row has score 55.

On which server does B's INSERT wait for A to end?

The 30-second version

InnoDB stores each table inside its primary key's B-tree, and its other indexes end their lookups there. Its default level is REPEATABLE READ, where locking statements also lock the gaps between the index entries they scan, so inserts there wait; Postgres locks only rows. DDL commits the open transaction. Old versions live in undo logs, cleared by purge.

Same SQL, different engine: InnoDB keeps rows in key order, locks the gaps it scans, commits on DDL and rebuilds old versions from undo logs.

How it works

Step 1 of 14

The table is an index. InnoDB stores each row in the leaf pages of its primary key's B-tree, the clustered index, in key order.

In the kitchen

each patient's folder stands on the shelves in hospital-number order → the clustered index (step 1)

Analogy: A hospital's medical records library

In the records library	In InnoDB
each patient's folder stands on the shelves in hospital-number order	the clustered index (step 1)
clinic lists by surname are kept apart from the shelves, and every look-up in one ends at the main shelves	a secondary index leading to the clustered index (step 3)
while a clerk audits a run of numbers, no new folder may be filed in that stretch, even under a number nobody holds yet	a gap lock (step 8)
a correction is written into the folder itself; the old wording goes into the amendments book	an in-place update and its undo log (step 13)
a back-office clerk shreds amendment pages; no separate clean-up crew sweeps the shelves	purge (step 14)
relabelling the shelves is an order of its own, not one more filing in the clerk's batch	DDL in a transaction (step 12)

In real terms: the same statements land on different structures, so the same isolation-level name gives different waits and different leftovers.

Breaks down when: a clerk can see that no folder will ever be filed under a number; InnoDB locks the whole gap whatever values might arrive, and keeps the lock until the transaction ends.

Contrasting case: a records store that shelves each folder wherever there is room: that is the Postgres heap (step 5), where an audit holds folders, never a stretch of empty shelf.

Examples, run for real

4.1 ExamHall EXH-4660 → MR !4960: the review sweep that stalled new results (cited, MySQL 8.4 manual; no MySQL server runs)

ExamHall's legacy grading service runs on MySQL 8.4 with server defaults, and results has an index on score. A review sweep ran in one long transaction (EXH-4660):

START TRANSACTION;
UPDATE results SET needs_review = true WHERE score BETWEEN 45 AND 65;
-- … the sweep then exported the flagged rows, one by one, before COMMIT

At REPEATABLE READ the UPDATE took next-key locks on the score entries from 45 to 65 and the gaps between them, so every new result scored in that stretch waited until COMMIT (steps 7–9). MR !4960 reads the ids first with a plain SELECT, which locks nothing (step 6), then updates each row by its primary key in short transactions.

4.2 Proof domain, payments ledger: Postgres rolls DDL back

A ledger migration creates a currency table, fills it, adds a column to entries, and is rolled back, on PostgreSQL 18.3 in PGlite:

4.2 PostgreSQL 18.3 (PGlite 0.5.8) · BEGIN ok · CREATE TABLE fx_rates ok · INSERT INTO fx_rates ok · ALTER TABLE entries ADD COLUMN currency text REFERENCES fx_rates ok · ROLLBACK ok
4.2 afterwards: fx_rates exists false · entries.currency exists false · entries rows 2 (as before)

Nothing of it is left: Postgres keeps DDL inside the transaction. MySQL does not (step 12).

Try it

Answer first, then check. Nothing is saved.

Question 1 of 7

A payments ledger keeps entries (id, account_id, amount_cents) with a secondary index on account_id, on a MySQL 8.4 server and on a PostgreSQL 17 server. Its primary key id is a 16-byte UUID on both (BINARY(16) on MySQL, uuid on Postgres). The team moves id to an 8-byte BIGINT on both, with the same rows. The MySQL half is this lesson's model of the manual's entry layout.

On which server does the account_id index get smaller?

Question 2 of 7

ExamHall's migration runner sends this script in one session to its MySQL 8.4 server (server defaults, autocommit on):

START TRANSACTION;
INSERT INTO grade_bands (name, min_score) VALUES ('merit', 70);
ALTER TABLE grade_bands ADD COLUMN max_score INT NULL;
INSERT INTO grade_bands (name, min_score, max_score) VALUES ('pass', 50, 69);
ROLLBACK;

Which of its changes are still there after the ROLLBACK?

Question 3 of 7

A payments ledger runs on MySQL 8.4 at its default isolation level. At 09:00 a support analyst runs SET autocommit = 0; and then SELECT sum(amount_cents) FROM entries WHERE account_id = 42;, and leaves for the day without a COMMIT. Until evening the settlement job commits 2 million UPDATEs to entries.

What builds up because the analyst's session is still open?

Question 4 of 7

The same schedule runs on a MySQL 8.4 server and on a PostgreSQL 17 server, both sessions at REPEATABLE READ. Account 7's balance is 100. (1) A and B each begin a transaction and run SELECT balance FROM accounts WHERE id = 7; both read 100. (2) A runs UPDATE accounts SET balance = balance + 50 WHERE id = 7 and commits. (3) B runs UPDATE accounts SET balance = balance - 30 WHERE id = 7, then COMMIT.

What is account 7's balance afterwards on each server?

Question 5 of 7

Write three sentences answering an interviewer for ExamHall's backend team: what is a clustered index, and why does a random UUID primary key hurt InnoDB more than Postgres?

Your answer (optional, stays on this page)

Question 6 of 7

A payments ledger on MySQL 8.4, server defaults, keeps entries (id, account_id, booked_on, reconciled) with a non-unique index on booked_on. Each night a job runs UPDATE entries SET reconciled = 1 WHERE booked_on BETWEEN '2026-10-01' AND '2026-10-15' inside a transaction that stays open about four minutes, and card refunds dated inside that range, arriving as new rows meanwhile, wait for it.

Which one change lets those inserts go in without waiting, with the job still one transaction?

Question 7 of 7

ExamHall's legacy audit_events table on MySQL 8.4 was created with no PRIMARY KEY. It has two UNIQUE indexes, created in this order: ux_event_uuid on event_uuid, a nullable column, then ux_seq on seq, a column declared NOT NULL.

In what order does InnoDB keep the table's rows?

Go deeper

Pro tip

When an interviewer asks how MySQL and Postgres differ, answer with mechanisms, not settings: name the clustered index against the heap, gap locks against snapshots, DDL that commits against DDL that rolls back, and undo logs against row versions, then say which one the question's symptom comes from. When: a "have you used MySQL?" or "why would this behave differently on MySQL?" question. Status: SELF; not run.

Source

Valid as of 2026-10-16 (MySQL 8.4 LTS). The row names the first two; the others are pages of the same manual, which the brief's lane names as the source ("cited only from the MySQL 8.4 manual"), recorded in this lesson's CL row.

MySQL 8.4 Reference Manual, 17.7.1 InnoDB Locking (named in the row): https://dev.mysql.com/doc/refman/8.4/en/innodb-locking.html · mysql-innodb-locking.txt. "prevents other transactions from inserting a value of 15 into column t.c1, whether or not there was already any such value in the column"; "Gap locks in InnoDB are “purely inhibitive”, which means that their only purpose is to prevent other transactions from inserting to the gap."; "A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record." Supports steps 8, 9, 11.
MySQL 8.4 Reference Manual, 17.6.2.1 Clustered and Secondary Indexes (named in the row): https://dev.mysql.com/doc/refman/8.4/en/innodb-index-types.html · mysql-innodb-index-types.txt. "Each InnoDB table has a special index called the clustered index that stores row data." Supports steps 1–3; its other two quotations are in the assessment note below.
MySQL 8.4 Reference Manual, 17.6.2.2 The Physical Structure of an InnoDB Index: https://dev.mysql.com/doc/refman/8.4/en/innodb-physical-structure.html · mysql-physical-structure.txt. "If records are inserted in a random order, the pages are from 1/2 to 15/16 full." Supports step 4.
MySQL 8.4 Reference Manual, 17.7.2.1 Transaction Isolation Levels: https://dev.mysql.com/doc/refman/8.4/en/innodb-transaction-isolation-levels.html · mysql-isolation-levels.txt. "The default isolation level for InnoDB is REPEATABLE READ."; "For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range." Supports steps 6, 7, 9, 10; its third quotation is in the assessment note below.
MySQL 8.4 Reference Manual, 17.7.2.3 Consistent Nonlocking Reads: https://dev.mysql.com/doc/refman/8.4/en/innodb-consistent-read.html · mysql-consistent-read.txt. Supports steps 6, 7; its quotation is in the assessment note below.
MySQL 8.4 Reference Manual, 17.7.3 Locks Set by Different SQL Statements in InnoDB: https://dev.mysql.com/doc/refman/8.4/en/innodb-locks-set.html · mysql-locks-set.txt. Supports step 9; its quotation is in the assessment note below.
MySQL 8.4 Reference Manual, 15.3.3 Statements That Cause an Implicit Commit, 15.1.1 Atomic Data Definition Statement Support and 15.3.1 START TRANSACTION, COMMIT, and ROLLBACK Statements: https://dev.mysql.com/doc/refman/8.4/en/implicit-commit.html, https://dev.mysql.com/doc/refman/8.4/en/atomic-ddl.html, https://dev.mysql.com/doc/refman/8.4/en/commit.html · mysql-implicit-commit.txt, mysql-atomic-ddl.txt, mysql-commit.txt. Supports step 12; their quotations are in the assessment note below.
MySQL 8.4 Reference Manual, 17.3 InnoDB Multi-Versioning and 17.8.9 Purge Configuration: https://dev.mysql.com/doc/refman/8.4/en/innodb-multi-versioning.html, https://dev.mysql.com/doc/refman/8.4/en/innodb-purge-configuration.html · mysql-multi-versioning.txt, mysql-purge.txt. "Records in a clustered index are updated in-place, and their hidden system columns point undo log entries from which earlier versions of records can be reconstructed."; "Purge operations are performed in the background by one or more purge threads." Supports steps 13, 14.
PostgreSQL 17 and 18 manuals, 13.3 Explicit Locking, 13.2 Transaction Isolation, 11.9 Index-Only Scans and Covering Indexes, CLUSTER: https://www.postgresql.org/docs/17/explicit-locking.html, …/transaction-iso.html, …/indexes-index-only-scans.html, …/sql-cluster.html (and the same pages under /docs/18/) · pg17-*.txt, pg18-*.txt. "Row-level locks do not affect data querying; they block only writers and lockers to the same row."; "All indexes in PostgreSQL are secondary indexes, meaning that each index is stored separately from the table's main data area"; "Clustering is a one-time operation: when the table is subsequently updated, the changes are not clustered." Each quote is checked verbatim in both versions (quote-check.mjs). Supports steps 5 and 11.

Checked against the tools and docs as of 2026-10-16.

Predict first