Konteks dan pertanyaan
Sebuah tim menyimpan pemesanan ruang rapat. Setiap data memiliki ruangan, waktu mulai, dan waktu selesai; rentang waktu untuk satu ruangan tidak boleh tumpang tindih, sedangkan pemesanan yang berdekatan boleh bersentuhan di titik akhir. Aplikasi sudah memeriksa konflik, tetapi okupansi ganda masih muncul dalam kondisi konkurensi. Berikan rancangan PostgreSQL dan jelaskan interval setengah terbuka (half-open intervals), null, zona waktu, penulisan bersamaan, penanganan kesalahan, dan migrasi data yang ada.
Pertanyaan ini cocok untuk posisi data engineering, backend, dan database. Kuncinya adalah mengekspresikan aturan bisnis lintas baris sebagai invarian database daripada mempercayai setiap pemanggil untuk menjalankan kueri yang sama. Jawaban yang kuat membedakan batasan UNIQUE, CHECK, trigger, dan exclusion constraint, lalu menghubungkan kegagalan constraint dengan alur kerja produk.
Apa yang sedang diuji oleh pewawancara
Jawaban yang kuat memodelkan waktu pemesanan sebagai tstzrange atau tipe range lain yang sesuai dan secara eksplisit menggunakan [start, end) sehingga rentang yang berdekatan tidak saling bertentangan. Jawaban tersebut menggabungkan kesamaan ruangan dan tumpang tindih waktu dalam GiST exclusion constraint. Jawaban tersebut menjelaskan kapan btree_gist diperlukan, mengapa pra-pemeriksaan di tingkat aplikasi tidak dapat menghilangkan race condition, bagaimana memetakan exception constraint, bagaimana menangani batas tak hingga dan range kosong, serta bagaimana menemukan konflik yang ada sebelum migrasi.
Pertanyaan untuk diklarifikasi terlebih dahulu
- Apa kebijakan zona waktu untuk waktu mulai dan selesai, dan apakah pemesanan dapat melewati transisi waktu musim panas (daylight saving)?
- Apakah waktu selesai harus setelah waktu mulai, dan apakah pemesanan dengan durasi nol memiliki arti bisnis?
- Apakah cakupan konflik hanya satu ruangan, atau juga lantai, perangkat, atau tenant?
- Apakah interval yang berdekatan boleh bersentuhan, dan apakah baris yang dibatalkan atau dihapus secara halus (soft-deleted) masih mengonsumsi sumber daya?
- Apakah data yang ada sudah mengandung tumpang tindih, dan dapatkah penulisan dijeda sebentar selama migrasi?
Kerangka jawaban 30 detik
"Saya akan menormalisasi waktu mulai dan selesai menjadi range setengah terbuka yang sadar zona waktu, tstzrange(start_at, end_at, '[)'), dan menambahkan EXCLUDE USING gist (room_id WITH =, during WITH &&) di database. Rentang yang tumpang tindih untuk satu ruangan akan ditolak sementara rentang yang berdekatan dapat berdampingan; btree_gist memungkinkan kunci ruangan bertipe integer atau UUID berpartisipasi dalam perbandingan GiST. Saya akan mencoba penulisan secara langsung dan memetakan konflik constraint ke respons bisnis yang dapat dicoba kembali (retryable) daripada mengandalkan periksa-lalu-sisipkan (check-then-insert). Sebelum peluncuran, saya akan memindai dan memperbaiki konflik lama, mengaktifkan constraint secara bertahap, dan memantau kegagalan."
Solusi langkah demi langkah
Langkah 1: pilih semantik waktu
Gunakan tstzrange untuk waktu absolut daripada menyerahkan string waktu lokal ke database. [start, end) menyertakan awal dan mengecualikan akhir, sehingga [10:00, 11:00) dan [11:00, 12:00) tidak tumpang tindih. Dokumentasi PostgreSQL mencatat && sebagai operator tumpang tindih dan menggunakan range constraint untuk jenis invarian ini.
Validasi start_at < end_at saat penulisan dan putuskan apakah range kosong memiliki arti. Simpan representasi zona waktu yang konsisten, lalu format untuk zona waktu pengguna; jangan menyimpulkan durasi dari aritmetika jam lokal pada hari transisi waktu musim panas.
Langkah 2: ekspresikan aturan sebagai exclusion constraint
Range dapat berupa generated column atau dibuat dalam ekspresi constraint. Kolom range eksplisit memudahkan kueri dan audit:
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE room_reservations (
reservation_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
room_id bigint NOT NULL,
during tstzrange NOT NULL,
CHECK (NOT isempty(during)),
EXCLUDE USING gist (
room_id WITH =,
during WITH &&
)
);Constraint ini mensyaratkan setidaknya satu perbandingan di antara setiap pasangan baris bernilai false atau null. Ketika room_id = dan during && keduanya bernilai true, baris kedua akan ditolak. PostgreSQL secara otomatis membuat indeks dari tipe yang dipilih untuk exclusion constraint.
Langkah 3: pahami btree_gist dan biaya indeks
Range memiliki operator class GiST. Nilai skalar seperti integer, teks, atau UUID biasanya tidak memiliki kelas GiST default untuk kesetaraan, sehingga btree_gist dapat menyediakan operator class mirip B-tree yang berpartisipasi dalam constraint GiST yang sama. Perlakukan ekstensi ini sebagai dependensi deployment dan verifikasi di lingkungan migrasi.
Indeks constraint GiST menambah biaya penulisan dan pembaruan. Operasi baca harus menggunakan operator range dan predikat selektif. Jangan membuat indeks GiST range duplikat hanya karena suatu indeks terlihat; periksa execution plan dan konfirmasikan apakah indeks constraint sudah melayani beban kerja pembacaan.
Langkah 4: tangani konkurensi dan transaksi
Jangan menjalankan SELECT untuk memeriksa konflik lalu menjalankan INSERT; dua transaksi dapat sama-sama melihat rentang kosong. Biarkan constraint database yang menengahi, tangkap constraint yang bersangkutan, dan kembalikan pesan "rentang waktu sudah terisi," sehingga pengguna dapat memuat ulang atau memilih slot lain.
Pemesanan mungkin juga memicu pembayaran, notifikasi, atau kuota. Lakukan commit pemesanan dalam transaksi singkat, lalu publikasikan outbox atau event yang andal untuk efek samping eksternal. Lakukan retry hanya pada error serialisasi atau transient yang aman untuk dicoba kembali; konflik constraint adalah fakta bisnis, sehingga retry tanpa penyesuaian tidak akan berhasil.
Langkah 5: tentukan pembatalan, multi-tenancy, dan penghapusan
Apakah baris yang di-soft-delete masih menempati ruangan harus menjadi bagian dari model constraint. Jika pembatalan membebaskan rentang waktu, pisahkan pemesanan aktif dari riwayat atau rancang transisi status yang dapat ditegakkan. Filter WHERE status = 'active' dalam kueri aplikasi tidak membuat exclusion constraint normal mengabaikan baris lain.
Untuk multi-tenancy, sertakan tenant key ketika namespace sumber daya bersifat lokal untuk tenant, misalnya (tenant_id WITH =, room_id WITH =, during WITH &&), dan tegakkan otorisasi agar satu tenant tidak dapat menulis ke ruangan tenant lain. Constraint melindungi dari konflik; constraint tidak menggantikan izin tingkat baris atau state machine bisnis.
Langkah 6: migrasikan data yang ada
Temukan pasangan yang tumpang tindih per ruangan dengan self-join atau window query, lalu catat jumlah dan pemiliknya. Selesaikan setiap konflik dengan menggabungkan, membagi, membatalkan, atau meminta keputusan bisnis; jangan memotong data secara diam-diam. Setelah data bersih, buat constraint pada jendela waktu berisiko rendah. Untuk tabel besar, evaluasi lock, waktu pembuatan indeks, rollback, dan lakukan gladi bersih backup-restore.
Langkah 7: rancang error dan observabilitas
Beri nama constraint, misalnya room_reservations_no_overlap, sehingga nama constraint dari driver dapat dipetakan ke respons pengguna yang stabil. Catat ruangan, pengenal permintaan, dan ringkasan rentang waktu tanpa data pribadi yang tidak perlu. Pantau tingkat konflik, sisa migrasi, latensi transaksi, dan pertumbuhan indeks, membedakan contention normal dari retry storm.
Langkah 8: uji skenario konkurensi
Uji tumpang tindih dalam satu ruangan (gagal), rentang yang berdekatan dalam satu ruangan (berhasil), tumpang tindih di ruangan berbeda (berhasil), waktu ekuivalen di berbagai zona waktu, pembaruan yang menciptakan konflik, pembatalan, range kosong, dan penanganan null. Gunakan dua transaksi konkuren, bukan hanya skrip berurutan, dan verifikasi pemulihan, backup restore, serta perilaku rebuild constraint.
Pertukaran (trade-offs) dan batasan
Exclusion constraint cocok untuk aturan yang dipelihara terus-menerus bahwa tidak ada pasangan baris yang boleh memenuhi serangkaian perbandingan pada saat yang sama. Ini lebih dekat ke sumber data daripada mutex aplikasi dan menghindari protokol trigger terpisah yang rawan race condition. Konsekuensinya adalah amplifikasi penulisan GiST, dependensi ekstensi, dan kebutuhan aplikasi untuk memahami error constraint.
Jika aturan mencakup banyak tabel, memiliki kapasitas dinamis, atau mengizinkan sejumlah tumpang tindih tertentu, satu exclusion constraint mungkin tidak cukup. Pertimbangkan slot yang dapat dikunci, penguncian tingkat transaksi, atau layanan penjadwalan, sambil tetap mempertahankan constraint database untuk invarian yang dapat diekspresikan. Constraint CHECK tidak dapat secara andal mereferensikan baris lain untuk menjaga aturan lintas baris ini.
Rencana peluncuran dan bukti
Muat data produksi ke dalam tabel bayangan (shadow table), jalankan pemindaian tumpang tindih, dan buat daftar perbaikan berdasarkan ruangan dan tenant. Kemudian instal ekstensi dan constraint, putar ulang penulisan konkuren, serta verifikasi pemetaan error, biaya indeks, backup restore, dan peringatan. Aktifkan untuk sebagian kecil trafik, bandingkan konflik constraint dengan konflik yang diamati secara manual, dan beralih ke tabel utama setelah hasilnya stabil.
Dokumentasi range PostgreSQL mendefinisikan operator seperti && dan menunjukkan GiST exclusion constraint yang mencegah reservasi tumpang tindih. Dokumentasi constraint-nya mendefinisikan semantik eksklusi berpasangan dan mencatat bahwa penambahan constraint akan membuat indeks yang ditentukan. Sumber-sumber primer ini mendukung klaim terkait tipe data, operator, dan indeks; detail penerapan tetap memerlukan pengujian terhadap versi PostgreSQL yang digunakan secara nyata.
Materi wawancara sistem pemesanan publik juga mencantumkan pencegahan pemesanan ganda secara konkuren dan exclusion constraint PostgreSQL sebagai poin diskusi wawancara. Artikel ini mempertahankan skenario umum tersebut namun membatasi jawabannya pada invarian data, migrasi, dan verifikasi kegagalan alih-alih mengulang perancangan sistem pemesanan secara lengkap.
Kesalahan umum dan tindak lanjut
Hanya melakukan "periksa, lalu sisipkan"
Transaksi yang berjalan bersamaan dapat sama-sama lolos pemeriksaan. Pertahankan kueri sebagai petunjuk pengalaman pengguna jika berguna, tetapi biarkan constraint database yang menentukan hasil akhir.
Menyimpan waktu lokal di timestamp
String yang sama dapat mewakili waktu yang berbeda di berbagai wilayah dan perubahan daylight-saving. Tentukan kebijakan zona waktu, simpan waktu absolut, dan konversikan hanya untuk tampilan.
Menggunakan UNIQUE(room_id, start_at) untuk mencegah tumpang tindih
Unique constraint memblokir nilai awal yang sama, bukan interval panjang yang menutupi beberapa interval yang lebih pendek. Operator range mengekspresikan tumpang tindih secara langsung.
Membuat constraint normal mengabaikan baris yang di-soft-delete
Exclusion constraint normal membandingkan setiap baris. Pisahkan data aktif dan historis atau rancang ulang model status; pemfilteran hanya pada kueri aplikasi tidaklah memadai.
Mengapa tidak hanya menggunakan trigger?
Trigger harus mengimplementasikan semantik konkurensi, penguncian, dan error-nya sendiri serta dapat menciptakan kasus ekstrem yang sulit pada pencadangan dan pemulihan. Jika aturan dapat diekspresikan dengan range dan operator, exclusion constraint bawaan biasanya lebih jelas; gunakan trigger atau scheduler jika aturan melampaui model tersebut.