ID Generation: Sequences, UUIDv4, UUIDv7 and Snowflake

In one line

Sequences give compact, ordered IDs from one database; UUIDv4 gives globally unique random IDs that fragment B-tree indexes; UUIDv7 and Snowflake-style IDs put a timestamp first so they're unique across nodes and still insert in roughly increasing order. Choose by uniqueness scope, ordering, size, and whether IDs may leak information.

0/3 · 0%

Think of it like this

Numbering tickets. One counter at one desk gives 1, 2, 3 (a sequence). Many desks need either coordination, random numbers (UUIDv4), or "time + desk number + counter" (Snowflake), which never collides and still sorts by time.

Key ideas

01
Sequences / identity columns: bigint GENERATED ALWAYS AS IDENTITY. 8 bytes, monotonic, inserts append to the right edge of the index. Gaps are normal (rolled-back transactions and cached values consume numbers). Downsides: one database is the authority, and IDs reveal volume ("order 1,024,331").
02
UUIDv4: 122 random bits, generated anywhere. Random order means each insert lands on a random index page, so with an index larger than memory you get page splits, cache misses and much more WAL (full-page writes). Also 16 bytes vs 8.
03
UUIDv7 (RFC 9562, 2024): 48-bit Unix millisecond timestamp, then random bits. Generated anywhere, roughly time-ordered, so it keeps B-tree locality. PostgreSQL 18 added a built-in uuidv7(); on older versions generate it in the application. It reveals creation time.
04
Snowflake-style (Twitter): 64 bits = 41-bit millisecond timestamp + 10-bit machine ID + 12-bit per-millisecond sequence → 4,096 IDs/ms per node, ~69 years of range from a custom epoch. Needs unique machine IDs (assigned via config or a coordinator) and handling of clock going backwards (wait, or refuse).
05
Other options: ULID (timestamp + random, 26-char sortable text), KSUID, database-side segment allocation (each app server leases a block of 1,000 IDs, as in Meituan Leaf / Flickr ticket servers).
06
Expose external IDs carefully: sequential IDs let outsiders enumerate records (/invoice/1001, /invoice/1002). Authorisation must never depend on unguessable IDs, but an opaque public ID reduces scraping and business-volume leakage.

Code & diagrams

ids.sqlsql

-- sequence-backed identity
CREATE TABLE invoice (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, total numeric(12,2));

-- UUIDv4 (pgcrypto/core) vs UUIDv7 (PostgreSQL 18+)
SELECT gen_random_uuid();   -- 3f1c1b0e-8e6b-4b0f-9d6e-2a4b8f6c1d77  random
SELECT uuidv7();            -- 01936f2e-7c3a-7b41-8f2d-5e9a0c1b4d26  time-ordered prefix

CREATE TABLE event (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  payload jsonb NOT NULL
);

Snowflake.javajava

public final class Snowflake {
    private static final long EPOCH = 1704067200000L;     // 2024-01-01T00:00:00Z
    private final long machineId;                          // 0..1023, unique per node
    private long lastMs = -1, seq = 0;

    public Snowflake(long machineId) {
        if (machineId < 0 || machineId > 1023) throw new IllegalArgumentException();
        this.machineId = machineId;
    }

    public synchronized long next() {
        long now = System.currentTimeMillis();
        if (now < lastMs) throw new IllegalStateException("clock moved back " + (lastMs - now) + " ms");
        if (now == lastMs) {
            seq = (seq + 1) & 4095;                        // 12-bit sequence
            if (seq == 0) while ((now = System.currentTimeMillis()) <= lastMs) { /* spin to next ms */ }
        } else {
            seq = 0;
        }
        lastMs = now;
        return ((now - EPOCH) << 22) | (machineId << 12) | seq;
    }
}

Interview problem

The problem

Design a distributed ID generator

Design ID generation for an order service running 200 instances across 3 regions, needing 50K IDs/sec total, IDs sortable by time, 64-bit (legacy clients), and no single point of failure.

You're given

64-bit
Roughly time-ordered
No central coordinator on the hot path
Survive clock skew

When it breaks

UUIDv4 primary keys on a large, write-heavy table

What you see

Insert throughput drops and WAL volume multiplies as the index outgrows memory; every insert dirties a random page.

Fix & prevent

Switch new tables to UUIDv7 or bigint identity; for existing tables, consider a new time-ordered key for the clustered or primary index.

Two nodes with the same Snowflake worker ID

What you see

Duplicate IDs; inserts fail on the primary key, or worse, collide across shards where no global constraint exists.

Fix & prevent

Lease worker IDs with expiry from a coordinator; refuse to start without a lease; include the worker ID in logs to detect it.

Explain it without notes

Why do random UUIDs hurt B-tree insert performance, and how does UUIDv7 fix it?

Practice

Calculate how long a 41-bit millisecond timestamp lasts from its epoch.

Trade-offs

↔
Sequences: compact and fast but centralised and guessable. UUIDv7: decentralised, ordered, 16 bytes, reveals time. Snowflake: 64-bit and ordered but needs worker-ID management and clock discipline.

Done when you can

I can choose and design an ID scheme and explain its effect on indexes.

Command Palette