Choosing a Primary Key: Deciding Between bigint, UUIDv4 and UUIDv7

The primary key is usually the least considered decision when a new table is created. People either type an auto-incrementing integer out of habit or reach for a random UUID because "distributed systems use UUIDs". Both choices can become expensive years later. A leaked integer tells the world how many customers you have, and a 32-bit column that fills up forces a table rewrite. A random UUID scatters index writes across a large table, wastes cache and inflates WAL volume. This post compares the three common options, says which one fits where, and lists the traps people actually fall into.

A key has two jobs

A primary key does two separate things, and most problems come from mixing them up.

The first is storage identity: the value the database uses to locate a row, join it through foreign keys and index it. Here you want something small, ordered and cheap to compare.

The second is public identity: the value that appears in URLs, API responses and support tickets. Here you want something unguessable, something that does not reveal business volume and something that can move between systems.

One column can do both, but you need to know when it cannot.

Option 1: bigint with an identity column

In PostgreSQL the recommended form today is the standard SQL identity column rather than serial:

CREATE TABLE orders (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    created_at timestamptz NOT NULL DEFAULT now()
);

GENERATED ALWAYS stops the application from writing values by hand, which closes the classic failure where someone inserts explicit ids, the sequence falls behind and the next insert hits a unique violation. Eight bytes keep the index compact, every new row lands on the right edge of the index and the hot pages stay in memory.

Its weaknesses are all external. A sequential value in a URL exposes your order volume and growth rate. You cannot merge rows produced by two different databases, and you cannot assign an id on the client before the row reaches the database.

Option 2: UUIDv4

It carries 122 random bits, collisions are not a practical concern, it can be generated anywhere and it leaks nothing. As a public identifier it is excellent.

As a primary key on a large table it has a cost. Each new value lands on a random leaf page of the B-tree. While the table fits in memory nobody notices. Once the index outgrows shared buffers, nearly every insert needs a cold page from disk, page splits increase and the index ends up less densely packed. In PostgreSQL the first change to any page after a checkpoint writes the full page to WAL, so inserts scattered across the index generate far more WAL for the same amount of data. That comes back as replication lag and larger backups.

In engines where the primary key is the clustered index, such as MySQL InnoDB, the effect is harsher: rows are physically stored in key order and every secondary index carries the primary key, so a 16-byte random key enlarges every secondary index too.

Option 3: UUIDv7

UUIDv7, defined in RFC 9562, puts a millisecond Unix timestamp in its first 48 bits and fills the rest with random bits. It is still a 128-bit UUID that can be generated anywhere, but it increases over time. New rows pile up on the right edge of the index and write behavior gets close to bigint.

PostgreSQL 18 ships a built-in uuidv7() function:

CREATE TABLE documents (
    id uuid PRIMARY KEY DEFAULT uuidv7(),
    title text NOT NULL
);

On older versions you generate the value in the application; maintained UUID libraries in most languages can produce v7 now. Either way, the column should use the native uuid type.

Which one to pick

Here is the opinion: use UUIDv7 as the default for new projects. Needs like generating ids in several places, merging data between systems or assigning ids on the client tend to appear later, and changing a primary key at that point is very expensive. UUIDv7 keeps that flexibility while removing most of the write penalty of random UUIDs.

bigint is the better choice for internal tables whose ids are never exposed, for event tables with billions of rows referenced by many foreign keys (eight bytes per reference adds up), and for systems that will clearly never leave a single database.

The only good reason left to choose UUIDv4 as a primary key is when it is unacceptable for the creation time to be readable from the id. Even then, the better design is often an ordered storage key plus a separate v4 column for public use.

Traps

Storing UUIDs as text. A varchar(36) or char(36) column takes more than twice the space of the native type, comparisons slow down under collation rules, and the same value in upper and lower case can behave like two different rows. If there is no native type, use a 16-byte binary column.

Starting with int. A signed 32-bit integer runs out at about 2.1 billion. Deleted rows and rolled-back transactions also consume values, so you reach the limit sooner than the row count suggests. Converting the column to bigint later rewrites the table in PostgreSQL and requires changing every foreign key column that points to it. Choosing bigint up front costs almost nothing.

Assuming the sequence has no gaps. Sequence values are not returned when a transaction rolls back, and cached values can be skipped after a restart. Do not derive a number that must be gapless by law, such as an invoice number, from the primary key. Produce it with a dedicated counter, under a lock if necessary.

Believing UUIDv7 gives a strict order. The timestamp has millisecond resolution and clocks on different machines drift. Two ids generated on different servers within the same millisecond have no defined order. Do not build "latest record" queries on id order; use a real timestamp column and a tiebreaker if you need one.

Forgetting that UUIDv7 leaks time. Anyone who sees the id can read when the record was created. If an account's signup date or a document's creation moment is sensitive, expose a v4 for those records.

Treating unguessable ids as authorization. A random id makes it harder to reach someone else's record but does not prevent it: ids end up in logs, shared links and browser history. Check on the server, on every request, that the record belongs to the caller. The format of an id is not a security layer.

Encoding integers to get short ids. Libraries that turn sequential integers into short strings provide obfuscation, not encryption. If the alphabet and parameters are known, the encoding can be reversed. If the goal is hiding volume, this is not enough.

Changing an existing system

Instead of replacing the primary key of a live table, adding a new column is usually enough. Add a uuid column with a default, backfill old rows in small batches, build the unique index concurrently and start exposing that column to the outside world. Internal foreign keys can stay bigint. You change the public identifier without one big lock or a full table rewrite.

When not to bother

On tables that will stay under a few hundred thousand rows and fit comfortably in memory, you will not be able to measure the write difference between v4 and v7. There, decide based on what you expose and whether data needs to move between systems, not on performance. Migrating a schema that works fine to UUIDv7 just to be modern is not worth the migration risk either. Do not change a key before you have measured a problem.