0

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.
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