Pseudo-tabela EXCLUDED w PostgreSQL reprezentuje wiersz proponowany do wstawienia podczas operacji ON CONFLICT. Historycznie, programiści migrujący z środowisk MySQL lub Oracle często zakładają, że bezpośrednie porównania równości (=) wystarczą do wykrywania zmian wartości w wzorcach upsert. Jednakże, trójwartościowa logika standardu SQL dyktuje, że NULL reprezentuje stan nieznany, co oznacza, że NULL = NULL ocenia się na NULL (nieznane), a nie TRUE.
To stwarza istotny problem, gdy klauzula rozwiązania konfliktu próbuje optymalizować aktualizacje, dodając klauzulę WHERE taką jak WHERE EXCLUDED.phone != users.phone. Jeśli zarówno istniejący wiersz, jak i proponowany wiersz zawierają NULL dla kolumny telefonu, porównanie zwraca NULL, co skutkuje niepowodzeniem predykatu WHERE. W konsekwencji baza danych pomija aktualizację, mimo że wartości mogą być naprawdę różne w kontekście logiki biznesowej, lub nie możemy odróżnić NULL w nowych danych od NULL w starych danych.
Rozwiązanie polega na wykorzystaniu operatora IS DISTINCT FROM, który traktuje NULL jako porównywalną wartość. Dokonując strukturyzacji klauzuli aktualizacji z WHERE EXCLUDED.column IS DISTINCT FROM table.column, porównanie zwraca FALSE, gdy obie wartości są NULL (co oznacza brak zmiany) i TRUE, gdy jedna jest NULL, a druga nie. To zapewnia deterministyczne zachowanie, jednocześnie zapobiegając niepotrzebnym zapisom.
INSERT INTO patient_records (clinic_id, external_id, phone, updated_at) VALUES (101, 'P-2024-001', NULL, NOW()) ON CONFLICT (clinic_id, external_id) DO UPDATE SET phone = COALESCE(EXCLUDED.phone, patient_records.phone), updated_at = EXCLUDED.updated_at WHERE EXCLUDED.phone IS DISTINCT FROM patient_records.phone;
Sieć szpitalna musiała synchronizować codzienne dane o przyjęciach pacjentów z 50 zewnętrznych klinik do centralnego hurtowni danych PostgreSQL. Każda klinika eksportowała pliki CSV, gdzie brakujące numery telefonów pacjentów pojawiały się jako puste ciągi, które komenda COPY konwertowała na NULL podczas wczytywania. Istniejący skrypt ETL w Pythonie używał SQLAlchemy do wykonywania masowych upsertów z ON CONFLICT (clinic_id, external_id) DO UPDATE SET phone = EXCLUDED.phone.
Problem pojawił się, gdy personel kliniki zgłosił, że ważne numery telefonów wprowadzone bezpośrednio do systemu centralnego tajemniczo znikają po nocnej synchronizacji. Śledztwo ujawniło, że kiedy zewnętrzny kanał wysyłał NULL (co wskazywało na nieznany telefon), nadpisywał istniejące ważne numery, ponieważ klauzula SET była wykonywana bezwarunkowo. Dodanie naiwnego filtru WHERE EXCLUDED.phone != patient_records.phone nie zadziałało, ponieważ kiedy obie były NULL, porównanie zwróciło NULL (nieznane), co spowodowało, że aktualizacja została pominięta niewłaściwie, a gdy nowa wartość była NULL, a stara nie, logika działała niespójnie w różnych mniejszych wersjach PostgreSQL.
Oceniono trzy rozwiązania.
Pierwsze podejście używało COALESCE wyłącznie w klauzuli SET: SET phone = COALESCE(EXCLUDED.phone, patient_records.phone). To zapobiegało nadpisywaniu NULL, ale wymuszało aktualizację w każdym konflikcie, wyzwalając kosztowne przebudowy indeksu B-Tree na kolumnie phone oraz uruchamiając wyzwalacze audytowe, które rejestrowały zmiany „no-op” jako legitime modyfikacje. To zwiększyło ruch WAL (Write-Ahead Log) o 300%, zagrażając opóźnieniu replikacji i nasyceniu operacji dyskowych.
Drugie rozwiązanie próbowało wyraźnej logiki boolowskiej do obsługi NULL: WHERE (EXCLUDED.phone != patient_records.phone) OR (EXCLUDED.phone IS NULL AND patient_records.phone IS NOT NULL). Choć logicznie poprawne, to obszerne wzorce wymagały starannego utrzymania w 15 nullable kolumnach i dezorientowały optymalizator zapytań. Planista zrezygnował z przeszukiwania indeksów na rzecz przeszukiwania sekwencyjnego w tabeli z 20 milionami wierszy, co spowodowało, że zadanie ETL przekroczyło swoje sześciogodzinne okno konserwacji.
Trzecie rozwiązanie zaimplementowało IS DISTINCT FROM dla wszystkich nullable kolumn w klauzuli WHERE. To zapewniło zwięzły, sargable predykat, który poprawnie identyfikował rzeczywiste zmiany danych, w tym przejścia między NULL. Pozwoliło to na aktualizacje tylko wtedy, gdy to konieczne, eliminując zbędne wyzwalanie wyzwalaczy oraz generowanie WAL, jednocześnie utrzymując spójne plany zapytań.
Zespół wybrał trzecie rozwiązanie dla krytycznych pól kontaktowych i pierwsze rozwiązanie dla niekrytycznych metadanych, gdzie ochrona przed nadpisaniem była ważniejsza od wydajności. Rezultat był dramatyczny: czas trwania zadania synchronizacyjnego spadł z 45 minut do 12 minut, opóźnienie replikacji ustabilizowało się poniżej pięciu sekund, a incydenty „znikających numerów telefonów” całkowicie ustały w ciągu pierwszego tygodnia wdrożenia.
Dlaczego klauzula WHERE EXCLUDED.column != table.column pomija wiersze, kiedy obie wartości są NULL, i jak to współdziała z mechanizmem aktualizacji PostgreSQL?
Wielu kandydatów zakłada, że jeśli dwa NULL nie są równe, porównanie powinno zwrócić TRUE i umożliwić aktualizację. Jednak SQL używa trójwartościowej logiki: NULL reprezentuje nieznaną wartość. Jakiekolwiek porównanie z NULL (w tym NULL = NULL lub NULL != NULL) skutkuje NULL (nieznane), a nie booleanskim TRUE lub FALSE. W klauzuli WHERE PostgreSQL, tylko wiersze, które oceniają do TRUE, przechodzą dalej; NULL traktowane jest jako FALSE. Więc podczas porównywania dwóch numerów telefonów NULL, wynik to NULL, aktualizacja zostaje pominięta, a system niepoprawnie zakłada, że niepotrzebna jest zmiana. IS DISTINCT FROM zwraca FALSE dla NULL vs NULL, poprawnie wskazując, że są identyczne i pomijając aktualizację tylko wtedy, gdy jest to odpowiednie, jednocześnie zwracając TRUE, gdy jedna wartość jest NULL, a druga nie.
Jak kolejność kolumn w unikalnym ograniczeniu z wieloma kolumnami wpływa na wydajność rozwiązania ON CONFLICT, a co się dzieje, jeśli cel konfliktu nie odpowiada dokładnie definicji indeksu?
Kandydaci często pomijają fakt, że PostgreSQL wymaga, aby cel konfliktu (kolumny wymienione w ON CONFLICT (...)) dokładnie odpowiadał definicji indeksu unikalnego, w tym kolejności kolumn i wszelkich wyrażeń funkcyjnych. Jeśli istnieje unikalny indeks na (clinic_id, external_id), ale zapytanie wskazuje ON CONFLICT (external_id, clinic_id), planista może nie zdołać wywnioskować indeksu, zgłaszając błąd „nie ma unikalnego ograniczenia lub ograniczenia wykluczenia odpowiadającego specyfikacji ON CONFLICT”. Nawet jeśli mu się to uda, niedopasowana kolejność kolumn może uniemożliwić optymalizatorowi użycie skanowania tylko-indeksów do zlokalizowania konfliktującej krotki, zmuszając do pobrania z kopca i znacznego zwiększenia kosztu I/O.
Jaka jest różnica między używaniem COALESCE(EXCLUDED.column, table.column) w klauzuli SET a używaniem WHERE EXCLUDED.column IS DISTINCT FROM table.column, szczególnie w kontekście uruchamiania wyzwalaczy i wersjonowania wierszy?
Użycie COALESCE w klauzuli SET bezwarunkowo zapisuje wartość do wiersza (zarówno nowe dane, jak i zachowane stare dane). Ta operacja generuje nową wersję wiersza (CTID), zapisuje do WAL i uruchamia wszystkie BEFORE i AFTER wyzwalacze związane z tabelą, nawet jeśli ostateczna wartość pozostaje identyczna z wcześniejszym stanem. To generuje „szum” w tabelach audytu i zwiększa obciążenie replikacji. Z drugiej strony, klauzula WHERE z IS DISTINCT FROM całkowicie zapobiega modyfikacji wiersza, jeśli nie doszło do rzeczywistej zmiany. Nie tworzy się nowa krotka wersji, wyzwalacze się nie uruchamiają, a generowanie WAL jest unika. Ta różnica ma kluczowe znaczenie dla systemów o wysokiej przepustowości z logowaniem audytów lub cascady kluczy obcych, gdzie aktualizacje „no-op” tworzą istotne obciążenie.