• Databases
  • Engineering
  • Reference

UUID v4, UUID v7 or ULID? Picking an ID for your database

What is inside each ID, why random keys make B-tree inserts slower (we measured 30x the page writes), v7's same-millisecond caveat, and what to store.

The utilyx team10 min read
On this page

You picked UUIDs for primary keys because they can be generated anywhere: in the app, on the client, in three services at once, without asking the database for the next number. Then the table grew, inserts got slower than they had any right to be, and someone on the team said the word "fragmentation".

They were probably right, and the fix is usually not to abandon UUIDs. It is to pick the kind whose bits are in a useful order. Our short answer: use UUID v7 for database keys, keep v4 for identifiers that must reveal nothing, and reach for ULID only if you want its shorter string. Below: the bits, the math, and an insert benchmark you can rerun.

What's inside each one

All three are 128 bits. The difference is how those bits are spent. UUIDs are specified by RFC 9562 (May 2024), which obsoleted RFC 4122 and added versions 6, 7 and 8. Here are the two layouts that matter, from its §5.4 and §5.7, with one 32-bit row per line:

text
UUID v4 (RFC 9562 §5.4)
 0                   1                   2                   3
 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|                           random_a                            |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|          random_a             |  ver  |       random_b        |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|var|                       random_c                            |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|                           random_c                            |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

UUID v7 (RFC 9562 §5.7)
 0                   1                   2                   3
 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|                           unix_ts_ms                          |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|          unix_ts_ms           |  ver  |       rand_a          |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|var|                        rand_b                             |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+
|                            rand_b                             |
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+

Both spend 6 bits on fixed values: a 4-bit ver field (0100 for v4, 0111 for v7) and a 2-bit var field (10). That leaves v4 with 122 random bits. v7 puts a 48-bit big-endian Unix timestamp in milliseconds first, then 74 bits (rand_a 12, rand_b 62) of randomness, some of which an implementation may use as a counter. In the familiar hex form you can read the layout directly:

text
v4  xxxxxxxx-xxxx-4xxx-Nxxx-xxxxxxxxxxxx   N is 8, 9, a or b (variant bits 10)
v7  tttttttt-tttt-7xxx-Nxxx-xxxxxxxxxxxx   t = 48-bit Unix time in ms

Generating a v7 is a few lines. Node 26.1 added crypto.randomUUIDv7(), but if you're on an older version, or want to see the layout in code, here it is with no dependencies:

js
import { randomFillSync } from "node:crypto";

function uuidv7(ms = Date.now()) {
  const b = randomFillSync(new Uint8Array(16));
  // Bytes 0–5: 48-bit big-endian Unix time in milliseconds.
  for (let i = 5; i >= 0; i--) {
    b[i] = ms % 256;
    ms = Math.floor(ms / 256);
  }
  b[6] = (b[6] & 0x0f) | 0x70; // version 7
  b[8] = (b[8] & 0x3f) | 0x80; // variant 0b10
  const h = Buffer.from(b).toString("hex");
  return `${h.slice(0, 8)}-${h.slice(8, 12)}-${h.slice(12, 16)}-${h.slice(16, 20)}-${h.slice(20)}`;
}

console.log(uuidv7());
console.log(uuidv7());
text
01a1fee7-7b07-7aac-87ff-d0f09646cefe
01a1fee7-7b08-75f0-89dd-9228aff37a5d

The two IDs were created a millisecond apart, and it shows: they share the first eleven hex digits. That shared, increasing prefix is the entire point of v7.

ULID predates v7 and has the same idea with a different budget: a 48-bit millisecond timestamp followed by 80 random bits, with no version or variant bits. Its canonical form is 26 characters of Crockford base32 (10 for the time, 16 for the randomness), an alphabet that drops I, L, O and U to avoid misreading. Python 3.14 added uuid.uuid7() to the standard library, and Node 26.1 and PostgreSQL 18 have built-ins too. None of the three generates ULIDs.

Will a v4 ever collide?

With 122 random bits, the birthday bound gives the probability of at least one collision among n IDs as approximately:

text
p ≈ 1 − e^(−n² / (2 · 2^122))

We computed it for a few sizes (Python, using math.expm1 to keep precision at tiny values):

IDs generatedChance of any collision
1 million9.4 × 10⁻²⁶
1 billion9.4 × 10⁻²⁰
1 trillion9.4 × 10⁻¹⁴
1 quadrillion (10¹⁵)9.4 × 10⁻⁸
2.7 × 10¹⁸50%

Even odds take a billion IDs per second for about 86 years. Real v4 collisions come from broken randomness, such as a non-cryptographic generator or a VM snapshot restored twice, not from math. Use the platform's CSPRNG (crypto.randomUUID(), uuid.uuid4(), gen_random_uuid()) and keep the unique constraint a primary key gives you anyway.

v7 and ULID can only collide within the same millisecond, so what matters is how many you create per millisecond. If an implementation fills all 74 v7 bits randomly, a million IDs inside one millisecond collide with probability about 2.6 × 10⁻¹¹; ULID's 80 bits give 4.1 × 10⁻¹³. Implementations that use some of those bits as a counter trade randomness for ordering, as we'll see below.

Why random keys hurt a B-tree

Most databases keep a primary key in a B-tree: keys sorted across fixed-size pages (4 KiB in SQLite by default, 8 KiB in Postgres). Inserting a key means finding the one leaf page where it belongs in sort order and rewriting that page. When the page is full, it splits in two, and the parent page is rewritten to point at both.

With time-ordered keys, every new key sorts after every existing one, so every insert lands on the same rightmost leaf. One page stays hot in memory, fills up, and the tree grows at its right edge. With random keys, each insert lands on a random leaf. Ten thousand inserts touch thousands of different pages, each of which has to be read into memory if it isn't already cached, modified for one small row, and written back in full. That is write amplification: a 100-byte row costing a 4 KiB page write. Once the index outgrows memory, the reads cost real I/O too.

RFC 9562 says as much in §6.11: time-ordered UUIDs benefit from greater database-index locality, and it puts the difference at "one order of magnitude or more". We wanted a number we could see, so we measured.

The measurement

Setup: SQLite 3.46.1 from Python 3.14.4, a WITHOUT ROWID table (so the table is the primary-key B-tree), 16-byte binary keys plus a 64-byte payload, inserted in transactions of 10,000 rows. The database file was on tmpfs on a laptop with an Intel Core i7-1355U, so this isolates the B-tree work from disk speed. To count page writes independently of hardware, the script turns off automatic WAL checkpoints and adds up the pages each commit writes to the write-ahead log:

python
# Python 3.14+ (uuid.uuid7)
import os, sqlite3, sys, time, uuid

N = int(sys.argv[1]) if len(sys.argv) > 1 else 1_000_000
BATCH = 10_000

def run(kind, make):
    path = f"{kind}.db"
    for p in (path, path + "-wal", path + "-shm"):
        if os.path.exists(p): os.remove(p)
    db = sqlite3.connect(path)
    db.execute("PRAGMA journal_mode=WAL")
    db.execute("PRAGMA wal_autocheckpoint=0")
    db.execute("CREATE TABLE t (id BLOB PRIMARY KEY, payload TEXT) WITHOUT ROWID")
    written = 0  # 4 KiB pages written to the WAL, summed over every commit
    t0 = time.perf_counter()
    for _ in range(N // BATCH):
        rows = [(make(), "x" * 64) for _ in range(BATCH)]
        db.executemany("INSERT INTO t VALUES (?, ?)", rows)
        db.commit()
        written += db.execute("PRAGMA wal_checkpoint(PASSIVE)").fetchone()[1]
    elapsed = time.perf_counter() - t0
    pages = db.execute("PRAGMA page_count").fetchone()[0]
    db.close()
    os.remove(path)
    print(f"{kind}  {elapsed:5.1f} s  {N/elapsed:8,.0f} rows/s  final size {pages:6,} pages  "
          f"pages written {written:9,}  ({written/pages:.1f}x final size)")

print(f"sqlite {sqlite3.sqlite_version}, python {sys.version.split()[0]}, {N:,} rows, batches of {BATCH:,}")
run("v4", lambda: uuid.uuid4().bytes)
run("v7", lambda: uuid.uuid7().bytes)
text
sqlite 3.46.1, python 3.14.4, 1,000,000 rows, batches of 10,000
v4    7.3 s   137,627 rows/s  final size 24,425 pages  pages written   746,865  (30.6x final size)
v7    2.1 s   481,940 rows/s  final size 24,422 pages  pages written    25,341  (1.0x final size)
RowsKeyInsert timePages writtenWritten ÷ final size
1 MUUID v47.3 s746,86530.6×
1 MUUID v72.1 s25,3411.0×
2 MUUID v418.6 s1,943,48039.7×
2 MUUID v74.1 s50,7111.0×

The v7 run wrote each page about once: the data, and essentially nothing else. The v4 run wrote 30 times more pages for the same million rows, about 7,500 pages per 10,000-row commit, and the ratio grew as the table did (40× at two million). Insert throughput was 3.5 to 4.5 times higher with v7, even though Python's uuid7() is the slower generator (981 ns versus 583 ns per call in timeit).

What we did not see: a size difference. Both final files were about 24,400 pages, and a separate run with SQLite's dbstat table showed leaf pages 87% full for both. SQLite rebalances across neighboring pages on a split, so random inserts didn't leave it half-empty. Other engines split differently, so don't read our size result as universal.

Limits of this test. It is SQLite, single-threaded, with the file in RAM. The write counts depend on batch size: smaller transactions rewrite the same hot pages more often. On disk, and once the index outgrows the cache, random keys also pay for reads, which this setup hides. Treat the 30× as a demonstration of the mechanism, not a prediction for your Postgres or MySQL cluster. Rerun the script on your own hardware; it takes under a minute.

v7's caveat: ordering inside one millisecond

v7 is sorted by millisecond. Within a millisecond, a generator that fills the rest with random bits, like ours above, produces IDs in random order. Node's built-in does the same: its docs say the timestamp comes from a non-monotonic clock and is not guaranteed to be strictly increasing. On a fast machine most IDs share a millisecond with their neighbors:

js
import { randomUUIDv7 } from "node:crypto"; // Node 26.1+

const ids = Array.from({ length: 10_000 }, () => randomUUIDv7());
const outOfOrder = ids.filter((id, i) => i > 0 && id < ids[i - 1]).length;
const millis = new Set(ids.map((id) => id.slice(0, 13))).size;
console.log({ generated: ids.length, distinctMillis: millis, outOfOrder });
text
{ generated: 10000, distinctMillis: 9, outOfOrder: 5001 }

Ten thousand IDs over 9 milliseconds, and half are smaller than the ID generated just before them. For an index that's mostly harmless: they still land on the same few pages at the right edge. It matters when code relies on ORDER BY id meaning "creation order", such as cursor pagination or event logs.

RFC 9562 §6.2 describes three ways to fix this within one generator: a dedicated counter in the bits after the timestamp (Method 1), treating the random bits themselves as a number that is incremented by a random amount (Method 2), or spending up to 12 bits on sub-millisecond clock precision (Method 3). Real implementations differ:

  • Python 3.14 uses Method 1: a 42-bit counter seeded randomly each millisecond and incremented within it, plus 32 fresh random bits. If the clock goes backwards, it uses one millisecond past the last timestamp instead. The same test in Python gives zero out-of-order IDs:
python
import uuid  # Python 3.14+

ids = [str(uuid.uuid7()) for _ in range(10_000)]
out_of_order = sum(b < a for a, b in zip(ids, ids[1:]))
print({"generated": len(ids), "distinct_millis": len({i[:13] for i in ids}), "out_of_order": out_of_order})
text
{'generated': 10000, 'distinct_millis': 16, 'out_of_order': 0}
  • PostgreSQL 18's uuidv7() uses Method 3, according to the comments in its source (src/backend/utils/adt/uuid.c), and guarantees each value is greater than the previous one generated by the same backend, meaning the same connection.
  • ULID's spec says to increment the random part by one within the same millisecond, and that generation fails if that ever overflows.

The caveat none of them can lift: ordering holds per generator. Two app servers, two Postgres connections, or two worker processes each produce their own sequence, and their IDs interleave by clock, with whatever skew the clocks have. The RFC is explicit that its monotonicity methods are for a single node. If you need a total order across writers, use a sequence or a timestamp column and sort by that.

ULID or UUID v7?

UUID v7ULID
Specified byRFC 9562, an IETF standardA community spec on GitHub
Timestamp48-bit Unix ms48-bit Unix ms
Remaining bits74 random or counter (6 fixed)80 random
Text form36 chars, hex with hyphens26 chars, Crockford base32
Same-ms orderingImplementation's choice of three methodsIncrement by one; fails on overflow
Native DB typeAny uuid columnNone. Text, or 16 bytes that aren't a valid RFC UUID

Their sort behavior and index behavior are the same: both are a millisecond timestamp followed by noise. The real differences are the string and the ecosystem. A ULID is 10 characters shorter and avoids ambiguous letters, which is pleasant in URLs and support tickets. A v7 fits a native uuid column, validates with every UUID parser you already have, and is now generated natively by Postgres 18, Python 3.14 and Node 26.1. You can store a ULID's 16 bytes in a uuid column, but the version and variant positions will hold whatever random bits the ULID had there, so tools that read the version will report nonsense.

For a new system we'd choose v7. If you already use ULIDs, there's no index reason to migrate.

Your IDs say when the row was created

Anyone holding a v7 or a ULID can read its creation time. No key needed:

js
const createdAt = (id) => new Date(parseInt(id.replace(/-/g, "").slice(0, 12), 16));

console.log(createdAt("019535d9-3df7-79fb-b466-fa907fa17f9e").toISOString());
text
2025-02-24T02:46:24.503Z

That ID is the example from the PostgreSQL 18 docs, and the decoded time matches the one they show for uuid_extract_timestamp(). Often that's harmless. But put a v7 in a public URL and you publish when each user signed up or each order was placed. RFC 9562 §8 calls the embedded timestamp "a very small attack surface" and notes that with a counter it "does signal the order of creation".

The same section is firmer on a related point: UUIDs "MUST NOT be used as security capabilities", identifiers whose mere possession grants access, and implementations shouldn't assume they're hard to guess. Where an application does use UUIDs in a security-relevant role, it recommends v4. A practical split: v7 as the internal primary key, and a separate v4, or better, a purpose-built random token, for anything exposed in links, password resets or invitations.

Store 16 bytes, not 36 characters

The text form of a UUID is 36 characters for 16 bytes of information. RFC 9562 §6.13 says UUIDs "SHOULD be stored within database applications as the underlying 128-bit binary value", and calls text storage unnecessarily verbose: 288 bits to hold 128.

In PostgreSQL, use the native uuid type, which stores the 128-bit value. A text column holding the same value takes 37 bytes (Postgres stores a short string as 1 byte plus the string). In MySQL, use BINARY(16). In SQLite, a BLOB. The difference compounds, because the key is repeated in every index and every foreign key that references it. We measured a primary-key B-tree of a million v7 keys in SQLite:

python
# Python 3.14+ (uuid.uuid7)
import sqlite3, uuid

db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE as_text (id TEXT PRIMARY KEY) WITHOUT ROWID")
db.execute("CREATE TABLE as_blob (id BLOB PRIMARY KEY) WITHOUT ROWID")
ids = [uuid.uuid7() for _ in range(1_000_000)]
db.executemany("INSERT INTO as_text VALUES (?)", ((str(u),) for u in ids))
db.executemany("INSERT INTO as_blob VALUES (?)", ((u.bytes,) for u in ids))
for name in ("as_text", "as_blob"):
    pages = db.execute("SELECT count(*) FROM dbstat WHERE name = ?", (name,)).fetchone()[0]
    print(f"{name}: {pages:,} pages ({pages * 4096 / 2**20:.1f} MiB) for 1,000,000 keys")
text
as_text: 11,509 pages (45.0 MiB) for 1,000,000 keys
as_blob: 5,890 pages (23.0 MiB) for 1,000,000 keys

Text also brings correctness traps. 'A0EEBC99-…' and 'a0eebc99-…' are the same UUID but different strings, so a text column can hold both and a lookup with the wrong case misses. A native uuid column normalizes on input; Postgres accepts uppercase, braces and missing hyphens, and always outputs the standard lowercase form.

PostgreSQL 18, released in September 2025, added uuidv7() and an explicit uuidv4() alias for gen_random_uuid(). If you're on 18, the key can be generated by the database:

sql
CREATE TABLE orders (
  id         uuid PRIMARY KEY DEFAULT uuidv7(),
  total_cents bigint NOT NULL
);

-- Creation time, straight from the key (v1 and v7 only; NULL for others)
SELECT id, uuid_extract_timestamp(id) FROM orders;

uuidv7() also takes an optional interval that shifts the embedded timestamp. On older versions, generate v7 in the application: a library for your language, a built-in like Node's, or the dozen lines above.

What we'd pick

  • Primary keys in a new system: UUID v7, stored as native uuid or 16 bytes.
  • IDs that leave your system and should reveal nothing: v4. Not as an access token, though; use a real token for that.
  • Need strict creation order across many writers: none of these. Sort by a sequence or a timestamp column.
  • Single database, no offline or client-side generation: a plain bigint identity column is still smaller and simpler than any of them.

When you just need a few IDs for a fixture, a test or a curl command, the utilyx UUID generator makes v4, v7 and ULIDs with crypto.getRandomValues, and lets you paste one in to see its version, variant and embedded timestamp. One limit to know: a batch shares a single timestamp and is sorted after generation, rather than using a per-millisecond counter. That's fine for test data. For keys your database will index, generate them where the rows are written: in Postgres 18, or with your language's library.

Try the UUID generator
Free, no sign-up, and it runs in your browser.
Open the tool →