Postgres at the edge: rethinking primary keys for global writes

A serial primary key is not a key. It is a coordinated agreement between every writer that they will take turns. Honour that agreement across two regions and you have, by definition, given up either availability or freshness.

ULIDs and UUIDv7 are not exotic. They are the most boring possible answer to "how do I let a second region write without phoning home for a sequence number". The interesting part is everything that depends on the old key (foreign keys, indexes, audit tables, the analytics pipeline) and how you migrate without a Saturday-night cutover.

Why sequences break at the edge

A bigserial primary key requires a centralised sequence counter. Every insert in region B must either wait for a round-trip to region A, or risk a gap in the sequence. At 40ms cross-region latency and 5,000 writes per second, that's 200 concurrent requests stacking up waiting for a number.

The alternatives split into two families. Random identifiers like UUIDv4 are globally unique without coordination, but unsortable. Index fragmentation on large tables is severe, and joining on UUID columns is measurably slower than joining on integers. Time-ordered identifiers (UUIDv7 and ULID) are globally unique, roughly sortable by creation time, and insert-friendly for B-tree behaviour. That is the right answer for new tables.

The migration path (no downtime)

We moved 240M rows in the orders table. The strategy is the shadow column approach: add the new key alongside the old, backfill, build a covering index, swap behind a view.

-- shadow column, backfill, swap. boring on purpose.
alter table orders add column id_v2 uuid;

update orders set id_v2 = uuidv7_from_timestamp(created_at)
where id_v2 is null;

create unique index concurrently orders_id_v2_uq on orders (id_v2);

-- swap behind a view; cut over in one transaction.
begin;
  alter table orders rename to orders_legacy;
  create view orders as
    select id_v2 as id, /* ...other cols... */ from orders_legacy;
commit;

The uuidv7_from_timestamp function converts the existing created_at to a time-ordered UUIDv7, preserving the sort order that the downstream analytics pipeline depends on. It's a single C extension, ~50 lines.

The view trick

The view lets us rename the column atomically from the application's perspective. Old code that reads orders.id keeps working. New code uses the same column name. We run both in production for two weeks, verify foreign key references, then drop the legacy table.

What we didn't anticipate

The audit table had order_id bigint as a foreign key. We had to backfill that too. It took longer than the orders table backfill, the audit table was three times larger and had no created_at column, so we had to join back to orders to derive the timestamps. Next time we would migrate the audit table first using logical replication and swap it in the same transaction.

Results

Write throughput in region B went from 1,200 to 4,800 inserts per second. The p99 write latency dropped from 38ms to 4ms. The sequence server (a single Postgres instance that did nothing but serve nextval and became the most important machine in the fleet) was decommissioned.