Postgres vs MySQL: Deep Dive into Indexes, Query Costs, and MVCC Differences

Index Fundamentals

Both databases use B+‑tree indexes to accelerate lookups, storing keys in leaf nodes and caching pages in a shared buffer. In MySQL, the primary‑key index’s value is the entire row (clustered index), while secondary indexes store a pointer to the primary key. In PostgreSQL, all indexes are technically secondary and point to a tuple ID (tid) that references the heap page where the row resides.

MySQL Index Details

The primary‑key index stores the full row, enabling direct retrieval without extra I/O. Secondary indexes store the primary‑key value as their leaf‑node payload, requiring a second lookup to fetch the row data.

PostgreSQL Index Details

PostgreSQL does not distinguish primary‑key indexes; all indexes point to a tuple ID. The heap stores rows unordered, so updates create new tuple versions, and the original tuple IDs remain for MVCC. Locating a row therefore requires an extra heap page read after the index lookup.

Query Cost Comparison

Given a simple table T with a primary key PK, a secondary indexed column C2, and an unindexed column C1, the query SELECT * FROM T WHERE C2 = 'x2'; triggers two B+‑tree lookups in MySQL (secondary index → PK → row) but only one index lookup plus a heap fetch in PostgreSQL.

When C2 is non‑unique and many rows match, PostgreSQL can reduce random I/O by using a bitmap index scan: it aggregates matching tuple IDs by page, loads each page once, and filters rows in memory.

For range scans on the primary key ( SELECT * FROM T WHERE PK BETWEEN 1 AND 3;), MySQL excels because it can traverse the clustered index leaf chain and read rows sequentially, whereas PostgreSQL must fetch each tuple from the heap after the secondary index traversal, incurring extra random reads.

Impact of Data Types

In MySQL, the primary‑key type directly affects secondary‑index size because each secondary entry stores the full primary‑key value (e.g., a UUID makes secondary indexes bulky). PostgreSQL’s secondary indexes always store a fixed‑size 4‑byte tuple ID, so primary‑key type has little impact on index size.

Undo Logs and MVCC

Both systems implement MVCC, but MySQL relies on undo logs to reconstruct previous row versions for concurrent transactions. PostgreSQL instead creates a new tuple version for every update, recording the creating and deleting transaction IDs; old versions become invisible to later transactions and are later reclaimed by the VACUUM process.

PostgreSQL also features HOT (Heap‑Only Tuple) optimization, which can avoid updating secondary indexes when the updated columns are not indexed, by linking the new tuple to the old one within the same heap page.

Process vs Thread Model

MySQL uses a multi‑threaded architecture, sharing the same address space among connections, while PostgreSQL spawns a separate process per connection. Threads are lighter weight, but processes provide stronger isolation at the cost of higher resource overhead.