0
Challenge · 15SQL II: Tables, Types & Constraints
Primary keys: identity vs uuid
Schemaused in ~90% of apps
Every row needs a name that never changes and never repeats — that's the primary key's whole job. But the two mainstream ways to generate one make opposite tradeoffs: a small counting number is cheap for the index and trivial for a stranger to guess; a random 128-bit value is unguessable and scatters across the index. Pick wrong on the table an attacker can reach directly, and you've built an enumeration bug into the schema itself, before a single line of app code exists.
Core idea. GENERATED ALWAYS AS IDENTITY gives you a small, tightly sequential integer — cheap to index, but 1, 2, 3… is guessable by anyone who owns row 40 and tries 41. uuid with gen_random_uuid() gives you a 122-bit random value — unguessable, but scattered across the keyspace in a way that costs the index locality. uuidv7() promises both — time-ordered, so it indexes like an identity column while still being hard to guess — but it only exists in your Postgres 18 sandbox today; Supabase's Postgres 17 does not have it yet, and a migration that uses it errors the moment it runs for real.
poke it, drag it,
try to break it
try to break it
no slides here.
you drive.
you drive.
Playground, this isn't a screenshot
id bigint generated always as identity primary key
keyspace — lowthe six inserts, in order, land herehigh
1
2
3
4
5
6
#1 1#2 2#3 3#4 4#5 5#6 6
Index locality
Strictly increasing — every insert appends to the right edge.
Guessable / enumerable
Yes — 1, 2, 3… an attacker who owns row 40 can try 41.
On Supabase today
Everywhere, always has been.
The one rule under all of it: a small, sequential key is cheap to index but easy to guess; a random key is unguessable but scatters the index. Time-ordered UUIDs (v7) exist to get both — but only IDENTITY and plain UUID (v4) are guaranteed to work on your actual Supabase project today.
↑ play a little first, then dig in