Skip to content

Keys and constraints: UUIDv4, UUIDv7 or bigint, and constraints as the last line of defence

Pick a key that sorts by time; let constraints refuse bad rows, and know where NULLs, row-by-row checks and range ends slip through.

Predict first

Before you read: predict

ExamHall's answers table has a b-tree primary key. Three test databases each load the same 100,000 answers in arrival order, then take 1,000 more, one insert at a time. Only the key differs: bigint GENERATED ALWAYS AS IDENTITY, a UUIDv4 the app generates, or a UUIDv7 the app generates. (A handful means under 10.)

Afterwards, how many different leaf pages of each primary-key index hold the last 1,000 answers?

Pick one

The 30-second version

A primary key is a B-tree, so each insert lands where its key sorts. Identity numbers and UUIDv7 grow with time and append at the right edge; UUIDv4 is random and lands anywhere. Constraints are checked inside every write. Watch their edges: what a NULL compares with, when a UNIQUE is checked, and where a range ends.

Pick a key that sorts by time; let constraints refuse bad rows, and know where NULLs, row-by-row checks and range ends slip through.

How it works

Step 1 of 13

Surrogate or natural. A natural key (a handle, an email) can change; a surrogate id never has to change.
In the kitchen
  • each hut gets the office's own number; the name painted on its door changes when it is sold → a surrogate key that never has to change (step 1)

Analogy: A seaside council's beach-hut office and its register binder (seaside leisure)

The office files every hut in one binder of pages, sorted by hut number.

At the beach-hut office In the database
each hut gets the office's own number; the name painted on its door changes when it is sold a surrogate key that never has to change (step 1)
the binder's pages, sorted by hut number the primary key's leaves (step 2)
numbers issued in order: every new hut goes on the last page identity and UUIDv7 (steps 3, 6)
numbers drawn from a hat: each new hut goes on a random page, and a full page is split in two UUIDv4 and its splits (steps 4–5)
the clerk refuses a second letting of a hut for a week already let, whoever asks a constraint checked on every write (step 9)
a letting marked "hut to be assigned" names no hut the clerk can compare NULL in a key column (step 11)
a letting covers its arrival morning but not its departure morning a range that includes its start and excludes its end (step 13)

In real terms: choose a key that new rows append to, and let the table refuse what must never be stored.

Breaks down when: a clerk sees two requests arrive together and asks which one was meant; PostgreSQL checks each row as written and knows nothing of intent (steps 10, 12).

Examples, run for real

4.1 ExamHall EXH-3840 → MR !4140: one live attempt per candidate and exam

ExamHall's NestJS service started an attempt by checking for a live one, then inserting. Two taps on Start ran both checks before either insert. MR !4140 adds a partial unique index over status = 'in_progress':

4.1 EXH-3840 as shipped (check, then insert)     request A sees 0, request B sees 0 · A's insert ok · B's insert ok · live attempts 2
4.1 MR !4140 (partial unique index added)        request A sees 0, request B sees 0 · A's insert ok · B's insert refused 23505 · live attempts 1

Both checks saw an empty table, because neither insert had happened yet (step 10). With the index, the second insert is refused with 23505, and the service answers with the attempt already running.

4.2 Media streaming: one table, three keys

A video platform's views table is loaded with 100,000 rows in arrival order, once per key type:

4.2 views keyed by bigint 100,000 rows · primary-key leaf pages 274 · rows per leaf 365
4.2 views keyed by v4     100,000 rows · primary-key leaf pages 530 · rows per leaf 189
4.2 views keyed by v7     100,000 rows · primary-key leaf pages 384 · rows per leaf 260

Identity keys are 8 bytes, so more fit on a leaf. Both UUID keys are 16 bytes, yet v4 needs more leaves than v7 for the same rows: its splits happened all over the index and left room behind (steps 4–5).

4.3 Media streaming: what each constraint refuses

4.3 NOT NULL     a video with no channel     refused 23502
4.3 UNIQUE       a second @cookalong         refused 23505
4.3 FOREIGN KEY  a video for channel 9       refused 23503
4.3 CHECK        a video of 0 minutes        refused 23514
4.3 EXCLUDE      a second premiere at 18:30  refused 23P01

Each refusal names its kind, so the application can answer the user: a taken handle (23505) is not a missing channel (23503) (step 9).

Try it

Answer first, then check. Nothing is saved.

Question 1 of 6

A video platform soft-deletes accounts:

CREATE TABLE accounts (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email text NOT NULL, deleted_at timestamptz, UNIQUE (email, deleted_at));

The table holds [email protected]'s live account (deleted_at NULL) and [email protected]'s account, deleted at 2026-09-01 08:00 UTC. Three inserts run, each on its own against those two rows:

  1. INSERT INTO accounts (email) VALUES ('[email protected]')
  2. INSERT INTO accounts (email) VALUES ('[email protected]')
  3. INSERT INTO accounts (email, deleted_at) VALUES ('[email protected]', '2026-09-01 08:00+00')

Which does PostgreSQL refuse?

Pick one

Question 2 of 6

ExamHall's schema has attempts (id bigint PRIMARY KEY, …) and answers (id bigint PRIMARY KEY, attempt_id bigint REFERENCES attempts (id), choice text). After last night's import, a data check SELECT count(*) FROM answers a WHERE NOT EXISTS (SELECT 1 FROM attempts t WHERE t.id = a.attempt_id) returns 37. The foreign key was there all along.

Which explains the 37?

Pick one

Question 3 of 6

A music app keeps playlist_tracks (playlist_id bigint, track_id bigint, position int, UNIQUE (playlist_id, position)). Playlist 7 holds five tracks at positions 1 to 5, inserted in that order. To put a new track first, the app runs UPDATE playlist_tracks SET position = position + 1 WHERE playlist_id = 7, then inserts the new track at position 1. The UPDATE fails.

Which change lets the move succeed while the table still refuses two tracks at one position?

Pick one

Question 4 of 6

A live-streaming service books its one studio with bookings (show text, during tstzrange, EXCLUDE USING gist (during WITH &&)). The table holds one show on 20 October, stored as tstzrange('2026-10-20 10:00+00', '2026-10-20 11:00+00'). Three more are inserted the same way, each on its own against that one row: 11:00–12:00, 10:30–10:45 and 09:00–10:00.

Which does PostgreSQL refuse?

Pick one

Question 5 of 6

ExamHall runs two exam centres, each issuing certificates from its own database; at the end of a sitting each issues hundreds a second, and the two servers share one clock. Next year the two certificates tables merge into one.

Which primary key, generated at each centre, is unique across both centres' rows and sorts in the order the certificates were issued?

Pick one

Question 6 of 6

A video platform lets a channel change its handle. The channel @rivertales has 40 videos and renames itself to @riverstories. The team wants three things: the rename writes one row; all 40 videos still join their channel; and a second channel can never take a handle already in use.

Which schema gives all three?

Pick one

Go deeper

Pro tip
  1. Check what your ids really are. On PostgreSQL 17 or 18, uuid_extract_version(id) returns 4 or 7 and uuid_extract_timestamp(id) the time inside a v7 id. When: after switching an ORM or a generator to v7, before trusting that inserts append (steps 6–7).
Sources

Related lessons

Checked against the tools and docs as of 2026-10-13.