Wawancara backend: Bagaimana Anda memilih antara virtual versus stored generated columns di PostgreSQL 18?

Arahan dan ruang lingkup

Sebuah layanan pesanan memiliki unit_price, quantity, dan discount, serta membutuhkan net_amount yang diturunkan hanya dari baris yang sama. Pewawancara menanyakan apakah nilai tersebut sebaiknya dihitung saat penulisan atau pada setiap pembacaan, sementara basis data harus mendukung indeks, replikasi logis, rollback, dan subscriber versi lama. Keahlian intinya adalah menentukan batas persistensi, bukan sekadar mengingat sintaksis.

Hal yang dievaluasi pewawancara

• Apakah Anda membedakan komputasi saat baca pada VIRTUAL dari komputasi saat tulis dan biaya penyimpanan pada STORED.
• Apakah Anda memastikan bahwa ekspresi hanya menggunakan baris saat ini, fungsi immutable, dan tipe data yang didukung.
• Apakah Anda mengetahui bahwa kolom virtual tidak dapat menggunakan tipe data atau fungsi buatan pengguna, sedangkan kolom tersimpan memiliki lebih sedikit batasan.
• Apakah frekuensi kueri, volume tulis, pengindeksan, dan topologi replikasi mendasari pilihan Anda.
• Apakah publisher PostgreSQL 18 dan subscriber versi lama memiliki jalur kompatibilitas dan rollback yang eksplisit.

Pertanyaan untuk diklarifikasi terlebih dahulu

• Apakah net_amount digunakan untuk filter yang sering, pengurutan, atau keunikan? Keberadaan indeks biasanya lebih menguntungkan jika nilai dimaterialisasi saat penulisan.
• Apakah beban kerja lebih dominan baca atau dominan tulis? Kolom virtual menghemat penyimpanan tetapi melakukan komputasi pada setiap pembacaan.
• Apakah subscriber replikasi logis menggunakan PostgreSQL 18? Versi yang lebih lama tidak menyalin generated columns selama sinkronisasi awal.
• Mungkinkah ekspresi bergantung pada fungsi pengguna, tabel eksternal, atau waktu saat ini? Hal tersebut mengubah determinisme dan dukungan sistem.

Kerangka jawaban 30 detik

Pertama-tama, saya akan menetapkan formula dan batas konsistensinya. Untuk nilai sederhana yang jarang dibaca dan tidak memerlukan replika fisik, saya akan menggunakan VIRTUAL dan membiarkan PostgreSQL menghitungnya saat dibaca. Jika nilai tersebut memerlukan indeks yang stabil, beban CPU baca yang lebih rendah, atau harus tiba dalam kondisi sudah dihitung di subscriber, saya akan menggunakan STORED. Saya akan memverifikasi sifat immutable pada ekspresi dan dukungan versi, menguji publisher, subscriber, indeks, dan rollback, lalu membandingkan latensi baca, amplifikasi tulis, dan perilaku replikasi menggunakan data yang menyerupai produksi.

Penalaran langkah demi langkah

PostgreSQL 18 menjadikan VIRTUAL sebagai jenis generated-column default: tidak memakan penyimpanan baris dan dihitung saat dibaca. STORED dihitung saat insert atau update dan memakan penyimpanan. Kedua jenis ini tidak dapat diberi nilai secara langsung di INSERT atau UPDATE; ekspresi hanya dapat mereferensikan baris saat ini dan fungsi immutable.

Aturan keputusannya adalah menempatkan beban pada bagian di mana beban kerja kurang sensitif. Pembacaan yang sering, indeks, atau replika yang harus menggunakan hasilnya lebih cocok dengan STORED, mengorbankan ruang dan CPU tulis demi pembacaan yang stabil. Pembacaan yang jarang dengan jalur tulis yang sibuk serta ekspresi yang pendek lebih cocok dengan VIRTUAL, mengorbankan penyimpanan dan beban kerja tulis demi CPU baca. Kolom virtual bukanlah cache hasil bersama hanya karena namanya terdengar mirip.

Contoh SQL dan replikasi

Contoh ini secara eksplisit menyimpan jumlahnya; mengubah STORED menjadi VIRTUAL membuat pembacaan mengevaluasi kembali ekspresi tersebut:

CREATE TABLE order_line (
  id bigint PRIMARY KEY,
  unit_price numeric(12, 2) NOT NULL,
  quantity integer NOT NULL CHECK (quantity > 0),
  discount numeric(5, 4) NOT NULL CHECK (discount BETWEEN 0 AND 1),
  net_amount numeric(12, 2)
    GENERATED ALWAYS AS (unit_price * quantity * (1 - discount)) STORED
);

CREATE INDEX order_line_net_amount_idx ON order_line (net_amount);

CREATE PUBLICATION order_pub
  FOR TABLE order_line
  WITH (publish_generated_columns = 'stored');

Publisher dapat memilih untuk memublikasikan stored generated columns; kolom virtual tidak memiliki nilai fisik untuk disalin melalui jalur yang sama. Jika subscriber lebih lama dari PostgreSQL 18, sinkronisasi awalnya tidak menyalin generated columns meskipun publisher mengaktifkan opsi tersebut, sehingga subscriber memerlukan rencana komputasi ulang atau fallback.

Migrasi, indeks, dan jalur kegagalan

Uji kedua pilihan pada shadow table menggunakan distribusi data yang sebenarnya. Bandingkan latensi tulis, CPU baca, ukuran indeks, dan waktu penyesuaian replika. Perkenalkan nilai baru sebagai kolom biasa dengan skema dual-write, rekonsiliasi data tersebut, dan baru kemudian beralih ke generated column; jangan mengubah tabel besar selama jendela waktu tersibuknya.

Untuk topologi replikasi versi campuran, catat pengaturan publisher, versi subscriber, dan status sinkronisasi awal. Jika nilai berbeda, jeda konsumen downstream yang bergantung pada kolom tersebut dan hitung ulang dari kolom dasar alih-alih menganggap celah replikasi sebagai nol. Simpan kolom asli dan versi formula untuk rollback hingga pemeriksaan kesetaraan sampel dan penuh berhasil.

Kesalahan umum

• Menganggap VIRTUAL sebagai cache dan lupa bahwa setiap pembacaan akan menghitungnya kembali.
• Mengasumsikan setiap ekspresi boleh memanggil waktu saat ini, subkueri, atau fungsi buatan pengguna.
• Memilih kolom virtual untuk jalur yang diindeks tanpa memverifikasi versi dan dukungan indeks.
• Hanya meningkatkan versi publisher dan mengabaikan perilaku sinkronisasi awal subscriber versi lama.
• Menghapus kolom sumber sehingga replikasi dan rollback tidak lagi dapat menghitung ulang nilainya.

Pertanyaan lanjutan

Kapan Anda lebih memilih VIRTUAL?

Pilih jenis ini untuk ekspresi pendek, frekuensi baca terbatas, penulisan yang padat, dan tidak memerlukan indeks fisik. Sebelum peluncuran, gunakan pengujian CPU baca, tail latency, dan pembacaan konkuren untuk membuktikan bahwa penghematan penyimpanan tidak berubah menjadi biaya komputasi yang tidak dapat diterima.

Kapan STORED diperlukan?

Gunakan jenis ini ketika nilainya memerlukan indeks, pemeriksaan keunikan, pembacaan replika yang stabil, atau subscriber yang tidak dapat menghitung ulang dengan aman. Sertakan nilai ini dalam audit penulisan dan perlakukan perubahan formula sebagai migrasi data.

Bagaimana Anda meningkatkan topologi replikasi versi campuran?

Inventarisasi versi publisher dan subscriber. Pertahankan subscriber versi lama pada komputasi ulang kolom dasar atau kolom transisi terplikasi biasa; setelah meningkatkan versi dan menyelesaikan sinkronisasi awal, aktifkan publish_generated_columns. Rekonsiliasi jumlah baris, hash, dan sampel jumlah nilai, dengan jalur rollback jika ada pemeriksaan yang gagal.