# How to migrate uuidv4 primary keys to uuidv7 without downtime

> **Quick answer:** Change the column default to `uuidv7()` on PostgreSQL 18 or
> later. Both versions are values of the same `uuid` type, so nothing is
> rewritten and old rows keep their existing keys. Leave the rows alone and
> rebuild the index: measured below, a mixed index does not compact itself, and
> `REINDEX INDEX CONCURRENTLY` recovered 40% of its size. The `ALTER` is
> metadata-only but not lock-free, and if the application supplies the
> identifier the default is not what is in use.

## What is the problem?

A version 4 UUID is random. As a primary key, every insert lands at a random
place in the index. The index pages fill unevenly and split, writes target
scattered pages rather than a sequential run, and the working set is the whole
index rather than its right-hand edge.

A version 7 UUID puts a millisecond timestamp in its first 48 bits, so values
generated one after another mostly ascend. In a fresh index, inserts land at the
right-hand edge, filling the current page before moving on. This is close to the
behavior of a sequential key. Both "mostly" and "close to" matter, and much of
this page covers where they stop holding.

PostgreSQL 18 added
[`uuidv7()`](https://www.postgresql.org/docs/18/functions-uuid.html) as a
built-in function. When the database generates the identifier, this is a
default change rather than an application change. When the application supplies
`id` explicitly, the default is never consulted and the migration is a code
deploy.

## What the difference actually measures

Two tables, identical in every way except the default. 300,000 rows each, on
PostgreSQL 18.6, then `pgstatindex` on each primary key:

| | uuidv4 | uuidv7 |
|---|---|---|
| index size | 11,870,208 bytes | 9,486,336 bytes |
| leaf pages | 1,441 | 1,150 |
| average leaf density | 71.85% | 89.95% |
| leaf fragmentation | 49.55% | 0 |

The v7 index is about 20% smaller. The larger difference is fragmentation: zero
against nearly half. Here, fragmentation means that leaf pages are not in
physical order, which makes a range scan over the index seek instead of read
forward.

These are two fresh indexes, each built by one backend inserting in one loop.
This is the best case for v7, not the state of an index that already holds v4
keys. The mixed-index result appears further down and is worse than this one.

Reproduce it on your own hardware because index sizes depend on row width and
fill factor:

<!-- sql-check: pg 18+ -->
```sql
CREATE TABLE IF NOT EXISTS k4 (id uuid PRIMARY KEY DEFAULT uuidv4(), pad text);
CREATE TABLE IF NOT EXISTS k7 (id uuid PRIMARY KEY DEFAULT uuidv7(), pad text);

INSERT INTO k4 (pad) SELECT repeat('x', 100) FROM generate_series(1, 300000);
INSERT INTO k7 (pad) SELECT repeat('x', 100) FROM generate_series(1, 300000);
```

Then measure both indexes. `pgstattuple` is a contrib module, so it may not be
installed:

```sql
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT 'v4' AS kind, index_size, avg_leaf_density, leaf_fragmentation
FROM pgstatindex('k4_pkey')
UNION ALL
SELECT 'v7', index_size, avg_leaf_density, leaf_fragmentation
FROM pgstatindex('k7_pkey');
```

## The migration

### Change the default

Set `lock_timeout` first, in the same session, before the statement it protects.
`SET` lasts for the session, so a `lock_timeout` run afterwards protects the
next statement rather than this one:

<!-- sql-check: pg 18+ -->
```sql
SET lock_timeout = '3s';

ALTER TABLE k4 ALTER COLUMN id SET DEFAULT uuidv7();
```

That is the migration, and it does not rewrite anything. Measured on 18.6, the
table's `relfilenode` was identical before and after, which is direct evidence
that no rewrite happened.

It is not, however, lock-free. The
[PostgreSQL 18 `ALTER TABLE` reference](https://www.postgresql.org/docs/18/sql-altertable.html)
says, in its description of the subforms, "Note that the lock level required may
differ for each subform. An ACCESS EXCLUSIVE lock is acquired unless explicitly
noted." `SET DEFAULT` is not among the noted exceptions. Measured on 18.6,
`pg_locks` showed the statement holding `AccessExclusiveLock` on the table.

The hold is brief. The wait need not be, and while the `ALTER` waits it blocks
everything behind it. Reproduced on 18.6 in a single `pg_locks` snapshot: one
backend running a 15 second read held `AccessShareLock`; the `ALTER` waited,
ungranted, for `AccessExclusiveLock`; and a plain `SELECT count(*)` issued after
the `ALTER` waited too, ungranted, for an `AccessShareLock` it would otherwise
have been given immediately. That queue can block reads. A statement that takes
a millisecond of lock can still stop reads for as long as the query in front of
it runs.

So `lock_timeout` prevents this queue: the `ALTER` gives up instead of waiting,
and you run it again later. Watch the queue while it happens:

<!-- sql-check: pg 18+ -->
```sql
SELECT a.pid, l.mode, l.granted, left(a.query, 60) AS query
FROM pg_locks l
JOIN pg_stat_activity a USING (pid)
WHERE l.relation = 'k4'::regclass
ORDER BY l.granted DESC, a.query_start;
```

### Leave the old rows alone

Both versions are values of the `uuid` type. Nothing distinguishes them to the
column, the index, or a foreign key. Measured on 18.6, a table holding 300,000
v4 and 300,000 v7 keys behaved normally:

<!-- sql-check: pg 18+ -->
```sql
SELECT uuid_extract_version(id) AS version, count(*)
FROM k4
GROUP BY 1
ORDER BY 1;
```

The reason not to backfill is blast radius rather than effort. Changing a
primary key means updating every row in every table that references it, inside
one transaction or with a carefully staged dual-key scheme, under locks, for a
benefit that only ever applied to inserts you had not made yet. Rebuilding the
index gets you the density without touching a single key.

### The mixed index gets worse, and you have to rebuild it

This is the part that is easy to get backwards. Once v7 inserts start arriving
in order, the index does not gradually tidy itself. Measured on 18.6, the
opposite happened.

One table, one index. 300,000 v4 rows, then v7 inserts on top in two stages:

| | index size | leaf pages | avg leaf density | leaf fragmentation |
|---|---|---|---|---|
| 300k v4 only | 11,640,832 bytes | 1,412 | 73.32% | 50.14% |
| after 300k v7 | 28,704,768 bytes | 3,481 | 59.55% | 20.37% |
| after 900k v7 | 62,840,832 bytes | 7,619 | 54.44% | 9.31% |

Density fell at every step. The index ended at 62,840,832 bytes for 1.2M rows,
against 37,871,616 bytes for a fresh v7 index over the same 1.2M rows.

The cause is where v7 values land in a key space that v4 already occupies. A v7
UUID starts with the current millisecond, so today's values begin `01a0`. A v4
UUID is random across the whole range, so its keys are spread evenly from `00`
to `ff`. Of the 300,000 v4 keys in that table, 298,028 sorted above every v7
key and 1,972 sorted below. Check it on your own table:

<!-- sql-check: pg 18+ -->
```sql
WITH bounds AS (
  SELECT (SELECT id FROM k4 WHERE uuid_extract_version(id) = 7 ORDER BY id LIMIT 1) AS lo_v7
)
SELECT count(*) FILTER (WHERE id > (SELECT lo_v7 FROM bounds)) AS v4_above_all_v7,
       count(*) FILTER (WHERE id < (SELECT lo_v7 FROM bounds)) AS v4_below_all_v7
FROM k4
WHERE uuid_extract_version(id) = 4;
```

The v7 inserts are not appending at the right-hand edge of the index. They are
piling into a narrow band near its bottom, with 99% of the existing keys above
them. PostgreSQL applies its rightmost-page optimisation, which splits a full
page 90/10 and leaves the left side packed, only when the insert is at the end.
Everywhere else, a full page splits down the middle, so each split leaves two
half-empty pages. That causes the falling density, and more inserts create more
such splits.

Rebuilding is the only thing that fixes it:

<!-- sql-check: pg 18+ -->
```sql
REINDEX INDEX CONCURRENTLY k4_pkey;
```

Measured on 18.6, that took the 1.2M row mixed index from 62,840,832 bytes at
54.44% density to 37,871,616 bytes at 89.98% density and zero fragmentation:
byte for byte and page for page identical to a fresh v7 index over the same rows.
The v4 keys are not the problem once they are packed. The splitting was.

`CONCURRENTLY` does not block reads or writes, and the
[PostgreSQL 18 `REINDEX` reference](https://www.postgresql.org/docs/18/sql-reindex.html)
is direct about the price: "PostgreSQL must perform two scans of the table for
each index that needs to be rebuilt and wait for termination of all existing
transactions that could potentially use the index. This method requires more
total work than a standard index rebuild and takes significantly longer to
complete as it needs to wait for unfinished transactions that might modify the
index." Budget it like a maintenance job, not like a statement.

It has two failure modes, both shown below.

A concurrent build that fails leaves the index behind, marked invalid. Measured
on 18.6, a unique index built concurrently over duplicate values:

<!-- sql-check: skip needs a deliberately failing concurrent build on a scratch database -->
```sql
CREATE UNIQUE INDEX CONCURRENTLY dup_v_uq ON dup (v);
-- ERROR:  could not create unique index "dup_v_uq"
-- DETAIL:  Key (v)=(1) is duplicated.

SELECT c.relname, i.indisvalid, i.indisready, pg_relation_size(c.oid) AS bytes
FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
WHERE i.indrelid = 'dup'::regclass;
```

That returns `dup_v_uq` with `indisvalid` and `indisready` both false at 0 bytes.
It is not used by the planner and it is not maintained, but it still occupies the
name. A retry therefore fails until you `DROP INDEX CONCURRENTLY` it. The
original error is then replaced by a name collision.

An index backing an exclusion constraint cannot be rebuilt this way at all. The
same reference says "indexes for exclusion constraints cannot be reindexed
concurrently. If such an index is named directly in this command, an error is
raised. If a table or database with exclusion constraint indexes is reindexed
concurrently, those indexes will be skipped." Measured on 18.6 against a
`tstzrange` exclusion constraint, all three parts of that hold:

<!-- sql-check: skip needs btree_gist and an exclusion constraint on a scratch database -->
```sql
REINDEX INDEX CONCURRENTLY room_id_during_excl;
-- ERROR:  concurrent index creation for exclusion constraints is not supported

REINDEX TABLE CONCURRENTLY room;
-- WARNING:  cannot reindex exclusion constraint index
--           "public.room_id_during_excl" concurrently, skipping

REINDEX INDEX room_id_during_excl;
-- REINDEX
```

The skip matters because a table-wide concurrent reindex can report success
while leaving the target index untouched. A UUID primary key is a plain btree
and none of this applies to it, but a table often carries both.

### If the application generates the identifiers

Many do, because the application wants the identifier before it writes the row.
Then the database default is not what is in use, and the migration is in your
code: swap the v4 generator for a v7 one. Many libraries exist, but check two
things about the one you pick rather than assuming them: that it implements
[RFC 9562](https://www.rfc-editor.org/rfc/rfc9562.html#name-uuid-version-7) version
7, and whether it is monotonic within a process. Neither is guaranteed by the
name, and PostgreSQL's guarantees do not apply to a value your application
generated.

The database side is then a check rather than a change:

<!-- sql-check: pg 18+ -->
```sql
SELECT uuid_extract_version(id) AS version,
       count(*),
       min(uuid_extract_timestamp(id)) FILTER (WHERE uuid_extract_version(id) = 7) AS first_v7
FROM k7
GROUP BY 1
ORDER BY 1;
```

Run it after the deploy. A count of version 7 rows that is not rising means the
new code path is not the one writing.

## What else uuidv7 gives you

The timestamp is readable:

<!-- sql-check: pg 18+ -->
```sql
SELECT uuid_extract_version(uuidv7()) AS version,
       uuid_extract_timestamp(uuidv7()) AS created_at;
```

The query above calls `uuidv7()` twice, so the version and the timestamp come
from two different values. Generate one and take both from it:

<!-- sql-check: pg 18+ -->
```sql
WITH one AS (SELECT uuidv7() AS u)
SELECT uuid_extract_version(u) AS version,
       uuid_extract_timestamp(u) AS generated_at,
       uuid_extract_timestamp(u) - clock_timestamp() AS skew
FROM one;
```

Measured on 18.6, that returns `7` and a time within a millisecond of
`clock_timestamp()`. Compare against `clock_timestamp()`, not `now()`, because
`now()` is fixed at the start of the transaction. In a transaction that had
been open three seconds, the same comparison against `now()` measured
`00:00:03.005287`, which is the age of the transaction rather than any property
of the UUID.

The embedded time is generation time, not insert time and not commit time. A
value generated in the application, or on a row that sat in an open transaction,
carries the earlier moment. Keep a `created_at` column if the timestamp is
something you rely on.

The ordering holds within one backend. Ten thousand values generated in sequence
on 18.6 produced zero pairs out of order:

<!-- sql-check: pg 18+ -->
```sql
SELECT count(*) AS out_of_order
FROM (
  SELECT u, lag(u) OVER (ORDER BY i) AS prev
  FROM (SELECT i, uuidv7() AS u FROM generate_series(1, 10000) i) s
) t
WHERE prev IS NOT NULL AND u < prev;
```

Across backends it does not, and that is expected rather than a bug: PostgreSQL's
monotonic counter is per backend, and two sessions generating in the same
millisecond have no ordering between them. Measured on 18.6, three concurrent
sessions writing 20,000 values each produced 1,124 out-of-order pairs in
59,999 when read back in insert order, while each session on its own produced
zero. Treat v7 as approximately ordered under concurrency: good enough for index
locality, not a sequence number.

And `uuidv7()` takes an optional interval that shifts the embedded timestamp,
which is the tool for backfilling identifiers that need to sort with historical
rows:

<!-- sql-check: pg 18+ -->
```sql
SELECT uuid_extract_timestamp(uuidv7(interval '-1 hour')) AS an_hour_ago;
```

## The two reasons not to do this

**The creation time is disclosed.** If identifiers appear in URLs, in emails or
in an API that untrusted clients can read, a version 7 UUID tells anyone holding
it when the row was created, and comparing two tells them the gap. For a user
account or an order that is usually harmless. For anything where the existence
and timing of a record is the sensitive part, it is not, and version 4 is the
right choice for exactly that reason.

**Sequential keys concentrate writes.** Ordered inserts all land on the same
index page, which makes them efficient and also makes that page a point of
contention under very high insert concurrency. This is a real effect at high
enough rates and it is not the problem most people have. If it is yours, it is
the argument for the random key you already had.

If neither applies, the trade is a smaller, denser index for an identifier that
reveals its creation time.

## Why does AI-generated code cause this?

`uuid_generate_v4()` and `gen_random_uuid()` are what a model writes for a UUID
primary key, because that has been the answer for a decade and both are correct
functions that produce correct identifiers. Nothing about "give this table a UUID
primary key" mentions index locality, and the cost does not appear until the
table is large.

The second pattern is the migration this page exists to prevent. Asked to
migrate a table from v4 to v7, the generated plan is often a backfill: add a
column, populate it, swap the constraint, update the referencing tables. It is a
thorough answer to the question as asked, it is a week of work, and the one-line
default change gets the index-locality benefit.

## How do I stop it coming back?

**Make `uuidv7()` the default on new tables**, so there is nothing to migrate
later and no mixed index to rebuild.

**Rebuild the primary key index after you switch an existing table**, once, with
`REINDEX INDEX CONCURRENTLY`. A mixed index does not compact itself, and waiting
makes it larger rather than smaller.

**Check the version distribution after the deploy**, rather than assuming the
default is what is writing. An application that generates its own identifiers
never touches the default you changed.

**Decide the disclosure question once**, and write it down next to the schema
convention. This choice is not reversible by editing a default.

**Watch the index rather than the theory.** `pgstatindex` gives you fragmentation
and density on your own data, which is the number this page is about.

## How DBGorilla helps

Whether this change was worth making is a question about index size and index
scans over time, not about a benchmark on an empty table. DBGorilla connects
read-only and gives your AI coding agent table and index sizes, live and dead
row counts, index usage and scan counts, the schema's history, and the workload
by query fingerprint. That is what shows whether an index actually got denser
after a default changed, rather than assuming it did. It surfaces and explains;
it does not write to your database.
[Get started free →](https://app.dbgorilla.com/signup)

## Related

- [Which PostgreSQL index type should I use?](https://www.dbgorilla.com/learn/postgres/which-postgresql-index-type-should-i-use/)
- [Why is my table bloated when autovacuum runs?](https://www.dbgorilla.com/learn/postgres/why-is-my-table-bloated-when-autovacuum-runs/)
- [How to handle database migrations safely](https://www.dbgorilla.com/learn/postgres/how-to-handle-database-migrations-safely/)