AUTO_INCREMENTworks until you have more than one database. Then you need IDs that are unique across machines, generated without coordination, and — ideally — sortable by time.
Prerequisites: Sharding, Indexing Time to read: ~18 minutes
One database: AUTO_INCREMENT / BIGSERIAL. Unique, sequential, tiny, sorted by creation time. It’s
perfect.
Then you shard, and it breaks completely:
You need IDs generated independently on any machine, guaranteed unique, and fast.
And the requirements are usually stricter than just “unique”:
| Requirement | Why |
|---|---|
| Unique | Obviously |
| Sortable by time | So ORDER BY id works, and for cursor pagination |
| Compact | It’s in every index, every foreign key, every API response |
| No coordination | Generate locally, at millions/second |
| Not guessable | 🚨 /orders/1001 invites someone to try /orders/1002 |
🚨 The last two conflict with each other, and that tension drives the whole design space.
128 bits of randomness. f47ac10b-58cc-4372-a567-0e02b2c3d479.
✅ Zero coordination. Generate anywhere, no shared state, no network call. ✅ Collision probability is negligible (you’d need to generate ~10¹⁸ to have a meaningful chance). ✅ Not guessable.
❌ Not sortable. ORDER BY id is meaningless.
❌ 16 bytes (36 as a string) — twice a bigint, and it’s in every index.
❌ 🚨 Random inserts destroy B-tree performance. This is the important one.
Why random IDs hurt so much: a B-tree index on a sequential key appends to the rightmost leaf, which is always in memory — one hot page, fast writes, dense pages. A random key inserts into a random leaf, so:
📐 Benchmarks routinely show 2–10× slower inserts with random UUIDs versus sequential keys on large tables. In InnoDB it’s worse still, because the primary key is the clustered index — random primary keys mean the table data itself is written randomly, and every secondary index carries a full 16-byte primary key copy.
Use v4 for: low-volume entities, external-facing identifiers, idempotency keys, request IDs, anything where insert throughput doesn’t matter.
Twitter’s design, and the one to reach for in interviews. A 64-bit integer with structure:
1 41 bits 10 bits 12 bits
┌─┬──────────────────────┬──────────────┬──────────────┐
│0│ timestamp (ms) │ machine ID │ sequence │
└─┴──────────────────────┴──────────────┴──────────────┘
sign since epoch 1024 machines 4096 per ms
class Snowflake:
EPOCH = 1735689600000 # your own epoch, e.g. 2025-01-01
def __init__(self, machine_id):
self.machine_id = machine_id # 0..1023
self.sequence = 0
self.last_ts = -1
def next_id(self):
ts = int(time.time() * 1000)
if ts < self.last_ts:
raise ClockMovedBackwards() # see below
if ts == self.last_ts:
self.sequence = (self.sequence + 1) & 4095
if self.sequence == 0: # 4096 exhausted this ms
while ts <= self.last_ts:
ts = int(time.time() * 1000) # spin to the next ms
else:
self.sequence = 0
self.last_ts = ts
return ((ts - self.EPOCH) << 22) | (self.machine_id << 12) | self.sequence
📐 The numbers:
✅ 64 bits — fits a BIGINT, half the size of a UUID.
✅ Roughly time-sortable — because the timestamp is in the high bits, sorting by ID sorts by
creation time. This means ORDER BY id works, cursor pagination works, and B-tree inserts stay near
the right edge.
✅ No coordination at generation time — each machine generates locally.
❌ Requires assigning unique machine IDs.
❌ Guessable — you can infer creation time and approximate volume.
🚨 The two hard parts, and interviewers ask about both:
1. Assigning machine IDs. Options: static configuration (simple, error-prone — two machines with the same ID silently produce duplicates); ZooKeeper/etcd assigning ephemeral sequential IDs (correct, adds a dependency at startup only); or derive from the pod/instance identity (Kubernetes StatefulSet ordinals work well). Getting this wrong produces duplicate IDs that surface much later as mysterious constraint violations.
2. Clock skew and NTP going backwards. If the clock jumps back, you can reissue IDs you already generated. Handling: refuse to generate and raise an error (Twitter’s original behaviour — better to fail loudly than corrupt data); or wait until the clock catches up if the jump is small; or use a monotonic clock source. Never silently continue. → Time & Clocks
Variants worth knowing: Instagram embeds the shard ID in the ID, so a row’s ID tells you which shard it lives on with no lookup. Discord uses Snowflake and derives the time-bucket partition key from it. Sonyflake trades sequence bits for a longer lifespan.
Standardized in 2024 (RFC 9562). A UUID with a millisecond timestamp in the high bits, then randomness.
48 bits 4 12 bits 2 62 bits
┌──────────────┬─────┬────────────┬─────┬───────────┐
│ timestamp ms │ ver │ random │ var │ random │
└──────────────┴─────┴────────────┴─────┴───────────┘
✅ Time-sortable, like Snowflake — so B-tree inserts stay sequential and ORDER BY id works.
✅ No machine ID to assign, no coordination at all — the rest is random.
✅ Standard UUID format, so every library and database type supports it.
❌ Still 128 bits (16 bytes).
❌ Leaks creation time.
🎙️ This is increasingly the right default, and knowing it is a genuine 2026 signal: “I’d use UUID v7 rather than v4. It’s time-ordered, so we keep sequential B-tree inserts and cursor pagination, without needing to assign machine IDs the way Snowflake does. The cost is 16 bytes instead of 8.”
Related predecessors: ULID (same idea, base32-encoded, 26 characters, lexicographically sortable as a string) and KSUID. UUID v7 has largely won because it’s a standard.
One database whose only job is issuing IDs.
REPLACE INTO tickets (stub) VALUES ('a');
SELECT LAST_INSERT_ID();
✅ Simple, sequential, no gaps. ❌ Single point of failure, network round trip per ID, and a throughput ceiling.
Flickr’s fix — two servers with offset sequences:
Server A: 1, 3, 5, 7, ... (odd, auto_increment_increment=2, offset=1)
Server B: 2, 4, 6, 8, ... (even, auto_increment_increment=2, offset=2)
Redundant, still sequential-ish. Clever, and worth knowing as a historical answer, but it doesn’t scale past a handful of servers.
The practical compromise: each server requests a range of IDs, then serves them locally.
App server 1 → allocator: "give me a block" → 1..1000
App server 2 → allocator: "give me a block" → 1001..2000
Each server then hands out IDs from its block with zero network calls, requesting a new block when it runs low (asynchronously, before exhaustion).
✅ Small, sequential-ish IDs with a network call only once per 1,000 (or 10,000) IDs. ✅ Central allocator load is reduced by the block size. ❌ Gaps when a server restarts with an unused block. (Usually irrelevant — but if your finance team requires gapless invoice numbers, this matters, and that’s a genuine real-world constraint.) ❌ IDs are only roughly time-ordered — server 2’s block 1001–2000 might be used before server 1 finishes 1..1000.
This is what many ORMs’ “hi-lo” generators do, and it’s a good answer when you want compact integer IDs at scale without Snowflake’s machine-ID management.
| Size | Sortable | Coordination | Guessable | Insert perf | |
|---|---|---|---|---|---|
| Auto-increment | 8 B | ✅ | Single DB | ✅ Yes | ⭐⭐⭐⭐⭐ |
| UUID v4 | 16 B | ❌ | None | ❌ No | ⭐ |
| UUID v7 | 16 B | ✅ | None | Partially | ⭐⭐⭐⭐ |
| Snowflake | 8 B | ✅ | Machine ID assignment | Partially | ⭐⭐⭐⭐⭐ |
| Ticket server | 8 B | ✅ | Central service | ✅ Yes | ⭐⭐⭐⭐⭐ |
| Batched blocks | 8 B | Roughly | Occasional | ✅ Yes | ⭐⭐⭐⭐ |
🚨 A pattern worth knowing, and a strong thing to propose unprompted.
The requirements conflict: you want small sequential IDs internally (for index performance and joins) and unguessable IDs externally (so customers can’t enumerate your orders).
Use both:
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY, -- internal: joins, indexes, foreign keys
public_id UUID UNIQUE DEFAULT gen_random_uuid(), -- external: URLs, APIs
...
);
Internally everything joins on the small sequential id. Externally, /orders/f47ac10b-... reveals
nothing about volume or other customers’ data.
🚨 The enumeration attack this prevents is real. Sequential IDs in URLs leak your growth rate (sign up on two days, subtract the IDs — that’s your competitor’s daily signups) and invite IDOR — Insecure Direct Object Reference — where an attacker increments the ID and reads someone else’s data. The IDs are not the security control (authorization is), but they remove the easy attack. → OWASP Top 10
For URL shorteners and share links, you want short and typeable, not 36 characters.
Base62 encoding (0-9a-zA-Z) of a counter:
62^6 = 56 billion → 6 characters
62^7 = 3.5 trillion → 7 characters
Base62 avoids URL-encoding issues. Base58 (used by Bitcoin) additionally removes visually
ambiguous characters — 0, O, I, l — which matters when humans read codes aloud or type them
from a printed page.
🚨 Don’t encode a raw sequential counter — the codes become guessable and enumerable. Either encode a Snowflake ID, or apply a reversible permutation (Feistel network) to the counter before encoding, or generate random codes and check for collisions. → Design a URL Shortener
| Decision | Gain | Cost |
|---|---|---|
| UUID v4 | Zero coordination, unguessable | Not sortable; 2–10× slower inserts on large tables |
| UUID v7 | Sortable, no coordination, standard | 16 bytes; leaks creation time |
| Snowflake | 8 bytes, sortable, fast | Machine ID assignment; clock skew handling |
| Ticket server | Simple, gapless | SPOF, round trip per ID, throughput ceiling |
| Batched blocks | Compact, few round trips | Gaps on restart; only roughly ordered |
| Two-ID pattern | Fast internally, safe externally | An extra column and an extra index |
cus_, ch_, pi_ prefixes plus a random suffix) are unguessable and
self-describing — you can tell what type of object an ID refers to at a glance, which makes logs
and support conversations far easier. A small design choice with large ergonomic payoff.1. Measure the UUID insert penalty. This is the exercise that makes the argument concrete:
CREATE TABLE t_seq (id BIGSERIAL PRIMARY KEY, payload TEXT);
CREATE TABLE t_uuid (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), payload TEXT);
-- Insert 5 million rows into each, timing both
INSERT INTO t_seq (payload) SELECT repeat('x', 100) FROM generate_series(1, 5000000);
INSERT INTO t_uuid (payload) SELECT repeat('x', 100) FROM generate_series(1, 5000000);
-- Then compare index sizes
SELECT pg_size_pretty(pg_relation_size('t_seq_pkey')),
pg_size_pretty(pg_relation_size('t_uuid_pkey'));
Note both the time difference and the index size difference (random inserts leave pages half-full). Then repeat with UUID v7 and watch it behave like the sequential case.
2. Implement Snowflake. Write the generator above. Then run two instances with the same machine ID and confirm they produce duplicates within the same millisecond. That’s why machine ID assignment matters.
3. Break it with the clock. Generate IDs, set your system clock back 5 seconds, generate more. Observe the duplicates. Then add the “refuse and raise” guard.
4. Base62 encode. Write encode/decode for base62 and see how short an ID for 10 billion items actually needs to be.