· Tech · 4 min read
What Is the Best Primary Key?
Auto-increment IDs are fast and simple—but are they the best choice for a primary key? A practical breakdown of UUID v4, UUID v7, ULID, cuid2, Nano ID, and when each makes sense.
One of the most important parts of designing tables is how to choose a primary key.
For indexing purposes, using an incrementing number seems like a no-brainer—until it isn’t.
Why? Why not use auto-increment? It’s fast because it’s sequential. It’s automatic—once you set it up, you’re done and can forget about it.
So, the reason for using something other than an auto-incrementing number isn’t obvious, especially since indexing performance is one of the most important factors to consider.
And when managing users or groups (such as companies, departments, or student organizations), the ID represents that entity.
Typically, you manage this data via a UI, and navigating to a page will reveal the ID as part of the URL, like so:
something.com/users/1234
something.com/users?userId=1234One obvious issue is that once you’re logged in, it’s very easy to guess other users’ page URLs. It’s also easy to estimate the scale of the service by looking at user IDs, which competitors could do as well.
If you sign up for a service and get an ID like 4567, you know roughly how many users existed before you. Not all of them may be active or legitimate (some might be internal or test accounts), but you can make a good estimate of the service’s scale. By signing up periodically, you can also estimate the growth rate.
Then, What Should We Use…?
Enter the UUID: Universally Unique Identifier. Simply put, it’s a unique string that doesn’t exist anywhere else.
Because no two UUIDs are the same, the ID is guaranteed to be unique.
This means that even if the ID is generated on your computer, server, or smartphone, it won’t collide.
Many records—such as image filenames or user scores in online games—use this to guarantee uniqueness.
BUT Isn’t It Slow? Like, It’s Random?
Yes, it can be slow—but not always.
When using UUID v4 (yes, there are different versions), “INSERT” operations can get slower as the table grows. For example, in MySQL, you might not notice any slowdown until you have around 5 million records, but after that, performance starts to drop. With PostgreSQL, this threshold is closer to 10 million records. Once you hit those numbers, inserting a new record can take almost a full second in MySQL when the table has about 30 million rows.
For high-traffic, customer-facing services like Facebook or Amazon, that kind of delay isn’t acceptable. But for many use cases, that number is totally fine—especially for B2B or internal systems, or for smaller companies where the scale is much lower.
But I’m Developing My Own Facebook and Don’t Know the Scale Yet!
Fair enough.
So when you just don’t know which one is best, or if you want to take advantage of both worlds, or simply want to cover all your bases, here are the choices:
| name | length | type | size | Pre-allocation | Native Support | RFC Spec | Charset | Sortable | Performance |
|---|---|---|---|---|---|---|---|---|---|
| Auto Increment | 19 digits | BigInt | 8 bytes | Yes | Yes | No | 0-9 | Yes | Excellent |
| UUID v4 | 36 chars | Binary | 16 bytes | Yes | Yes | Yes | 0-9 a-f - | No | Good |
| UUID v7 | 36 chars | Binary | 16 bytes | Yes | Partial(1) | Yes | 0-9 a-f - | Yes | Good |
| ulid | 26 chars | String | 26 bytes | Yes | No | No | 0-9 A-Z (3) | Yes | Good |
| cuid2 | 24 chars(2) | String | 24 bytes | Yes | No | No | 0-9 a-z | No | Fair |
| Nano ID | 21 chars(2) | String | 21 bytes | Yes | No | No | 0-9 a-z A-Z _ - (4) | No | Good |
(1) PostgreSQL 18 and up
(2) Configurable
(3) Excludes ILOU
(4) Used Chars Configurable
Legend
- length: Number of digits or characters
- type: Data type (e.g., BigInt, String, Binary)
- size: Actual byte size
- Pre-allocation: Whether IDs can be generated in advance by the application
- Native Support: Whether the database natively supports it
- RFC Spec: Whether there is an official RFC specification (applies only to UUID)
- Charset: Character set used for the key
- Sortable: Whether IDs are sortable in chronological order
- Performance: Speed of ID generation (Excellent / Good / Fair)
So What Should I Use?
If simple numbers are okay—and in plenty of cases, they are—Auto Increment is a solid choice. If you want a hash string while keeping sortability, UUID v7 (which is relatively new and was developed for this purpose) is likely your go-to. If you want a hash but also care about performance, Nano ID is a solid choice for both speed and shorter length, and it’s fast enough even for runtime use on the frontend.
There’s also an alternative, less optimized but easier-to-understand approach: simply use Auto Increment as your primary key, and add an alternative ID column that uses UUID v4 or Nano ID, enforcing a unique index. This way, you get performance for indexing and inserting, and you have a safe, unguessable URL. However, some people might not like having two separate de facto primary keys.
There isn’t a one-size-fits-all approach, and you have options.
This article references these two awesome Japanese tech YouTube channels—check them out if you understand the language or can translate!
