Temu duga data: Bagaimanakah anda menggunakan NULLS NOT DISTINCT untuk kekunci pilihan dalam PostgreSQL 18?

Gesaan dan konteks

Jadual orders membenarkan satu rujukan luaran pilihan bagi setiap penyewa: paling banyak satu baris bagi setiap penyewa boleh mempunyai NULL. Bagaimanakah anda menguatkuasakan ini dalam PostgreSQL 18 sambil mengendalikan duplikasi sejarah, penulisan serentak, dan rollback?

Indeks unik lalai PostgreSQL menganggap nilai NULL sebagai berbeza (distinct), jadi beberapa nilai NULL tidak akan berkonflik. PostgreSQL 18 menambah NULLS NOT DISTINCT, menjadikan nilai NULL mengambil bahagian dalam keunikan. Soalan ini menguji reka bentuk kekangan, keselamatan migrasi, dan semantik keserentakan dan bukannya memindahkan semakan perniagaan yang terdedah kepada keadaan perlumbaan (race condition) ke dalam kod aplikasi.

Perkara yang diuji oleh penemu duga

• Sama ada anda menerangkan keunikan NULL lalai berbanding NULLS NOT DISTINCT dengan tepat.
• Sama ada anda mengetahui bahawa pilihan ini terpakai pada indeks B-tree unik atau kekangan unik, bukan perbandingan biasa.
• Sama ada anda mencari dan menyelesaikan gabungan duplikasi sejarah NULL dan bukan-NULL terlebih dahulu.
• Sama ada anda mereka bentuk migrasi dalam talian, pelan kunci (lock plan), tingkah laku penulisan serentak, dan rollback.
• Sama ada ORM, replikasi, pemetakan (partitioning), dan kontrak hiliran kekal serasi.

Soalan untuk dijelaskan terlebih dahulu

• Adakah skopnya merangkumi seluruh jadual, atau satu peraturan bagi setiap penyewa, wilayah, atau keadaan aktif?
• Adakah NULL bermaksud tidak diperuntukkan, tidak diketahui, atau rujukan kongsi yang disengajakan?
• Adakah baris sejarah mengandungi beberapa NULL, rentetan kosong, varian huruf besar/kecil, atau kekunci yang dipadam secara lembut (soft-deleted)?
• Apakah kadar penulisan, belanjawan kunci (lock budget), dan tetingkap migrasi?
• Adakah aplikasi, ORM, CDC, dan laporan menganggap bahawa NULL boleh berulang?

Jawapan tiga puluh saat

“Saya akan menjelaskan maksud NULL dan skopnya, kemudian mengaudit duplikasi sejarah. Bagi peraturan berskopkan penyewa, saya akan meletakkan penyewa dan rujukan dalam kekunci unik komposit dengan NULLS NOT DISTINCT; pilihan ini menjadikan NULL mengambil bahagian dalam keunikan kekunci tersebut tetapi tidak mengubah perbandingan logik tiga nilai SQL atau menjadikan lajur tersebut NOT NULL. Saya akan membersihkan atau memutuskan konflik sejarah, menggunakan pengendalian konflik sebelum indeks dibina, membinanya dengan pelan kunci dan kependaman, serta menguji tingkah laku ORM dan CDC. Pangkalan data menjadi pihak berkuasa muktamad keserentakan; jika semantik perniagaan didapati salah, saya akan membuat rollback terhadap kekangan dan dasar aplikasi dan bukannya bergantung pada pra-semakan yang terdedah kepada keadaan perlumbaan.”

Panduan mendalam langkah demi langkah

Langkah 1: Sahkan maksud dan skop NULL

Bezakan nilai yang belum diperuntukkan daripada nilai yang tidak diketahui. Jika NULL bermaksud belum diperuntukkan, satu NULL mungkin merupakan peraturan yang dimaksudkan; jika ia bermaksud tidak diketahui dan pengulangan adalah sah, keunikan adalah tidak wajar. Tentukan sama ada kekangan adalah global atau dikumpulkan mengikut penyewa, wilayah, dan keadaan aktif, kemudian pilih susunan kekunci komposit dan sama ada indeks separa diperlukan.

Langkah 2: Pilih ungkapan pangkalan data

PostgreSQL 18 menyokong NULLS NOT DISTINCT pada indeks unik. Lalai menganggap nilai NULL sebagai tidak sama dan membenarkan berbilang NULL. Kekangan unik boleh menjadikan model lebih jelas, manakala indeks B-tree unik boleh disesuaikan untuk migrasi dalam talian. Pilihan ini hanya mengubah perbandingan keunikan: WHERE value = NULL masih mengikut logik tiga nilai, dan lajur boleh kekal boleh-batal (nullable).

Langkah 3: Audit dan bersihkan data sejarah

Kumpulkan mengikut kekunci yang dicadangkan dan kira berbilang NULL, rentetan kosong, varian huruf besar/kecil, dan baris yang dipadam secara lembut yang masih menduduki kekunci. Tentukan bagi setiap konflik sama ada untuk menggabungkan pesanan, mengisi rujukan, mengekalkan satu baris dan memindahkan yang lain, atau mendokumentasikan pengecualian. Jadikan pembersihan boleh dimainkan semula (replayable) dan boleh diaudit, serta sahkan dalam persekitaran bayangan sebelum penciptaan indeks mendedahkan konflik yang belum selesai.

Langkah 4: Reka bentuk migrasi dalam talian

Lancarkan pengendalian aplikasi yang serasi untuk konflik unik terlebih dahulu, kemudian cipta indeks atau kekangan semasa tetingkap terkawal. Untuk jadual yang besar, nilaikan penciptaan serentak, tahap kunci, ruang cakera, dan kependaman penulisan; pantau konflik dan transaksi yang panjang sepanjang masa. Jika penyewa perlu dilaksanakan secara berperingkat, cipta peraturan secara berkelompok dan rekodkan tanda aras penyiapan (watermark). Kekalkan pra-semakan lama sehingga kekangan pangkalan data dan pemetaan ralat siap.

Langkah 5: Kendalikan keserentakan dan kontrak hiliran

Indeks unik adalah penentu muktamad untuk sisipan dan kemas kini serentak. Pendekatan aplikasi “semak kemudian masukkan” boleh menambah baik mesej tetapi tidak boleh menggantikan kekangan. Petakan pelanggaran keunikan kepada ralat perniagaan yang boleh dicuba semula atau dapat dilihat oleh pengguna tanpa percubaan semula tanpa had. Semak CDC, replikasi, skema ORM, laporan, dan cache untuk andaian bahawa NULL boleh berulang, kemudian kemas kini kontrak dan amaran.

Langkah 6: Sahkan, pantau, dan rollback

Dalam persekitaran staging dan penyewa canary, uji satu NULL, NULL kedua, nilai bukan-NULL yang sama, nilai bukan-NULL yang berbeza, kemas kini, padam-dan-cipta-semula, dan penulisan serentak. Pantau pembinaan indeks, menunggu kunci (lock waits), kadar konflik, ralat aplikasi, dan kependaman hiliran. Jika semantik atau kadar konflik tidak boleh diterima, hentikan laluan penulisan baharu, alih keluar kekangan, pulihkan pengendalian ralat yang serasi, dan kekalkan jejak audit untuk analisis.

Contoh jawapan berkualiti tinggi

Saya akan terlebih dahulu mengesahkan maksud NULL dan skopnya. Jika setiap penyewa dibenarkan mempunyai satu rujukan pilihan, saya akan memasukkan penyewa dan rujukan dalam kekunci unik komposit dan menggunakan NULLS NOT DISTINCT dalam PostgreSQL 18. Ia menjadikan NULL mengambil bahagian dalam keunikan bagi kekunci tersebut, manakala logik tiga nilai SQL biasa dan lajur boleh-batal kekal tidak berubah.

Sebelum pelancaran, saya akan mengaudit berbilang NULL, rentetan kosong, varian huruf besar/kecil, dan baris yang dipadam secara lembut, memutuskan bagaimana setiap konflik digabungkan atau diisi, dan merekodkan keputusan tersebut. Saya akan menggunakan pengendalian konflik unik sebelum membina indeks, kemudian memantau kunci, ruang, transaksi yang panjang, dan konflik. Ujian merangkumi satu dan dua NULL, nilai bukan-NULL yang sama dan berbeza, kemas kini, padam-dan-cipta-semula, dan keserentakan. Pangkalan data ialah pihak berkuasa muktamad, dan ORM, CDC, laporan, serta cache mesti menerima pakai kontrak yang sama; jika maksud perniagaan terbukti salah, saya akan mengalih keluar kekangan dan membuat rollback dasar aplikasi.

Kesilapan biasa

• Menganggap UNIQUE membenarkan hanya satu NULL secara lalai → PostgreSQL menganggap NULL sebagai berbeza → gunakan NULLS NOT DISTINCT secara eksplisit.
• Memperlakukannya sebagai NOT NULL → Pilihan ini masih membenarkan satu NULL → asingkan maksud nilai yang hilang daripada keunikan.
• Hanya menggunakan pra-semakan aplikasi → Permintaan serentak masih mengalami keadaan perlumbaan → biarkan indeks unik pangkalan data membuat keputusan.
• Mengabaikan rentetan kosong dan varian huruf besar/kecil → Duplikasi perniagaan mungkin bukan konflik NULL → tentukan penormalan dan pembersihan terlebih dahulu.
• Membina dalam talian tanpa pembersihan sejarah → Duplikasi sedia ada boleh menggagalkan atau menyekat migrasi → audit, buat keputusan, dan pantau transaksi yang panjang.
• Hanya mengubah pangkalan data → ORM, CDC, dan laporan mungkin masih menganggap NULL boleh berulang → kemas kini kontrak data dan pemetaan ralat.

Soalan susulan

Adakah NULLS NOT DISTINCT mengubah perbandingan NULL biasa?

Tidak. Ia hanya mengubah sama ada NULL bertembung dalam indeks unik. WHERE value = NULL masih mengikut logik tiga nilai SQL dan harus menggunakan IS NULL. Semantik pertanyaan, semantik indeks, dan kebolehbatalan lajur mesti diterangkan secara berasingan.

Bagaimanakah anda boleh berhijrah tanpa masa henti (downtime) apabila dua NULL sudah wujud?

Pilih baris yang dikekalkan bagi setiap penyewa dan status perniagaan, gabungkan atau isi yang lain, dan rekodkan keputusan dalam jadual audit. Gunakan pengendalian konflik, bina indeks secara berkelompok sambil memantau kunci dan transaksi yang panjang, dan tangguhkan penyewa yang konfliknya tidak dapat diselesaikan dalam tetingkap masa tersebut.

Patutkah peraturan berbilang penyewa menggunakan kekunci komposit atau indeks separa?

Jika setiap keadaan mesti unik, gunakan penyewa bersama rujukan dalam kekunci unik komposit. Jika hanya baris aktif yang dikekang, indeks separa yang dihadkan kepada keadaan aktif mungkin sesuai. Pilihan bergantung pada kontrak pemadaman, pemulihan, dan peralihan keadaan, serta mesti dibuktikan dengan ujian keserentakan.