How to choose an id scheme for service entities#

The orders table has a random UUID primary key and forty million rows. Inserts that took two milliseconds take twenty, the index no longer fits in cache, and every write dirties a page nothing else touched. The invoices table went the other way, with a sequence, and a customer has been reading your monthly volume off the invoice URL.

What you get

You will end up with a benchmark that runs five id schemes at your row count and reports locality, plus a check that each id survives a URL and a ticket. This is for you if your service creates rows and nobody has said who mints the id.

Short answer

Let the service mint the id, and make it a UUIDv7 as RFC 9562 defines it: 48 bits of clock, then random bits. Each row then lands at the end of the index, and the caller holds the id before the write returns. Take a bigserial sequence only when nothing will ever merge with another store, and never publish it, because it counts your rows. Prove the choice by inserting your own row count.

You will need

Node 22.13 or later, for the built-in SQLite the benchmark writes to, and a service that creates rows. Verified 2026-09-24 against Node 22.22.2. The sample builds all five schemes from RFC 9562 and the two specifications linked in the table, so it runs offline and installs nothing.

Approaches compared

The split is between who mints the id and what it gives away. A sequence is minted by the database, so the service cannot retry a write it never got an answer to. It also publishes your row count to anyone who sees two of them. A UUIDv4 is minted by the service and gives away nothing, and pays for that with an index nothing can keep warm. UUIDv7 and ULID keep the service-minted id and put the clock back, at the cost of a timestamp in every id.

Mint the id where the retry happens

A service that mints its own id can send the same write twice and let the store reject the second, which is what makes a retry safe. A sequence hands the id back after the write, so a timeout leaves the service holding a row it cannot name.

UUIDv7 is the version built for the job. The first 48 bits are Unix milliseconds, the next four say version 7, and the rest is random, except for one thing.

export function uuidv7({ random, now } = defaults, state = {}) {
  const b = Buffer.from(random(16))
  const ms = now()
  if (ms === state.ms) state.seq += 1
  else Object.assign(state, { ms, seq: b.readUInt16BE(6) & 0x07ff })
  b.writeUIntBE(ms, 0, 6)
  b.writeUInt16BE(0x7000 | (state.seq & 0x0fff), 6)
  b[8] = (b[8] & 0x3f) | 0x80
  return { bytes: b, text: format(b) }
}

Two ids minted in the same millisecond are ordered by the twelve bits after the version, which start at a random value and count up. That is method 1 of section 6.2, and it is the difference between a burst of inserts that lands in order and one that scatters within each millisecond. Without it a UUIDv7 is time-ordered to the millisecond and random inside it. That is fine for a service that writes ten rows a second and not for one that writes ten thousand.

Measure locality at your own row count

The benchmark inserts the same number of rows under each scheme into a fresh table whose primary key is the id, then reads the index back through dbstat. Pass your own row count as the first argument.

node bench.mjs

rows per scheme: 100000

scheme     key bytes  index KB  last 1000 rows on   time-ordered  known before write  leaks row count
bigserial  8          860       4 pages             yes           no                  yes
uuidv4     16         2296      471 pages           no            yes                 no
uuidv7     16         2360      4 pages             yes           yes                 no
ulid       26         3484      3 pages             yes           yes                 no
nanoid     21         2804      536 pages           no            yes                 no

The fourth column is the one to read. The last thousand rows written under a time-ordered key sit on three or four pages, because each landed beside the one before it. Under a random key they sit on 471 or 536 pages, one row per page, because each landed somewhere in the middle of the index. Every one of those pages was read, split if full, and written back.

const leaves = db.prepare("SELECT ncell FROM dbstat WHERE name = 't' AND pagetype = 'leaf' ORDER BY path").all()
const pageOfRank = leaves.flatMap((leaf, page) => Array(leaf.ncell).fill(page))
const ordered = db.prepare('SELECT id FROM t ORDER BY id').all().map((r) => keyText(r.id))
const rank = new Map(ordered.map((k, i) => [k, i]))
const tailPages = new Set(keys.slice(-TAIL).map((k) => pageOfRank[rank.get(keyText(k))]))

The index size column says less than you would expect, and that is a property of the stand-in. SQLite redistributes a WITHOUT ROWID table’s pages on every split, so a random key and a sequential one end at much the same fill. The size difference here is the key length alone. Postgres does not. Its B-tree fills leaf pages to the fillfactor, 90 percent by default, when extending the index at the right. A random key that lands inside a full page splits it in the middle. Run the same insert against your own table before you quote a size.

The last block the command prints, inserts per second, depends on the machine, and the benchmark says so.

Put the id through a URL, a log line, and a ticket

An id spends most of its life outside the database. The second script mints one id per scheme and pushes each through the three places it ends up.

node survive.mjs

scheme     example                               in a URL  in a log line  typed from a ticket  carries a date
bigserial  48211                                 yes       yes            yes                  no
uuidv4     7955d4ed-ac83-4dce-bfd5-3a9c8848d815  yes       yes            yes                  no
uuidv7     01977420-dc00-701d-94e2-6910cb44c64a  yes       yes            yes                  2025-06-15T15:06:40.000Z
ulid       01JXT21Q00AAFMM9F1WK4PTXMN            yes       yes            yes                  2025-06-15T15:06:40.000Z
nanoid     iYE9dp-gqSX1ZUglf8M-F                 yes       yes            no                   no

Every scheme survives a URL and a log line, because none of them contains a space or a character that needs escaping. The ticket is where they part. The script folds each id to lower case and swaps a zero for the letter o and a one for the letter l. That is what a person does between reading an id and typing it. The script then asks the scheme’s own reader for the id back. Hex has no o or l, so a UUID reads back. The ULID alphabet leaves those letters out on purpose,¹ so it reads back. nanoid uses both cases and all three confusable pairs, so the typed id is a different id.

The last column is the other half of a time-ordered id. A UUIDv7 or a ULID tells a support desk when the row was created without a query, and tells anyone else the same thing.

Check it worked

Seven tests pin the properties the two tables show, and the fifth is the one the ticket column depends on.

node --test ids.test.mjs

1..7
# tests 7
# suites 0
# pass 7
# fail 0
# cancelled 0
# skipped 0
# todo 0
# duration_ms 171.229539

The first test mints two thousand ids under each scheme and asserts that UUIDv7 and ULID come out in the order they were made and that UUIDv4 and nanoid do not. The fourth calls a sequence a thousand times and asserts the answer is 1000, which is the leak in one line. The sixth types a ULID and a UUID badly and asserts each reads back, and that a nanoid does not.

When it goes wrong

Inserts get slower as the table grows and the CPU is idle. The primary key is random, so each insert waits on a page the cache dropped. Switch the key to UUIDv7, or keep the random id as a public handle and add a sequence as the primary key.

Ids minted in one burst come back out of order. The generator draws fresh random bits for every id and orders them only to the millisecond. Use a generator that keeps a counter within the millisecond, which is what the sample does and what section 6.2 of the RFC describes.

Two services mint ids that sort by a clock neither of them shares. UUIDv7 orders by the minting machine’s clock, so a skewed clock puts a row minutes into the past or the future. Sort user-facing lists by a created_at the database sets, and treat the id’s timestamp as a hint.

A sequence skips numbers and someone reads the gaps as lost rows. A value handed out by nextval is never handed out again, even when the transaction that took it rolls back,² so gaps are normal. Count rows to count rows.

When not to do this

Do not put a sequence in a URL. /invoices/48211 tells a competitor your volume, and the next number tells them the rate. Give the row a second id for the outside world, or mint a UUIDv7.

Do not pick UUIDv7 when the creation time is itself something to protect. The first 48 bits are a clock in plain sight, and a ULID carries the same 48 bits in its first ten characters. A UUIDv4 or a nanoid says nothing about when the row was made.

Do not read the index size column here as your Postgres number. The stand-in is SQLite, and the size difference shows up in Postgres and not there. The locality column transfers; the size column does not.

Do not ship the generators in the sample. The seeded random source exists so that two runs insert the same keys and the page counts agree, and it is not random at all. Use randomUUID for version 4, and a maintained library for version 7,³ ULID, and nanoid.

Related how-tos

• Make queue consumer writes safe under redelivery

• Encode and sign opaque pagination cursors

Last verified

Verified 2026-09-24 against Node 22.22.2. Every output block is what the preceding command printed. The store is Node’s built-in SQLite, standing in for the Postgres table the task describes; the page says where the two differ.