Identity, Sequences, and UUIDs
Every Cartwheel table needs a key that is unique, cheap to generate and friendly to a B-tree. Three candidates are worth arguing about: a sequence-backed bigint, a random UUID, and a time-ordered UUID. The gap between the second and the third is measured in dirtied pages, WAL volume and index size — not in preference.
The audit left one item open. orders.id is an integer whose sequence has already issued 1.35 billion of its 2.1 billion values, and the migration that widens it belongs to the next chapter. Deciding what it should be widened to belongs here, along with the related question Cartwheel has never asked: what identifier should appear in the order-confirmation email a customer can forward to anyone.
IDENTITY vs serial
serial is a notational convenience that expands into a sequence, a column default and an ownership link between the two. GENERATED ALWAYS AS IDENTITY is the SQL-standard feature that replaced it, and the sequence it creates is owned by the column as part of the column's own definition.
-- legacy: a sequence, a default, and an ownership link to keep intact id serial PRIMARY KEY -- SQL standard, and what Cartwheel's widened key becomes id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
ALWAYS is the part that earns its keep. With it, an INSERT that supplies its own value is rejected unless the statement explicitly says OVERRIDING SYSTEM VALUE, so a bulk loader that writes explicit ids and leaves the sequence behind fails at the first row instead of producing a duplicate-key storm months later when the sequence catches up. BY DEFAULT lets the supplied value win, a property a data migration wants and an application should never have. serial still works; its weakness is that the ownership link is a separate catalogue fact, and a careless ALTER can sever it.
Sequences Are Non-Transactional
A sequence lives outside the transaction system on purpose. nextval and setval are never rolled back, so gapless assignment is not something a sequence can offer. On top of that, each session allocates a block of CACHE values in one visit and takes any it did not spend with it when it disconnects.
BEGIN;
INSERT INTO orders (customer_id, placed_at, status, total)
VALUES (77219, now(), 'pending', 41.90); -- consumes 1352914699
ROLLBACK;
SELECT last_value FROM orders_id_seq;
1352914699
The rollback undid the row and kept the number. That is the mechanism behind 40 million rows sitting under a sequence at 1.35 billion, and it is not a defect: a sequence guarantees uniqueness and never density. Gapless numbering — an invoice series a tax authority will read — is a different requirement entirely, met by a counter row that every issuing transaction serializes on, which turns concurrent inserts into a queue. With a cache above one you cannot even assume the values arrive in order across sessions, only that they are distinct.
Random UUIDs and the Cost of Randomness
gen_random_uuid() has been built into Postgres since 13, and 18 added uuidv4() as an explicit alias for the same thing. Both produce a version 4 UUID, which is random, and randomness is exactly the property that makes it expensive as a primary key on a table taking millions of rows a day.
A sequence-backed key appends at the right-hand edge of the index, so the pages being modified are a handful that stay resident in shared_buffers and get written once for many inserts. A random key lands in a uniformly distributed leaf, so nearly every insert touches a different page: the index's write working set becomes the entire index rather than its edge, and once that no longer fits in cache each insert reads a page in and dirties it. The write amplification that follows is routinely misdiagnosed as a vacuum problem. Each of those pages costs the full-page image the JSONB topic priced — 8 KB in the write-ahead log for the first change after a checkpoint, on a table taking 4 million rows a day.
uuidv7, Ordered by Time
Since 18, Postgres generates version 7 UUIDs natively. A v7 value carries a 48-bit UNIX millisecond timestamp, then sub-millisecond precision, then randomness, so values generated later compare as greater. The timestamp can be read back out.
SELECT uuidv7(); 019ff9c0-6777-79fb-b466-fa907fa17f9e SELECT uuid_extract_version('019ff9c0-6777-79fb-b466-fa907fa17f9e'), uuid_extract_timestamp('019ff9c0-6777-79fb-b466-fa907fa17f9e'); 7 | 2026-08-13 06:12:44.023+00
Inserts therefore land at the right-hand edge of the index the way a sequence does, while keeping the two properties that made UUIDs attractive: any client can generate one without asking the server, and the next one cannot be guessed. What it gives away is in that second query — a v7 identifier discloses the millisecond it was created to whoever is holding it. For an order reference in a customer's own email that is harmless. For anything whose creation time is itself sensitive, it is a disclosure, and v4 is the honest choice despite the cost. Before 18 the same shape came from an application library rather than the server.
Choosing
A bigint is 8 bytes and a uuid is 16, and that difference is not paid once. It is paid in the table, in the primary key index, in every foreign key column that references the key, and in every index that includes one of those columns. On Cartwheel, order_items.order_id carries the key several times per order, so the eight bytes multiply through the largest tables in the schema. Against that, a sequence value has to come from the server and it leaks: consecutive order ids tell a competitor the order rate, and enumerating other customers' orders takes an increment.
Cartwheel resolves it by refusing to make one identifier do both jobs. Internal keys become bigint GENERATED ALWAYS AS IDENTITY, because nothing in this system generates keys away from the database and the eight bytes matter across 40 million orders and their line items. The identifier customers see becomes a separate uuid column on orders, generated with uuidv7(), unguessable and safe to paste into a URL. Two identifiers, sixteen extra bytes on orders and nothing on order_items.
What a Key Change Costs Later
A primary key type is not a property of one table. It propagates into every foreign key that references it, every index containing those columns, every API contract that returns it, and every integration that stored a copy. Changing it after launch is a coordinated rewrite of several tables plus a client migration, and the headroom left on orders.id is still measured in years rather than months. Chapter 3 takes the schema this chapter produced — corrected numeric and time types, one deliberate document column, a range with an exclusion constraint behind it — and turns it into constraints that make bad rows impossible and migrations that ship without taking Cartwheel down, starting with that widening.
bigint identity — 8 bytes, monotonic inserts, the best index locality available, and it needs the server to allocate it. It also leaks your volume and invites enumeration, so it should stay behind the API rather than appear in a URL.
UUIDv4 — 16 bytes, generated anywhere, unguessable, and the worst possible index locality. The write amplification it causes is measurable on any table taking millions of rows a day, and it is usually blamed on something else.
UUIDv7 — 16 bytes, generated anywhere, unguessable in practice, index locality close to a sequence, and it discloses its own creation time. When distributed generation is a real requirement, this is the version to use; when it is not, bigint is still smaller and faster.
- Using a random v4 UUID as the primary key of a high-insert table and then treating the resulting index growth as a vacuum problem — the cause is insert order, and tuning autovacuum will not touch it.
- Exposing sequential
bigintids in public URLs — the increment tells a competitor your order volume, and walking the range reaches other customers' objects. - Building an invoice series on a sequence and promising it has no gaps — rollbacks and per-session caching produce holes by design, and no setting removes them.
- Keeping
serialin new code because it is shorter — a rewrite that severs the sequence ownership leaves a column default pointing at a sequence that no longer exists. - Storing a UUID in a
textcolumn — 37 bytes instead of 16, no validation on write, slower comparisons, and a primary key index more than twice the size it needed to be. - Declaring an identity column
BY DEFAULTfor application traffic — a client that supplies its own value wins without comment, and the sequence keeps handing out numbers that will collide with it later.
- Use
bigint GENERATED ALWAYS AS IDENTITYfor internal keys unless a specific requirement — offline clients, cross-system merges — says otherwise. - Generate v7 with
uuidv7()whenever a UUID is genuinely required, and fall back to v4 only when the creation timestamp must not be readable. - Store UUIDs in a
uuidcolumn, never intext, so the value is validated on write and the index stays at 16 bytes per entry. - Give customer-facing objects their own external identifier, so the internal key can stay small, sequential and private behind the API.
- Treat gapless numbering as a business requirement with its own serialized counter, and measure what that serialization costs before agreeing to it.
- Reserve
OVERRIDING SYSTEM VALUEfor data migrations, and reset the sequence withsetvalafterwards so the next application insert does not collide.
Knowledge Check
A transaction inserts one order and then rolls back. What happens to the sequence value it consumed?
- It stays spent, because nextval calls are never rolled back
- It is returned to the sequence when the transaction is rolled back
- It is queued for reuse and handed to the next session that asks for one
- It is reclaimed the next time autovacuum processes the orders table
Why does a UUIDv4 primary key cost more write throughput than a bigint identity on a table taking millions of rows a day?
- Each key is twice the size, which doubles the work of every index insert
- Inserts scatter across the whole index instead of concentrating at its edge
- Generating a random UUID is far more expensive than calling nextval
- Comparing two UUID values takes much longer than comparing two integers
What does switching from uuidv4 to uuidv7 for a public order reference give up?
- The ability to generate the value on the client without asking the server
- Unguessability, since consecutive v7 values can be predicted from one another
- Privacy of the creation time, which anyone holding the value can extract
- Compact storage, because v7 values need more bytes than v4 values do
Why does GENERATED ALWAYS beat GENERATED BY DEFAULT for a column the application inserts into?
- ALWAYS allocates values faster because it skips the sequence cache
- It rejects a client-supplied value instead of quietly letting it win
- It guarantees the generated values arrive with no gaps between them
- It makes the column implicitly unique without needing a separate constraint
Cartwheel needs an unguessable identifier for order-confirmation links. Which design does the topic argue for?
- Make the orders primary key itself a uuid so there is only one identifier
- Keep a bigint key internally and add a separate uuidv7 column for links
- Expose a hash of the bigint id, computed in the application on each request
- Store a random uuid in a text column so the value can carry a prefix
You got correct