DEV Community

Cover image for UUIDv7 vs BIGINT Primary Keys: The 2026 Verdict
Gabriel Anhaia
Gabriel Anhaia

Posted on

UUIDv7 vs BIGINT Primary Keys: The 2026 Verdict

#postgres #database #backend #performance

You switched the primary key to a random UUID because you wanted IDs the client could generate, IDs that didn't leak your row count, IDs that merged cleanly across shards. Six months later the same table that flew on BIGSERIAL is doing twice the write I/O, the index is 40% bigger, and nobody can explain why inserts got slower as the table grew.

The UUID wasn't the mistake. UUIDv4 was. A random 128-bit value lands in a random spot in your B-tree on every insert, and that single property is what turned a fast table into a slow one.

UUIDv7 is the fix, and it's been a published RFC since 2024. The interesting question in 2026 isn't UUID vs integer. It's UUIDv7 vs BIGINT, because UUIDv7 erases most of what made UUIDs slow while keeping what made them useful.

Why random UUIDs hurt a B-tree

Postgres stores its primary key in a B-tree. The tree keeps entries in sorted order across pages. When you insert a BIGSERIAL value, the new key is always larger than every existing key, so it goes to the rightmost leaf page. That page is already in memory, it fills up, Postgres allocates the next one. Sequential, cache-friendly, almost no page splits.

Now insert a UUIDv4. The value is random, so it sorts into the middle of the tree. The target leaf page is probably not in the buffer cache, so Postgres reads it from disk. The page is already full, so Postgres splits it: half the rows stay, half move to a new page, parent pointers update. Every insert touches a different cold page.

Three costs stack up:

  • Page splits. Random inserts into full pages split them constantly. Splits are extra writes plus WAL records.
  • Cache misses. The working set for inserts becomes the whole index instead of the right edge. A 4GB index that used to need 50MB of hot buffer now wants all 4GB.
  • Write amplification. Each split is logged. Full-page images in the WAL after a checkpoint make it worse.

This is why the slowdown is invisible at small scale and brutal at large scale. While the whole index fits in shared_buffers, random inserts are fine. The day the index outgrows the cache, your insert latency falls off a cliff and nothing in your code changed.

What UUIDv7 actually does

UUIDv7 puts a Unix millisecond timestamp in the high bits, then fills the rest with randomness. The layout is 48 bits of timestamp, then a version field, 12 random bits, a variant field, and 62 more random bits, for 74 random bits total.

Because the timestamp leads, two UUIDv7 values generated a second apart sort in time order. New IDs are always near the high end of the tree, the same property that makes BIGSERIAL fast. You get back the right-edge insert pattern: hot pages stay hot, splits go away, the WAL calms down.

You keep the parts you wanted from UUIDs:

  • Clients and services generate IDs without a round trip to the database.
  • IDs don't expose how many rows you have or how fast you're growing.
  • IDs merge across shards and regions without a coordination step.

Postgres 18, released in 2025, ships uuidv7() as a built-in. Before that you generated them in the app or with an extension.

-- Postgres 18+
CREATE TABLE orders (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  customer_id bigint NOT NULL,
  total_cents int NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
Enter fullscreen mode Exit fullscreen mode

On older versions, generate it client-side. Most languages have a library now. In Go:

import "github.com/google/uuid"

id, err := uuid.NewV7()
if err != nil {
    return err
}
// pass id.String() to your INSERT
Enter fullscreen mode Exit fullscreen mode

Seeing the locality difference

You don't have to trust the theory. pageinspect and pgstatindex show the damage directly. Build two tables, fill each with a million rows, compare the index leaf density.

CREATE EXTENSION IF NOT EXISTS pgstattuple;

CREATE TABLE k_v4 (id uuid PRIMARY KEY, n int);
CREATE TABLE k_v7 (id uuid PRIMARY KEY, n int);

-- random v4 keys
INSERT INTO k_v4
SELECT gen_random_uuid(), g
FROM generate_series(1, 1000000) g;

-- time-ordered v7 keys (PG18 builtin)
INSERT INTO k_v7
SELECT uuidv7(), g
FROM generate_series(1, 1000000) g;
Enter fullscreen mode Exit fullscreen mode

Now read the leaf-page fill factor for each primary-key index:

SELECT 'v4' AS kind, avg_leaf_density, leaf_fragmentation
FROM pgstatindex('k_v4_pkey')
UNION ALL
SELECT 'v7', avg_leaf_density, leaf_fragmentation
FROM pgstatindex('k_v7_pkey');
Enter fullscreen mode Exit fullscreen mode

The v7 index reports a higher avg_leaf_density and near-zero leaf_fragmentation, because rows arrived in order and pages filled to capacity before moving on. The v4 index shows lower density and visible fragmentation from all the mid-tree splits. Same row count, same data, different index health. Run it on your own box before you cite any number.

The BRIN bonus you get for free

Time-ordered keys unlock an index you couldn't use before. A BRIN index stores a min/max summary per block range and only works when the column's values track the physical row order. Random UUIDs destroy that correlation, so BRIN on a UUIDv4 column is useless.

With UUIDv7, the key correlates with insert order, which correlates with heap order on an append-mostly table. A BRIN index on the key becomes viable, and BRIN is roughly a thousand times smaller than the equivalent B-tree:

CREATE INDEX idx_orders_id_brin
ON orders USING brin (id);
Enter fullscreen mode Exit fullscreen mode

You wouldn't usually replace the primary key's B-tree with BRIN, but for range scans over recent IDs on a huge table, the tiny BRIN can carry the query at a fraction of the storage.

When BIGINT still wins

UUIDv7 closes the locality gap. It does not close the size gap, and the size gap still costs you.

A bigint is 8 bytes. A uuid is 16 bytes. That difference is not just the primary-key column. Every foreign key that points at this table carries the wider value, and every secondary index that includes the key stores 16 bytes instead of 8. On a schema with a dozen tables all referencing each other, doubling the key width inflates the whole working set. More bytes per row means fewer rows per page means more pages in cache means more I/O.

Pick BIGINT when:

  • The table is a single Postgres with no sharding plan. If a sequence is fine and you never need client-generated IDs, the 8-byte key is smaller and slightly faster on every join.
  • The table is referenced by many foreign keys. The width tax multiplies across every child table and every covering index.
  • You join these keys constantly in hot paths. Comparing 8 bytes beats comparing 16, and it shows up at high join volume.
  • You want human-readable IDs in logs and URLs. order/40231 is easier to read out loud than a 36-character UUID.

Pick UUIDv7 when:

  • You need client- or service-generated IDs without a database round trip.
  • You shard, replicate multi-master, or merge data across regions and can't share one sequence.
  • You don't want IDs to leak business metrics like row counts or growth rate.
  • An offline client creates rows that sync later and must not collide.

A common middle path: keep a BIGINT surrogate key as the internal primary key and clustering key, and add a UUIDv7 external ID for APIs and clients. You pay for two keys, but each does the job it's good at.

CREATE TABLE invoices (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  public_id uuid NOT NULL DEFAULT uuidv7() UNIQUE,
  amount_cents int NOT NULL
);
Enter fullscreen mode Exit fullscreen mode

Don't store a UUID as text

One mistake undoes everything above. If you store UUIDs in a varchar or text column instead of the native uuid type, you pay 36 bytes per value instead of 16, your comparisons run as string comparisons, and your indexes bloat past even the v4 case.

-- wrong: 36 bytes, string comparison, fat index
id varchar(36) PRIMARY KEY

-- right: 16 bytes, integer-ish comparison, compact index
id uuid PRIMARY KEY
Enter fullscreen mode Exit fullscreen mode

Use the uuid type. Always. This single choice often matters more than v4 vs v7.

The verdict

UUIDv4 as a primary key was a real performance trap, and the teams that got burned were right to be wary. UUIDv7 removes the trap by making the key time-ordered, so it inserts like a sequence while keeping the distributed-ID properties you reached for in the first place.

If you need distributed or client-side IDs, reach for UUIDv7 and store it as the native uuid type. If you have a single database, heavy joins, and no sharding on the horizon, BIGINT is still smaller and still wins on raw join speed. The wrong answer in 2026 is a random UUIDv4 primary key on a table you expect to grow.

What did your last table grow into, and did the key type hold up? Drop the schema decision that bit you in the comments.

If this was useful

This post pulls from the key-design section of the Database Playbook: Choosing the Right Store for Every System You Build. The book sits one level up from a single key decision: which store fits which workload, where surrogate keys help, and how index locality plays out across Postgres, MySQL, DynamoDB, and the rest. If you've ever picked a primary key by reflex, the book is the slower, kinder version of that call.

Database Playbook

Top comments (0)