Temu Bual Data: Memindahkan Ungkapan Pelaporan ke Lajur Terjana Maya PostgreSQL 18

Gesaan dan konteks

Soalan ini tidak membandingkan setiap pilihan lajur terjana. Ia memberi tumpuan kepada penghijrahan ungkapan baris berulang kepada lajur terjana maya PostgreSQL 18. Matlamatnya adalah satu kontrak pengiraan tanpa pengisian semula (backfill) lajur tersimpan, di samping mengawal CPU bacaan, sekatan ungkapan dan perubahan keistimewaan.

Perkara yang dinilai oleh penemu bual

Mengetahui bahawa PostgreSQL 18 memperkenalkan lajur terjana maya dan menjadikannya sebagai lalai.
Membuktikan bahawa ungkapan hanya menggunakan baris semasa serta fungsi dan jenis terbina dalam yang tidak boleh diubah (immutable).
Mengesahkan dengan dwi-bacaan dan pelan pertanyaan pengeluaran, bukan sekadar DDL yang berjaya.
Memahami bahawa nilai maya dikira semasa dibaca, tidak menggunakan storan baris dan tidak boleh menjadi kunci pemetakan (partition key).

Penjelasan sebelum menjawab

Sahkan bahawa ungkapan hanya menggunakan fungsi terbina dalam, kenal pasti bacaan dan penapis yang merujuknya, semak kewujudan indeks ungkapan sedia ada, dan tanya sama ada aplikasi boleh membandingkan ungkapan lama dengan lajur baharu buat sementara waktu. Semak peranan juga, kerana lajur terjana dan lajur asas mempunyai keistimewaan yang berasingan. Jika lajur terjana bertujuan untuk mengasingkan lajur asas, sahkan bahawa setiap fungsi, pengendali dan penukaran jenis (cast) dalam ungkapan memenuhi keperluan LEAKPROOF.

Rangka jawapan 30 saat

Mula-mula saya akan membuktikan bahawa ungkapan tersebut memenuhi sekatan lajur maya PostgreSQL 18, kemudian menambah lajur yang jelas VIRTUAL. Semasa migrasi, aplikasi melakukan bacaan bayangan (shadow-read) pada ungkapan lama dan lajur baharu merentasi nilai nol (null), Unikod, input luar biasa dan petak sejarah. Saya akan membandingkan CPU, kependaman ekor (tail latency) dan pelan untuk pertanyaan yang representatif. Selepas melepasi kawalan semantik, prestasi dan keistimewaan, bacaan beralih ke lajur baharu sambil mengekalkan rollback pantas ke ungkapan lama.

Analisis mendalam langkah demi langkah

sql

ALTER TABLE report_events
ADD COLUMN normalized_country text
GENERATED ALWAYS AS (lower(trim(country_code))) VIRTUAL;

Nilai maya dikira apabila dibaca dan tidak menduduki storan baris. Ungkapannya hanya boleh merujuk baris semasa, tidak boleh mengandungi subpertanyaan atau lajur terjana yang lain, dan mesti menggunakan fungsi yang immutable. Lajur maya juga tidak boleh bergantung pada fungsi atau jenis yang ditentukan oleh pengguna. Menulis VIRTUAL secara eksplisit menghalang niat migrasi daripada bergantung pada tetapan lalai PostgreSQL 18.

Imbas data sejarah dan bandingkan normalized_country IS NOT DISTINCT FROM lower(trim(country_code)), termasuk NULL, ruang putih, huruf besar-kecil dan input bukan ASCII. Kemudian jalankan semakan EXPLAIN (ANALYZE, BUFFERS) yang representatif. Pengiraan pada masa bacaan mungkin meningkatkan CPU; jika penapis memerlukan indeks, sahkan laluan indeks tepat yang disokong dan kos penulisan daripada menganggap tiada storan bermakna tiada kos.

Lepaskan secara berperingkat: tambah lajur; buat bacaan bayangan kedua-dua bentuk sementara ungkapan lama kekal berwibawa; beralih hanya selepas sifar perbezaan dan prestasi yang boleh diterima. Rollback memulihkan ungkapan lama. Akhir sekali, uji keistimewaan lajur asas dan lajur terjana dengan peranan aplikasi sebenar. Jika mana-mana fungsi, pengendali atau cast tidak dapat dibuktikan sebagai LEAKPROOF, keistimewaan lajur terjana tidak membentuk sempadan keselamatan yang lengkap di sekeliling lajur asas.

Contoh jawapan yang kukuh

Saya akan menganggap ini sebagai migrasi kontrak pertanyaan. Selepas membuktikan bahawa ungkapan hanya menggunakan fungsi terbina dalam baris semasa yang immutable, saya akan menambah lajur maya eksplisit. Ia mengelakkan backfill fizikal tetapi memindahkan kerja ke operasi baca, jadi ujian CPU dan kependaman ekor berbentuk pengeluaran adalah wajib.

Aplikasi terlebih dahulu membandingkan hasil lama dan baharu serta mengumpulkan percanggahan mengikut kelas input. Ia hanya beralih selepas melepasi kawalan semantik, pelan dan keistimewaan. Jika tingkah laku merosot, data asas kekal utuh dan pertanyaan kembali kepada ungkapan lama serta-merta.

Kesilapan biasa

Meninggalkan VIRTUAL dan bergantung pada tetapan lalai khusus versi untuk menjelaskan niat.
Hanya menyedari fungsi meruap (volatile), subpertanyaan atau jenis yang ditentukan pengguna semasa DDL.
Menguji nilai ASCII biasa sambil terlepas pandang NULL dan tingkah laku Unikod.
Menganggap tiada storan baris bersamaan dengan tiada CPU pertanyaan.
Mengalih keluar ungkapan lama semasa cutover dan kehilangan keupayaan rollback pantas.

Soalan susulan

Mengapa tidak menggunakan STORED di sini?

Tugasan ini menyasarkan penormalan masa baca yang murah dan mahu mengelakkan backfill fizikal. Jika imbasan pengeluaran, pengisihan atau penapis menjadikan CPU bacaan tidak boleh diterima, lajur tersimpan menjadi keputusan storan dan replikasi yang berasingan.

Bolehkah lajur maya menjadi kunci pemetakan?

Tidak. PostgreSQL 18 tidak membenarkan lajur terjana sebagai kunci pemetakan. Gunakan lajur biasa yang dikekalkan oleh laluan tulis apabila penghalaan petak memerlukan nilai tersebut.

Bagaimanakah anda menguji bahawa keistimewaan tidak diperluas?

Pertanyakan lajur asas dan lajur terjana dengan peranan aplikasi sebenar, memeriksa kebenaran lajur (grants) dan keistimewaan pelaksanaan untuk fungsi dalam ungkapan. Apabila lajur terjana bertujuan untuk menyembunyikan lajur asas, periksa juga sama ada setiap fungsi, termasuk fungsi di sebalik pengendali dan cast, ditandakan sebagai LEAKPROOF; PostgreSQL tidak menguatkuasakan syarat ini untuk aplikasi. Jika mana-mana laluan ungkapan tidak dapat dibuktikan kalis bocor (leakproof), jangan anggap geran lajur terjana sebagai pengasingan yang lengkap.

Bilakah dwi-bacaan boleh ditamatkan?

Selepas kitaran perniagaan penuh, petak sejarah dan beban puncak telah berlalu dengan sifar perbezaan semantik serta keputusan prestasi dan keistimewaan yang boleh diterima.