Temu duga backend: Bagaimanakah anda memilih antara lajur janaan maya dan tersimpan dalam PostgreSQL 18?

Gesaan dan skop

Satu perkhidmatan pesanan mempunyai unit_price, quantity dan discount, serta memerlukan net_amount yang diterbitkan hanya daripada baris yang sama. Penemu duga bertanyakan sama ada untuk mengiranya semasa menulis atau pada setiap bacaan, manakala pangkalan data mesti menyokong indeks, replikasi logikal, rollback dan pelanggan versi lama. Kemahiran terasnya adalah menentukan sempadan ketahanan (persistence), bukan mengingati sintaks.

Perkara yang dinilai oleh penemu duga

• Sama ada anda membezakan pengiraan masa baca VIRTUAL daripada pengiraan masa tulis dan kos storan STORED.
• Sama ada anda menyemak bahawa ungkapan hanya menggunakan baris semasa, fungsi tak boleh ubah (immutable) dan jenis yang disokong.
• Sama ada anda mengetahui bahawa lajur maya tidak boleh menggunakan jenis atau fungsi yang ditakrifkan pengguna, manakala lajur tersimpan mempunyai sekatan yang lebih sedikit.
• Sama ada kekerapan pertanyaan, volum penulisan, pengindeksan dan topologi replikasi mendorong pilihan tersebut.
• Sama ada penerbit (publisher) PostgreSQL 18 dan pelanggan versi lama mempunyai laluan keserasian dan rollback yang jelas.

Soalan untuk dijelaskan terlebih dahulu

• Adakah net_amount digunakan untuk penapisan kerap, pengisihan atau keunikan? Indeks biasanya memihak kepada menjadikannya nyata (materialize) semasa menulis.
• Adakah beban kerja tertumpu kepada pembacaan atau penulisan? Lajur maya menjimatkan storan tetapi mengira pada setiap bacaan.
• Adakah pelanggan replikasi logikal menggunakan PostgreSQL 18? Versi yang lebih lama tidak menyalin lajur janaan semasa penyegerakan awal.
• Mungkinkah ungkapan tersebut bergantung pada fungsi pengguna, jadual luaran atau masa semasa? Perkara itu mengubah kedeterministikan dan sokongan.

Rangka kerja jawapan 30 saat

Saya akan menetapkan formula dan sempadan konsistensi terlebih dahulu. Untuk nilai yang mudah dan jarang dibaca yang tidak memerlukan replika fizikal, saya akan menggunakan VIRTUAL dan membiarkan PostgreSQL mengiranya semasa baca. Jika nilai tersebut memerlukan indeks yang stabil, CPU baca yang lebih rendah, atau mesti tiba dalam keadaan sudah dikira di pelanggan, saya akan menggunakan STORED. Saya akan mengesahkan ketakbolehubahan ungkapan dan sokongan versi, menguji penerbit, pelanggan, indeks dan rollback, kemudian membandingkan kependaman baca, amplifikasi tulis dan tingkah laku replikasi dengan data berbentuk pengeluaran.

Penaakulan langkah demi langkah

PostgreSQL 18 menjadikan VIRTUAL sebagai jenis lajur janaan lalai: ia tidak menduduki storan baris dan dikira apabila dibaca. STORED dikira semasa sisipan atau kemas kini dan menduduki storan. Kedua-dua jenis tidak boleh diberikan nilai secara langsung dalam INSERT atau UPDATE; ungkapan hanya boleh merujuk baris semasa dan fungsi tak boleh ubah.

Peraturan keputusan adalah meletakkan kos di tempat beban kerja kurang sensitif. Pembacaan yang kerap, indeks atau replika yang sepatutnya menggunakan hasil tersebut memihak kepada STORED, menukar ruang dan CPU tulis untuk bacaan yang stabil. Pembacaan yang jarang dengan laluan tulis yang sibuk dan ungkapan pendek memihak kepada VIRTUAL, menukar storan dan kerja tulis untuk CPU baca. Lajur maya bukanlah cache hasil kongsi semata-mata kerana namanya kelihatan seperti itu.

Contoh SQL dan replikasi

Contoh ini menyimpan jumlah tersebut secara eksplisit; menukar STORED kepada VIRTUAL menyebabkan pembacaan menilai semula ungkapan 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');

Penerbit boleh memilih untuk menerbitkan lajur janaan tersimpan; lajur maya tidak mempunyai nilai fizikal untuk disalin melalui laluan yang sama. Jika pelanggan lebih lama daripada PostgreSQL 18, penyegerakan awalnya tidak menyalin lajur janaan walaupun penerbit mendayakan pilihan tersebut, jadi pelanggan memerlukan pengiraan semula atau pelan sandaran (fallback).

Migrasi, indeks dan laluan kegagalan

Uji kedua-dua pilihan dalam jadual bayangan (shadow table) menggunakan taburan data sebenar. Bandingkan kependaman tulis, CPU baca, saiz indeks dan masa mengejar replika. Perkenalkan nilai baharu sebagai lajur dwi-tulis biasa, selaraskannya, dan hanya selepas itu beralih kepada lajur janaan; jangan ubah jadual besar semasa tetingkap waktu paling sibuknya.

Untuk topologi replikasi versi bercampur, rekodkan tetapan penerbit, versi pelanggan dan status penyegerakan awal. Jika nilai berbeza, jedakan pengguna hiliran yang bergantung pada lajur tersebut dan kira semula daripada lajur asas daripada menganggap jurang replikasi sebagai sifar. Simpan lajur asal dan versi formula untuk rollback sehingga semakan kesaksamaan sampel dan penuh lulus.

Kesilapan lazim

• Menganggap VIRTUAL sebagai cache dan terlupa bahawa setiap bacaan akan mengiranya.
• Menganggap setiap ungkapan boleh memanggil masa semasa, subpertanyaan atau fungsi yang ditakrifkan pengguna.
• Memilih lajur maya untuk laluan berindeks tanpa mengesahkan sokongan versi dan indeks.
• Hanya menaik taraf penerbit dan mengabaikan tingkah laku penyegerakan awal pelanggan versi lama.
• Memadamkan lajur sumber sehingga replikasi dan rollback tidak lagi dapat mengira semula nilai tersebut.

Soalan susulan

Bilakah anda lebih memilih VIRTUAL?

Utamakannya untuk ungkapan pendek, kekerapan bacaan terhad, penulisan yang sibuk dan tiada keperluan untuk indeks fizikal. Sebelum pelancaran, gunakan ujian CPU baca, kependaman ekor (tail latency) dan bacaan serentak untuk membuktikan penjimatan storan tidak menjadi kos pengiraan yang tidak boleh diterima.

Bilakah STORED diperlukan?

Gunakan pilihan ini apabila nilai memerlukan indeks, semakan keunikan, bacaan replika yang stabil, atau pelanggan yang tidak boleh mengira semula dengan selamat. Sertakan nilai tersebut dalam pengauditan tulis dan anggap perubahan formula sebagai migrasi data.

Bagaimanakah anda menaik taraf topologi replikasi versi bercampur?

Inventori versi penerbit dan pelanggan. Kekalkan pelanggan versi lama pada pengiraan semula lajur asas atau lajur peralihan tereplikasi biasa; selepas menaik taraf dan melengkapkan penyegerakan awal, dayakan publish_generated_columns. Selaraskan kiraan baris, cincangan (hash) dan jumlah sampel, dengan laluan rollback jika sebarang semakan gagal.