When you create a table in the Supabase Studio table editor, it hands you an id column of type uuid with a default of gen_random_uuid(). Most people accept it and move on. But that one default decision propagates into every foreign key, every index, and every join for the life of your database — and on a self-hosted instance where you own the disk, the RAM, and the backup bill, the difference between a UUID and a BIGINT primary key is something you actually pay for.
This guide breaks down the three realistic choices for a self-hosted Supabase primary key — random UUID, BIGINT identity, and the new time-ordered UUIDv7 — with honest trade-offs on performance, storage, and security. If you're still setting up your instance, start with the installation guide; if you already have tables in production, the migration notes at the end are for you.
Why Supabase defaults to UUID (and why that's defensible)
Postgres ships no default primary key type at all — you choose. Supabase chose uuid, and the reasoning is sound for a platform built to help people ship fast:
- No coordination needed. A client, an edge function, or two separate services can all generate IDs without asking the database first. That matters for offline-first apps and distributed writes.
- They don't leak information. A sequential
BIGINTin a URL tells a competitor exactly how many users you have and lets anyone enumerate/orders/1001,/orders/1002, and so on. UUIDs make that guessing game impractical — a real defense-in-depth win that pairs with, but does not replace, your row-level security policies. - They play nicely with
auth.users. Supabase Auth already keys users by UUID, so foreign keys back to a user are UUIDs whether you like it or not.
For the majority of application tables, UUID is the least dangerous default. The problem is the kind of UUID.
The hidden cost of random UUIDs
The classic gen_random_uuid() produces a UUIDv4 — 122 bits of randomness. Two real costs follow, and both hit self-hosters harder because you're the one provisioning the hardware.
1. Size. A UUID is 16 bytes; a BIGINT is 8. That sounds trivial until you remember the primary key is copied into every index, every foreign key column, and every index on those foreign keys. Double the key width and you roughly double the B-tree overhead across the whole schema. Bigger indexes mean less of your working set fits in shared_buffers, which means more disk reads on a box that probably has less RAM than a managed cloud tier.
2. Insert locality. This is the one that surprises people. Because v4 values are random, each INSERT lands in a random leaf of the primary key's B-tree. Postgres has to read, modify, and dirty a different page almost every time, causing page splits and cache churn. A BIGINT, by contrast, always appends to the rightmost page — cheap and cache-friendly.
The numbers are not subtle. In a widely-cited 50-million-row benchmark, a bulk insert took ~20 minutes with random UUIDv4 versus ~1.8 minutes with a time-ordered UUID, and the resulting index was about 25% smaller (Neon). If you've ever watched a large migration or seed crawl on your self-hosted box, random keys are a prime suspect. This interacts directly with index health and bloat — random inserts fragment indexes faster and lean harder on autovacuum.
BIGINT: still the right call sometimes
Don't let the security argument scare you off integers entirely. BIGINT GENERATED ALWAYS AS IDENTITY is the fastest, smallest, most cache-friendly option Postgres offers, and it's the correct choice for:
- High-volume internal tables — event logs, time-series rows, join tables, analytics fact tables — that are never exposed by ID in a public URL.
- Append-heavy workloads where insert throughput on modest hardware is the bottleneck.
- Tables you'll partition later. Monotonic integer keys make range partitioning trivial.
create table events ( id bigint generated always as identity primary key, actor uuid references auth.users (id), kind text not null, data jsonb, at timestamptz not null default now() );
Note the pattern: a BIGINT primary key for the high-volume table, but a UUID foreign key back to auth.users. Mixing types per-table is not a code smell — it's the point. Use the key that fits the table's access pattern, not one global rule.
UUIDv7: the option that changes the math in 2026
The old debate was "UUID for safety, BIGINT for speed — pick your pain." UUIDv7 collapses most of that trade-off. Specified in RFC 9562, a v7 UUID puts a millisecond timestamp in its most significant bits, so values generated over time are sortable and insert sequentially into the B-tree — just like a BIGINT — while keeping the global uniqueness and no-coordination benefits of a UUID.
PostgreSQL 18 ships a native uuidv7() function (PostgreSQL 18 docs via Neon), and its implementation even uses sub-millisecond precision to stay monotonic within the same millisecond. This is directly relevant to self-hosters right now: the default db image in the self-hosted docker-compose.yml moved from Postgres 15 to Postgres 17 in June 2026, and Postgres 18 is next on that track. Once you're on 18:
create table documents ( id uuid primary key default uuidv7(), owner uuid references auth.users (id), title text not null, created timestamptz not null default now() );
Still on Postgres 17 or earlier? You don't have native uuidv7(), but you can get 90% of the benefit today with the pg_uuidv7 extension or a small SQL function, then swap to the native call after you upgrade. Because the on-disk type is still uuid, moving from gen_random_uuid() to a v7 generator is a default change only — no column rewrite, no foreign key churn.
One caveat worth stating plainly: UUIDv7 leaks the creation timestamp of the row. That's usually harmless, but if the exact insert time is sensitive, stick with v4 for that specific table.
A practical decision framework
For a typical self-hosted Supabase app:
| Table type | Recommended key |
|---|---|
| User-facing entities (posts, orders, projects) exposed by ID in URLs | UUIDv7 (or v4 if timestamps are sensitive) |
Anything foreign-keyed to auth.users | UUID — you have no choice, and that's fine |
| High-volume internal / log / join tables | BIGINT identity |
| Small lookup/reference tables | Either — it doesn't matter at that scale |
The meta-rule: default new tables to UUIDv7, drop to BIGINT for hot internal tables, and never expose a sequential integer in a public URL. Then confirm your choice held up under real load — see PostgreSQL performance tuning for self-hosted Supabase for measuring index size and buffer cache hit rates on your own box.
Migrating an existing table
Changing a primary key type on a live table is a genuine rewrite — every foreign key referencing it must change too, so treat it as a real schema migration with a tested backup, not an afternoon ALTER. The good news: if you're only switching the generator (v4 → v7) and not the type, it's a one-line default change with zero rewrite. That alone speeds up future inserts and shrinks index growth without touching a single existing row.
Whatever you change, do it behind a backup you've actually restored once. Self-hosting means the safety net is yours to build — Supascale automates scheduled S3 backups with one-click restore so a schema experiment gone wrong is a five-minute rollback instead of a bad night. You can compare that against rolling your own on the pricing page.
Conclusion
Supabase's UUID default is a reasonable starting point, not a law. On self-hosted infrastructure where you pay for every byte of index and every page split, the right move in 2026 is usually UUIDv7 — you keep the safety and portability of UUIDs while recovering nearly all the insert and cache performance of an integer. Reserve plain BIGINT for high-volume internal tables, keep UUIDs wherever you touch auth.users, and never ship a guessable sequential ID in a public URL. Pick per table, measure on your own hardware, and the default in the Studio table editor stops being a decision you inherited and becomes one you actually made.