Wawancara data engineering: Bagaimana temporal constraints PostgreSQL mencegah validitas yang tumpang tindih?

Prompt dan cakupan

Catatan harga, sewa, jadwal, dan izin menggabungkan business key dengan periode validitas. Pewawancara ingin database menolak periode yang tumpang tindih untuk satu produk atau tenant, termasuk concurrent writes. Pertanyaan ini menguji pemodelan temporal, semantik constraint, dan rencana migrasi yang aman alih-alih kueri yang hanya mencari konflik.

Apa yang dievaluasi pewawancara

• Apakah Anda membedakan WITHOUT OVERLAPS temporal primary atau unique key dari B-tree unique key biasa.
• Apakah Anda mendefinisikan endpoint rentang, rentang kosong, perilaku NULL, tanggal diskret, dan timestamp kontinu.
• Apakah Anda memahami bahwa foreign key PERIOD memeriksa cakupan waktu, bukan hanya business key yang cocok.
• Apakah Anda dapat merencanakan pembersihan historis, dampak lock, rollback, dan validasi konkuren.
• Apakah database constraints, pesan aplikasi, dan metrik audit memiliki tanggung jawab yang terpisah.

Semantik dan batasan

PostgreSQL mendefinisikan temporal constraints pada kolom rentang (range). WITHOUT OVERLAPS dapat digunakan dalam primary-key dan unique constraints; untuk bagian ordinary key yang sama, rentang terkait tidak boleh tumpang tindih. Kolom rentang secara implisit non-null, serta rentang kosong atau multirange tidak membentuk temporal key yang valid. Invarian database ini tidak dapat digantikan oleh urutan aplikasi "periksa, lalu masukkan".

PERIOD digunakan untuk temporal foreign keys. Business key dan periode anak harus dicakup oleh satu atau lebih baris induk; membuktikan bahwa baris induk dengan business key yang sama ada tidaklah cukup. Penghapusan induk atau pemendekan periode yang dicakup harus mengikuti aksi foreign-key dan urutan transaksi.

Langkah pemodelan

Pilih interval setengah terbuka atau tertutup dan gunakan konvensi yang sama pada setiap jalur penulisan. Validitas tanggal umumnya menggunakan [start, end); validitas timestamp harus menentukan zona waktu dan presisi. Tempatkan kolom business-key biasa sebelum kolom rentang, dengan memilih daterange, tsrange, atau tstzrange sesuai kebutuhan.

Buat temporal unique constraint pada catatan induk dan foreign key PERIOD pada catatan yang dicakup. Aplikasi dapat memberikan pesan yang ramah, tetapi database yang menentukan apakah commit berhasil. Sebelum memigrasikan data historis, gunakan laporan atau pemeriksaan bergaya exclusion untuk mengidentifikasi tumpang tindih, lalu tentukan apakah setiap konflik digabungkan, dipecah, atau dipensiunkan.

Contoh SQL

Contoh ini mencegah harga yang tumpang tindih untuk satu plan_id dan mewajibkan aturan dicakup sepenuhnya oleh rencana harga:

CREATE TABLE price_plan (
  plan_id bigint,
  valid_during daterange NOT NULL,
  amount numeric(12, 2) NOT NULL,
  PRIMARY KEY (plan_id, valid_during WITHOUT OVERLAPS)
);

CREATE TABLE plan_rule (
  plan_id bigint,
  valid_during daterange NOT NULL,
  rule_code text NOT NULL,
  CONSTRAINT plan_rule_plan_period_fk
    FOREIGN KEY (plan_id, PERIOD valid_during)
    REFERENCES price_plan (plan_id, PERIOD valid_during)
);

Validasi sintaksis dan perilaku dalam shadow table sebelum produksi, dan konfirmasikan bahwa client driver menampilkan konflik database dengan cara yang stabil. Contoh ini menunjukkan invarian inti; mata uang, presisi jumlah, dan kolom audit tetap mengikuti kebutuhan produk.

Concurrent writes dan migrasi

Sebelum menambahkan constraint, hitung rentang yang berkonflik dan urutkan berdasarkan business key; jangan menghapus baris historis yang "tampak duplikat" tanpa keputusan bisnis. Untuk tabel besar, perkirakan waktu pembuatan indeks, waktu tunggu lock, dan lag replikasi. Gunakan pembersihan bertahap (batch), jendela lalu lintas rendah, dan penanda progres yang dapat dipantau.

Dua insert konkuren untuk periode yang tumpang tindih harus dikoordinasikan oleh database saat commit. Aplikasi harus mengubah konflik keunikan menjadi kesalahan bisnis yang dapat dicoba lagi atau dijelaskan; pemeriksaan awal yang berhasil tidak menjamin insert berikutnya. Pertahankan kolom lama dan jalur penulisan untuk rollback hingga validasi shadow, rekonsiliasi dual-write, dan latihan pemulihan berhasil.

Kesalahan umum

• Hanya membuat indeks (plan_id, start_at) unik, yang masih memungkinkan periode tumpang tindih.
• Gagal mendefinisikan aturan endpoint dan mencampur tanggal yang berakhir pada 2026-02-01 dengan awal periode berikutnya.
• Memperlakukan foreign key PERIOD sebagai foreign key biasa yang hanya memeriksa business key.
• Menambahkan constraint langsung ke tabel produksi besar tanpa memeriksa riwayat, lock, atau lag replikasi.
• Membiarkan retry aplikasi menyembunyikan konflik constraint dan menghasilkan harga duplikat atau cakupan parsial.

Pertanyaan lanjutan

Bagaimana Anda menangani tumpang tindih yang ada?

Buat laporan konflik yang dikelompokkan berdasarkan business key dan diurutkan berdasarkan rentang. Minta pemilik bisnis memilih semantik penggabungan, pemecahan, atau penghentian; perbaiki baris, putar ulang penulisan dalam shadow table, buktikan bahwa laporan kosong, dan baru kemudian tambahkan constraint.

Mengapa tidak menggunakan exclusion constraint atau trigger?

Exclusion constraint dapat mengekspresikan saling pengecualian interval, tetapi WITHOUT OVERLAPS secara langsung menyatakan semantik temporal primary atau unique dan dapat disusun dengan foreign key PERIOD. Trigger dapat melewatkan konkurensi, rekursi, atau jalur replikasi; gunakan trigger hanya untuk efek samping lintas tabel tambahan sambil menjaga invarian inti dalam constraints.

Bagaimana Anda membuktikan migrasi mempertahankan waktu bisnis?

Bandingkan jumlah interval, sampel batas, tingkat konflik yang ditolak, dan query plans sebelum dan sesudah. Jalankan pemeriksaan cakupan pada replika baca dan di lingkungan pemulihan. Selama peluncuran, lakukan rekonsiliasi dengan logika lama; jika penulisan yang valid ditolak, lakukan rollback pergantian constraint tanpa menghapus riwayat.