PostgreSQL'deki EXCLUDED sahte tablosu, bir ÇATIŞMA işlemi sırasında eklenmesi önerilen satırı temsil eder. Tarihsel olarak, MySQL veya Oracle ortamlarından geçen geliştiriciler, değerdeki değişiklikleri algılamak için doğrudan eşitlik karşılaştırmalarının (=) yeterli olduğunu varsayarlar. Ancak, SQL standart üç değerli mantığı, NULL'in bilinmeyen bir durumu temsil ettiğini belirtir; bu da NULL = NULL ifadesinin NULL (bilinmeyen) olarak değerlendirilmesi anlamına gelir, DOĞRU değil.
Bu, çelişki çözümleme ifadesinin, WHERE EXCLUDED.phone != users.phone gibi bir WHERE ifadesi ekleyerek güncellemeleri optimize etmeye çalıştığında kritik bir sorun yaratır. Eğer hem mevcut satır hem de önerilen satır phone sütununda NULL içeriyorsa, karşılaştırma NULL döner, bu da WHERE predikatını geçersiz kılar. Sonuç olarak, veritabanı yerde bir güncelleme atlar, oysa değerler ticari mantık bağlamında gerçekten farklı olabilir ya da yeni verideki NULL ile eski verideki NULL arasında ayrım yapamaz.
Çözüm, NULL'ı karşılaştırılabilir bir değer olarak ele alan IS DISTINCT FROM operatörünü kullanmaktır. Güncelleme ifadesini WHERE EXCLUDED.column IS DISTINCT FROM table.column ile yapılandırarak, her iki değer de NULL olduğunda karşılaştırma YANLIŞ döner (değişiklik olmadığını belirterek) ve bir değer NULL iken diğeri değilse DOĞRU döner. Bu, gereksiz yazımları önlerken belirleyici bir davranış sağlar.
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;
Bir hastane ağı, 50 dış klinikten günlük hasta kabul verilerini merkezi bir PostgreSQL veri ambarına senkronize etmemiz gerekti. Her klinik, eksik hasta telefon numaralarının boş dizeler olarak göründüğü CSV dosyaları ihraç etti ve COPY komutu bunları alım sırasında NULL'a dönüştürdü. Mevcut Python ETL betiği, ON CONFLICT (clinic_id, external_id) DO UPDATE SET phone = EXCLUDED.phone ile toplu güncellemeleri gerçekleştirmek için SQLAlchemy kullandı.
Sorun, klinik personelinin, merkezi sisteme doğrudan girilen geçerli telefon numaralarının gece senkronizasyonundan sonra kaybolduğunu bildirmesiyle ortaya çıktı. Araştırma, dış beslemenin NULL (bilinmeyen telefon) gönderdiğinde geçerli numaraları ezdiğini ortaya çıkardı, çünkü SET ifadesi koşulsuz olarak gerçekleştiriliyordu. Kaba bir filtre eklemek WHERE EXCLUDED.phone != patient_records.phone başarısız oldu, çünkü her ikisi de NULL olduğunda karşılaştırma NULL döndü (bilinmeyen), bu da güncellemenin yanlış bir şekilde atlanmasına neden oldu; ayrıca yeni değer NULL'ken eski değilse, mantık farklı PostgreSQL küçük sürümleri arasında tutarsız davranıyordu.
Üç çözüm değerlendirildi.
İlk yaklaşım, SET ifadesinde yalnızca COALESCE kullanmak oldu: SET phone = COALESCE(EXCLUDED.phone, patient_records.phone). Bu, NULL ile ezilmeyi önledi ancak her çelişki durumunda bir güncellemeyi zorunlu kıldı, bu da phone sütunundaki pahalı B-Tree dizin yeniden yapılandırmalarını tetikledi ve tüm BEFORE ve AFTER tetikleyicileri "no-op" değişiklikleri gerçek değişiklikler olarak kaydetti. Bu, WAL (Write-Ahead Log) trafiğini %300 artırdı, çoğaltma gecikmesini tehdit etti ve disk I/O'yu doyurdu.
İkinci çözüm, NULL ile başa çıkmak için açık boolean mantığı denemiştir: WHERE (EXCLUDED.phone != patient_records.phone) OR (EXCLUDED.phone IS NULL AND patient_records.phone IS NOT NULL). Mantıksal olarak doğru olsa da, bu ayrıntılı desen 15 nullable sütun arasında dikkatli bir bakım gerektiriyordu ve sorgu optimizasyonunu karmaşık hale getirdi. Planlayıcı dizin taramalarını bıraktı ve 20 milyon satırlık tabloda sıralı taramaları tercih etti, bu da ETL işinin altı saatlik bakım penceresini aşmasına neden oldu.
Üçüncü çözüm, WHERE ifadesinde tüm nullable sütunlar için IS DISTINCT FROM uygulamaktı. Bu, NULL geçişleri dahil gerçek veri değişikliklerini doğru bir şekilde tanımlayan özlü, sargable bir predikat sağladı. Yalnızca gerektiğinde güncellemeleri sağladı, gereksiz tetikleyici yürütmelerini ve WAL üretimini ortadan kaldırdı ve tutarlı sorgu planlarını korudu.
Ekip, kritik iletişim alanları için üçüncü çözümü ve performanstan daha çok ezilme korumanın önemli olduğu kritik olmayan meta veriler için birinci çözümü seçti. Sonuç dramatik oldu: senkronizasyon işinin süresi 45 dakikadan 12 dakikaya düştü, çoğaltma gecikmesi beş saniyenin altında stabilize oldu ve "kaybolan telefon numarası" olayları, uygulamanın ilk haftası içinde tamamen sona erdi.
Neden WHERE EXCLUDED.column != table.column her iki değer NULL olduğunda satırları atlıyor ve bu, PostgreSQL'in güncelleme mekanizması ile nasıl etkileşiyor?
Birçok adayın, iki NULL'ın eşit olmadığı durumda karşılaştırmanın DOĞRU döneceğini ve güncellemeye izin vereceğini varsaydığını gözlemliyoruz. Ancak, SQL üç değerli mantık kullanır: NULL bilinmeyen bir değeri temsil eder. NULL ile yapılan herhangi bir karşılaştırma (NULL = NULL veya NULL != NULL de dahil) NULL (bilinmeyen) şeklinde sonuçlanır, boole DOĞRU veya YANLIŞ değil. PostgreSQL'in WHERE ifadesinde, yalnızca DOĞRU olan değerlendirmeleri olan satırlar ilerler; NULL YANLIŞ olarak değerlendirilir. Dolayısıyla, iki NULL telefon numarasını karşılaştırdığınızda sonuç NULL olur, güncelleme atlanır ve sistem yanıtı yanlış bir şekilde değişiklik olmadığını varsayar. IS DISTINCT FROM, NULL ile NULL arasındaki karşılaştırmanın YANLIŞ döndürerek bunların özdeş olduğunu doğru bir şekilde gösterir ve güncellemeyi yalnızca gerekli olduğunda atlar. NULL olan bir değer ile diğerinin olmadığında ise DOĞRU döner.
Çok sütunlu benzersizlik kısıtlamasındaki sütunların sıralaması, ON CONFLICT çözümlemesinin performansını nasıl etkiler ve çelişki hedefi, dizin tanımına tam olarak uymadığında ne olur?
Adaylar çoğunlukla, PostgreSQL'in çelişki hedefinin (ON CONFLICT (...) içinde listelenen sütunlar) bir benzersiz dizin tanımına tam olarak uyması gerektiğini, sütun sıralaması ve herhangi bir işlevsel ifadeyi de içerecek şekilde göz ardı eder. (clinic_id, external_id) üzerine bir benzersiz dizin varsa, ancak sorgu ON CONFLICT (external_id, clinic_id) belirtiyorsa, planlayıcı dizini çıkaramayabilir ve "ÇATIŞMA özel bir benzersiz veya dışlama sınırlaması ile eşleşen bir kısıtlama yoktur" hatası verir. Başarırlarsa bile, eşleşmeyen sütun sıralaması, optimizatörün çelişen tuple'ı bulmak için bir dizin-tabanlı tarama kullanmasını engelleyebilir, bu da bir yığın alımını zorlayarak I/O maliyetini önemli ölçüde artırır.
SET ifadesinde COALESCE(EXCLUDED.column, table.column) kullanmak ile WHERE EXCLUDED.column IS DISTINCT FROM table.column kullanmak arasındaki fark, özellikle tetikleyici yürütme ve satır versiyonlama açısından nedir?
SET ifadesinde COALESCE kullanmak, satıra bir değer (ya yeni veriyi ya da korunmuş eski veriyi) koşulsuz olarak yazmayı sağlar. Bu işlem yeni bir satır versiyonu (CTID) oluşturur, WAL'ye yazar ve tablodaki tüm BEFORE ve AFTER tetikleyicileri ateşler, nihai değer önceki durumla aynı kaldığında bile. Bu, denetim tablolarında "gürültü" yaratır ve çoğaltma yükünü artırır. Diğer yandan, IS DISTINCT FROM ile WHERE ifadesi, eğer herhangi bir gerçek değişiklik gerçekleşmemişse satır değişikliğini tamamen engeller. Yeni bir tuple versiyonu oluşturulmaz, tetikleyiciler ateşlenmez ve WAL üretimi önlenir. Bu ayrım, denetim kaydı veya yabancı anahtar kaskadları olan yüksek hacimli sistemler için kritiktir; çünkü "no-op" güncellemeler önemli yükler yaratır.