Primary Keys in PostgreSQL: UUID vs BIGINT

Every table in the last article had a primary key, and I quietly used BIGINT GENERATED ALWAYS AS IDENTITY without explaining the choice. That was deliberate, because the primary-key decision deserves its own article. It's one of the few schema choices that touches storage size, index performance, URL design, and how easy it is to merge data from two systems, all at once.

This is part 3 of the PostgreSQL Masterclass, following schema design. The question is narrow and the answer has real tradeoffs: should your primary key be a sequential integer or a UUID?

The two honest options

For a surrogate primary key, a value with no business meaning that just identifies the row, you have two good choices in modern Postgres.

The first is an identity column backed by a sequence:

CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

The second is a UUID, generated by the database:

CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid()
);

Before comparing them, one note on style. Use GENERATED ALWAYS AS IDENTITY rather than the old SERIAL pseudo-type. SERIAL still works, but it has quirks around ownership and permissions that identity columns fixed, and identity is the SQL-standard spelling. If you're on Postgres 10 or newer, prefer it.

Here's the tradeoff at a glance before the details:

Where BIGINT wins

Sequential integers have three advantages, and they're not small.

Storage is the obvious one. A bigint is 8 bytes; a uuid is 16. That difference is invisible on the row itself and very visible on indexes. Every index on the table stores the primary key, and every foreign key that references this table stores a copy in another table's index. Double the key width and you roughly double that overhead across the whole schema.

Insert performance is the subtler one. A B-tree index stays efficient when new keys arrive in roughly increasing order, because inserts land at the right edge of the index and pages fill neatly. Sequential integers do exactly that. Random UUIDs land all over the index, which causes more page splits and worse cache behavior on write-heavy tables. On a table taking thousands of inserts a second, this is measurable.

Readability rounds it out. order 48213 is easy to say on a support call. order f47ac10b-58cc-4372-a567-0e02b2c3d479 is not.

Where UUID wins

UUIDs solve problems that sequential integers can't.

They're generatable anywhere. A client, a queue worker, or a service can create a UUID before the row exists in the database, which means you can assign an ID, reference it, and insert later. With a sequence, the database has to hand you the ID, so you can't know it until after the insert.

They don't leak business information. A sequential ID in a URL tells anyone that /invoices/1052 is your 1052nd invoice, and that /invoices/1053 probably exists. That's a slow information leak competitors and scrapers happily read. UUIDs expose nothing.

They merge cleanly. If you ever combine two databases, or shard, or sync records across systems, sequential IDs collide because both systems started counting at 1. UUIDs are designed to be globally unique, so two independently generated sets don't clash.

The pattern that gets you both

You don't always have to choose. A common and effective pattern is to keep a bigint identity as the internal primary key, and add a separate UUID for anything that faces the outside world:

CREATE TABLE invoices (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  public_id   UUID NOT NULL DEFAULT gen_random_uuid() UNIQUE,
  org_id      BIGINT NOT NULL REFERENCES organizations(id),
  total       numeric(12, 2) NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

Internally, foreign keys and joins use the compact, fast bigint. Externally, URLs and API responses use public_id, which leaks nothing and can be generated by clients. You pay for one extra unique index, and in return you get the storage and insert benefits of integers with the privacy and portability of UUIDs. For a lot of production systems this is the right default.

If you do go all-UUID, use the right kind

Sometimes a single UUID key is genuinely simpler, especially in distributed systems where client-side generation matters more than raw insert throughput. If you go that way, be aware that the insert-ordering problem has a fix.

The random UUIDs from gen_random_uuid() are version 4, fully random, which is what causes the scattered index inserts. UUID version 7 encodes a timestamp in the high bits, so the values sort roughly by creation time and behave much more like sequential keys in a B-tree. Postgres added a built-in uuidv7() function in version 18. On earlier versions you can generate v7 in the application layer or with an extension.

-- Postgres 18+
CREATE TABLE events (
  id UUID PRIMARY KEY DEFAULT uuidv7()
);

If you want UUID keys and you're worried about write performance, v7 gets you most of the way back to the insert behavior of an integer while keeping global uniqueness.

A decision you can actually use

Here's how I'd choose without overthinking it:

• Internal-only table, high write volume, no cross-system merging: bigint identity.
• Anything with IDs in URLs or public APIs: bigint internal key plus a uuid public_id.
• Distributed system where clients generate IDs, or you expect to shard: a single uuid key, ideally v7.

The wrong move is storing a UUID as text, which wastes 21 bytes per value and skips validation. If you use UUIDs, use the uuid type.

The primary key is hard to change once a table is large and other tables reference it, so it's worth the 5 minutes of thought up front. Get it right and you rarely think about it again.

Next in the series, we look at constraints: the rules that keep the data honest no matter what the application code does.