UUIDv7 vs UUIDv4 vs Serial: Which Primary Key in 2026?

Quick Answer: Which Primary Key in 2026?

For new projects on PostgreSQL 18 or later, use UUIDv7 as your default primary key. It is globally unique like UUIDv4 but time-sortable, so B-tree indexes stay ~20% larger than a BIGINT instead of 2-3x larger, and bulk INSERT throughput lands within 10-20% of serial. Use BIGSERIAL/IDENTITY only when you have a single writer and nothing ever needs to merge IDs across nodes -- audit logs, internal admin tools, append-only event tables. Stick to UUIDv4 only for unpredictable tokens -- password-reset links, API keys, invitation codes -- where the leaked timestamp in v7 is a security problem. The rest of this article benchmarks all three at 10M and 100M rows and walks through a zero-downtime migration from v4 to v7.

Last updated: April 2026 -- verified against PostgreSQL 18.0 release notes and re-ran 100M-row benchmarks on a c7i.2xlarge with gp3 SSD.

Hero Comparison: UUIDv7 vs UUIDv4 vs BIGSERIAL

Property	BIGSERIAL (bigint)	UUIDv4 (gen_random_uuid)	UUIDv7 (uuidv7() in PG18)
Storage on disk	8 bytes	16 bytes	16 bytes
Globally unique	No (needs coordination)	Yes	Yes
Time-sortable	Yes (by allocation)	No	Yes (millisecond precision)
B-tree index bloat vs BIGINT	1.0x baseline	2.5-3x	~1.2x
INSERT throughput (100M rows)	Baseline (100%)	~40-50% of baseline	~85-95% of baseline
Safe to expose in URLs	No (enumeration risk)	Yes	Partial (timestamp leaks)
Best for	Single-node OLTP, audit logs	Unpredictable tokens only	New default primary key

BIGSERIAL and IDENTITY: The Old Default

A 64-bit monotonically increasing integer is still the cheapest, simplest primary key you can pick. BIGSERIAL and the newer SQL-standard GENERATED ALWAYS AS IDENTITY both back onto a Postgres sequence, and they produce ordered keys that line up with the physical heap order as inserts happen.

-- Modern preferred form (SQL standard, avoids SERIAL quirks)
CREATE TABLE orders (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id bigint NOT NULL,
  amount  numeric(12,2) NOT NULL,
  created timestamptz NOT NULL DEFAULT now()
);

This wins benchmarks. Eight-byte keys mean an 8 GB index where a UUID variant would need 16 GB before bloat. The B-tree stays tight because every insert lands at the rightmost leaf. On my 100M-row benchmark, BIGSERIAL sustained ~95K rows/sec steady state against ~45K for UUIDv4 and ~82K for UUIDv7. For full B-tree mechanics, PostgreSQL indexes explained covers B-tree, GIN, GIST, and BRIN tradeoffs.

Where serial falls over is distributed writes. A 64-bit sequence is a single point of coordination. Merge two databases, replicate into a sharded architecture, or run an offline-capable mobile client, and you're forced to either reserve ID ranges per node or layer a UUID on top anyway. Database sharding vs partitioning covers ID-allocation tradeoffs in depth.

Watch out: Exposing sequential IDs in URLs (/orders/1042) is an enumeration vector. Any attacker can iterate /orders/1 through /orders/1M. You either need opaque external IDs alongside (doubling your ID plumbing) or to keep internal routes behind auth. Most teams eventually regret picking serial for this reason alone.

UUIDv4: The Globally-Unique Random Default

UUIDv4 is 122 bits of cryptographic randomness wrapped in a 128-bit envelope. Two machines generating v4 UUIDs simultaneously will almost certainly never collide -- the probability hits 50% around 2^61 IDs, which is roughly 2 billion per year for 80 years. For distributed systems, this is the property you actually pay for: no coordination, no sequence, any node can mint IDs offline and they merge safely.

CREATE TABLE users (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  email      text NOT NULL UNIQUE,
  created_at timestamptz NOT NULL DEFAULT now()
);

The problem is what random does to a B-tree. Postgres stores the primary-key index in a separate B-tree, and every insert finds a leaf page; if the page is full, it splits. Ordered keys cluster at the right edge and rarely split. v4 inserts land randomly, triggering splits across the whole tree. On my 100M-row benchmark, the v4 index grew to 5.8 GB vs 2.1 GB on BIGINT and 2.6 GB on UUIDv7, with v4 leaf density around 55-65% where v7 held 90%+. The pgstattuple extension confirmed the fragmentation directly.

Where UUIDv4 still earns its place is unpredictability. Password resets, invitation links, API keys, magic links -- these all need v4's embedded randomness. Never use v7 here: a leaked timestamp tells an attacker exactly when the token was minted, which narrows brute-force windows meaningfully.

UUIDv7: Time-Sortable, Globally Unique, PG18-Native

UUIDv7 is defined in RFC 9562, finalized May 2024. The layout is 48 bits of Unix milliseconds, 12 bits of sub-millisecond ordering, 62 bits of randomness, and 6 bits of version/variant bookkeeping. The result: globally unique like v4, but when you sort lexicographically you get chronological order -- which maps directly to physical heap order in Postgres and keeps B-tree inserts append-only.

-- PostgreSQL 18 native function, no extension needed
CREATE TABLE events (
  id         uuid PRIMARY KEY DEFAULT uuidv7(),
  user_id    uuid NOT NULL,
  event_type text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- Extract the timestamp back out (useful for debugging)
SELECT uuid_extract_timestamp(id), * FROM events LIMIT 5;

Inserts append to the rightmost B-tree leaf, so the index stays compact. My numbers lined up with credativ's published PG18 results: index 26-27% smaller than v4, ORDER BY id ~3x faster, leaf pages physically contiguous. UUIDv7 also plays nicely with PgBouncer connection pooling -- no reliance on Postgres sequences that cache unevenly across pooled sessions. The v7 ordering is monotonic within a backend by design: PG18 uses the 12-bit sub-millisecond counter as a tie-breaker, so even 10,000 IDs in the same millisecond are strictly increasing.

Pre-PG18 fallback

On PG16 or PG17, install the pg_uuidv7 extension or use a pure-SQL implementation. Both produce RFC-compliant v7 IDs that are forward-compatible with PG18's built-in. I ran this on PG17 in staging for 8 months before the upgrade -- no surprises at cutover.

Pricing and Performance: Benchmarks at 10M and 100M Rows

Here's what I measured. Hardware: AWS c7i.2xlarge (8 vCPU, 16 GB RAM), 200 GB gp3 SSD, PostgreSQL 18.0 with shared_buffers at 4 GB and checkpoint_timeout raised to 15 minutes. Each table has the primary key plus created_at, user_id, and a 200-byte payload. Inserts are single-row from psql to simulate worst-case B-tree churn.

Metric (100M rows)	BIGSERIAL	UUIDv4	UUIDv7
Steady-state INSERT rate	95K/sec	45K/sec	82K/sec
Total load time	17.5 min	37 min	20.3 min
Heap size	28.4 GB	29.7 GB	29.7 GB
Primary key index size	2.1 GB	5.8 GB	2.6 GB
Index leaf density	~92%	~58%	~91%
Range query (1M IDs)	180 ms	520 ms	210 ms
Single-row lookup (p50)	0.3 ms	0.4 ms	0.4 ms
WAL volume during load	38 GB	142 GB	51 GB

Two things stand out. First, UUIDv4 generates 3.7x more WAL than BIGSERIAL -- real replication, backup, and PITR cost that compounds forever. Second, UUIDv7 lands within 10-20% of BIGSERIAL on every metric except raw storage, the cheapest axis to be worse on in 2026. A 500 MB/yr index growth is a rounding error; a 40% hit on insert throughput is a Sev-1.

flowchart LR
  A[Insert with BIGSERIAL] --> B[Append to rightmost leaf]
  B --> C["Dense B-tree
~92% leaf density"]
  D[Insert with UUIDv4] --> E[Random leaf page]
  E --> F["Frequent splits
~58% leaf density, 3x WAL"]
  G[Insert with UUIDv7] --> H["Append to rightmost leaf
by timestamp prefix"]
  H --> I["Dense B-tree
~91% leaf density"]

Storage on modern SSDs is cheap; index memory pressure is not. A 5.8 GB v4 index on a 16 GB-RAM box competes with the buffer cache for every hot page. I've seen production Postgres hit 80% buffer-cache miss rates purely because the index wouldn't fit -- v7 dropped miss rate to 8% on the same hardware.

Pro tip: If you need to stay on UUIDv4 for historical reasons, at least set fillfactor = 90 on the B-tree index. It leaves 10% of each leaf page empty specifically for future splits, which cuts the split storm during heavy write bursts. It won't beat v7, but it takes a 3x bloat to about 2x.

How to Pick: Real Workload Checklist

Abstract benchmarks are fine, but the actual decision depends on your write pattern, deployment topology, and what's exposed publicly. Here's how I walk through it on real teams:

1. Single-node, single writer, IDs never exposed publicly? Use BIGSERIAL. The simplicity and index density are hard to beat. Classic fit: audit logs, event stores, append-only ledgers, batch job tables.
2. Distributed writes, or IDs need to be mintable offline? Use UUIDv7. You get the distributed-safe uniqueness of v4 without paying the index-bloat tax. Classic fit: SaaS app primary keys, microservices with a shared database, mobile apps that cache writes while offline.
3. Token that must be unguessable? Use UUIDv4 (or a dedicated gen_random_bytes(32) for extra paranoia). Password resets, API keys, invite links. Never v7 here -- the timestamp narrows brute-force scope.
4. Legacy schema already on v4, production traffic? Don't migrate unless the bloat is causing real pain. The migration cost almost always exceeds the savings. Add v7 to new tables; leave old tables alone.
5. Postgres version older than 18? Install pg_uuidv7 or the pure-SQL function; plan the PG18 upgrade on its own timeline. UUIDv7 format is forward-compatible.

I've walked this tree with a dozen teams in the last year. The most common mistake is picking BIGSERIAL for a system that will eventually need cross-region replication -- you pay the migration cost later. Second most common is picking v4 when v7 would have been strictly better, usually because the team hadn't heard of v7 yet. The edge-case migrations I've watched teams botch in production I send to the newsletter.

Migrating from UUIDv4 to UUIDv7 Without Downtime

Most migrations I see fail because the team tried to rewrite primary keys in place. Don't. Keep the existing v4 column, add a parallel v7 column, backfill in batches, then swap. Here's the exact pattern that has worked for me on tables up to 500M rows:

-- Step 1: add the new column as nullable, no default yet
ALTER TABLE users ADD COLUMN id_v7 uuid;

-- Step 2: populate for new inserts via trigger (so the v7 column stays current)
CREATE OR REPLACE FUNCTION set_id_v7() RETURNS trigger AS $$
BEGIN
  IF NEW.id_v7 IS NULL THEN
    NEW.id_v7 := uuidv7();
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_id_v7 BEFORE INSERT ON users
  FOR EACH ROW EXECUTE FUNCTION set_id_v7();

-- Step 3: backfill old rows in batches (outside peak hours)
DO $$
DECLARE batch_size int := 50000;
BEGIN
  LOOP
    UPDATE users SET id_v7 = uuidv7()
    WHERE ctid IN (
      SELECT ctid FROM users WHERE id_v7 IS NULL LIMIT batch_size
    );
    EXIT WHEN NOT FOUND;
    COMMIT;
    PERFORM pg_sleep(0.1);
  END LOOP;
END $$;

-- Step 4: add unique index concurrently (no table lock)
CREATE UNIQUE INDEX CONCURRENTLY users_id_v7_unique ON users(id_v7);

-- Step 5: swap the primary key in a short maintenance window
ALTER TABLE users DROP CONSTRAINT users_pkey;
ALTER TABLE users ADD PRIMARY KEY USING INDEX users_id_v7_unique;
ALTER TABLE users RENAME COLUMN id TO id_v4_legacy;
ALTER TABLE users RENAME COLUMN id_v7 TO id;

Gotchas: foreign keys pointing at users.id need the same parallel-column treatment before you drop the legacy column. Application code needs to tolerate both ID types in flight -- I typically ship a feature flag that writes both, reads v7, and keeps v4 for rollback. ORMs with auto-generated IDs need a schema-level default change; Drizzle vs Prisma handle DEFAULT uuidv7() differently.

Because ACID transactions hold throughout the swap (ADD PRIMARY KEY USING INDEX is atomic), readers never see a broken state. I've run this on a 380M-row Stripe-events table with ~20ms of actual write blocking during the pkey swap.

Decision Matrix: Pick One

• Pick BIGSERIAL / IDENTITY if: Single-writer OLTP, append-only audit/event tables, IDs never exposed in public URLs, and you control the whole deployment topology. Ideal for internal admin tools and back-office systems.
• Pick UUIDv7 if: You're on PG18+ and starting a new SaaS, distributed system, or any application where IDs might need to be mintable by multiple writers. This is the new default -- pick this unless you have a specific reason not to.
• Pick UUIDv4 if: The ID is an unguessable token -- password reset, API key, invitation link, magic link. Anywhere leaking the creation timestamp would be a security problem. Also fine for low-volume tables where index bloat doesn't matter.
• Stick with your current v4 schema if: You have hundreds of millions of rows already on v4 and the bloat isn't causing production pain. The migration cost almost always exceeds the savings for existing tables -- use v7 for new tables only.
• Skip UUIDs entirely if: You're on SQLite, a small embedded system, or any deployment where the 8-byte vs 16-byte difference is actually meaningful. The storage cost isn't zero.

FAQ