Random UUIDs are killing your database performance
You switched from integer IDs (1, 2, 3…) to UUIDs (a1b2-3c4d-…) for security or distributed generation.
Then your database writes get slower, sometimes much slower.
Here’s why:
Index Fragmentation.
Most database indexes are B-Trees (balanced, sorted trees). The physical location of your data matters.
1./ 𝐒𝐞𝐪𝐮𝐞𝐧𝐭𝐢𝐚𝐥 𝐈𝐃𝐬
When you insert sequential integers (1, 2, 3), new data always goes to the rightmost leaf page of the index.
Writes are predictable and sequential.
Cache hits are maximized.
Pages stay 100% full.
This is the speed limit of your database.
2./ 𝐑𝐚𝐧𝐝𝐨𝐦 𝐔𝐔𝐈𝐃𝐯4
UUIDv4 values are uniformly random. This means a new insert can land anywhere in the tree structure.
Because the inserts are scattered:
- The database must constantly load random pages from disk to memory (Random I/O).
- Page Splitting => When a target page is full, the database has to split it in half to make room, leaving you with two half-empty pages.
- 'Swiss Cheese' Effect => Your index becomes larger and full of holes, wasting RAM and disk space.
This can degrade write throughput by 20–90% once your index size exceeds your available RAM.
3./ 𝐔𝐔𝐈𝐃𝐯7
Stop using UUIDv4 for primary keys. Use UUIDv7 (Standardized in RFC 9562).
UUIDv7 embeds a timestamp at the start of the ID, making it sortable.
This gives you the best of both worlds:
- Distributed generation => (No central counter needed).
- Monotonic inserts => They behave like sequential integers in a B-Tree, eliminating fragmentation.
- Security => Prevents trivial ID enumeration (attackers can't guess that user 101 follows user 100), though note that it does reveal the record's creation time.
You get the utility of UUIDs without the performance penalty.
You switched from integer IDs (1, 2, 3…) to UUIDs (a1b2-3c4d-…) for security or distributed generation.
Then your database writes get slower, sometimes much slower.
Here’s why:
Index Fragmentation.
Most database indexes are B-Trees (balanced, sorted trees). The physical location of your data matters.
1./ 𝐒𝐞𝐪𝐮𝐞𝐧𝐭𝐢𝐚𝐥 𝐈𝐃𝐬
When you insert sequential integers (1, 2, 3), new data always goes to the rightmost leaf page of the index.
Writes are predictable and sequential.
Cache hits are maximized.
Pages stay 100% full.
This is the speed limit of your database.
2./ 𝐑𝐚𝐧𝐝𝐨𝐦 𝐔𝐔𝐈𝐃𝐯4
UUIDv4 values are uniformly random. This means a new insert can land anywhere in the tree structure.
Because the inserts are scattered:
- The database must constantly load random pages from disk to memory (Random I/O).
- Page Splitting => When a target page is full, the database has to split it in half to make room, leaving you with two half-empty pages.
- 'Swiss Cheese' Effect => Your index becomes larger and full of holes, wasting RAM and disk space.
This can degrade write throughput by 20–90% once your index size exceeds your available RAM.
3./ 𝐔𝐔𝐈𝐃𝐯7
Stop using UUIDv4 for primary keys. Use UUIDv7 (Standardized in RFC 9562).
UUIDv7 embeds a timestamp at the start of the ID, making it sortable.
This gives you the best of both worlds:
- Distributed generation => (No central counter needed).
- Monotonic inserts => They behave like sequential integers in a B-Tree, eliminating fragmentation.
- Security => Prevents trivial ID enumeration (attackers can't guess that user 101 follows user 100), though note that it does reveal the record's creation time.
You get the utility of UUIDs without the performance penalty.
• • •
Missing some Tweet in this thread? You can try to
force a refresh
