Are UUID primary keys still bad? (and what changed with UUIDv7)

Quick answer: Not automatically. The old objection was UUIDv4's randomness, which scatters inserts across B-tree leaf pages. PostgreSQL 18 ships a native uuidv7(), producing time-ordered UUIDs that insert at the right edge like a sequence. UUIDs are still 16 bytes versus 8 for bigint, so bigint remains the cheaper default when you generate IDs centrally.

Why did random UUIDs hurt in the first place?

gen_random_uuid() returns a version 4 UUID: 122 bits of randomness. As a primary key that is pathological for a B-tree, for three compounding reasons.

Inserts land everywhere. With a sequence, consecutive inserts hit the same rightmost leaf page, which stays in shared buffers and gets written once per checkpoint. With random UUIDs each insert targets a different leaf page, chosen uniformly at random from the whole index.

The hot working set becomes the entire index. A sequential key needs a handful of hot pages cached. A random key needs all of them, because the next insert could go anywhere. Once the index outgrows shared_buffers you start reading a page from disk to insert a single row.

WAL amplifies. The first time a page is modified after a checkpoint, PostgreSQL writes the whole page image into WAL (full_page_writes). Sequential inserts touch few distinct pages between checkpoints; random inserts touch many, so far more full-page images end up in WAL. More WAL means more I/O, longer recovery, and more replication traffic.

There is a fourth, subtler effect: B-tree page splits. Ascending inserts hit a rightmost-split fast path that packs pages tightly. Random inserts split pages down the middle all over the index, leaving them half-full, a larger index that holds the same rows.

This is why the last decade produced ULIDs, uuid_generate_v1mc, Instagram-style snowflake IDs, and a small industry of "sequential UUID" extensions. They were all attacking the same problem: the randomness, not the UUID.

What did PostgreSQL 18 change?

PostgreSQL 18 (released 25 September 2025) added a native generator. From the release notes:

Add UUID version 7 generation function uuidv7(). This UUID value is temporally sortable. Function alias uuidv4() has been added to explicitly generate version 4 UUIDs.

A version 7 UUID starts with a 48-bit Unix timestamp in milliseconds, followed by sub-millisecond precision and randomness. Because the timestamp is the most significant part, byte-order sort equals time order, so new rows cluster at the right edge of the index exactly like a sequence, and the three problems above mostly evaporate.

-- PostgreSQL 18+
CREATE TABLE orders (
  id          uuid PRIMARY KEY DEFAULT uuidv7(),
  customer_id uuid NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

SELECT uuidv7();
-- 019535d9-3df7-79fb-b466-fa907fa17f9e

SELECT uuid_extract_version(uuidv7());   -- 7
SELECT uuid_extract_timestamp(uuidv7()); -- the embedded creation time

uuidv7() also accepts an optional interval argument that shifts the embedded timestamp, useful for backfills. uuid_extract_timestamp() and uuid_extract_version() arrived earlier, in PostgreSQL 17, but with a catch worth knowing if you generate v7 in your application on 17: on that version uuid_extract_timestamp() only understands version 1 UUIDs and returns NULL for a v7. PostgreSQL 18 extended it to version 7. So on 17, uuid_extract_version() will correctly report 7, but you cannot pull the embedded timestamp out server-side until 18.

What does UUIDv7 not fix?

Locality is solved. Size is not, and size is permanent.

Cost	bigint	uuid
Bytes per value	8	16
Primary key index	baseline	~2× the key bytes
Every FK column referencing it	8	16
Every index on those FK columns	baseline	~2× the key bytes
Entries per index page	more	fewer → deeper tree

The multiplier is what people miss. A uuid primary key is not 8 extra bytes on one table. It is 8 extra bytes in the PK, in each of the (often several) tables that reference it, and in every index on every one of those FK columns. Fewer index entries fit per 8 KB page, so the tree is deeper and joins touch more pages. On a small schema this is noise. On a wide, heavily-referenced core table it is measurable.

Three more honest caveats:

• UUIDv7 leaks creation time. That is the point of the format, but it means "opaque, non-enumerable ID" is now only half true. Anyone holding an ID can recover roughly when the row was created, and comparing two IDs reveals their relative order. If that matters (competitor signup-rate inference is the classic case), UUIDv7 is not the privacy tool UUIDv4 was.
• Ordering across machines is approximate. Millisecond timestamps plus clock skew mean IDs from different hosts are roughly, not strictly, ordered. Never use a UUIDv7 as a substitute for a real created_at column or for strictly-monotonic cursors.
• Only new rows benefit. Adding DEFAULT uuidv7() does nothing for the billion random v4 rows already in the index.

Which should I actually choose?

Choose bigint (GENERATED ALWAYS AS IDENTITY) when:

• One database generates the IDs, and the app is happy to wait for it.
• IDs are internal, or you expose a separate public slug/token.
• The table is large and widely referenced, so key width compounds.

Choose uuid with UUIDv7 when:

• Clients or multiple services generate IDs, or you need the ID before the insert (offline-first apps, event pipelines, idempotency keys).
• You merge data across shards, regions, or tenants and cannot coordinate sequences.
• Sequential integers in URLs are unacceptable, enumerable IDs leak volume and invite ID-guessing bugs.

Do not choose UUIDv4 for a new primary key. If you need a UUID and you can get a v7, take the v7. Keep v4 for cases where unguessability and zero embedded metadata genuinely matter, password-reset tokens, share links, which are usually not primary keys anyway.

What do I do on PostgreSQL 17 or earlier?

Two workable options, and one trap.

1. Generate in the application. RFC 9562 v7 libraries exist for every major language runtime, and this works on any PostgreSQL version. It also suits client-generated-ID architectures, which are usually why you wanted UUIDs.
2. Use a third-party extension that provides a v7 generator, if you control the server and can install extensions. Check it is maintained and that your managed provider allows it, many do not.

The trap: do not store UUIDs in text or varchar. The canonical 36-character string form costs 37 bytes versus 16 for the native uuid type, kills comparison performance, and makes every index more than twice as large. This single mistake accounts for a large share of "UUIDs are slow" reports. Column type uuid, always.

If you are on an older version and cannot move yet, note that all currently supported majors below 18 (14 through 17) lack uuidv7(), so application-side generation is the portable answer.

Is migrating an existing table's primary key worth it?

Almost never as a standalone performance project, and it is not a quick win.

First, be clear about what a v4 → v7 retrofit actually is. Both are the uuid type.uuidv4() and uuidv7() differ only in how the 128 bits are generated. There is no ALTER COLUMN ... TYPE and no type-driven table rewrite here. If someone tells you the migration is expensive because of a type change, they have the wrong model.

It is still expensive, for a different and worse reason: you would be changing the values of an existing primary key. That means an UPDATE touching every row (which writes a new row version for each one, doubling the table before vacuum catches up), rebuilding the PK index, and propagating the new values to every foreign key column in every referencing table, each of which needs its own backfill and index rebuild. Foreign keys have to be dropped and recreated, and revalidating them scans both sides. Any external system that stored those IDs now holds dangling references.

On a large, referenced table this is a planned data migration with a rollback path, not an afternoon. That is the case against it, not a phantom ALTER TYPE.

The cheap 90% instead:

-- New rows become time-ordered; existing rows are left alone.
ALTER TABLE orders ALTER COLUMN id SET DEFAULT uuidv7();

The index stops accumulating new random insert points immediately. Existing v4 keys stay where they are, and REINDEX CONCURRENTLY can tidy the accumulated bloat separately if it is genuinely hurting. See which ALTER TABLE statements lock a PostgreSQL table before you run anything on a live table.

Why does aI-generated code get this wrong?

Two stale answers, both common.

Scaffolding tools and coding agents reach for id uuid DEFAULT gen_random_uuid() because that is the overwhelming default in the training data, producing random v4 keys on a PostgreSQL 18 server that could have given you v7 for free.

Ask about the trade-off and you often get the opposite stale answer: a confident recitation of a 2015 blog post about UUID index fragmentation, with no awareness that the ordering problem has a native fix. Some agents will propose a full PK value migration as if it were a config change, and will often mis-describe it as a type change, which it is not.

Underneath both: the agent does not know your server version, your row counts, how many tables reference this key, or whether your IDs are generated client-side. It is answering a general question when the answer is entirely specific.

How do I stop it coming back?

• Record your PostgreSQL major version in the repo's agent instructions (CLAUDE.md, .cursorrules) so agents stop defaulting to v4.
• Add a schema-lint or migration-review check that flags new uuid PK columns defaulting to gen_random_uuid() and any UUID stored as text/varchar.
• Decide the ID strategy once, per service, and write it down. Mixed strategies across a schema cost more than either choice would have.

How DBGorilla helps

DBGorilla connects read-only and gives your AI coding agent (Claude Code, Cursor) the specifics this decision needs: your actual PostgreSQL version, the real column types on your keys and foreign keys, how many tables and indexes reference each key, table and index sizes, and the queries joining on them. That turns "UUIDs are bad, I read somewhere" into a grounded recommendation for your schema. It surfaces and explains through your agent. It does not change schemas or run migrations. Get started free →

FAQ

Are UUID primary keys still bad? The objection was to random v4 UUIDs, which scatter inserts across the whole B-tree. Time-ordered UUIDv7 largely fixes that. What remains is size: 16 bytes versus 8 for bigint, in the PK and in every FK and index referencing it.

Does PostgreSQL have a built-in uuidv7()? Yes, from PostgreSQL 18. It also added a uuidv4() alias; gen_random_uuid() still returns a version 4 random UUID.

Should I use bigint or UUIDv7? bigint when one database generates IDs and enumerable identifiers are fine. It is half the size everywhere. UUIDv7 when IDs must be generated by clients or multiple services, or when guessable sequential IDs are unacceptable.

Can I use UUIDv7 before PostgreSQL 18? Yes, generate it in your application with an RFC 9562 library, or use a third-party extension. Store it in the native uuid type; never in text.

Should I migrate an existing UUIDv4 primary key to v7? Usually not as a retrofit, and not for the reason people usually give. It is not a type change; both are the uuid type. It is expensive because you would be rewriting the key values: an UPDATE over every row, a PK index rebuild, and a matching backfill of every referencing FK column and index, with anything external that stored those IDs left dangling. Setting DEFAULT uuidv7() so new rows are time-ordered captures most of the benefit for almost none of the risk.

Related

• How to optimize ORM-generated queries
• Do I still need the leading column? Multicolumn indexes and skip scan
• Which ALTER TABLE statements lock a PostgreSQL table?
• Product: DBGorilla docs