Avoid UUID Version 4 for primary keys

Andrew Atkinson’s excellent post on avoid UUID Version 4 primary keys perfectly articulates what I’ve been telling my clients for a while now. As he puts it:

“UUIDs are 16 bytes (128 bits) per value, which is double the space of bigint (8 bytes), or quadruple the space of 4-byte integers. This extra space adds up once many tables have millions of rows, and copies of a database are being moved around as backups and restores.”

Wherever possible, just use integer IDs. If you NEED to use UUIDs, use UUID v7. While UUID v4 is purely random, it causes database index fragmentation, page splits and slow writes.

UUID v7 adds a millisecond-precision Unix timestamp to the front, making it time-ordered. This dramatically improves database performance—faster inserts, smaller indexes, better locality—by acting like sequential IDs, making it ideal for primary keys in high-write systems. However, this does expose timestamp information, which may be a security concern.

My strategy: obfuscated integer IDs

In applications where we don’t want database IDs exposed in APIs and URLs, encrypting the integer primary key and using those encrypted bytes provides an elegant solution. By encrypting database IDs, we get the best of both worlds: the performance benefits of integer keys internally, and opaque, non-guessable identifiers externally.

The key advantage is that when someone attempts to scan your API with invalid identifiers, the decryption fails immediately without requiring a database round trip, making malicious scanning attempts obvious and easy to detect.