How Storage Engines Affect Primary Key Performance: MySQL vs PostgreSQL

When picking an ID for database rows, developers often debate between auto-incrementing numbers, random IDs (UUIDv4), or time-based IDs (UUIDv7).

Most people think the bottleneck is just the ID format. But the real difference comes down to how your database actually stores data on disk.

I ran a simple experiment comparing MySQL and PostgreSQL to see how their storage engines react when inserting random vs sequential IDs into a table with 1 million rows.

1. The Big Difference: How They Store Data

MySQL and PostgreSQL store table data in two completely different ways.

MySQL (InnoDB): Index-Organized Table

In MySQL, the table is the primary key index.

The actual row data lives directly inside the leaf nodes of the B+Tree:

flowchart TB
  accTitle: MySQL - one tree, complete rows
  accDescr: Each leaf page contains both the primary key and the full row. The row is already there when the index lookup ends.
  subgraph tree["PRIMARY-KEY INDEX"]
    root["Root page"] --> branch["Branch page"]
    branch --> left["Leaf page<br/>Key + full row"]
    branch --> selected["Leaf page<br/>Key + full row"]
    branch --> right["Leaf page<br/>Key + full row"]
  end
  classDef routing fill:#1b1b1b,stroke:#525252,color:#d6d6d6
  classDef muted fill:#121212,stroke:#343434,color:#a3a3a3
  classDef rowData fill:#182b24,stroke:#6c9a84,color:#e0f0e7,stroke-width:1.5px
  class root,branch routing
  class left,right muted
  class selected rowData
  linkStyle 2 stroke:#82b49b,stroke-width:2px

Because row data is stored right inside the index, inserting a random ID forces MySQL to find a specific spot somewhere in the tree. If that page is full, MySQL has to split the page and move existing data around.

PostgreSQL: Heap Storage + Separate Index

PostgreSQL takes a different approach. It puts row data in a simple file called a heap, and keeps indexes separate:

flowchart TB
  accTitle: PostgreSQL - index and heap
  accDescr: The index stores a key and row pointer. Reading the full row requires following that pointer to a separate heap page.
  subgraph index["PRIMARY-KEY INDEX"]
    root["Root page"] --> entry["Leaf entry<br/>UUID + row pointer"]
  end
  subgraph heap["SEPARATE HEAP STORAGE"]
    row[("Heap page<br/>Full row data")]
  end
  entry -->|"follow row pointer"| row
  classDef routing fill:#1b1b1b,stroke:#525252,color:#d6d6d6
  classDef rowData fill:#182b24,stroke:#6c9a84,color:#e0f0e7,stroke-width:1.5px
  class root,entry routing
  class row rowData
  linkStyle 1 stroke:#82b49b,stroke-width:2px

The index only holds the ID and a small pointer to where the row lives in the heap.

2. Random vs. Sequential IDs

To test this, I used two types of IDs:

1. UUIDv4 (Random): Lands in random spots across the index tree:

flowchart TB
  accTitle: UUIDv4 - scattered writes
  accDescr: Random keys target different leaf pages. These illustrative inserts arrive in order 1, 2, 3 but land on pages B, A, C.
  incoming["Random UUIDv4 inserts"]
  incoming -->|"2nd: 2a1b..."| a["Page A"]
  incoming -->|"1st: 8f3c..."| b["Page B"]
  incoming -->|"3rd: c79e..."| c["Page C"]
  classDef source fill:#1b1b1b,stroke:#525252,color:#dedede
  classDef scattered fill:#2c1c1c,stroke:#a86f6f,color:#f0d6d6,stroke-width:1.5px
  class incoming source
  class a,b,c scattered
  linkStyle 0,1,2 stroke:#b88080,stroke-width:1.5px,stroke-dasharray:5 4

2. UUIDv7 (Time-Ordered): Starts with a timestamp, so new IDs always go to the end of the tree in order:

flowchart LR
  accTitle: UUIDv7 - writes at the edge
  accDescr: Time-ordered inserts tend to reach the rightmost leaf page instead of scattering across older pages. This is a simplified view of the index.
  incoming["New time-ordered UUIDv7"]
  subgraph pages["LEAF PAGES IN KEY ORDER"]
    direction LR
    older["Older keys"] ~~~ recent["Recent keys"] ~~~ edge["Rightmost page<br/>New insert"]
  end
  incoming -->|"append at the edge"| edge
  classDef source fill:#1b1b1b,stroke:#525252,color:#dedede
  classDef older fill:#121212,stroke:#343434,color:#999999
  classDef active fill:#182b24,stroke:#6c9a84,color:#e0f0e7,stroke-width:1.5px
  class incoming source
  class older,recent older
  class edge active
  linkStyle 2 stroke:#82b49b,stroke-width:2px

3. The Experiment Setup

The setup was kept simple and identical for both databases:

• MySQL 8.4 and PostgreSQL 17 running in Docker
• 1,000,000 existing rows in each table before testing
• 10,000 new inserts per test
• All 10,000 inserts sent in one single transaction
• High-resolution timer measuring individual insert times

The table structure:

4. Results

Here are the numbers after inserting 10,000 rows into a 1M-row table:

MySQL / InnoDB

In MySQL, random UUIDv4 was 20.5% slower than ordered UUIDv7.

PostgreSQL

In PostgreSQL, random UUIDv4 was only 1.63% slower than ordered UUIDv7.

Side-by-Side Comparison

5. The Trade-off: Reads vs. Writes

Why did MySQL slow down by 20% while PostgreSQL barely noticed?

• MySQL: Because full rows live directly in the index, inserting random keys forces MySQL to split full pages and move heavy row data around. The benefit comes during reads: looking up a row by primary key is very fast with zero extra lookups because the data is already right there.

• PostgreSQL: When inserting rows, Postgres just appends data to the end of the heap file, so random IDs only touch the small index. But during reads, Postgres has to first check the index, then do an extra disk lookup to fetch the actual row from the heap.

Neither design is "better" or "worse". They just make different trade-offs:

• MySQL optimizes for faster primary key reads at the cost of slower random writes.
• PostgreSQL optimizes for faster, flexible writes at the cost of an extra lookup during reads.

If you are generating distributed IDs on a clustered B+Tree database like MySQL/InnoDB, switching from UUIDv4 to time-ordered UUIDv7 gives you the best of both worlds: global uniqueness with fast sequential write throughput.

6. References

1. Database Internals: A Deep Dive into How Distributed Data Systems Work by Alex Petrov