01a1d36c-3100-7293-a238-4c9ce305d4a4
^ ^
| └─ variant (first hex digit 8, 9, a or b = RFC variant)
└────── version (13th hex digit) → 7
Version: Tells you how the rest of the bits were made
Variant: Always 10xx bits for standard UUIDs
Special Values: Nil 00000000-0000-0000-0000-000000000000, Max ffffffff-ffff-ffff-ffff-ffffffffffff
All Versions
Version
Made From
Sortable by Time
Deterministic
Use Today?
v1
Timestamp (100 ns since 1582) + MAC address
No (time bytes stored low-first)
No
Avoid — leaks MAC & time
v2
DCE Security (v1 + POSIX UID/GID)
No
No
Practically never
v3
MD5(namespace + name)
No
Yes
Prefer v5
v4
122 random bits
No
No
Yes — when order/time must not leak
v5
SHA-1(namespace + name)
No
Yes
Yes — stable IDs from names
v6
v1 with time bytes reordered
Yes
No
Only for v1 legacy migration
v7
48-bit Unix ms timestamp + random
Yes
No
Yes — default for DB keys
v8
Custom / vendor-defined layout
Depends
Depends
Special cases
v4 — Random
Bits: 122 random + 6 fixed (version/variant)
Collisions: Need ~2.7 × 10¹⁸ UUIDs for a 50% chance of one duplicate — ~1 billion/sec for ~85 years
Downside: Random order → every insert lands on a random index page
v5 / v3 — Name-Based
Same Input → Same UUID: uuid5(NAMESPACE_URL, "https://x.com/a") always gives the same value
Use Case: Idempotent imports, dedupe keys, mapping external IDs to UUIDs
Note: Not secret — anyone with the name can compute it
v7 — Time-Ordered
| 48 bits unix_ts_ms | 4 ver | 12 bits rand_a | 2 var | 62 bits rand_b |
Sorted by Creation Time: New values are (almost) always bigger than old ones
Within One Millisecond: Postgres 18 puts a sub-millisecond fraction in rand_a, so values from one backend are strictly increasing
Across Servers: Only roughly ordered (clock skew), which is still good enough for index locality
Bonus: Creation time can be extracted from the ID
Why v4 Hurts Primary Keys (B-Tree)
Sequential keys (bigint, v7): Every insert goes to the rightmost leaf page. When it fills, Postgres does a “rightmost split” and leaves the old page ~90% full. Hot pages stay in cache
Random keys (v4): Every insert goes to a random leaf page → 50/50 page splits all over the index, pages ~50–70% full, the whole index must be in memory, many more dirty pages → more WAL (full-page images after each checkpoint) and more I/O
MySQL InnoDB is worse: The table is the PK index (clustered), so random keys fragment the table rows themselves, not just an index
Verified on PostgreSQL 17.8 (3M inserts, shared_buffers = 16MB)
PK Type
Insert Time
WAL Written
PK Index Size
Leaf Density
Leaf Fragmentation
bigint identity
7.8 s
405 MB
64 MB
90%
0%
uuid v4
24.2 s
491 MB
123 MB
66%
50%
uuid v7
16.7 s
446 MB
90 MB
90%
0%
v4 vs v7: 37% bigger index, half the leaf pages out of order, ~45% slower inserts
v7 vs bigint: Index is bigger only because a UUID is 16 bytes vs 8. The layout is just as clean
Caveat: On PG 17, v7 came from a PL/pgSQL function (below), so part of its extra time is function overhead. PG 18’s built-in uuidv7() is C. The gap to v4 grows when the index no longer fits in RAM
Measured With: pgstatindex() from pgstattuple, and pg_wal_lsn_diff() before/after each insert
Storage
Stored As
Bytes
uuid type
16
text / varchar(36)
37–40
bigint
8
Rule: Always use the native uuid type (Postgres) or BINARY(16) (MySQL), never strings
-- PG 18+CREATE TABLE orders ( id uuid PRIMARY KEY DEFAULT uuidv7(), created_at timestamptz NOT NULL DEFAULT now());SELECT uuid_extract_timestamp(id) FROM orders LIMIT 1; -- creation time from the key
v7 on PostgreSQL 17 and Older (verified on 17.8)
CREATE FUNCTION uuid_v7() RETURNS uuid LANGUAGE plpgsql VOLATILE AS $$DECLARE us bigint := (extract(epoch FROM clock_timestamp()) * 1000000)::bigint; r text := replace(gen_random_uuid()::text, '-', '');BEGIN -- 48-bit ms | '7' | 12-bit sub-ms fraction | variant + random (from the v4) RETURN (lpad(to_hex(us / 1000), 12, '0') || '7' || lpad(to_hex(((us % 1000) * 4096 / 1000)::int), 3, '0') || substr(r, 17, 16))::uuid;END $$;SELECT uuid_v7(), uuid_extract_version(uuid_v7()); -- → 7
Or generate v7 in the app (most languages have a library) and insert it
MySQL
UUID(): Returns v1
Store: BINARY(16) with UUID_TO_BIN(UUID(), 1) — the 1 (swap flag) moves the time bits to the front so v1 values become time-ordered
Read Back: BIN_TO_UUID(id, 1)
Better: Generate v7 in the app and store as BINARY(16)
Choosing an ID
Situation
Choose
Internal IDs, single database
bigint GENERATED ALWAYS AS IDENTITY (smallest, fastest)
IDs made in the app / client / multiple DBs, or exposed in URLs
UUID v7
Must not reveal creation time or order
UUID v4
Same input must always map to same ID
UUID v5
Legacy system with v1
Keep, or migrate to v6/v7
Common Pattern: bigint PK internally + a uuid (v4 or v7) public ID column with a UNIQUE index for URLs/APIs
Security Notes
Not Enumerable: UUIDs stop /orders/1, /orders/2 guessing — but that is not access control. Always check authorization
v7 Leaks Time: Anyone can read when a row was created from its ID
v1 Leaks MAC Address: Identifies the machine that created it
Not a Secret Token: Don’t use UUIDs as session tokens, password-reset tokens or API keys. Use a CSPRNG with ≥128 bits (e.g. crypto.randomBytes(32)); the RFC doesn’t require v4 to come from a secure random source
Likely Interview Questions
Why not use UUIDv4 as a PK? Random inserts → page splits, ~50% fragmented and bigger index, more cache misses and WAL
How does v7 fix it? Timestamp prefix → new keys append at the right end of the B-tree like a sequence
UUID vs bigint? UUID: global uniqueness, generate anywhere, non-guessable. bigint: half the size, fastest, needs a central sequence, enumerable
Can two UUIDs collide? Theoretically yes; for v4 practically never (2.7 × 10¹⁸ for a 50% chance)
Same UUID every time for the same email? v5 with a namespace
Summary
v1: time + MAC (avoid) · v3/v5: hash of a name, deterministic · v4: random · v6: sortable v1 · v7: Unix-ms time + random, sortable · v8: custom
Database Keys: Prefer bigint identity or UUID v7. Avoid v4 as a clustered/B-tree PK at scale
Measured: v4 PK index 37% bigger, 50% fragmented, ~45% slower inserts than v7 on PG 17.8
Postgres: gen_random_uuid() (v4), uuidv7() from PG 18, function above for PG 17
Storage: Native uuid / BINARY(16), never text
Security: v7 reveals creation time; no UUID is a substitute for a secure random token