プロンプトと適用されるコンテキスト
PostgreSQL 18のordersテーブルに5億件の有効行があり、継続的な更新が発生しています。チームは次の4つの事実を観測しています：
n_dead_tupが増加し続けている；- autovacuumワーカーが定期的に出現する；
- 通常のバキューム後も
pg_relation_size('orders')が減少しない； - あるアプリケーションセッションが6時間
idle in transaction状態であり、古いbackend_xminを保持している。
MVCCの可視性から物理的なクリーンアップに至る完全な連鎖を説明してください。インシデントを診断し、安全な復旧手順を選択し、解決済みと宣言する前に必要な証拠を提示してください。数値は演習用の入力値であり、汎用的な運用しきい値ではありません。
これは、候補者が並行性セマンティクスと本番ストレージの挙動を結び付けて説明する必要があるバックエンド、データベース、プラットフォーム、SREの面接に適用されます。SQLはあくまで調査用言語にすぎません。
面接官が評価しているポイント
最初のテストは、候補者がUPDATEがバージョン作成であることを理解しているかどうかです。PostgreSQLは、挿入トランザクションID（xmin）や削除・置換トランザクションID（xmax）などのタプルメタデータを保存します。スナップショットはトランザクション境界とコミット状態を組み合わせて、どのバージョンが可視であるかを判断します。このルールは「最も大きいxminを持つ行を選ぶ」という単純なものよりも厳密です。
2番目のテストは、候補者が可視性とクリーンアップを関連付けられるかどうかです。アクティブなスナップショットがまだ必要としている可能性がある古いバージョンは削除できません。長時間開いたトランザクション、準備されたトランザクション（prepared transaction）、レプリケーションスロットは、クリーンアップホライズンの前進を妨げる可能性があります。Autovacuumが正常に実行されても、デッド状態だが削除不能なタプルが報告されることがあります。
3番目のテストは運用の正確さです。通常のVACUUMは通常、リレーション内部のデッドスペースを再利用可能にするだけであり、リレーションファイル自体を縮小することは通常ありません。VACUUM FULLはリレーションを書き換え、一時的な追加ディスク領域を必要とし、ACCESS EXCLUSIVEロックを取得します。これは例外的なメンテナンス操作であり、デッドタプル推定値の増加に対する最初の対応策ではありません。
最後に、候補者は4つのシグナルを明確に区別する必要があります：推定タプル数、回収可能領域、リレーションサイズ、そしてユーザーに見えるパフォーマンスです。これらは関連していますが、同じものではありません。n_dead_tupの減少はOSファイルが縮小したことを証明するものではなく、ファイルサイズが変わらないことはバキュームが失敗したことを証明するものではありません。
回答前に確認すべき明確化のための質問
- どの分離レベルが使用されていますか？ Read Committedでは通常、各文が新しいスナップショットを取得しますが、Repeatable ReadとSerializableはトランザクションレベルのスナップショットを保持します。アイドルトランザクションは、処理を行っていなくてもスナップショットホライズンを保持し続ける可能性があります。
- 古い
backend_xminがグローバルなブロッカーですか？ 推測するのではなく相関関係を確立してください。準備されたトランザクション、論理/物理レプリケーションスロット、他のセッションがより古いホライズンを保持している可能性があります。 - インシデントの判断材料として十分な鮮度の統計情報ですか？
n_dead_tupとn_live_tupは推定量です。最終バキューム時刻、進捗状況、ログ、リレーションサイズ、ワークロードの挙動と併せて読み取ってください。 - 目標はディスクの再利用ですか、それとも即時のファイル縮小ですか？ 定常的なバキュームは定常状態での再利用を目的としています。大量の領域をOSに返却するには、書き換えや適切なオンラインリビルド計画が必要です。
- アプリケーションは6時間のトランザクションを安全に終了できますか？ まずオーナーと業務処理を特定してください。キャンセルまたは強制終了すると未完了の作業がロールバックされ、ユーザーフローに影響を与える可能性があります。
- ワークロードで何が変化しましたか？ 更新レート、インデックス対象列、行の幅、autovacuum設定、ワーカーの飽和度、トランザクションの寿命はすべて、バージョンの生成量とクリーンアップ能力に影響を与えます。
- どの程度のロックおよびI/Oの影響が許容されますか？ 復旧計画は、メンテナンスを迅速に終わらせるだけでなく、レイテンシ、レプリケーション、ディスクの空き容量、可用性を維持しなければなりません。
30秒の回答フレームワーク
「PostgreSQLのMVCCは、更新によって新しいタプルバージョンを作成しながら、各文が一貫したスナップショットを読み取れるようにします。古いバージョンは、アクティブなスナップショットから参照されなくなるまで残ります。ここでは、6時間開いたままのトランザクションがbackend_xminを保持している可能性があるため、autovacuumはテーブルをスキャンできても、可視である可能性が残るバージョンを削除できません。
私ならまず、セッション、準備されたトランザクション、レプリケーションスロット全体で最も古いホライズンを確認し、テーブル統計、バキューム進捗、ログ、サイズと相関付けた上で、確認されたブロッカーを所有アプリケーション経由で終了します。その後、測定されたI/O制限の下で通常のバキュームを実行するか追いつかせ、デッドタプルの推定量と再利用挙動が安定することを確認します。通常のVACUUMは領域を再利用可能にするもので、通常ファイルは縮小しません。VACUUM FULLはテーブルを書き換えて排他ロックを取得するため、独立したメンテナンス判断が必要です。最後に、トランザクション寿命の制限、リレーションごとのホットテーブル調整、XID年齢の監視を行い、古いXIDが周回ホライズンを超えないようにフリーズを維持します。」
ステップバイステップの詳細解説
ステップ1：MVCCを通る1つの更新を追跡する
トランザクション100が注文バージョンを挿入したと仮定します。そのタプルヘッダーにはxminに挿入XIDが記録されます。その後、トランザクション220が注文を更新します。PostgreSQLは後継タプルを作成し、xmaxを含むトランザクションメタデータを使用して古いバージョンを無効としてマークします。論理モデルが示唆するように古いバイト列をその場で上書きすることはありません。
読み取り側はスナップショットとトランザクションコミット状態をチェックして、どのバージョンが可視かを判断します。単純化して言えば、コミットされていないトランザクションやスナップショットよりも未来のトランザクションによって挿入されたバージョンを拒否し、削除トランザクションがまだ可視でないバージョンを保持することがあります。実際の可視性ルールは、現在のトランザクション、アボートされたトランザクション、コマンドID、ヒントビットも処理するため、数値としてのxminとxmaxの値のみを比較することは正しい実装ではありません。
このモデルにより、読み取り/書き込みのロック競合が減少します。通常の読み取り側は書き込み側をブロックせず、書き込み側も通常の読み取り側をブロックしません。ただし、書き込み側同士が互いをブロックしないという意味ではありません。同じ論理行を更新する2つのトランザクションは依然として待機または競合する可能性があり、より高い分離レベルでは保証を維持するためにトランザクションをアボートすることがあります。
ステップ2：クリーンアップホライズンを導出する
トランザクション220がコミットした後、古いタプルは新しいスナップショットにとっては不要になります。ただし、それ以前に開始されたスナップショットからまだ可視である可能性がある場合、即座に削除することはできません。バキュームは、関連する最も古いホライズンに基づいてカットオフを選択します。その安全境界よりも新しいバージョンは「最近のデッド（recently dead）」となる可能性があります。つまり、現在の処理にとっては論理的に不要ですが、まだ安全に削除できません。
6時間のidle in transactionセッションが危険なのは、クライアントが開いたトランザクションを残しているためです。サーバーが次のクライアントコマンドを待機している間でも、そのbackend_xminが古いスナップショットを保持し続ける可能性があります。同様の調査には以下も含める必要があります：
pg_prepared_xacts（準備されたトランザクションが古いXIDを保持できるため）；pg_replication_slots（xminまたはcatalog_xminが必要な行やカタログを保持できるため）；- 古い
backend_xidまたはbackend_xminを持つ他のpg_stat_activity行； - レプリカフィードバックおよび論理デコーディング構成（レプリケーション要件がクリーンアップに影響を与える可能性があるため）。
したがって、因果関係の連鎖は次のようになります：長期存続するホライズン → 古いバージョンが潜在的に可視のまま残る → バキュームが回収できない → ヒープとインデックスの作業が蓄積する → キャッシュ効率とスキャンコストが悪化する可能性がある。この連鎖は、アイドル状態のセッション名から推測するのではなく、揃えられたタイムスタンプとホライズンによって証明する必要があります。
ステップ3：推定量、進捗、サイズを個別に診断する
まず、アクティビティとリレーション統計の読み取り専用スナップショットから開始します：
SELECT pid,
usename,
application_name,
state,
xact_start,
age(backend_xid) AS xid_age,
age(backend_xmin) AS xmin_age,
wait_event_type,
wait_event,
left(query, 120) AS query_sample
FROM pg_stat_activity
WHERE backend_xid IS NOT NULL OR backend_xmin IS NOT NULL
ORDER BY GREATEST(
COALESCE(age(backend_xid), 0),
COALESCE(age(backend_xmin), 0)
) DESC;
SELECT relid::regclass AS relation,
n_live_tup,
n_dead_tup,
n_tup_upd,
n_tup_hot_upd,
last_vacuum,
last_autovacuum,
vacuum_count,
autovacuum_count
FROM pg_stat_user_tables
WHERE relid = 'orders'::regclass;
SELECT pg_size_pretty(pg_relation_size('orders')) AS heap_size,
pg_size_pretty(pg_indexes_size('orders')) AS index_size,
pg_size_pretty(pg_total_relation_size('orders')) AS total_size;n_dead_tupは推定量であり、正確な膨張（bloat）測定値ではありません。last_autovacuumはワーカーが完了したことを証明するものであり、すべての不要なバージョンを削除したことを証明するものではありません。解放されたページが新しいバージョンの到着と同じ速度で再利用されているなら、大きなリレーションであっても健全である可能性があります。逆に、リレーションサイズが安定していても、レイテンシの上昇やインデックスの頻繁な更新（churn）が隠れている場合があります。
バキュームがアクティブな間は、フェーズとスキャンされたヒープブロックについてpg_stat_progress_vacuumを調査します。autovacuumログまたはVACUUM (VERBOSE)の出力を使用して、削除されたタプル数、削除不能のまま残ったタプル数、およびフリーズが進んだかどうかを確認します。同じタイムライン上でpg_stat_all_tables、リレーションサイズの履歴、クエリレイテンシ、バッファおよびI/O負荷、WAL生成レート、レプリケーション遅延、ディスクの空き容量を確認します。
ステップ4：最も安全な順序で復旧する
まず、最も古いトランザクションのオーナーと目的を特定します。破棄されたものであれば、アプリケーションまたは接続オーナー経由で終了します。アクティブな業務処理である場合は、キャンセルする前にロールバックが許容されるかどうかを判断します。PostgreSQLはpg_cancel_backendおよびpg_terminate_backendを提供していますが、関数へのアクセス権があることと、本番環境を中断する権限があることは別です。
次に、より古い準備されたトランザクションまたは不要になったレプリケーションスロットを、その所有システムを通じて解決します。アクティブなスロットを削除すると、レプリカの再構築が必要になったり、想定されるデコーディング位置が失われたりする可能性があるため、これは明示的な復旧判断となります。
ホライズンが進んだ後、autovacuumを追いつかせるか、測定された時間枠内で対象を絞った通常のVACUUM (VERBOSE, ANALYZE) ordersを実行します。レイテンシ、I/O、WAL、レプリケーション遅延、バキューム進捗、残りのディスク空き容量を監視します。最初の処理に時間がかかっているという理由だけで、競合する複数のメンテナンスジョブを立ち上げてはいけません。
その後、結果を検証します：
- 関連する最も古い
backend_xminまたはスロットホライズンが進んだこと； - 以前保持されていたデッドバージョンが削除可能になり、削除されたとバキュームが報告していること；
- 統計更新後に
n_dead_tupが減少傾向を示すこと； - 新しい更新が利用可能な領域を再利用し、リレーションの増加が期待される定常状態に戻ること；
- リクエストレイテンシ、インデックススキャンコスト、WAL、レプリカ遅延が合意された制限内に留まっていること；
age(relfrozenxid)およびデータベースXID年齢に安全な余裕があること。
この証拠が得られて初めて、チームは物理的なコンパクションを評価すべきです。VACUUM FULL ordersは新しいコンパクトなコピーを作成し、書き換え中に余分なディスクを必要とし、ACCESS EXCLUSIVEロックを保持します。5億行のテーブルでは、代わりにオンラインリビルド戦略や計画的なパーティション置換が必要になる場合があります。適切な選択は、サイズメトリックを小さくしたいという要求ではなく、ダウンタイム、空きディスク、レプリケーション、外部キー、書き込みの収束、ロールバックに依存します。
ステップ5：autovacuumの仕組みを客観的に説明する
Autovacuumは累積統計に反応します。更新と削除について、PostgreSQL 18は以下の形式のトリガーを使用します：
vacuum threshold = min(
autovacuum_vacuum_max_threshold,
autovacuum_vacuum_threshold
+ autovacuum_vacuum_scale_factor * pg_class.reltuples
)挿入主導のバキュームには、挿入されたタプルとフリーズされていないページの割合に基づく個別のしきい値があります。周回防止（anti-wraparound）バキュームは、テーブルに対して通常のautovacuumが無効化されている場合でも、XID年齢によって強制的に実行されます。
更新頻度の高い非常に大規模なリレーションでは、グローバルなスケールファクターを設定すると変更が蓄積するのを待ちすぎたり、突発的な負荷を引き起こしたりする可能性があります。測定されたバージョン生成量とバキューム能力からテーブルストレージパラメータを調整してください。また、ワーカーの可用性とコスト遅延も調査してください。適切なトリガーがあっても、ワーカーが即座に開始されることや、ワークロードがゴミを生成する速度よりも速く完了することは保証されません。
防止策は書き込みパスにも関係します。トランザクションを短く保ち、その中でのネットワーク呼び出しやユーザーの考慮時間を避けてください。コネクションプールや長時間実行される正当なジョブには適切な処理が必要なため、idle_in_transaction_session_timeoutは適切なアプリケーションロールに選択的に適用してください。必要な列のみを更新してください。インデックス対象列が変更されず、新しいタプルが同じヒープページに収まる場合、HOT更新によって新しいインデックスエントリの作成を回避できます。fillfactorは、初期状態でページ領域を意図的に空けておくことで、その機会を高めることができます。
ステップ6：フリーズをXID周回（wraparound）に関連付ける
通常のトランザクションIDは32ビットであり、巡回空間で比較されます。通常のXIDには、約20億件の古いとみなされるIDと、約20億件の新しいとみなされるIDがあります。タプルが通常の挿入XIDを無期限に保持し続けると、最終的に非常に古い値が未来の値に見えてしまう可能性があります。
バキュームは、十分に古いコミット済みタプルバージョンをフリーズすることでこれを防ぎます。最新のPostgreSQLでは、調査のための可視性を維持するために元のxminを保持しつつ、タプルの状態によってフリーズを表現します。フリーズされたバージョンは、あらゆる通常のトランザクションよりも古いものとして扱われます。テーブルおよびデータベースのフリーズXIDマーカーは、この作業がどこまで進んだかを記録します。
これは正確性の要件であり、任意の膨張対策メンテナンスではありません。age(pg_class.relfrozenxid)とage(pg_database.datfrozenxid)を監視し、周回防止バキュームを調査し、それらが完了するための十分なキャパシティを確保してください。フリーズ制限を引き上げることは作業を先送りにして安全ウィンドウを狭めるだけであり、巡回XIDの制約を取り除くものではありません。
模範回答
「私はこのインシデントを、バージョン生成と安全な回収のバランスとしてモデル化します。PostgreSQLのMVCCは、各文またはトランザクションにスナップショットを提供します。更新は後継タプルを作成し、トランザクションメタデータを介して古いバージョンをマークします。読み取り側はxmin、xmax、コミット状態、および自身のスナップショットを評価して可視バージョンを選択します。これにより、通常の読み取りと書き込みが読み取りロックの競合なしに進行できるようになりますが、同じ行の書き込み側同士は依然としてブロックまたはアボートする可能性があります。
古いバージョンは、関連するスナップショットから参照されなくなるまで削除できません。私はすべてのセッションで古いbackend_xidとbackend_xminを調査し、次に準備されたトランザクションとレプリケーションスロットを調査します。開いたトランザクションはスナップショットホライズンを保持できるため、6時間のアイドルトランザクションは有力な候補ですが、終了する前にそれが最も古いブロッカーであることを証明します。
そのホライズンをpg_stat_user_tables、pg_stat_progress_vacuum、autovacuumログ、ヒープとインデックスのサイズ、リレーションの増加、レイテンシ、I/O、WAL、レプリケーション遅延と相関付けます。n_dead_tupは推定量であり、autovacuumのタイムスタンプは実行されたことのみを証明します。オーナーがトランザクションの破棄を確認した場合、それをクローズし、より古いホライズンを解決し、測定された負荷の下で対象を絞った通常のバキュームを追いつかせます。
通常のVACUUMは、削除しても安全なバージョンを削除し、その領域を再利用可能にします。通常、リレーションファイルサイズはそのまま残ります。VACUUM FULLはリレーションを書き換え、一時ディスクを必要とし、テーブルを排他的にロックするため、ダウンタイムまたはオンラインリビルド戦略を伴う計画的なコンパクションの判断の下でのみ検討します。
防止策としては、トランザクション寿命を制限し、ロールに応じたアイドルタイムアウトを設定し、最も古いホライズンとXID年齢を監視し、測定された更新頻度からホットテーブルごとにautovacuumを調整し、スキーマとワークロードが許す限りHOT更新を促進します。バキュームはまた、十分に古いコミット済みバージョンをフリーズして、そのXIDが常に過去のものとして扱われるようにし、周回を防ぎます。成功の基準は、ブロッキングホライズンが進み、削除可能なタプルがクリーンアップされ、領域の再利用によって増加が安定し、サービスSLOが健全に保たれ、フリーズXID年齢に安全な余裕が維持されることです。」
よくある間違いと改善点
UPDATEがその場で行を変更すると述べる → PostgreSQLは通常、新しいヒープタプルバージョンを作成します → MVCCメタデータを通じて先行バージョンと後継バージョンを追跡してください。- 可視性を
xmin < current_xidのみに単純化する → コミット状態、スナップショット境界、アクティブなトランザクション、xmax、コマンドルールが重要です → 数値のショートカットを創作せずにスナップショットの判定を説明してください。 - 読み取り側と書き込み側は決してブロックしないと主張する → MVCCは通常の読み取り/書き込みロック競合を排除しますが、同一行の書き込み側や明示的なロックは依然として競合します → より限定された保証内容を正確に述べてください。
- 完了したautovacuumがすべてのデッドタプルを削除したと思い込む → 古いホライズンによりバージョンが削除不能のまま残る場合があります → 保持されたタプル、ブロッカーのホライズン、ログ、進捗を確認してください。
n_dead_tupを正確な膨張バイト数として扱う → これは推定行数です → ヒープ、インデックス、増加、再利用、パフォーマンスを個別に測定してください。- ファイルサイズが変わらないことをバキュームの失敗と呼ぶ → 通常のバキュームは通常、解放された領域を再利用のためにリレーション内に保持します → コンパクションを要求する前に定常状態の再利用を判断してください。
- 直ちに
VACUUM FULLを実行する → 書き換えには余分なディスクと排他ロックが必要です → コンパクション計画を立てる前に、ブロッカーを排除して通常のバキュームを追いつかせてください。 - グローバルなスケールファクターのみを調整する → ホットテーブルとワーカー能力は異なります → バージョン生成レート、完了時間、SLOの証拠に裏付けられたテーブルごとの設定を使用してください。
- 所有権の確認なしに最も古いPIDを強制終了する → トランザクションがロールバックされ、クライアントフローが失敗する可能性があります → まず目的、影響、復旧経路を確認してください。
- フリーズをストレージの最適化として扱う → フリーズは巡回XID比較における正確性を保護します → 安全管理としてフリーズXID年齢と周回防止作業を監視してください。
フォローアップ質問
フォローアップ1：VACUUMが成功した後もテーブルサイズが変わらないのはなぜですか？
通常のバキュームは、デッドタプルの領域を同じリレーション内で再利用可能としてマークします。物理的な末尾にある完全に空のページをOSに返却できる限定的なケースもありますが、日常的な挙動は内部再利用です。任意の空き領域を縮小するには、リレーションの書き換えまたは再編成が必要です。したがって、サイズが安定しており、レイテンシが安定し、継続的な再利用が行われている状態は健全である可能性があります。
フォローアップ2：Autovacuumが実行されたのに多くのデッドバージョンが残ったのはなぜですか？
それらが古いスナップショットからまだ可視であるか、準備されたトランザクションやレプリケーションホライズンによって保持されているか、ワーカーがクリーンアップできる速度よりも速く生成されている可能性があります。また、ワークロードやロックによってワーカーが遅延または中断された可能性もあります。詳細ログ、進捗状況、最も古いホライズン、ワーカーの飽和度、バージョン生成レートを使用してこれらのケースを区別してください。
フォローアップ3：VACUUMとANALYZEの違いは何ですか？
バキュームは再利用可能な領域を回収し、インデックスと可視性マップ（visibility map）を維持し、古いトランザクションメタデータをフリーズします。Analyzeはデータをサンプリングしてプランナ統計を更新します。VACUUM (ANALYZE)は両方を実行しますが、一方がもう一方の代わりになるわけではありません。正確な統計情報はデッドタプルを削除しませんし、回収された領域が正確なデータ分布モデルを保証するわけでもありません。
フォローアップ4：HOT更新はどのようにバキュームの負荷を軽減しますか？
更新によってインデックス対象列が変更されず、後継タプルが同じヒープページに収まる場合、PostgreSQLは新しいインデックスエントリの追加を回避できます。HOTチェーン内の中間バージョンは、通常のページアクセス中にプルーニング（削除）されることもあります。HOTはMVCCやバキュームの要件をなくすものではありませんが、インデックスの頻繁な更新やクリーンアップ作業を軽減します。n_tup_hot_updを総更新数と照らし合わせて監視し、fillfactorの変更を領域とキャッシュコストに対してテストしてください。
フォローアップ5：Autovacuumを無効にして夜間ジョブを実行してもよいですか？
それは変動するワークロードにとってリスクが高く、周回防止メンテナンスを無効にするものでもありません。日中のスパイクによって夜間の時間枠で回収できる以上の不要なバージョンが生成される可能性があり、静的なテーブルでも最終的にはフリーズが必要です。Autovacuumを有効のまま維持し、観測されたテーブルの変動に基づいて調整し、ワークロードがその選択を正当化する場合にのみ制御されたメンテナンスで補完してください。
フォローアップ6：アイドルトランザクションに対して有効なガードレールは何ですか？
idle_in_transaction_session_timeoutは、開いたトランザクション内で長く待機しすぎているセッションを終了できます。これを適切なロールに適用し、プール動作、リトライ、正当なジョブをテストしてください。また、アプリケーション境界も修正してください。データベース作業の直前にトランザクションを開始し、速やかにコミットまたはロールバックし、トランザクションを開いたままユーザー入力やリモートサービスを待機しないようにします。