Migrating UUID primary keys from v4 to v7 in a live production database requires careful planning. A direct ALTER TABLE ... ALTER COLUMN id TYPE uuid USING uuidv7() locks the table for the entire migration — unacceptable for tables with millions of rows. The zero-downtime approach uses a dual-column strategy: add the new UUID v7 column, backfill it without locking, update foreign keys, then swap the primary key when ready. This guide covers the full migration sequence for PostgreSQL with application-layer guidance for Node.js and Python services.
Migration overview
The migration has six phases, each independently deployable:
- Add new column —
id_v7 uuid DEFAULT uuidv7()(non-null default, no lock on new rows) - Backfill existing rows — batch UPDATE in the background
- Build new index —
CREATE INDEX CONCURRENTLYonid_v7 - Update application — write both
id(old) andid_v7(new) on every insert - Update foreign keys — add new FK columns pointing to
id_v7, backfill, promote - Swap primary key — drop old PK constraint, rename
id_v7toid
Phase 1: Add the new column
-- Non-blocking: adding a column with a volatile default (uuidv7()) requires
-- PostgreSQL 11+ to avoid full table rewrite; on older versions use nullable first
ALTER TABLE orders
ADD COLUMN id_v7 uuid;
-- Immediately set default for new rows going forward
ALTER TABLE orders
ALTER COLUMN id_v7 SET DEFAULT uuidv7();
Requires the pg_uuidv7 extension for PostgreSQL < 18. On PostgreSQL 18, uuidv7() is built-in. The ADD COLUMN with a DEFAULT of a volatile function does not rewrite existing rows — they get NULL until backfilled.
Phase 2: Backfill existing rows
Backfill in small batches to avoid long-running transactions and I/O spikes:
-- Run repeatedly until complete; adjust batch_size based on server load
DO $$
DECLARE
batch_size INT := 10000;
rows_updated INT;
BEGIN
LOOP
UPDATE orders
SET id_v7 = uuidv7()
WHERE id_v7 IS NULL
AND id IN (
SELECT id FROM orders WHERE id_v7 IS NULL LIMIT batch_size
);
GET DIAGNOSTICS rows_updated = ROW_COUNT;
EXIT WHEN rows_updated = 0;
PERFORM pg_sleep(0.05); -- 50ms pause between batches
END LOOP;
END $$;
The 50ms pause reduces I/O contention. Monitor with SELECT COUNT(*) FROM orders WHERE id_v7 IS NULL. Total time at 10,000 rows/batch ≈ 1 second per 200,000 rows — 10 million rows ≈ 50 minutes with the pause included.
Phase 3: Build index without blocking
-- CONCURRENTLY builds the index without holding a lock on reads/writes
-- Takes longer but production traffic is unaffected
CREATE INDEX CONCURRENTLY orders_id_v7_idx ON orders (id_v7);
-- After verification, add unique constraint using the existing index
ALTER TABLE orders
ADD CONSTRAINT orders_id_v7_unique UNIQUE USING INDEX orders_id_v7_idx;
The PostgreSQL CREATE INDEX CONCURRENTLY documentation explains the multi-pass algorithm and the requirement that no long-running transactions are open during the final phase.
Phase 4: Dual-write in the application
Update the application to write both IDs during the transition:
// Node.js / TypeScript — dual-write migration phase
import { v7 as uuidv7, v4 as uuidv4 } from 'uuid';
async function createOrder(data: CreateOrderInput) {
const id = uuidv4(); // Keep generating v4 for old column
const id_v7 = uuidv7(); // Generate v7 for new column
await db.query(`
INSERT INTO orders (id, id_v7, customer_id, total)
VALUES ($1, $2, $3, $4)
`, [id, id_v7, data.customerId, data.total]);
return { ...data, id, id_v7 };
}
During this phase both columns are populated on every new row. Existing rows have a backfilled id_v7.
Phase 5: Update foreign keys
For each child table that references orders.id:
-- 1. Add new FK column to child table
ALTER TABLE order_items ADD COLUMN order_id_v7 uuid;
-- 2. Backfill from parent join
UPDATE order_items oi
SET order_id_v7 = o.id_v7
FROM orders o
WHERE oi.order_id = o.id;
-- 3. Add FK constraint concurrently (PostgreSQL 12+)
ALTER TABLE order_items
ADD CONSTRAINT fk_order_items_order_id_v7
FOREIGN KEY (order_id_v7) REFERENCES orders (id_v7)
NOT VALID; -- Does not scan existing rows
ALTER TABLE order_items
VALIDATE CONSTRAINT fk_order_items_order_id_v7; -- Validates in background
NOT VALID followed by VALIDATE CONSTRAINT splits the validation into a non-blocking scan. The PostgreSQL ALTER TABLE documentation covers NOT VALID constraint validation.
Phase 6: Swap the primary key
BEGIN;
-- Drop old PK constraint (acquires brief ACCESS EXCLUSIVE lock)
ALTER TABLE orders DROP CONSTRAINT orders_pkey;
-- Rename new column to id
ALTER TABLE orders RENAME COLUMN id TO id_v4_old;
ALTER TABLE orders RENAME COLUMN id_v7 TO id;
-- Promote new index as PK
ALTER TABLE orders ADD CONSTRAINT orders_pkey PRIMARY KEY USING INDEX orders_id_v7_idx;
COMMIT;
The ACCESS EXCLUSIVE lock during PK swap blocks all other operations briefly — perform this during a low-traffic window and ensure the transaction completes in milliseconds. After verification, drop id_v4_old:
ALTER TABLE orders DROP COLUMN id_v4_old;
Python migration helper
import psycopg2
import time
def backfill_uuid_v7(conn, table: str, batch_size: int = 10_000):
"""Backfill id_v7 column in batches."""
with conn.cursor() as cur:
while True:
cur.execute(f"""
UPDATE {table} SET id_v7 = uuidv7()
WHERE id_v7 IS NULL AND id IN (
SELECT id FROM {table} WHERE id_v7 IS NULL LIMIT %s
)
""", (batch_size,))
updated = cur.rowcount
conn.commit()
if updated == 0:
break
print(f"Backfilled {updated} rows")
time.sleep(0.05)
The psycopg2 package on PyPI provides the PostgreSQL connection. Use psycopg3 (psycopg) for async backfill in asyncio applications.
Further reading
- UUID v4 to v7 Migration Guide — full migration reference
- UUID in Databases — PostgreSQL storage and index types
- UUID v7 Performance Benchmarks — why migration is worth it
External references
- PostgreSQL ALTER TABLE documentation
- PostgreSQL CREATE INDEX CONCURRENTLY
- pg_uuidv7 extension
- RFC 9562 — UUID standard
- psycopg2 on PyPI
Frequently asked questions
Can I migrate a UUID primary key column in place without downtime?
Migrating in-place requires an exclusive table lock for the duration of the migration, which causes downtime proportional to table size. The zero-downtime approach adds a new UUID v7 column alongside the existing UUID v4 primary key, backfills it in batches, updates all foreign key references, then swaps the primary key — all while the application continues to write. The key constraint is that both old and new IDs must be valid and queryable during the cutover window. PostgreSQL ALTER TABLE documentation covers lock levels for each DDL operation.
How do I handle foreign key references during a UUID v7 migration?
Add the new UUID v7 column to all child tables alongside the existing UUID v4 foreign key. Backfill child tables by joining to the parent on the old UUID v4 ID. During the cutover window, write both old and new IDs on every insert. After verifying the new ID is consistent, drop the old foreign key and promote the new one. The PostgreSQL CREATE INDEX CONCURRENTLY documentation covers building indexes on new columns without blocking reads or writes.
How long does backfilling 10 million rows with UUID v7 values take?
On a modern SSD-backed PostgreSQL instance, batched backfill of 10 million rows (10,000 rows per batch with a short sleep between batches to avoid I/O saturation) typically completes in 15–45 minutes depending on server load and index count. The pg_uuidv7 extension generates UUID v7 values inside PostgreSQL — use UPDATE orders SET new_id = uuidv7() WHERE new_id IS NULL AND id BETWEEN $start AND $end for efficient batch updates without application-layer round trips.