後端面試：PostgreSQL 18 的虛擬生成欄與儲存生成欄怎麼選？

題干與適用場景

訂單服務有 unitprice、quantity 和 discount，需要得到只由同一列計算出的 netamount。面試官追問這個值應在寫入時保存，還是每次讀取時計算；資料庫也要支援索引、邏輯複製、回滾和舊版本訂閱端。核心是持久化邊界與遷移決策，不是背語法。

面試官考察點

• 能否區分 VIRTUAL 的讀取時計算與 STORED 的寫入時計算及空間代價。
• 是否檢查生成表達式只能依賴同一列、不可變函式與版本限制。
• 是否理解虛擬欄不能使用使用者自訂型別或函式，而儲存欄限制較少。
• 能否依查詢熱度、寫入量、索引需求與複製拓撲做選擇。
• 是否為 PostgreSQL 18 發布端與舊版本訂閱端設計相容和回滾路徑。

回答前需要釐清的問題

• net_amount 是否用於高頻篩選、排序或唯一性約束？若需要索引，寫入時物化通常更直接。
• 讀多寫少還是寫入吞吐更重要？虛擬欄省儲存，但把計算放到每次讀取。
• 邏輯複製的訂閱端是否為 PostgreSQL 18？舊版本初始同步不會複製生成欄。
• 計算是否可能依賴使用者自訂函式、外部表或目前時間？這會改變可用性與確定性。

30 秒回答框架

我先固定公式和資料一致性邊界。若計算簡單、讀取頻率低且不需要物理副本，我傾向 VIRTUAL，讓資料庫在讀取時重算；若需要穩定索引、降低讀取 CPU，或訂閱端需要接收已計算值，則選 STORED。我會核對生成表達式的不可變性與版本支援，驗證發布端、訂閱端、索引和回滾，再用生產形狀資料比較讀延遲、寫放大與複製行為。

分步驟深入解答

PostgreSQL 18 預設生成欄是 VIRTUAL：不佔行儲存，讀取時計算；STORED 在插入或更新時計算並佔用儲存。兩者都不能在 INSERT 或 UPDATE 直接賦值，生成表達式只能引用同一列並使用不可變函式。

選擇規則是把成本放在較不敏感的方向。高頻讀取、需要索引或希望副本直接消費結果時，STORED 用空間和寫入 CPU 換穩定讀取；低頻讀取、公式簡單且寫入很熱時，VIRTUAL 減少儲存和寫入放大。不能因為欄名像快取就假設虛擬欄會自動共享結果。

SQL 與複製範例

以下把金額計算明確設定為儲存欄；若改為 VIRTUAL，讀取時會重新執行表達式：

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');

邏輯複製發布端可選擇發布儲存生成欄；虛擬欄沒有物理值，不能走同一複製路徑。若訂閱端早於 PostgreSQL 18，初始同步不會複製生成欄，即使發布端開啟選項，也必須準備訂閱端重算或降級方案。

遷移、索引與故障路徑

遷移先在影子表用真實資料分布重算兩種方案，比較寫入延遲、讀取 CPU、索引大小和副本追趕時間。先把新欄位作為普通欄位雙寫，完成等價性對帳，再切換成生成欄；不要在大表高峰期直接改定義。

若複製鏈路混合 PostgreSQL 版本，發布端記錄生成欄發布設定、訂閱端版本與初始同步狀態。出現不一致時，暫停依賴該欄的下游消費，以基礎欄位重算並回填，不要把複製缺口當成零值。回滾保留原始欄位和公式版本，直到新舊結果在抽樣和全量驗證一致。

常見錯誤

• 把 VIRTUAL 當快取，忽略每次讀取都會計算。
• 認為所有生成表達式都能呼叫目前時間、子查詢或自訂函式。
• 需要索引時只建立虛擬欄，卻沒有驗證版本和索引支援。
• 只升級發布端，未檢查舊版本訂閱端的初始同步行為。
• 直接刪除原始欄位，讓複製或回滾失去重算依據。

追問及應對

什麼時候優先 VIRTUAL？

公式短小、讀取頻率有限、寫入量高且不需要物理索引時優先虛擬欄。上線前以讀取 CPU、尾延遲和並發讀壓測證明省下的儲存沒有變成不可接受的計算成本。

什麼時候必須 STORED？

需要索引、唯一性檢查、穩定的副本讀取或訂閱端無法安全重算時，選擇儲存欄。計算結果也要納入寫入稽核，公式變更則當成資料遷移處理。

如何升級混合版本複製拓撲？

先盤點發布端與訂閱端版本，舊訂閱端以基礎欄位重算或普通複製欄位過渡；升級並完成初始同步後，再開啟 publishgeneratedcolumns。用行數、雜湊和抽樣金額對帳，確認失敗時可回滾。