Perintah dan konteks
Pertanyaan ini tidak membandingkan setiap opsi kolom tergenerasi. Pertanyaan ini berfokus pada migrasi ekspresi baris berulang ke kolom tergenerasi virtual PostgreSQL 18. Tujuannya adalah satu kontrak kalkulasi tanpa pengisian ulang (backfill) kolom tersimpan, sembari mengontrol CPU pembacaan, pembatasan ekspresi, dan perubahan hak istimewa.
Hal yang dievaluasi pewawancara
- Mengetahui bahwa PostgreSQL 18 memperkenalkan kolom tergenerasi virtual dan menjadikannya sebagai default.
- Membuktikan bahwa ekspresi hanya menggunakan baris saat ini serta fungsi dan tipe bawaan yang immutable.
- Memvalidasi dengan pembacaan ganda (dual reads) dan rencana kueri produksi, bukan hanya keberhasilan DDL.
- Memahami bahwa nilai virtual dihitung saat dibaca, tidak memakan penyimpanan baris, dan tidak dapat menjadi kunci partisi.
Klarifikasi sebelum menjawab
Konfirmasikan bahwa ekspresi hanya menggunakan fungsi bawaan, identifikasi pembacaan dan filter yang mereferensikannya, periksa keberadaan indeks ekspresi yang ada, dan tanyakan apakah aplikasi dapat membandingkan ekspresi lama dengan kolom baru untuk sementara waktu. Tinjau juga peran (roles), karena kolom tergenerasi dan kolom dasar memiliki hak istimewa yang terpisah. Jika kolom tergenerasi dimaksudkan untuk mengisolasi kolom dasar, verifikasi bahwa setiap fungsi, operator, dan pengubahan tipe (cast) dalam ekspresi memenuhi persyaratan LEAKPROOF.
Kerangka jawaban 30 detik
Pertama, saya akan membuktikan bahwa ekspresi tersebut memenuhi batasan kolom virtual PostgreSQL 18, kemudian menambahkan kolom yang secara eksplisit VIRTUAL. Selama migrasi, aplikasi melakukan shadow-read pada ekspresi lama dan kolom baru di seluruh nilai null, Unicode, input yang tidak biasa, dan partisi historis. Saya akan membandingkan CPU, latensi ekor (tail latency), dan rencana eksekusi untuk kueri yang representatif. Setelah gerbang semantik, performa, dan hak istimewa terpenuhi, pembacaan dialihkan ke kolom baru sambil mempertahankan rollback cepat ke ekspresi lama.
Pembahasan mendalam langkah demi langkah
ALTER TABLE report_events
ADD COLUMN normalized_country text
GENERATED ALWAYS AS (lower(trim(country_code))) VIRTUAL;Nilai virtual dihitung saat dibaca dan tidak memakan penyimpanan baris. Ekspresinya hanya dapat mereferensikan baris saat ini, tidak boleh berisi subkueri atau kolom tergenerasi lainnya, dan harus menggunakan fungsi immutable. Kolom virtual juga tidak boleh bergantung pada fungsi atau tipe yang ditentukan pengguna. Menulis VIRTUAL secara eksplisit mencegah maksud migrasi bergantung pada setelan default PostgreSQL 18.
Pindai data historis dan bandingkan normalized_country IS NOT DISTINCT FROM lower(trim(country_code)), termasuk NULL, spasi, huruf besar-kecil, dan input non-ASCII. Kemudian jalankan pemeriksaan EXPLAIN (ANALYZE, BUFFERS) yang representatif. Komputasi saat membaca dapat meningkatkan CPU; jika filter memerlukan indeks, verifikasi jalur indeks persis yang didukung dan biaya penulisan daripada berasumsi bahwa tidak adanya penyimpanan berarti tidak ada biaya.
Rilis secara bertahap: tambahkan kolom; lakukan shadow-read kedua bentuk sementara ekspresi lama tetap menjadi sumber otoritatif; beralih hanya setelah tidak ada perbedaan dan performa dapat diterima. Rollback memulihkan ekspresi lama. Terakhir, uji hak istimewa kolom dasar dan kolom tergenerasi dengan peran aplikasi yang sebenarnya. Jika ada fungsi, operator, atau cast yang tidak dapat dibuktikan sebagai LEAKPROOF, hak istimewa kolom tergenerasi tidak membentuk batas keamanan yang lengkap di sekitar kolom dasar.
Contoh jawaban yang kuat
Saya akan memperlakukan ini sebagai migrasi kontrak kueri. Setelah membuktikan bahwa ekspresi hanya menggunakan fungsi bawaan immutable pada baris saat ini, saya akan menambahkan kolom virtual eksplisit. Ini menghindari backfill fisik tetapi memindahkan beban kerja ke pembacaan, sehingga pengujian CPU dan latensi ekor yang menyerupai produksi wajib dilakukan.
Aplikasi pertama-tama membandingkan hasil lama dan baru serta mengelompokkan ketidaksesuaian berdasarkan kelas input. Pengalihan hanya dilakukan setelah gerbang semantik, rencana, dan hak istimewa terpenuhi. Jika terjadi regresi perilaku, data dasar tetap utuh dan kueri dapat segera kembali ke ekspresi lama.
Kesalahan umum
- Menghilangkan
VIRTUALdan mengandalkan default khusus versi untuk menjelaskan maksud. - Baru menemukan fungsi volatile, subkueri, atau tipe buatan pengguna saat eksekusi DDL.
- Menguji nilai ASCII biasa sementara mengabaikan
NULLdan perilaku Unicode. - Menganggap ketiadaan penyimpanan baris berarti tidak ada CPU kueri.
- Menghapus ekspresi lama saat cutover sehingga kehilangan opsi rollback cepat.
Pertanyaan lanjutan
Mengapa tidak menggunakan STORED di sini?
Tugas ini menargetkan normalisasi waktu-baca yang murah dan ingin menghindari backfill fisik. Jika pemindaian, pengurutan, atau filter produksi membuat CPU pembacaan tidak dapat diterima, kolom tersimpan menjadi keputusan penyimpanan dan replikasi yang terpisah.
Bisakah kolom virtual menjadi kunci partisi?
Tidak. PostgreSQL 18 tidak mengizinkan kolom tergenerasi sebagai kunci partisi. Gunakan kolom biasa yang dikelola oleh jalur penulisan ketika perutean partisi memerlukan nilai tersebut.
Bagaimana Anda menguji bahwa hak istimewa tidak meluas?
Kueri kolom dasar dan kolom tergenerasi dengan peran aplikasi yang sebenarnya, periksa izin kolom (grants) dan hak eksekusi untuk fungsi-fungsi dalam ekspresi. Ketika kolom tergenerasi dimaksudkan untuk menyembunyikan kolom dasar, periksa juga apakah setiap fungsi, termasuk fungsi di balik operator dan cast, ditandai sebagai LEAKPROOF; PostgreSQL tidak memaksakan kondisi ini untuk aplikasi. Jika ada jalur ekspresi yang tidak dapat dibuktikan anti-bocor (leakproof), jangan anggap izin kolom tergenerasi sebagai isolasi yang lengkap.
Kapan dual reads dapat diakhiri?
Setelah siklus bisnis penuh, partisi historis, dan beban puncak telah terlewati dengan nol perbedaan semantik serta hasil performa dan hak istimewa yang dapat diterima.