back to topology
backend
article··updated ·7 min read

Postgres with more than one writer: primary keys and how to migrate them

#postgres #distributed-systems #uuidv7 #migrations

A bigserial or identity column gets its values from a sequence. A sequence is local to one Postgres instance, so it only produces unique values if every insert goes through that instance. With a single primary that's already true, so uniqueness alone is no reason to change the key type. That changes when more than one node accepts writes, or when IDs are created outside the database.

When the key type matters

Changing the key type doesn't reduce write latency in a single-primary setup. A client in another region still sends the whole transaction to the primary. The sequence call is a negligible part of that round trip.

The key type matters when:

  • several nodes accept writes for the same table (bidirectional logical replication, multi-master extensions, sharding by region),
  • the identifier has to exist before the row reaches the database (offline clients, idempotency keys generated by the client, events published before the insert),
  • data from several independent databases is merged into one place.

In each of these cases two writers can produce the same bigint if they don't coordinate.

The options

Per-node sequences. Each node gets its own sequence with a different start and a shared step:

-- node 1
create sequence orders_id_seq start with 1 increment by 16;
-- node 2
create sequence orders_id_seq start with 2 increment by 16;

Keys stay 8-byte integers and existing foreign keys keep working. The cost is operational: the step caps the number of nodes, every new node needs a free offset, and a misconfigured node produces collisions that show up only at replication time.

UUIDv4. Unique without coordination, and random. Each insert lands on a random leaf page of the B-tree index. The working set of the index becomes the whole index, page splits are spread across it, and after a checkpoint more pages are written in full to WAL. This is usually tolerable while the index fits in memory, but on a large one inserts read and write noticeably more pages.

UUIDv7 (RFC 9562). The first 48 bits are a Unix timestamp in milliseconds; apart from the version and variant bits, the rest is random (Postgres 18 uses 12 of those bits for a sub-millisecond fraction). Values generated at roughly the same time are close in the index, so inserts go mostly to the rightmost pages, as with a sequence. Postgres 18 has a built-in uuidv7() function. For Postgres 13–17 it can be written in SQL:

create function uuidv7_at(ts timestamptz) returns uuid
language sql volatile as $$
  select encode(
    set_bit(set_bit(
      overlay(uuid_send(gen_random_uuid())
              placing substring(int8send(floor(extract(epoch from ts) * 1000)::bigint) from 3)
              from 1 for 6),
      52, 1), 53, 1),
    'hex')::uuid;
$$;

It takes a random v4 UUID, overwrites the first 6 bytes with the millisecond timestamp and changes the version bits from 4 to 7. The ts parameter also makes it usable for backfilling old rows from created_at. On Postgres 18 uuidv7((extract(epoch from created_at) - extract(epoch from clock_timestamp())) * interval '1 second') gives a UUID whose timestamp matches created_at to within microseconds, because uuidv7() accepts an interval that shifts the timestamp; those few microseconds occasionally push the stored millisecond up by one. The shorter uuidv7(created_at - clock_timestamp()) is only safe in a time zone without daylight saving, such as UTC: the difference of two timestamptz values carries days that uuidv7() adds in the session time zone, and across a daylight-saving change in Europe/Warsaw the timestamp came out shifted by an hour.

For new tables with more than one writer I'd choose UUIDv7, and accept the costs:

  • 16 bytes instead of 8 in the primary key and in every foreign key and index that contains it,
  • the identifier reveals when the row was created, which matters if IDs appear in public URLs,
  • ordering by ID is only approximately chronological across nodes, because it depends on their clocks.

Migrating an existing table: expand, backfill, swap

Take an orders table with a bigint primary key and an order_items table that references it. The goal is a UUIDv7 primary key without locking either table for longer than a few seconds.

1. Add the column and a default for new rows. Adding a nullable column without a default only changes the catalog. Setting the default afterwards affects new rows only.

alter table orders add column id_v2 uuid;
alter table orders alter column id_v2 set default uuidv7();            -- Postgres 18
-- alter table orders alter column id_v2 set default uuidv7_at(clock_timestamp());  -- Postgres 13–17

With several nodes, add the new columns (here and in step 4) on all of them before any of them starts filling them. A write to the table on a node that already has the new column stops replication to the nodes that don't have it yet, even when the column is empty. It resumes on its own once they add it. Set the default only once the empty column exists on every node. In the opposite order, a row inserted on a node without the column gets its own UUID from the default on the node that has it, but stays NULL on the node where it was inserted. Run the backfill on that node: its UUID reaches the other node through replication and replaces the one there. On the node that already has values the loop skips these rows, so the NULLs stay (checked on Postgres 18.6).

2. Backfill in batches. A single update on the whole table holds row locks for its entire duration and leaves a large number of dead tuples at once. Batches with a commit in between keep locks short and give autovacuum time to clean up between batches:

do $$
declare
  batch_size constant bigint := 10000;
  from_id bigint := 0;
  max_id bigint;
begin
  select max(id) into max_id from orders;
  while from_id <= max_id loop
    update orders
       set id_v2 = uuidv7_at(created_at)
     where id >= from_id and id < from_id + batch_size
       and id_v2 is null;
    commit;
    from_id := from_id + batch_size;
  end loop;
end $$;

The loop calls uuidv7_at, so on Postgres 18 either create that function too or use the epoch-based uuidv7(...) expression above instead. commit inside a do block works when the block isn't run inside an explicit transaction. In production it's easier to run the same loop from a script that can pause when replication lag grows.

3. Index and not null without a long lock.

create unique index concurrently orders_id_v2_uq on orders (id_v2);

alter table orders add constraint orders_id_v2_nn check (id_v2 is not null) not valid;
alter table orders validate constraint orders_id_v2_nn;
alter table orders alter column id_v2 set not null;   -- uses the validated check, no full scan
alter table orders drop constraint orders_id_v2_nn;

set not null skips the table scan when a validated check constraint already proves the condition (Postgres 12 and newer). validate constraint takes a lock that doesn't block reads or writes.

4. The same for child tables. Add order_id_v2, keep it filled for new rows with a trigger, backfill old rows in batches by joining to orders:

alter table order_items add column order_id_v2 uuid;

create function order_items_sync_order_id_v2() returns trigger
language plpgsql as $$
begin
  select o.id_v2 into new.order_id_v2 from orders o where o.id = new.order_id;
  return new;
end $$;

create trigger order_items_sync_order_id_v2
  before insert or update of order_id on order_items
  for each row execute function order_items_sync_order_id_v2();

update order_items i
   set order_id_v2 = o.id_v2
  from orders o
 where o.id = i.order_id
   and i.order_id_v2 is null
   and i.id >= 0 and i.id < 10000;   -- repeated per batch

Then the same not valid / validate sequence for not null on order_items.order_id_v2, plus an index for the future foreign key. A foreign key doesn't create one by itself. Without it, every delete on orders, every update of orders.id and every lookup of an order's items scans all of order_items:

create index concurrently order_items_order_id_v2_idx on order_items (order_id_v2);

5. Swap in one short transaction. If anything still looks rows up by the old numeric ID, create a unique index on orders (id) concurrently before this step, because dropping the primary key removes the old one.

begin;
set local lock_timeout = '5s';

drop trigger order_items_sync_order_id_v2 on order_items;
alter table order_items drop constraint order_items_order_id_fkey;

alter table orders drop constraint orders_pkey;
alter table orders add constraint orders_pkey primary key using index orders_id_v2_uq;

alter table orders rename column id to legacy_id;
alter table orders rename column id_v2 to id;
alter table order_items rename column order_id to legacy_order_id;
alter table order_items rename column order_id_v2 to order_id;
alter table order_items alter column legacy_order_id drop not null;  -- new rows no longer fill it
-- if the old id had no default (no bigserial/identity), also:
-- alter table orders alter column legacy_id drop not null;

alter table order_items add constraint order_items_order_id_fkey
  foreign key (order_id) references orders (id) not valid;
commit;

alter table order_items validate constraint order_items_order_id_fkey;

Every statement in the transaction changes only the catalog, so the access exclusive lock is held briefly. lock_timeout caps the wait: if the lock isn't granted within 5 s, the transaction rolls back instead of queueing behind a long-running query. The limit applies to each lock separately, and the transaction takes its locks one after another, so the wait can add up to several 5 s stretches. Queries that line up behind it wait all that time. The trigger is dropped in the same transaction because its body refers to the column names that are being renamed.

After the rename, orders.id is a uuid. The rename breaks any code that still uses id_v2 or treats id as a number, so the release that knows the new names has to go out together with step 5. Until it's live, anything in the old code that uses these IDs fails, reads included, so either pause traffic to these tables for those few seconds or accept the errors. A second, cleanup release removes legacy_id and legacy_order_id after the swap.

Before step 1, list everything that stores the old ID: tables, views, materialized views, report queries, external systems. Child tables are often larger than the parent, and their new column has to copy the parent's UUID, which is why their backfill goes through the join in step 4. Replication lag and autovacuum need watching while the backfill runs.

With several nodes on logical replication, schema changes (DDL) aren't replicated, so every alter and create has to run on each node separately. The swap in step 5 is the hard part. Logical replication matches columns by name, so between the swap on the first node and the swap on the last one, as soon as a change to orders or order_items arrives, the subscription that carries them stops with a "missing replicated column" error, along with every other table in it, and WAL piles up on the publisher's slot. For the swap, stop writes to these tables on all nodes, wait until replication has caught up (zero lag on every subscription), and only then swap everywhere. Resume writes after the swap on the last node. Disabling the subscriptions is not enough on its own: changes written before the swap arrive with the old column names.

The backfill, being ordinary DML, does replicate. Run it on one node only and let replication carry it to the others. uuidv7_at has a random part, so running it on every node would give the same row a different UUID on each. Replication sends the whole row, so if another node updates the same row before the backfill reaches it, the nodes diverge without any error: in a test on 18.6 the backfilled id_v2 came back as NULL on one node and the user's edit was lost on the other. While the backfill runs, pause updates to these tables on the other nodes, then compare the tables across nodes and rerun the loop for rows where id_v2 is still NULL. The same applies to order_items in step 4. The legacy columns stay until every consumer has switched, and dropping them is a separate change.

I'd also rehearse restoring a backup on a copy first, because that's the way back if the swap goes wrong. When I did that on a copy of my SaaS database, it turned out that pg_restore --clean --if-exists drops only the objects that are in the dump. A table added by a newer migration stayed in place, the restored migrations table didn't know about it, and migrate --force ended with SQLSTATE[42P07]: Duplicate table ... already exists. What worked was a fresh database: dropdb, createdb, restore, migrate, 1.5 s in total. I only did this on the copy, never on production. The copy you rehearse on shouldn't run an older major version than production, because the docs don't guarantee that a dump from 17 loads into 16. That matters for me: one of my production servers is a VPS on Postgres 17 (the distribution on it doesn't package 16), while my development machine runs 16.

end of node