Primary keys: UUIDv7 and identity bigint

Since your first table, every row has carried id: ...primaryKey() with the value left blank, a placeholder this chapter promised to finish. You finish it here, and it is the most permanent choice in the schema. A primary key is referenced by every foreign key that points at the row, baked into every URL and API response that names it, and effectively un-renameable once real data exists. Get a column type wrong and you fix it next week; get a primary key wrong and you live with it.

So this lesson is about a decision, not syntax. The mechanics are three small shapes you half-know already; the real question is the one an experienced engineer asks first: which primary-key strategy does this table want, and what does the wrong one cost later? Hold onto one concrete fork: would you rather hand customers a URL that reads /invoices/47 or one that reads /invoices/0193b5a1-…? Notice which one bothers you, and why.

What .primaryKey() guarantees

You have written .primaryKey() all chapter. Before choosing what fills it, pin down what it does, because it gives you more than one constraint.

A primary key is the column whose value uniquely names a row. It is the address other tables point at: every foreign key in your schema resolves to exactly one primary-key value, which is one row. .primaryKey() does that job by bundling three constraints into one call:

id: uuid().primaryKey(),

The column is implicitly NOT NULL, because a row with no name has no identity. It is implicitly UNIQUE, because two rows sharing one address would be a contradiction. And Postgres builds an index on it, because every join looks the address up and that has to be fast. That index is a B-tree , and the fact that it is sorted will matter two sections from now.

Caution

Never add a separate .unique() or .index() to a primary-key column. Both are already there, so a second one is redundant, and in a diff it tells a reviewer the author did not know what .primaryKey() includes.

The column’s contract is now fixed. The rest of the lesson is about choosing the value that fills it.

Surrogate or natural: the first fork

The first question applies to every table, before anything else:

Does the database mint this value, or does the outside world already own it?

A surrogate key is a value the database makes up. It carries no meaning beyond being unique: not the email, not the name, just an opaque identifier. A natural key is a value that already identifies the thing in the world: an email address, a URL slug, a country code, an ISBN.

The natural key tempts you because you already have the email, so why mint a second identifier? Here is the reason:

Danger

Domain values change. When a primary key changes, the change has to propagate through every foreign key pointing at it.

Suppose email is the primary key of your users table, and a hundred rows across the schema (invoices, sessions, audit entries) point at users by email. A user updates their email, a one-line edit. But that email is the value those hundred rows hold, so every one of them has to be rewritten in lockstep or its foreign key points at nothing. A routine profile edit becomes a schema-wide cascade. The same trap waits on a renamed slug or a reissued “permanent” code.

So the default is to use a surrogate wherever a value is user-facing. The natural-key exception is narrow: genuinely immutable, externally defined identifiers like ISO country codes ('US'), currency codes ('USD'), and ISBNs, fixed by an outside authority and never reissued under you. Even then, choose a natural key only when you are certain you will never want to rename the value.

One heuristic settles the call. Before you make any value a primary key, ask:

Would I be comfortable if this value changed, and every row pointing at it had to change too?

If the answer is anything short of a confident yes, use a surrogate. A column type you can tighten later, but a primary key is a promise you can’t take back.

slug: text().primaryKey(),                     // bad — a slug gets renamed, and the rename cascades
id: uuid().primaryKey(),                        // good — surrogate id, with a unique on slug instead

One mistake is worth naming now. Soon you will meet a way to make a primary key span two columns, and it is tempting to key an entity like a page by (orgId, slug): a page is unique within its org, so why not? Resist it. A composite key on a first-class entity is a tenancy-modeling mistake. Give the entity a surrogate id and add a unique(orgId, slug) constraint instead. Composite primary keys have exactly one good home, and we will get there.

Why UUIDv4 scatters inserts and UUIDv7 doesn’t

Once you have settled that user-facing entities want a surrogate, one question remains: a UUID or a bigint? Start with the UUID, because the obvious UUID is a trap and a newer one isn’t, and the difference stays invisible until it causes a serious problem in production.

A UUID is a 16-byte value, written as 32 hex digits. The two versions you care about look identical: same length, same hex, same - grouping. What differs is where a new one lands in the primary-key index, which is a sorted B-tree.

UUIDv4 is fully random. Every byte is noise, so each new id lands at a random spot in the sorted tree. Insert a thousand rows and you scatter a thousand writes across the index, each one potentially splitting a page to wedge the value into place. On a small table whose index fits in memory you never notice. But this is write amplification that compounds with table size: free in development, brutal in a populated production table, expensive at the exact moment you can least afford to change it.

UUIDv7 makes the value time-sortable. It prefixes the 16 bytes with a 48-bit millisecond timestamp, then fills the rest with randomness, which still gives you global uniqueness and non-guessability. Because of the prefix, a value minted now sorts after every value minted before it, so new ids all land at the tail of the index, appended in order like an auto-incrementing integer. You keep everything good about a UUID and recover sequence-like insert performance.

UUIDv4

random 16 bytes

f47ac10b -58cc-4e7a-… 9c3e6d22 -58cc-4e7a-… 2b6f04a1 -58cc-4e7a-… Leading bytes are random → every insert lands at a random spot → writes scatter across the whole index.

UUIDv7

timestamp (ms) ~6 bytes

random ~10 bytes

0193b5a1 -58cc-7e7a-… 0193b5a2 -58cc-7e7a-… 0193b5a4 -58cc-7e7a-… Shared timestamp prefix → new values sort after old ones → inserts append at the tail.

Same 16 bytes, same hex. Only the timestamp's position differs, and with it where new inserts land.

UUIDv7 was standardized by RFC 9562 in May 2024, and Postgres 18, which your project runs on via Neon, ships a native uuidv7() function with no extension to install. Before the standard, libraries like ULID and KSUID solved the same problem and still appear in older codebases; on Postgres 18, uuidv7() is the answer.

Watch video

Database Star walks through auto-increment, UUIDv4, and UUIDv7 in about ten minutes, backing the scatter-versus-tail story with a Postgres demo.

Caution

Drizzle’s .defaultRandom() generates a UUIDv4, the random one with the locality problem above, not the v7 path. Until a native v7 helper ships, reach for the SQL default in the next section, never .defaultRandom().

The three canonical shapes

The mechanics come down to three shapes, each paired with the trigger that should make you reach for it. The question to answer is when you write each one; the how is a single line every time.

UUIDv7

id: uuid().primaryKey().default(sql`uuidv7()`),

The default for any entity whose id is seen outside the database, whether in a URL, an API response, or a client payload. The default fires SQL-side through default(sql\uuidv7()`), so Postgres mints the id, and it does so for migrations, seed scripts, and a raw psqlinsert too, just as the previous lesson argued SQL-side defaults should.sqlis the tagged template from the generated-columns work, imported fromdrizzle-orm`.

identity bigint

id: bigint({ mode: 'number' }).primaryKey().generatedAlwaysAsIdentity(),

The choice for high-volume internal tables: an auto-incrementing 8-byte integer minted by Postgres from a sequence. Reach for it only when all of these hold: the table is high-volume and internal (an event log, analytics rows, a junction nobody fetches by id), the id never crosses a system boundary, and sharding is not on the roadmap. A bigint wins here because 8 bytes instead of 16 gives a tighter index, and since no outsider enumerates these rows, the leak and locality arguments for UUIDs do not apply. mode: 'number' maps the column to a JS number, safe up to 2⁵³.

natural key

code: text().primaryKey(), // ISO-3166, e.g. 'US' — immutable, externally defined

The narrow exception: a real domain value carrying .primaryKey() directly, for immutable external identifiers only. It belongs to a small, static reference table (countries, currencies) whose rows are owned by an outside authority and never renamed, never a user-facing entity. For anything a user creates or edits, use a UUID instead.

Two of the three are one decision seen from two sides: once you know the id is exposed, choose UUIDv7; once you know it stays internal, choose identity bigint. The natural key is the escape hatch, the rare case where the outside world already owns an immutable value worth keying on.

Two footnotes. If your team is not on Postgres 18 and has no native uuidv7() to call, mint the value in app code instead: id: uuid().primaryKey().$defaultFn(() => uuidv7()), with uuidv7 from the npm package of the same name. The result is the same id, but it runs app-side only, so it will not fire for a raw psql insert or a seed that bypasses Drizzle, which is why the native SQL-side version is this course’s default. For the bigint, generatedAlwaysAsIdentity() is the modern SQL-standard identity column ; the legacy bigserial still works, but write generatedAlwaysAsIdentity in new code.

The decision tree

You have the three shapes. What transfers is the order to ask the questions in: which one to ask first, and what each answer rules out. Walk the tree one click at a time, committing to an answer before you read the next, so you feel the questions narrow.

Which primary key does this table want?

UUIDv7 is the default you land on unless you have a specific reason to step off it. bigint identity is an opt-in you can defend, not a per-table coin flip. When you genuinely can’t decide, the tree decides UUIDv7 for you.

Composite primary keys for junction tables only

A primary key can span more than one column. You will need this later in the chapter for the table that links invoices to tags, so here is the syntax and the boundary around it.

You declare a composite key in the third argument of pgTable, the callback that returns table-level constraints:

db/schema.ts

export const invoiceTags = pgTable('invoice_tags', {
  invoiceId: uuid().notNull(),
  tagId: uuid().notNull(),
}, (t) => [primaryKey({ columns: [t.invoiceId, t.tagId] })]);

This primaryKey is the standalone import from drizzle-orm/pg-core, distinct from the .primaryKey() method you chain on a single column. It makes the combination of invoiceId and tagId the unique, indexed key, with the same three guarantees as a single-column key. An invoice can have many tags and a tag can sit on many invoices, but each pairing appears at most once. That is exactly the identity a join row has.

Danger

Composite primary keys belong on junction tables and almost nowhere else. On a junction the pair is the row’s identity, so there is nothing else to mint. A first-class entity is the modeling mistake from earlier: give it a surrogate id plus a unique constraint instead.

The difference is whether the row has an identity of its own. A junction row exists only to connect two things, so “this invoice, that tag” is all it is. An invoice or an organization is a thing in its own right, and deserves its own opaque, stable id.

Practice: pick the key for each table

Two passes: first sort each table into a strategy, then write the shapes.

Start by sorting each table’s id column into the strategy it should use. Ask the two questions in order: is the id exposed, and if not, is the table high-volume and internal?

Sort each table's id column into the primary-key strategy it should use. Drag each item into the bucket it belongs to, then press Check.

UUIDv7 Exposed, ours to mint

identity bigint Internal, high-volume

natural key Immutable, externally owned

An invoices row shown at /invoices/:id

A users row returned in an API response

An organizations row whose id is in every client payload

An append-only audit_logs row nobody fetches by id

A high-volume analytics_events row, internal-only

An ISO-3166 country code ('US')

An ISO-4217 currency code ('USD')

A US state abbreviation ('CA')

Check Reset

Now write the shapes. The starter stubs the id (or code) column on three tables: organizations is user-facing, auditLogs is internal and high-volume, and countries is a reference table the outside world owns. Finalize each with the right shape from the variants above. Column names are passed as explicit snake_case strings because the editor has no casing client.

Note

The checker reads the shape of each column: its type, its .primaryKey(), and whether it carries a database-side default. That catches the two real mistakes here: an id with no generated default, or audit_logs keyed on the wrong integer type. It can’t see which default expression you wrote, since uuidv7() and the v4 gen_random_uuid() look identical to it, so the prose above is the grader for that part: organizations.id wants uuidv7(), never .defaultRandom(). The mini-Postgres running here predates native uuidv7(), so a correct shape shows a green checklist alongside a “function uuidv7() does not exist” note from the sandbox; real Postgres 18 runs it.

Finalize each id with the primary-key strategy that table wants. organizations is user-facing — its id rides in every URL and API response. auditLogs is internal and high-volume — nobody ever fetches a row by its id. countries.code is an ISO-3166 value the outside world owns and never reissues. Write the canonical Drizzle shape for each; the column names are spelled out in snake_case because there's no casing client in scope.

Check schema Feedback Reset

Reveal the answer

db/schema.ts

export const organizations = pgTable('organizations', {
  // Exposed, ours to mint → UUIDv7. The SQL-side default fires for migrations,
  // seeds, and psql too — not just inserts that go through Drizzle.
  id: uuid('id').primaryKey().default(sql`uuidv7()`),
  name: text('name').notNull(),
});

export const auditLogs = pgTable('audit_logs', {
  // Internal, high-volume, never enumerated by an outsider → identity bigint.
  // 8 bytes, a tighter index, and the id never crosses a system boundary.
  id: bigint('id', { mode: 'number' }).primaryKey().generatedAlwaysAsIdentity(),
  action: text('action').notNull(),
});

export const countries = pgTable('countries', {
  // Immutable, externally owned → the natural key is correct as-is. Don't
  // "fix" it into a surrogate; the ISO code already names the row, forever.
  code: text('code').primaryKey(),
  name: text('name').notNull(),
});

Where to read more

Native UUIDv7 in Postgres is recent, so these primary and current sources are worth a look. They cover the Postgres 18 support and the locality argument in more depth than this lesson does.

PostgreSQL 18 UUIDv7 support

neon.com

Neon's explainer on the native uuidv7() function, with the v4-scatter-vs-v7-locality argument in depth.

PostgreSQL 18 release announcement

postgresql.org

The primary source: the official release note introducing native uuidv7() generation.

UUIDv7 generator & decoder

authgear.com

Mint a UUIDv7 and watch the tool pull the timestamp back out — the byte layout figure, made interactive.

RFC 9562 — UUID specification

datatracker.ietf.org

The 2024 IETF standard itself; Section 5.7 is the formal UUIDv7 layout this lesson summarized.

Every id column now has a shape you can defend. Next come foreign keys: you write the .references() that points one row’s column at another row’s primary key, and decide what happens to the children when the parent is deleted.