プロンプトとコンテキスト
この質問は、生成列のすべてのオプションを比較するものではありません。繰り返される行の式を PostgreSQL 18 の仮想生成列へ移行することに焦点を当てています。目標は、格納列のバックフィルを行わずに単一の計算契約を確立し、同時に読み取り CPU、式の制限、および権限の変更を制御することです。
面接官が評価するポイント
- PostgreSQL 18 で仮想生成列が導入され、デフォルトになったことを理解しているか。
- 式が現在の行および不変（immutable）な組み込み関数と型のみを使用していることを証明できるか。
- 単なる DDL の成功だけでなく、デュアルリードや本番クエリプランで検証できるか。
- 仮想値が読み取り時に計算され、行ストレージを消費せず、パーティションキーになれないことを理解しているか。
回答前の確認事項
式が組み込みのみを使用していることを確認し、それを参照する読み取りとフィルタを特定し、既存の式インデックスの有無をチェックし、アプリケーションが古い式と新しい列を一時的に比較できるかを質問します。生成列とベース列は権限が分離されているため、ロールも確認します。生成列がベース列を隔離することを意図している場合は、式内のすべての関数、演算子、およびキャストが LEAKPROOF の要件を満たしているかを検証します。
30秒の回答フレームワーク
まず式が PostgreSQL 18 の仮想列の制限を満たしていることを証明し、次に明示的に VIRTUAL な列を追加します。移行中、アプリケーションは null、Unicode、例外的な入力値、および履歴パーティション全体にわたって、古い式と新しい列のシャドウリードを実行します。代表的なクエリについて、CPU、テールレイテンシ、プランを比較します。セマンティクス、パフォーマンス、および権限のゲートを通過した後、古い式への迅速なロールバックを維持しながら、読み取りを新しい列に切り替えます。
ステップごとの詳細解説
ALTER TABLE report_events
ADD COLUMN normalized_country text
GENERATED ALWAYS AS (lower(trim(country_code))) VIRTUAL;仮想値は読み取り時に計算され、行ストレージを消費しません。その式は現在の行のみを参照でき、サブクエリや他の生成列を含めることはできず、immutable な関数を使用する必要があります。また、仮想列はユーザー定義関数や型に依存することはできません。明示的に VIRTUAL と記述することで、移行の意図が PostgreSQL 18 のデフォルト設定に依存することを防ぎます。
履歴データをスキャンし、NULL、空白、大文字小文字、非 ASCII 入力を含む normalized_country IS NOT DISTINCT FROM lower(trim(country_code)) を比較します。次に、代表的な EXPLAIN (ANALYZE, BUFFERS) チェックを実行します。読み取り時の計算により CPU が増加する可能性があります。フィルタでインデックスが必要な場合は、ストレージ不要がコストゼロを意味すると見なすのではなく、サポートされている正確なインデックスパスと書き込みコストを検証してください。
段階的にリリースします：列を追加し、古い式を正（authoritative）としたまま両方の形式でシャドウリードを行い、差異がゼロでパフォーマンスが許容範囲内であること確認した後にのみ切り替えます。ロールバック時は古い式に戻します。最後に、実際のアプリケーションロールを使用してベース列と生成列の権限をテストします。関数、演算子、またはキャストのいずれかが LEAKPROOF であると証明できない場合、生成列の権限はベース列に対する完全なセキュリティ境界を形成しません。
優れた回答例
これをクエリ契約の移行として扱います。式が現在の行の immutable な組み込みのみを使用していることを証明した後、明示的な仮想列を追加します。これにより物理的なバックフィルは回避されますが、負荷が読み取り時に移るため、本番環境を模した CPU およびテールレイテンシのテストが必須となります。
アプリケーションはまず新旧の結果を比較し、入力クラスごとに入力の不一致をグループ化します。セマンティクス、プラン、および権限のゲートを通過した後にのみ切り替えます。動作にリグレッションが発生した場合、ベースデータはそのまま保持されているため、クエリは即座に古い式に戻すことができます。
よくある間違い
VIRTUALを省略し、意図を説明するのにバージョン固有のデフォルトに依存すること。- DDL の実行時になって初めて volatile 関数、サブクエリ、またはユーザー定義型に気づくこと。
NULLや Unicode の挙動を見落とし、通常の ASCII 値のみをテストすること。- 行ストレージがないことをクエリ CPU コストがないことと同等として扱うこと。
- 切り替え時に古い式を削除してしまい、迅速なロールバック手段を失うこと。
フォローアップの質問
なぜここで STORED を使用しないのですか？
このタスクは読み取り時の安価な正規化を対象としており、物理的なバックフィルを回避することを目的としています。本番環境のスキャン、ソート、またはフィルタにより読み取り CPU が許容できなくなる場合、格納列の採用はストレージとレプリケーションに関する個別の決定事項となります。
仮想列をパーティションキーにすることはできますか？
いいえ。PostgreSQL 18 では、生成列をパーティションキーとして使用することはできません。パーティションのルーティングにその値が必要な場合は、書き込みパスによって維持される通常の列を使用してください。
権限が拡大していないことをどのようにテストしますか？
実際のアプリケーションロールを使用してベース列と生成列に対してクエリを実行し、列の権限（grant）および式内の関数の実行権限を確認します。生成列がベース列を隠蔽することを目的としている場合は、演算子やキャストの背後にある関数を含め、すべての関数に LEAKPROOF のマークが付いているかも検査します。PostgreSQL はアプリケーションに対してこの条件を強制しません。式のいずれかのパスでリークプルーフであることが証明できない場合は、生成列への権限付与を完全な分離として扱わないでください。
デュアルリードはいつ終了できますか？
完全なビジネスサイクル、履歴パーティション、およびピーク負荷を通過し、セマンティックな差異がゼロであり、パフォーマンスおよび権限の結果が許容できる状態になった後です。