The choice your database makes you face
When you design a table, the primary key is the first decision you cannot easily undo. Auto incrementing integer (1, 2, 3...) or random UUID (550e8400-e29b-41d4-a716-446655440000)?
Most tutorials default to the integer because it has been around longer. Most modern startups default to the UUID because it sounds safer. Both defaults are wrong half the time. The actual answer depends on three real questions: how big does your data set get, who sees the IDs, and do you ever combine data from multiple databases.
At a glance
| Feature | Auto increment integer | UUID (v4) |
|---|---|---|
| Predictability | Sequential, easy to guess | Random, opaque |
| Collision risk | None within one DB | Effectively zero, anywhere |
| Index size | 4 or 8 bytes, tight B-tree | 16 bytes, fragmented inserts |
| URL appearance | /users/4523 | /users/550e8400-e29b-... |
| When to pick | Single DB, internal IDs | Distributed writes, public URLs |
The case for the integer
Auto incrementing integers are small (4 or 8 bytes), sort naturally, and play perfectly with B-tree indexes. When you insert row 1001, it goes right after row 1000. The tree barely moves. Performance stays consistent for billions of rows.
Integers are also human readable. “User 4523” is easy to talk about. “User 550e8400-e29b-41d4-a716-446655440000” is not. Support tickets, log files, debug output all become more readable with integers.
The integer is the right choice for:
- A single application talking to a single database.
- Internal IDs that users never see.
- Lookup tables with bounded size.
- Anywhere you can afford a synchronisation point that hands out the next id.
The integer is the wrong choice when:
- You merge data from two databases (their
1, 2, 3...collide). - The ID is exposed in URLs and you do not want users guessing other users’ IDs.
- You distribute writes across multiple nodes (each node would need its own ID range).
The case for the UUID
UUIDs are unique without coordination. Generate one in any process, on any machine, at any time, and it will not collide with another. That property is what makes them the default for distributed systems and for anything where the ID has to be generated before a database round trip.
UUIDs are also unguessable. If your URL is /orders/8af3-9c2e-..., an attacker cannot try /orders/8af4-9c2e-... and find the next order. Integer IDs in URLs are an information leak (the order count, the rate of new signups, the relative timing of two users registering).
The UUID is the right choice for:
- Distributed systems with writes from multiple sources.
- Public facing IDs in URLs and APIs.
- Cases where you need to generate the ID before the row exists.
- Multi tenant systems where two tenants might collide on a serial counter.
The UUID is the wrong choice when:
- You want fast B-tree inserts on a single node (random UUIDs hurt index performance, see below).
- You want compact storage (UUID is 16 bytes vs 4 or 8 for an integer).
- You want IDs people can talk about (“user 4523” vs “user 550e8400-…”).
The B-tree problem and UUIDv7 fix
Random UUIDs hurt insert performance because B-tree indexes work best when new entries land at the end. A new integer ID always lands at the end. A new random UUID lands somewhere in the middle, and the tree has to rebalance.
In production, this can mean orders of magnitude slower inserts on a heavily used table. The performance gap shows up around the 10 million row mark and keeps growing.
UUIDv7 fixes this by encoding the current timestamp at the start of the UUID. Two v7 UUIDs generated close in time start with similar bytes, so they land near each other in the index. Performance is comparable to integers for typical workloads.
If you are reaching for a UUID in 2026 and your database has UUIDv7 support (PostgreSQL 18+, modern MySQL, modern SQL Server), use v7 by default. Use v4 only when you specifically need full unguessability and can take the index hit. The UUID generator on AldeaCode generates both v4 and v7 in batches, in your browser, no upload.
A practical decision tree
Single backend, single database, internal IDs: integer.
Public facing IDs in URLs, single backend: UUID v7. Sortable by time, hard to guess.
Multi node writes, distributed system: UUID v7 with the node id encoded in the high bits, or ULID. Coordinate generation locally, collision free globally.
Multi tenant where tenants must not see each other’s data: UUID v7 plus a strict access check. The unguessability is defence in depth, not the access control itself.
Numeric IDs you absolutely must keep but want to obfuscate in URLs: keep the integer in the database, expose a hashed slug in URLs, decode at the controller.
Migration is easier than people think
If you started with integers and now need UUIDs (a common growth story), the migration is incremental. Add a UUID column alongside the existing integer. Backfill it for existing rows. Switch new code to use the UUID. Remove the integer column once nothing reads it.
The reverse (going from UUID to integer) is rare and harder, because you have to choose how to map old UUIDs to new integers in a way that preserves any external reference.
When you need to inspect a UUID format (which version, what the timestamp says), the UUID generator, the timestamp converter and the hash generator on AldeaCode handle the common cases. The choice of primary key is one of those decisions that pays off or punishes you for years. Pick deliberately, not by habit.