# 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.

{/* sql-check: pg 18+ */}
```sql
-- 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:

{/* sql-check: pg 18+ */}
```sql
-- 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](https://www.dbgorilla.com/learn/postgres/which-alter-table-statements-lock-a-postgres-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 →](https://app.dbgorilla.com/signup)

## 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](https://www.dbgorilla.com/learn/postgres/how-to-optimize-orm-generated-queries/)
- [Do I still need the leading column?  Multicolumn indexes and skip scan](https://www.dbgorilla.com/learn/postgres/do-i-still-need-the-leading-column-multicolumn-indexes/)
- [Which ALTER TABLE statements lock a PostgreSQL table?](https://www.dbgorilla.com/learn/postgres/which-alter-table-statements-lock-a-postgres-table/)
- Product: [DBGorilla docs](https://www.dbgorilla.com/docs/)