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 sameuuidtype, 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, andREINDEX INDEX CONCURRENTLYrecovered 40% of its size. TheALTERis 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?
Section titled “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() 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
Section titled “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:
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:
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT 'v4' AS kind, index_size, avg_leaf_density, leaf_fragmentationFROM pgstatindex('k4_pkey')UNION ALLSELECT 'v7', index_size, avg_leaf_density, leaf_fragmentationFROM pgstatindex('k7_pkey');The migration
Section titled “The migration”Change the default
Section titled “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:
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
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:
SELECT a.pid, l.mode, l.granted, left(a.query, 60) AS queryFROM pg_locks lJOIN pg_stat_activity a USING (pid)WHERE l.relation = 'k4'::regclassORDER BY l.granted DESC, a.query_start;Leave the old rows alone
Section titled “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:
SELECT uuid_extract_version(id) AS version, count(*)FROM k4GROUP BY 1ORDER 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
Section titled “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:
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_v7FROM k4WHERE 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:
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
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:
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 bytesFROM pg_index i JOIN pg_class c ON c.oid = i.indexrelidWHERE 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:
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;-- REINDEXThe 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
Section titled “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 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:
SELECT uuid_extract_version(id) AS version, count(*), min(uuid_extract_timestamp(id)) FILTER (WHERE uuid_extract_version(id) = 7) AS first_v7FROM k7GROUP BY 1ORDER 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
Section titled “What else uuidv7 gives you”The timestamp is readable:
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:
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 skewFROM 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:
SELECT count(*) AS out_of_orderFROM ( SELECT u, lag(u) OVER (ORDER BY i) AS prev FROM (SELECT i, uuidv7() AS u FROM generate_series(1, 10000) i) s) tWHERE 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:
SELECT uuid_extract_timestamp(uuidv7(interval '-1 hour')) AS an_hour_ago;The two reasons not to do this
Section titled “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?
Section titled “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?
Section titled “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 do I migrate from uuidv4 to uuidv7 without downtime?
Change the column default to uuidv7() and stop. Both are values of the same uuid type, so no column changes and nothing is rewritten: measured on PostgreSQL 18.6, the table's relfilenode was unchanged. It is not lock-free, though. The ALTER takes an ACCESS EXCLUSIVE lock, which it holds for a moment and may wait a long time to get, and while it waits every new reader queues behind it. Run it with a lock_timeout set in the same session.
Do I have to backfill the old rows?
No. A uuid column holds both versions happily, and rewriting existing primary keys means updating every row that references them, under locks. What you should do instead is rebuild the index: measured on 18.6, a v4 index that then took 900,000 v7 inserts grew to 62,840,832 bytes at 54.44 percent density, and REINDEX INDEX CONCURRENTLY brought the same rows down to 37,871,616 bytes at 89.98 percent. The mixed index does not improve on its own.
How much does uuidv7 actually help?
On a fresh table, measured on PostgreSQL 18.6 with 300,000 rows each: the v4 primary key index was 11,870,208 bytes with 49.55 percent leaf fragmentation and 71.85 percent leaf density; the v7 index was 9,486,336 bytes with 0 fragmentation and 89.95 percent density. That is one single-session run on fresh indexes, and it is the best case. An existing v4 index that starts taking v7 inserts behaves differently, because v7 values sort near the bottom of the v4 key space rather than at the right-hand edge.
Is uuidv7 sortable and does it leak the creation time?
Both, with a caveat on each. The first 48 bits are a millisecond timestamp, which is what makes it ordered and what makes it disclose roughly when the value was generated. uuid_extract_timestamp returns it. Ordering is per backend: measured on 18.6, three concurrent sessions produced 1,124 out-of-order pairs in 59,999, while each session on its own produced none. And the embedded time is generation time, not creation or commit time, so keep a created_at column if the timestamp matters.
What if I am not on PostgreSQL 18?
uuidv7() is built in from 18, along with UUIDv7 support in uuid_extract_timestamp; uuid_extract_version arrived in 17. On 17 and earlier you generate the value in the application or add an extension that provides it. Check your generator against RFC 9562 and check whether it is monotonic within a process, because those properties vary by library and are not inherited from PostgreSQL. The database side of the migration is identical: change what writes the default.