PostgreSQLでインデックスをオンラインで安全に構築または再構築するにはどうすればよいですか？

1. 設問

注文テーブルが継続的に書き込みを受信している中で、クエリチームから複合インデックスの作成が要求されています。別のテーブルには肥大化のために再構築が必要なインデックスがあります。テーブルは大規模で、作業枠はオフピーク時であり、通常の読み取りと書き込みをブロックすることはできません。観測可能でロールバック可能なオンライン計画を提示してください。

2. 制約事項と前提確認

PostgreSQLのバージョン、テーブルとインデックスのタイプ、レプリケーショントポロジ、ディスクの空き容量、および書き込みピークを確認します。
CREATE INDEX、CREATE INDEX CONCURRENTLY、およびREINDEX CONCURRENTLYを区別します。
コンカレント構築は書き込みロックの影響を軽減しますが、複数回のスキャンを実行し、CPU/IOを消費し、トランザクションブロック内では実行できません。
実施前に、許容可能な構築時間、ロック待ち時間、レイテンシ、およびクリーンアップ枠を定義します。

3. コアアイデア

通常のインデックス構築は書き込みをブロックする可能性があります。コンカレント構築では、insert、update、deleteの継続が許可されますが、完了までに時間がかかり、より多くのリソースを消費します。まず現実的な規模のシャドー環境でコストを見積もります。本番環境では、lock_timeoutを設定し、statement_timeoutを制限し、リソースを監視します。作成後は、プランナの選択、ロールバックパス、およびレプリカの一貫性を検証します。DDLコマンドの成功は、ビジネス上のロールアウトの成功を意味するものではありません。

4. 参照フロー

text

preflight:
  verify_version_replicas_disk_and_query_shape()
  estimate_scan_cost_on_shadow_copy()
  reserve_maintenance_window_and_abort_thresholds()

build:
  set lock_timeout = short
  set statement_timeout = bounded
  CREATE INDEX CONCURRENTLY idx_orders_customer_time
    ON orders (customer_id, created_at DESC)

verify:
  inspect_index_state_and_size()
  EXPLAIN (ANALYZE, BUFFERS) representative_queries()
  compare_write_latency_replica_lag_and_error_rate()

再構築にはREINDEX CONCURRENTLYを優先します。障害によって無効な一時インデックスが残った場合は、再試行する前にドキュメントに従って特定し、クリーンアップします。デプロイスクリプトでは、命名、べき等性、およびアラートを追跡可能にする必要があります。通常のトランザクション移行内にコンカレントDDLを隠蔽してはなりません。

5. 障害ケースとトレードオフ

コンカレント構築は、長時間のトランザクション、スナップショットの競合、またはディスク容量不足によって失敗する可能性があります。失敗したオブジェクトは無効（invalid）なまま残り、スペースを消費し続ける場合があります。構築中のCPU、IO、およびWALの増加は、サービスを遅延させ、レプリカラグを増大させる可能性があるため、スロットルまたは一時停止を行います。コストが許容できない場合は、クエリの最適化、テーブルのパーティショニング、またはオンラインマイグレーションツールの使用を検討しますが、その場合でもトリガー、バックフィル、切り替え、およびロールバックの動作を検証してください。

6. 検証とオブザーバビリティ

インデックスの状態、サイズ、構築時間、ロック待ち、WAL、CPU/IO、およびレプリカラグを記録します。
代表的なクエリについて、プラン、スキャン行数、p95/p99レイテンシ、および書き込みスループットを比較します。
長時間トランザクション、無効なインデックス、重複インデックス、および制約の依存関係を確認します。
古いインデックスを削除する前に、複数の完全なトラフィックピークを経過させ、リカバリスクリプトを保持しておきます。

7. よくある間違い

CONCURRENTLYが一切ロックを取得しないと思い込み、短いロック待ちやリソース競合を無視すること。
CREATE INDEX CONCURRENTLYをトランザクションブロック内に配置し、即座に失敗させること。
無効なインデックス、レプリカラグ、実際のクエリプランを確認せず、DDLのリターンコードのみを確認すること。
ディスク、WAL、および長時間トランザクションのバジェットを考慮せずに巨大なテーブルを再構築すること。

8. 面接の評価ポイント

コンカレントDDLのセマンティクスを区別できるか

候補者は、通常およびコンカレントの作成または再構築操作におけるロック、スキャンパス、トランザクション制限、およびリソースコストを説明しているか。

本番運用の事前確認を計画できるか

候補者は、バージョン、ディスク、長時間トランザクション、レプリケーショントポロジ、クエリ形状を確認し、現実的なデータ量で見積もりを行っているか。

障害復旧を設計できるか

候補者は、無効なインデックス、タイムアウト、ディスク枯渇、レプリカラグを、クリーンアップ、再試行、ロールバックの手順を含めて処理しているか。

ビジネスメトリクスで検証できるか

候補者は、DDLのリターンコードだけを信用するのではなく、プラン、p95/p99、書き込みレイテンシ、WAL、ロック待ち、およびレプリカラグを比較しているか。