Prompt and scope
The service has a large orders table, many child tables, public URLs, event payloads, and replicas. PostgreSQL 18 provides a native uuidv7() generator for timestamp-ordered UUIDs. Move new records toward UUIDv7 while preserving the existing UUIDv4 contract and avoiding a table rewrite or long blocking lock.
This is a backend question because the core skill is an online identity and schema migration across APIs, storage, and asynchronous consumers.
What interviewers assess
First, can you distinguish identifier format from chronological truth? UUIDv7 embeds time ordering, but it is not a strict global commit sequence and should not replace an explicit business timestamp.
Second, can you preserve public identity? Existing URLs, caches, idempotency keys, events, and foreign keys must continue to resolve during the migration.
Third, can you design dual-read and dual-write phases with an unambiguous source of truth?
Fourth, can you backfill safely across replicas and regions without creating write amplification or replication lag?
Fifth, can you validate locality and latency instead of assuming UUIDv7 is automatically faster for every workload?
Questions to clarify first
- Is UUID exposed externally, used as a primary key, or both?
- Can clients accept a new identifier, or must the old UUID remain canonical?
- How many child tables, indexes, events, and search documents reference the key?
- Which PostgreSQL versions and extensions exist on every writer and replica?
- What are the write rate, replication-lag SLO, and rollback deadline?
- Is ordering needed for pagination, audit display, or only index locality?
30-second answer framework
“I would keep UUIDv4 as the stable public identifier, add a UUIDv7 surrogate or mapping column, and roll out dual writes before any backfill. Reads accept either key while the mapping is complete; child references and events remain compatible. Backfill in bounded primary-key ranges with lag and lock monitoring, then switch internal indexes and query paths after parity checks. UUIDv7 helps locality and time-oriented scans, but timestamps remain explicit. Rollback is a feature-flag reversal until all consumers are proven.”
Step-by-step answer
Step 1: Choose the compatibility shape
Do not silently change the meaning of an existing UUID. Keep orderidv4 as the external contract and add orderidv7 plus a unique constraint, or introduce a separate internal key with a durable mapping. Decide which key child tables and events use during each phase.
ALTER TABLE orders ADD COLUMN order_id_v7 uuid;
CREATE UNIQUE INDEX CONCURRENTLY orders_order_id_v7_uq
ON orders(order_id_v7) WHERE order_id_v7 IS NOT NULL;Use uuidv7() only on PostgreSQL versions that provide it; otherwise deploy an equivalent generator consistently before enabling the path.
Step 2: Roll out writes first
New writes generate both identifiers in one transaction and record the mapping. Existing writes that cannot populate the new column remain valid. Make retries idempotent so a repeated command cannot create two mappings.
Step 3: Add dual-read resolution
API lookup accepts either identifier, resolves to one canonical order, and emits the legacy ID in old responses. New endpoints may expose UUIDv7 behind versioned contracts. Cache keys should include the resolved canonical identity so both forms do not diverge.
Step 4: Backfill in bounded batches
Backfill rows using a stable cursor, small transactions, and a pause when replication lag, lock waits, or write latency crosses a limit. Index the new column after enough data exists, using concurrent operations where supported. Do not update child tables until the parent mapping is durable.
Step 5: Migrate references and events
Keep child foreign keys and event schemas compatible during the overlap. Add v7 fields as optional, publish both values, and update consumers before making v7 required. Reconcile missing mappings and duplicate mappings before switching constraints.
Step 6: Switch internal access paths
After parity checks, route internal joins and pagination to UUIDv7 where locality or time-oriented scans matter. Keep explicit created_at for business ordering and use a tie-breaker; UUIDv7 ordering is approximate at the application level.
Step 7: Validate and observe
Compare index size, page splits, cache behavior, insert latency, range-scan latency, replication lag, and error rates against a matched workload. Check that v4 and v7 lookups return the same row and that event consumers remain idempotent.
Step 8: Retire only after a rollback window
Keep the mapping, old indexes, and dual-read path until every client, replay tool, export, and replica has passed the migration window. Remove old constraints in separate deploys with an explicit rollback plan.
Model answer
“I would not rewrite the public UUID contract in place. I would add a UUIDv7 column and unique mapping, deploy dual writes, then dual reads that resolve either form to the same order. Backfill in bounded transactions while watching locks, replication lag, and write latency. Child references and events carry both IDs until every consumer understands v7. After parity and locality measurements, switch internal joins and pagination while keeping created_at as the business ordering field. The feature flag can reverse reads and writes until the old path is retired.”
Common mistakes
- Replacing public UUIDs immediately → links and events break → preserve a canonical contract and mapping.
- Assuming UUIDv7 is strict time order → pagination skips or reorders records → use explicit timestamps and tie-breakers.
- Backfilling one giant transaction → locks and replication lag spike → use bounded batches.
- Adding a foreign key before mappings exist → writes fail during overlap → migrate parent, children, then constraints.
- Generating IDs on mixed unsupported versions → semantics diverge → pin versions or use one consistent generator.
- Ignoring retries → duplicate mappings appear → make dual writes idempotent.
- Dropping v4 too soon → old exports and replays fail → wait through the rollback window.
Follow-up questions
Follow-up 1: Is UUIDv7 a replacement for created_at?
No. It can improve locality and provide timestamp-oriented bits, but business ordering needs an explicit timestamp and deterministic tie-breaker.
Follow-up 2: Can v4 and v7 share a UUID column?
Yes, the UUID type can hold both formats, but migration metadata, external contracts, and ordering semantics still need an explicit plan.
Follow-up 3: Why dual-read before switching writes?
It lets old and new records resolve consistently while backfill and consumer rollout are incomplete.
Follow-up 4: How do you throttle backfill?
Use bounded transactions and pause on replication lag, lock waits, CPU, or write-latency thresholds; resume from a durable cursor.
Follow-up 5: What must events contain?
During overlap, publish both IDs or a stable mapping reference, version the schema, and update consumers before making v7 mandatory.
Follow-up 6: When can the old column be removed?
Only after clients, exports, replays, replicas, and rollback checks pass the agreed window; remove constraints and indexes in separate reversible deploys.