De EXCLUDED pseudo-tabel in PostgreSQL vertegenwoordigt de rij die wordt voorgesteld voor invoering tijdens een ON CONFLICT operatie. Historisch gezien gaan ontwikkelaars die migreren van MySQL of Oracle omgevingen vaak ervan uit dat directe gelijkheid vergelijkingen (=) voldoende zijn voor het detecteren van waarde wijzigingen binnen upsert patronen. Echter, de drie-waarde logica van de SQL standaard stelt dat NULL een onbekende staat vertegenwoordigt, wat betekent dat NULL = NULL evalueert naar NULL (onbekend), niet naar TRUE.
Dit creëert een kritisch probleem wanneer de conflictoplossingsclausule probeert om updates te optimaliseren door een WHERE clausule toe te voegen zoals WHERE EXCLUDED.phone != users.phone. Als zowel de bestaande rij als de voorgestelde rij NULL bevatten voor de telefoonkolom, retourneert de vergelijking NULL, wat de WHERE voorwaarde laat falen. Bijgevolg slaat de database de update over, ook al kunnen de waarden in de context van de bedrijfslogica daadwerkelijk verschillend zijn, of het kan niet onderscheiden worden tussen een NULL in de nieuwe data versus een NULL in de oude data.
De oplossing houdt in het gebruik van de IS DISTINCT FROM operator, die NULL behandelt als een vergelijkbare waarde. Door de update clausule te structureren met WHERE EXCLUDED.column IS DISTINCT FROM table.column, retourneert de vergelijking FALSE wanneer beide waarden NULL zijn (wat geen wijziging aangeeft) en TRUE wanneer de ene NULL is en de andere niet. Dit zorgt voor deterministisch gedrag terwijl onnodige schrijfbewerkingen worden voorkomen.
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;
Een ziekenhuisnetwerk moest dagelijkse patiënten-invoergegevens van 50 externe klinieken synchroniseren in een centrale PostgreSQL datawarehouse. Elke kliniek exporteerde CSV-bestanden waarin ontbrekende patiënttelefoonnummers als lege strings verschenen, die de COPY-opdracht tijdens het inladen naar NULL converteerde. Het bestaande Python ETL-script gebruikte SQLAlchemy om bulk upserts uit te voeren met ON CONFLICT (clinic_id, external_id) DO UPDATE SET phone = EXCLUDED.phone.
Het probleem kwam aan het licht toen het kliniekpersoneel meldde dat geldige telefoonnummers die rechtstreeks in het centrale systeem waren ingevoerd, mysterieuze verdwijningen vertoonden na de nachtelijke synchronisatie. Onderzoek onthulde dat wanneer de externe feed NULL verzond (wat een onbekende telefoon aangeeft), dit de bestaande geldige nummers overschreef omdat de SET clausule onvoorwaardelijk werd uitgevoerd. Het toevoegen van een naïeve filter WHERE EXCLUDED.phone != patient_records.phone faalde omdat wanneer beide NULL waren, de vergelijking NULL retourneerde (onbekend), wat ertoe leidde dat de update ten onrechte werd overgeslagen, en wanneer de nieuwe waarde NULL was en de oude niet, gedroeg de logica zich inconsistente over verschillende versies van PostgreSQL.
Drie oplossingen werden geëvalueerd.
De eerste aanpak gebruikte COALESCE uitsluitend in de SET clausule: SET phone = COALESCE(EXCLUDED.phone, patient_records.phone). Dit voorkwam het overschrijven met NULL maar dwong een update af bij elke conflict, waardoor dure B-Tree index herbouwingen op de phone kolom werden geactiveerd en audit triggers werden afgevuurd die "no-op" wijzigingen als legitieme aanpassingen logden. Dit verhoogde het WAL (Write-Ahead Log) verkeer met 300%, wat de replicatietraagheid bedreigde en de schijf-I/O verzadigde.
De tweede oplossing probeerde expliciete booleaanse logica te hanteren voor NULL: WHERE (EXCLUDED.phone != patient_records.phone) OR (EXCLUDED.phone IS NULL AND patient_records.phone IS NOT NULL). Hoewel logisch correct, vereiste dit uitgebreide patroon zorgvuldige onderhoud over 15 nullable kolommen en verwarde het de query-optimalisator. De planner liet indexscans varen ten gunste van sequentiële scans op de tabel van 20 miljoen rijen, waardoor de ETL-taak zijn zes uur durende onderhoudsvenster overschreed.
De derde oplossing implementeerde IS DISTINCT FROM voor alle nullable kolommen in de WHERE clausule. Dit bood een beknopte, sargable predicate die echte dataveranderingen, inclusief NULL-overgangen, correct identificeerde. Het stond updates alleen toe wanneer dat nodig was, waardoor overbodige trigger-executies en WAL-generatie werden geëlimineerd, terwijl consistente queryplannen werden behouden.
Het team koos de derde oplossing voor kritieke contactvelden en de eerste oplossing voor niet-kritieke metadata waar bescherming tegen overschrijven belangrijker was dan prestaties. Het resultaat was dramatisch: de synchronisatietaak duurde van 45 minuten tot 12 minuten, de replicatietraagheid stabiliseerde onder vijf seconden, en de "verdwenen telefoonnummer" incidenten stopten volledig binnen de eerste week van implementatie.
Waarom slaat WHERE EXCLUDED.column != table.column rijen over wanneer beide waarden NULL zijn, en hoe interageert dit met PostgreSQL's update-mechanisme?
Veel kandidaten gaan ervan uit dat als twee NULLs niet gelijk zijn, de vergelijking TRUE zou moeten retourneren en de update zou moeten toestaan. Echter, SQL gebruikt drie-waarde logica: NULL vertegenwoordigt een onbekende waarde. Elke vergelijking met NULL (inclusief NULL = NULL of NULL != NULL) resulteert in NULL (onbekend), niet in een booleaanse TRUE of FALSE. In de WHERE clausule van PostgreSQL gaan alleen rijen die evalueren naar TRUE verder; NULL wordt behandeld als FALSE. Dus, wanneer twee NULL telefoon nummers worden vergeleken, is het resultaat NULL, wordt de update overgeslagen, en gaat het systeem ten onrechte ervan uit dat er geen wijziging nodig is. IS DISTINCT FROM retourneert FALSE voor NULL vs NULL, wat correct aangeeft dat ze identiek zijn en de update alleen overslaat wanneer dat gepast is, terwijl het TRUE retourneert wanneer de ene waarde NULL is en de andere niet.
Hoe beïnvloedt de volgorde van kolommen in een meerkoloms unieke beperking de prestaties van de ON CONFLICT oplossing, en wat gebeurt er als het conflictdoel niet precies overeenkomt met de indexdefinitie?
Kandidaten vergeten vaak dat PostgreSQL vereist dat het conflict doel (de kolommen vermeld in ON CONFLICT (...)) precies overeenkomt met een unieke indexdefinitie, inclusief kolomvolgorde en eventuele functionele expressies. Als er een unieke index bestaat op (clinic_id, external_id) maar de query ON CONFLICT (external_id, clinic_id) specificeert, kan de planner falen om de index af te leiden, wat een foutmelding "er is geen unieke of uitsluitingsbeperking die overeenkomt met de ON CONFLICT specificatie" oplevert. Zelfs als het lukt, kan een mismatch in kolomvolgorde de optimizer verhinderen een index-only scan te gebruiken om de conflicterende tuple te vinden, wat leidt tot een heap-fetch en aanzienlijk verhoogde I/O-kosten.
Wat is het verschil tussen het gebruik van COALESCE(EXCLUDED.column, table.column) in de SET clausule versus het gebruik van WHERE EXCLUDED.column IS DISTINCT FROM table.column, vooral in verband met trigger-executie en rijversiebeheer?
Het gebruik van COALESCE in de SET clausule schrijft onvoorwaardelijk een waarde naar de rij (hetzij de nieuwe data of de behouden oude data). Deze operatie genereert een nieuwe rijversie (CTID), schrijft naar de WAL, en activeert alle BEFORE en AFTER triggers die aan de tabel zijn gekoppeld, zelfs als de uiteindelijke waarde identiek blijft aan de vorige staat. Dit creëert "ruis" in audit-tabellen en verhoogt de replicatielast. Omgekeerd voorkomt de WHERE clausule met IS DISTINCT FROM dat de rijwijziging volledig wordt uitgevoerd als er geen daadwerkelijke wijziging plaatsvond. Er wordt geen nieuwe tupleversie gemaakt, triggers worden niet geactiveerd, en WAL-generatie wordt vermeden. Dit onderscheid is cruciaal voor systemen met een hoge doorvoer met audit logging of vreemde sleutel cascades, waar "no-op" updates significante overhead creëren.