# How to choose an id scheme for service entities > Decide who mints an entity id, the service or the database, then choose a sequence, UUIDv4, UUIDv7, ULID, or nanoid by measuring index locality and order. Source: https://voxgig.com/howto/choose-entity-ids-for-a-service-database - Audience: api-producer - Level: beginner - Languages: typescript - Verified: 2026-09-24 - Published: 2026-09-24 ## 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](https://nodejs.org/api/sqlite.html) 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](https://www.rfc-editor.org/rfc/rfc9562.html) and the two specifications linked in the table, so it runs offline and installs nothing. ## Approaches compared | Approach | When it fits | What it costs you | When to pick something else | | --- | --- | --- | --- | | [`bigserial` identity columns](https://www.postgresql.org/docs/current/datatype-numeric.html) | One database, internal ids, the smallest index, and the cheapest join | A round trip before the caller has the id, and a number that counts your rows for whoever sees two | Rows are minted in two places, or the id appears in a URL | | [nanoid](https://github.com/ai/nanoid) | Short ids in URLs, minted by the service, never sorted or read aloud | Random order in the index, and case-sensitive text a person cannot read back from a ticket | The table grows by insert, or a support desk types the id | | [ULID](https://github.com/ulid/spec) | Time-ordered ids as readable, case-insensitive text, with a library in most languages | 26 characters of text where a UUID is 16 bytes, so a larger index, and no RFC behind it | The store has a native 128-bit `uuid` column, where UUIDv7 costs less | | [UUIDv4](https://www.rfc-editor.org/rfc/rfc9562.html#section-5.4) | Ids minted anywhere and merged anywhere, with no clock and nothing to leak | Every insert lands on a random page, so the index stays cold and page splits slow a large table | The table is large and mostly appended to | | [UUIDv7](https://www.rfc-editor.org/rfc/rfc9562.html#section-5.7) | Service-minted ids that sort by creation time and fit a native `uuid` column | 48 bits that publish when the row was made, and in-order bursts need a counter | The creation time is itself sensitive, or you need short ids | 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](https://www.postgresql.org/docs/current/sql-createtable.html) 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. ```js title="ids.mjs" 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](https://www.rfc-editor.org/rfc/rfc9562.html#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`](https://sqlite.org/dbstat.html). Pass your own row count as the first argument. ```bash node bench.mjs ``` ```text output 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. ```js title="bench.mjs" 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`](https://sqlite.org/withoutrowid.html) 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`](https://www.postgresql.org/docs/current/sql-createindex.html), 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. ```bash node survive.mjs ``` ```text output 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,[^1] 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. ```bash node --test ids.test.mjs ``` ```text output 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,[^2] 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`](https://nodejs.org/api/crypto.html) for version 4, and a maintained library for version 7,[^3] ULID, and nanoid. ## Related how-tos - [Make queue consumer writes safe under redelivery](/howto/queue-consumer-writes-under-at-least-once) - [Encode and sign opaque pagination cursors](/howto/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. [^1]: [Crockford's base32](https://www.crockford.com/base32.html) drops four letters from the alphabet and gives a reason for each. `I` and `L` can be confused with `1`, `O` can be confused with `0`, and `U` is excluded for accidental obscenity. Three of the four exclusions are about eyesight. The fourth is about what 32 symbols can spell when a machine picks them at random, and it is the only one of the four the specification explains in two words. [^2]: The Postgres documentation for [`nextval`](https://www.postgresql.org/docs/current/functions-sequence.html) says the value is not reclaimed if the calling transaction later fails, so that concurrent transactions are never blocked on the same sequence. It adds that this can happen without a failure at all. An `INSERT` with an `ON CONFLICT` clause computes the tuple, takes its number, and may then do nothing with it. A sequence is therefore an upper bound on your row count. [^3]: Postgres 18 ships a [`uuidv7()`](https://www.postgresql.org/docs/current/functions-uuid.html) function of its own, and its documentation describes the layout as a millisecond timestamp, then a sub-millisecond timestamp, then random bits. That is method 3 of section 6.2 of the RFC rather than the counter in method 1: the bits the sample spends counting, Postgres spends on a finer clock. Both keep a burst in order, which is why neither project needs to say the other chose wrong.