Postgres z więcej niż jednym writerem: klucze główne i ich migracja

Kolumna bigserial albo identity bierze wartości z sekwencji. Sekwencja jest lokalna dla jednej instancji Postgresa, więc daje unikalne wartości tylko wtedy, gdy każdy insert przechodzi przez tę instancję. Przy jednym primary tak jest, więc sama unikalność nie jest powodem do zmiany typu klucza. To się zmienia, gdy zapisy przyjmuje więcej niż jeden węzeł albo gdy identyfikatory powstają poza bazą.

Kiedy typ klucza ma znaczenie

Zmiana typu klucza nie skraca czasu zapisu przy jednym primary. Klient z innego regionu i tak wysyła całą transakcję do primary. Wywołanie sekwencji to pomijalna część tej drogi.

Typ klucza ma znaczenie, gdy:

kilka węzłów przyjmuje zapisy do tej samej tabeli (dwukierunkowa replikacja logiczna, rozszerzenia multi-master, sharding po regionach),
identyfikator musi istnieć, zanim wiersz trafi do bazy (klienci offline, klucze idempotencji generowane po stronie klienta, zdarzenia publikowane przed insertem),
dane z kilku niezależnych baz są scalane w jednym miejscu.

W każdym z tych przypadków dwa węzły bez koordynacji mogą wygenerować ten sam bigint.

Opcje

Sekwencja per węzeł. Każdy węzeł ma własną sekwencję z innym startem i wspólnym krokiem:

-- węzeł 1
create sequence orders_id_seq start with 1 increment by 16;
-- węzeł 2
create sequence orders_id_seq start with 2 increment by 16;

Klucze zostają 8-bajtowymi liczbami, istniejące klucze obce działają bez zmian. Koszt jest operacyjny: krok ogranicza liczbę węzłów, każdy nowy węzeł potrzebuje wolnego offsetu, a źle skonfigurowany węzeł generuje kolizje, które wychodzą dopiero przy replikacji.

UUIDv4. Unikalny bez koordynacji i losowy. Każdy insert trafia na losową stronę liścia w indeksie B-tree. Roboczym zbiorem staje się więc cały indeks, podziały stron rozkładają się po całym drzewie, a po checkpoincie więcej stron trafia do WAL w całości. Póki indeks mieści się w pamięci, zwykle da się z tym żyć. Gdy przestaje, każdy insert czyta i zapisuje wyraźnie więcej stron.

UUIDv7 (RFC 9562). Pierwsze 48 bitów to znacznik czasu Unix w milisekundach. Poza bitami wersji i wariantu reszta jest losowa (Postgres 18 zużywa 12 z tych bitów na ułamek milisekundy). Wartości wygenerowane w podobnym czasie leżą w indeksie blisko siebie, więc inserty trafiają głównie na skrajne prawe strony, jak przy sekwencji. Postgres 18 ma wbudowaną funkcję uuidv7(). Na Postgresie 13–17 można ją napisać w 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;
$$;

Funkcja bierze losowy UUID v4, nadpisuje pierwsze 6 bajtów znacznikiem czasu w milisekundach i zmienia bity wersji z 4 na 7. Dzięki parametrowi ts nadaje się też do uzupełniania starych wierszy na podstawie created_at. Na Postgresie 18 UUID ze znacznikiem równym created_at z dokładnością do mikrosekund daje uuidv7((extract(epoch from created_at) - extract(epoch from clock_timestamp())) * interval '1 second'), bo uuidv7() przyjmuje interwał przesuwający znacznik czasu. Przez te mikrosekundy zapisana milisekunda czasem przeskakuje o jedną. Krótsze uuidv7(created_at - clock_timestamp()) jest bezpieczne tylko w strefie bez zmiany czasu, np. w UTC: różnica dwóch timestamptz zawiera dni, które uuidv7() dodaje w strefie czasowej sesji, i przy zmianie czasu w Europe/Warsaw znacznik wychodził przesunięty o godzinę.

Dla nowych tabel zapisywanych z kilku węzłów wybrałbym UUIDv7, godząc się na koszty:

16 bajtów zamiast 8 w kluczu głównym oraz w każdym kluczu obcym i indeksie, który go zawiera,
identyfikator zdradza czas utworzenia wiersza, co ma znaczenie, jeśli ID pojawia się w publicznych URL-ach,
sortowanie po ID jest chronologiczne tylko w przybliżeniu między węzłami, bo zależy od ich zegarów.

Migracja istniejącej tabeli: expand, backfill, swap

Weźmy tabelę orders z kluczem bigint i tabelę order_items, która się do niej odwołuje. Chcemy dojść do klucza głównego UUIDv7, nie blokując żadnej z nich na dłużej niż kilka sekund.

1. Kolumna i domyślna wartość dla nowych wierszy. Dodanie kolumny dopuszczającej null, bez wartości domyślnej, zmienia tylko katalog. Ustawienie domyślnej wartości później dotyczy wyłącznie nowych wierszy.

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

Przy kilku węzłach nowe kolumny (tu i w kroku 4) trzeba dodać na wszystkich, zanim którykolwiek zacznie je wypełniać. Zapis do tabeli na węźle, który ma już nową kolumnę, zatrzymuje replikację do węzłów, które jej jeszcze nie mają, nawet gdy kolumna jest pusta. Replikacja rusza sama, gdy kolumna pojawi się na pozostałych węzłach. Wartość domyślną ustaw dopiero wtedy, gdy pusta kolumna jest już na wszystkich węzłach. W odwrotnej kolejności wiersz wstawiony na węźle bez kolumny dostaje na węźle z wartością domyślną własny UUID, a na swoim węźle, po dodaniu kolumny, ma NULL. Backfill puść wtedy na węźle z NULL-ami: jego UUID dojdzie replikacją do drugiego węzła i zastąpi tamten. Na węźle, który ma już wartości, pętla z id_v2 is null niczego nie zmieni i NULL-e zostaną (sprawdzone na Postgresie 18.6).

2. Backfill w paczkach. Jeden update na całej tabeli trzyma blokady wierszy przez cały czas trwania i zostawia naraz ogromną liczbę martwych krotek. Paczki z commitem po każdej z nich skracają blokady i dają autovacuum czas na sprzątanie między paczkami:

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 $$;

Pętla woła uuidv7_at, więc na Postgresie 18 też trzeba tę funkcję utworzyć albo wpisać zamiast niej podane wyżej wyrażenie uuidv7(...) liczone od epoki. commit w bloku do działa, jeśli blok nie jest uruchomiony wewnątrz jawnej transakcji. Na produkcji wygodniej puścić tę samą pętlę ze skryptu, który zrobi przerwę, gdy rośnie opóźnienie replikacji.

3. Indeks i not null bez długiej blokady.

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;   -- korzysta ze zwalidowanego checka, bez pełnego skanu
alter table orders drop constraint orders_id_v2_nn;

Od Postgresa 12 set not null pomija skan tabeli, jeśli warunek gwarantuje już zwalidowane ograniczenie check. validate constraint bierze blokadę, która nie wstrzymuje odczytów ani zapisów.

4. To samo dla tabel podrzędnych. Dodaj order_id_v2, nowe wiersze uzupełniaj triggerem, stare uzupełnij w paczkach przez złączenie z 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;   -- powtarzane dla każdej paczki

Potem ta sama sekwencja not valid / validate dla not null na order_items.order_id_v2 i indeks pod przyszły klucz obcy. Klucz obcy sam indeksu nie tworzy, a bez niego delete w orders (i update samego klucza) oraz złączenia szukające pozycji danego zamówienia skanują całe order_items:

create index concurrently order_items_order_id_v2_idx on order_items (order_id_v2);

5. Podmiana w jednej krótkiej transakcji. Jeśli coś nadal wyszukuje wiersze po starym numerycznym ID, przed tym krokiem załóż współbieżnie unikalny indeks na orders (id), bo usunięcie klucza głównego usuwa stary indeks.

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;  -- nowe wiersze już jej nie wypełniają
-- jeśli stare id nie miało wartości domyślnej (bez bigserial/identity), także:
-- 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;

Każda instrukcja w tej transakcji zmienia tylko katalog, więc blokada access exclusive trwa krótko. lock_timeout ogranicza czekanie: jeśli w ciągu 5 s nie dostanie blokady, transakcja się wycofa, zamiast stać w kolejce za długim zapytaniem. Limit dotyczy każdej blokady osobno, a transakcja bierze je po kolei, więc czekanie może się złożyć z kilku odcinków po 5 s. Przez ten czas zapytania ustawione za nią i tak czekają. Trigger jest usuwany w tej samej transakcji, bo jego treść odwołuje się do kolumn, którym zmieniamy nazwy.

Po zmianie nazw orders.id jest typu uuid. Zmiana nazw psuje więc kod, który używa id_v2 albo traktuje id jak liczbę. Wydanie znające nowe nazwy musi wyjść razem z krokiem 5. Do chwili wdrożenia stary kod, który używa tych identyfikatorów, dostaje błędy, także przy odczytach, więc na te kilka sekund wstrzymaj ruch do tych tabel albo przyjmij błędy. Drugie wydanie, sprzątające, po podmianie usuwa legacy_id i legacy_order_id.

Przed krokiem 1 spisz wszystko, co przechowuje stare ID: tabele, widoki, widoki zmaterializowane, zapytania raportowe, systemy zewnętrzne. Tabele podrzędne bywają większe od nadrzędnej, a ich nowa kolumna musi skopiować UUID rodzica, dlatego ich backfill idzie przez złączenie z kroku 4. W trakcie backfillu trzeba pilnować opóźnienia replikacji i autovacuum.

Przy kilku węzłach z replikacją logiczną zmiany schematu (DDL) się nie replikują, więc każdy alter i create trzeba wykonać na każdym węźle osobno. Najtrudniejsza jest podmiana z kroku 5. Replikacja logiczna dopasowuje kolumny po nazwie, więc między podmianą na pierwszym węźle a podmianą na ostatnim, gdy tylko przyjdzie zmiana w orders albo order_items, staje z błędem „missing replicated column” cała subskrypcja, która je przenosi, razem z każdą inną tabelą w niej. Na slocie wydawcy rośnie w tym czasie WAL. Na czas podmiany trzeba zatrzymać zapisy do tych tabel na wszystkich węzłach, poczekać, aż replikacja dogoni (zero opóźnienia na każdej subskrypcji), i dopiero wtedy podmienić wszędzie. Zapisy wznawia się po podmianie na ostatnim węźle. Samo wyłączenie subskrypcji nie wystarczy: zmiany zapisane przed podmianą przyjdą ze starymi nazwami kolumn.

Backfill to zwykłe zapisy. Puszcza się go na jednym węźle, a do reszty dociera replikacją: uuidv7_at ma losową część i dwa węzły nadałyby temu samemu wierszowi różne UUID. Replikacja wysyła cały wiersz, więc jeśli inny węzeł zmieni ten sam wiersz, zanim dotrze do niego backfill, węzły się rozjadą bez żadnego błędu: w teście na 18.6 na jednym węźle uzupełnione id_v2 wróciło do NULL, a na drugim przepadła zmiana użytkownika. Na czas backfillu wstrzymaj update'y tych tabel na pozostałych węzłach, a potem porównaj tabele między węzłami i powtórz pętlę dla wierszy, w których id_v2 jest wciąż NULL. To samo dotyczy order_items w kroku 4. Stare kolumny zostają, dopóki wszyscy konsumenci się nie przełączą, a ich usunięcie to osobna zmiana.

Przed krokiem 1 przećwiczyłbym też na kopii odtwarzanie backupu, bo to droga powrotna, gdyby podmiana poszła źle. Kiedy robiłem to na kopii bazy mojego SaaS-a, okazało się, że pg_restore --clean --if-exists usuwa tylko obiekty obecne w dumpie. Tabela dodana przez nowszą migrację została, odtworzona tabela migrations nic o niej nie wiedziała i migrate --force kończyło się na SQLSTATE[42P07]: Duplicate table ... already exists. Zadziałał wariant ze świeżą bazą: dropdb, createdb, restore, migrate, razem 1,5 s. Robiłem to tylko na kopii, nie na produkcji. Kopia do ćwiczeń nie powinna mieć starszej wersji głównej niż produkcja, bo dokumentacja nie gwarantuje, że dump z 17 wgra się do 16. U mnie to ma znaczenie, bo jedna z moich produkcji stoi na VPS-ie z Postgresem 17 (system nie miał 16 w repozytoriach), a maszyna deweloperska ma 16.