TOAST został wprowadzony w PostgreSQL, aby obsługiwać dane wierszy przekraczające rozmiar strony 8KB poprzez kompresję dużych kolumn w osobnym fizycznym przechowywaniu. Gdy replikacja logiczna przesyła zmiany za pomocą WAL, ustawienie REPLICA IDENTITY określa, które starsze wartości krotki są dołączane. Domyślnie REPLICA IDENTITY DEFAULT wysyła tylko klucz główny, podczas gdy REPLICA IDENTITY FULL wysyła pełny obraz starego wiersza.
Gdy tabela zawiera kolumny JSONB lub TEXT, które przekraczają ~2KB i są kompresowane w TOAST, operacje UPDATE, które modyfikują tylko kolumny niebędące TOAST, mogą nie pobierać zewnętrznych wartości TOAST dla rekordu WAL. Proces dekodowania logicznego pomija niezmienione wskaźniki TOAST, aby zredukować I/O, co powoduje, że subskrybent otrzymuje NULL lub brakujące wartości dla tych dużych pól podczas rozwiązywania konfliktów.
Przełączenie na REPLICA IDENTITY FULL zmusza PostgreSQL do uwzględnienia pełnego starego krotki w rekordzie WAL, wyraźnie pobierając wszystkie wartości TOAST z zewnętrznego magazynu podczas zatwierdzenia. Choć gwarantuje to kompletność danych dla operacji UPSERT, znacznie zwiększa to wolumen WAL — często o 300-500% dla szerokich tabel JSONB — ponieważ każda AKTUALIZACJA musi rejestrować pełny obraz wstecz krotki.
Platforma handlu finansowego potrzebowała replikować migawki książki zamówień z głównego klastra PostgreSQL 15 do hurtowni danych w celu raportowania regulacyjnego. Tabela market_data przechowywała identyfikatory instrumentów oraz duże ładunki JSONB (10-50KB) zawierające informacje o głębokości książki zamówień. Replikacja używała pglogical z REPLICA IDENTITY DEFAULT (tylko klucz główny). Proces ETL po stronie hurtowni próbował przeprowadzić operacje UPSERT, aby utrzymać tabelę wymiarów o wolno zmieniających się danych, wymagając starych wartości JSONB do obliczenia zmian delta do dziennika audytu.
W okresach intensywnego handlu, gdy książka zamówień była często aktualizowana, ale ładunek JSONB pozostawał niezmieniony, strumień replikacji logicznej wysyłał rekordy UPDATE zawierające tylko klucz główny i dane nowego krotki. Stare wartości JSONB w formie TOAST nie były uwzględniane w zbiorze zmian, ponieważ instrukcja UPDATE dotykała tylko kolumny znaczników czasu updated_at. Proces ETL nie mógł uzyskać preaktualnego stanu JSONB, co uniemożliwiało obliczenie dokładnych zmian cenowych w dzienniku audytowym, naruszając wymogi zgodności MiFID II.
Rozwiązanie 1: Przełączenie na REPLICA IDENTITY FULL To podejście zmusiłoby wydawcę do zapisania pełnego obrazu starego wiersza w WAL dla każdej AKTUALIZACJI, uwzględniając całą zawartość JSONB z przechowywania TOAST. Zalety obejmowały gwarancję kompletności danych i prostą implementację wymagającą braku zmian w schemacie. Jednak wady były znaczące: generacja WAL wzrosłaby o około 400% przy obciążeniu 50KB, ryzykując wyczerpanie miejsca na dysku w głównym i zwiększając opóźnienie sieciowe do hurtowni. Dla tabeli przetwarzającej 10.000 aktualizacji na sekundę, uznano to za zbyt ryzykowne dla stabilności produkcji.
Rozwiązanie 2: Dziennikowanie na poziomie aplikacji z osobną tabelą historii
Zespół rozważył stworzenie wyzwalacza na głównym, który kopiowałby stare wartości JSONB do osobnej tabeli market_data_history przed aktualizacją. Zalety polegały na tym, że replikacja logiczna mogłaby osobno replikować tę tabelę historii, unikając problemu pominięcia TOAST w głównej tabeli, jednocześnie utrzymując niewielkie obciążenie WAL głównej tabeli. Wady obejmowały dodatkowy czas zapisu na głównym (zwiększający opóźnienie transakcji), dodatkowe wymagania przechowywania rosnące w tempie 2x oraz złożoność logiki ETL, aby skorelować rekordy historii z zmianami w głównej tabeli za pomocą identyfikatorów transakcji i znaczników czasu.
Rozwiązanie 3: Użycie REPLICA IDENTITY z indeksem pokrywającym zawierającym hasz JSONB
Ta strategia polegała na stworzeniu indeksu funkcjonalnego na md5(jsonb_column::text) i uwzględnieniu tego hashu w złożonym indeksie REPLICA IDENTITY. Zalety polegały na tym, że zmiany w zawartości JSONB byłyby wykrywane dzięki zmianie hasha w WAL bez wysyłania pełnego ładunku. Wady obejmowały niemożność uzyskania rzeczywistej starej wartości (tylko jego hash), co było niewystarczające do wymogu regulacyjnego pokazania dokładnego stanu przed aktualizacją oraz obciążenie utrzymania indeksu na tabelach o wysokiej rotacji.
Zespół wybrał Rozwiązanie 2 (Dziennikowanie na poziomie aplikacji), ale z modyfikacją. Wykorzystali optymalizację częściowej aktualizacji JSONB dostępną w wersji 14+ PostgreSQL i zaimplementowali wyzwalacz BEFORE UPDATE, który przechowywał tylko zmienione ścieżki (różnicę) zamiast pełnego starego wiersza. Zmniejszyło to wzrost tabeli historii, zapewniając jednocześnie dostępność wszystkich niezbędnych danych o obrazie. Zachowali REPLICA IDENTITY DEFAULT w głównej tabeli, aby uniknąć nadmiaru WAL, kierując ETL do łączenia danych w tabeli historii dla rekonstrukcji audytu.
Wielkość strumienia replikacji pozostała stabilna, co zapobiegło naciskowi na główną pamięć. Proces ETL pomyślnie zrekonstruował pełne ścieżki audytu, łącząc stan bieżącego wiersza z przechowywanymi różnicami z tabeli historii. Zgodność regulacyjna została osiągnięta przy tylko 15% wzroście w głównym miejscu (w porównaniu do 400% dla REPLICA IDENTITY FULL) i minimalnym wpływie na przezroczystość transakcji.
Dlaczego dekodowanie logiczne PostgreSQL pomija wartości TOAST, nawet gdy kolumna jest modyfikowana?
Wielu kandydatów zakłada, że każda AKTUALIZACJA automatycznie pobiera wszystkie wartości TOASTed do WAL. Jednak PostgreSQL przeprowadza "tuple unTOASTing" tylko wtedy, gdy wykonawca rzeczywiście odczytuje dany element, aby go zmodyfikować. Jeśli AKTUALIZACJA modyfikuje inną kolumnę (np. SET updated_at = NOW()) bez odniesienia do kolumny JSONB w jej liście docelowej lub klauzuli WHERE, wskaźnik TOAST pozostaje niezmieniony, a zewnętrzne przechowanie nie jest uzyskiwane. Rekord WAL zawiera zatem tylko krotkę na dysku z jej wskaźnikiem, a nie rzeczywiste dane. Ponieważ dekodowanie logiczne rekonstruuje krotki z WAL bez dostępu do tabel magazynowych lub TOAST dla starych wersji, pominięta wartość pojawia się jako NULL w strumieniu zmian.
Jak REPLICA IDENTITY FULL współdziała z aktualizacjami HOT (Heap-Only Tuple)?
Kandydaci często umykają, że REPLICA IDENTITY FULL dezaktywuje aktualizacje HOT dla tabeli. HOT updates pozwala PostgreSQL na łączenie wersji wierszy w tej samej stronie danych bez aktualizowania każdego wpisu w indeksie, pod warunkiem, że żadna kolumna indeksowana nie ulega zmianie. Gdy REPLICA IDENTITY FULL jest aktywne, każda AKTUALIZACJA musi rejestrować pełny obraz starego wiersza dla replikacji, co wymaga od systemu identyfikacji wiersza w sposób unikalny na podstawie jego pełnej zawartości. To łamie optymalizację HOT, ponieważ replikacja logiczna potrzebuje pełnych danych do porównania krotek, zmuszając aktualizacje indeksów dla każdej wersji wiersza, nawet przy modyfikacji kolumn nieindeksowanych. W konsekwencji tabele z tym ustawieniem doświadczają większego przepełnienia indeksu i zwiększonego I/O, co stanowi krytyczną wymianę dla tabel o wysokiej rotacji.
Jaka jest różnica między kompresją TOAST a kompresją WAL w PostgreSQL i jak współdziałają podczas replikacji logicznej?
To pytanie oddziela głęboką wiedzę systemową od powierzchownego zrozumienia. Kompresja TOAST zmniejsza rozmiar wiersza przy użyciu LZ4 lub PGLZ przed zapisaniem dużych kolumn w zewnętrznych tabelach. Kompresja WAL (włączana za pomocą wal_compression=lz4) kompresuje pełne obrazy stron zapisywane w WAL dla efektywności odzyskiwania po awarii. Jednak gdy używane jest REPLICA IDENTITY FULL, dane starej krotki wysyłane do dekodowania logicznego są wydobywane przed skompresowaniem rekordu WAL do przechowywania. Dlatego dekoder logiczny otrzymuje nieskompresowane dane TOAST (jeśli są pobrane), podczas gdy fizyczny plik WAL może je przechowywać w skompresowanej postaci, jeśli jest częścią obrazu pełnej strony, co wpływa na przepustowość sieci w porównaniu do I/O dysku inaczej.