How do you choose a primary key strategy — UUID vs. auto-increment vs. a Snowflake-style distributed ID?

#Three real options, three different guarantees

• Auto-increment / serial (SERIAL, IDENTITY, AUTO_INCREMENT) — the database assigns the next integer in sequence.
• UUID — a 128-bit value, almost always generated as random (v4) or, increasingly, time-ordered (v7).
• Snowflake-style ID — a 64-bit integer assembled from a timestamp, a machine/worker identifier, and a per-millisecond sequence counter, popularized by Twitter's Snowflake service and reused (with variations) by Discord, Instagram, and Sony's Sonyflake.

#Auto-increment: simple, until you need more than one writer

CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    total_cents INTEGER NOT NULL
);

Strengths: compact (8 bytes), monotonically increasing (great for B-tree insert locality — new rows always append at the right edge of the index rather than splitting pages randomly), trivially sortable by creation order, and human-friendly in logs and URLs.

Weaknesses: it requires a single point of coordination to hand out the next value, which is exactly what you don't have once you shard across multiple database instances (two shards independently generating id=4501 is a collision waiting to merge). It's also sequentially guessable/enumerable — /orders/4502 trivially implies /orders/4501 exists, which is an information-disclosure and enumeration-attack surface unless you deliberately don't expose it. And it can't be generated by the application before an insert — you don't have an ID to reference in related records or to return to a client until the database hands one back.

#UUID: no coordination needed, at a real cost

CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),  -- UUID v4, fully random
    customer_id BIGINT NOT NULL,
    total_cents INTEGER NOT NULL
);

Strengths: any node, anywhere — application server, mobile client, offline-first app — can generate a globally unique ID with zero coordination and zero risk of collision with any other node's IDs. Not enumerable. Ideal for merging data from multiple independently-writing sources (offline sync, multi-region active-active writes, event sourcing).

Weaknesses (this is the part interviewers actually want to hear): a random UUID (v4) has no relationship between its value and insertion order, so as rows are inserted, they land at random positions throughout the primary key's B-tree index rather than appending at the end. That means every insert has a good chance of touching a page that isn't in the buffer cache and of triggering page splits, which measurably degrades insert throughput and bloats the index compared to a sequential key — this is a well-documented, reproducible effect, not a theoretical one. It's also twice the storage of a bigint (16 bytes vs. 8), which compounds across every foreign key column referencing it and every index.

UUID v7 (and similarly, ULID) fixes the insert-locality problem by putting a millisecond timestamp in the high bits and randomness only in the low bits — so values are still globally unique and unpredictable in the low bits, but sort roughly by creation time and insert at (approximately) the right edge of the index, closing most of the gap with auto-increment while keeping the no-coordination property.

#Snowflake-style ID: the distributed-systems answer

Wrap long lines
class SnowflakeIdGenerator(private val workerId: Long, private val epoch: Long = 1_700_000_000_000L) {
    init {
        require(workerId in 0..1023) { "workerId must fit in 10 bits" }
    }

    private var lastTimestamp = -1L
    private var sequence = 0L

    // Layout (64 bits): 1 unused sign bit | 41 bits timestamp | 10 bits worker id | 12 bits sequence
    @Synchronized
    fun nextId(): Long {
        var timestamp = System.currentTimeMillis()

        if (timestamp == lastTimestamp) {
            sequence = (sequence + 1) and 0xFFF          // 12-bit sequence, wraps at 4096/ms
            if (sequence == 0L) {
                while (timestamp <= lastTimestamp) timestamp = System.currentTimeMillis()
            }
        } else {
            sequence = 0L
        }
        lastTimestamp = timestamp

        return ((timestamp - epoch) shl 22) or (workerId shl 12) or sequence
    }
}

Strengths: 64 bits (fits a BIGINT, half the storage of a UUID), roughly time-sortable like auto-increment (good index locality), and — the whole point — generated without a central sequence, because each worker only needs its own pre-assigned worker ID and the current time to produce an ID no other worker will produce. This is why it's the standard answer for "how do you generate IDs across many independent write nodes/shards without a bottleneck."

Weaknesses: worker ID allocation is itself a small coordination problem (usually solved once, at deploy time, via config or a coordination service like ZooKeeper/etcd, not per-ID) — and clock behavior matters: if a worker's clock jumps backwards (NTP correction), naive implementations can produce a duplicate or out-of-order ID, so production implementations detect backward clock jumps and either wait or fail loudly rather than silently emitting a bad ID.

#Decision table

🧠 Check your understanding
0/3 · 0/3 answered
1. Why does a randomly generated UUID v4 primary key tend to hurt insert performance on a large table compared to an auto-increment integer?
UUIDs cannot be indexed at allNew rows insert at random positions in the B-tree index instead of appending at the end, causing page splits and poor cache localityThe database must check global uniqueness across the internet before insertingUUIDs require a network round-trip to generate
2. What specific problem does a Snowflake-style ID solve that a plain auto-increment column cannot?
It allows multiple independent nodes to generate unique, roughly time-ordered IDs without a shared central counterIt guarantees IDs are non-sequential and unguessableIt removes the need for a primary key entirelyIt makes IDs shorter than an integer
3. What does UUID v7 change relative to UUID v4 that helps with database performance?
It removes the randomness component entirely, making it predictableIt embeds a timestamp in the high-order bits so values are roughly sortable and insert near the end of an index, unlike fully random v4It requires central coordination like an auto-increment columnIt reduces the ID to 64 bits