How Storage Engines Affect Primary Key Performance: MySQL vs PostgreSQL
A simple experiment comparing how MySQL and PostgreSQL handle random vs sequential IDs across 1 million rows.
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.
Table of Contents
- 01. The Big Difference: How They Store Data
- 02. Random vs. Sequential IDs
- 03. The Experiment Setup
- 04. Results
- 05. The Trade-off: Reads vs. Writes
- 06. References
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:
Root & Branch Nodes
↓
Leaf Nodes
↓
[ ID + All Other Column Data ]
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:
Primary Key Index (B+Tree)
└── Leaf Node: UUID Key ──> Pointer to Heap
│
↓
Heap File (Table Pages)
└── Row Data: [ All Column Data ]
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, we used two types of IDs:
- UUIDv4 (Random): Lands in random spots across the index tree:
UUIDv4 (Random): 8f3c... -> 2a1b... -> c79e... -> 19df...
- UUIDv7 (Time-Ordered): Starts with a timestamp, so new IDs always go to the end of the tree in order:
UUIDv7 (Time-Ordered): 2026-08...A -> 2026-08...B -> 2026-08...C
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:
id (UUID) | username (TEXT) | name (TEXT) | age (INT)
4. Results
Here are the numbers after inserting 10,000 rows into a 1M-row table:
MySQL / InnoDB
| ID Type | Average Time | Median | Total Time |
|---|---|---|---|
| UUIDv4 (Random) | 0.7688 ms | 0.6813 ms | 7.71 s |
| UUIDv7 (Ordered) | 0.6378 ms | 0.5980 ms | 6.39 s |
In MySQL, random UUIDv4 was 20.5% slower than ordered UUIDv7.
PostgreSQL
| ID Type | Average Time | Median | Total Time |
|---|---|---|---|
| UUIDv4 (Random) | 0.3937 ms | 0.3685 ms | 3.94 s |
| UUIDv7 (Ordered) | 0.3874 ms | 0.3617 ms | 3.88 s |
In PostgreSQL, random UUIDv4 was only 1.63% slower than ordered UUIDv7.
Side-by-Side Comparison
| ID Type | MySQL / InnoDB | PostgreSQL |
|---|---|---|
| UUIDv4 (Random) | 0.7688 ms | 0.3937 ms |
| UUIDv7 (Ordered) | 0.6378 ms | 0.3874 ms |
| Difference | +20.5% slower | +1.63% slower |
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.