Written with AI assistance from the source video. The video is the authority.
7:45Better Stack
Log in to leave a comment
No posts yet
レガシーシステムで親子関係や複数パスを探索する際に使用する再帰CTEは、データが深くなると破綻します。インデックスのファンアウトが揺らぐ瞬間、エンジンは各ステップごとに一時テーブルを作成し、セルフ結合を繰り返します。結果セットがメモリを満たし、ディスクにあふれるスピルオーバーが発生します。I/Oはブロックされ、CPUは枯渇します。PostgreSQL 14に導入されたネイティブCYCLE構文は配列の検索コストを削減しますが、探索深度が深くなるにつれて繰り返される補助インデックスのルックアップと大容量タプルのメモリコピーコストはそのまま残ります。
検索速度を向上させ、CPUを節約するには、SQL/PGQベースのネイティブグラフ構文に切り替える必要があります。第一に、既存のリレーショナルテーブルの物理構造を維持したまま CREATE PROPERTY GRAPH 構文を使用します。単体エンティティテーブルは VERTEX TABLES として、多対多の交差テーブルは EDGE TABLES として明示します。第二に、キーに指定したカラムがグラフ属性に自動的に含まれないため、PROPERTIES 句にフィールドを記述して GRAPH_TABLE のフィルタリング権限を確保します。第三に、探索深度が3段階を超え、平均分岐率が10個以上、またはタプル数が1000万件を超えるクエリは GRAPH_TABLE MATCH パターンに変更します。複雑な再帰コードがわずか1行のパターンに短縮され、メモリバッファの使用量が顕著に減少します。
分散環境でマルチスレッドが同時にデータを投入する際に発生する重複挿入エラーは、アプリケーションロックでは解決できません。ネットワークのRTTオーバーヘッドが発生し、トランザクションコミット直前の隙間で競合状態（レースコンディション）が爆発します。INSERT ON CONFLICT や MERGE 構文によって行レベルロックを内面化する必要があります。ON CONFLICT 構文はスペキュレイティブ挿入技法により、一意インデックスページにロックをかけて挿入を試みます。衝突が発生した場合は DO UPDATE や DO NOTHING に即座に分岐し、デッドロックセッションの頻度を低下させます。
アトミック構文を使用する際、トランザクション分離レベルを変更すると波及効果が大きくなります。READ COMMITTED では、後続トランザクションが先行トランザクションのコミットを待機してから最新のタプルを再読み込みし、安全に処理します。しかし、REPEATABLE READ や SERIALIZABLE に引き上げると、先行トランザクションと衝突した際に待機なしで即座に直列化失敗エラーをスローしてロールバックします。ロールバックコストを削減し、コネクションプールの枯渇を防ぐには、40001および40P01エラーコードに対してのみリトライループを設定する必要があります。基本の待機時間にランダムなジッター値を加算して試行タイミングを分散させ、最大リトライ回数を3回から5回の間に制限して上位のビジネスレイヤーに送出します。
ブロックの断片化やデッドタプルが蓄積し、インデックススキャン効率が低下するタイミングを把握することがストレージ管理の第一歩です。MVCCアーキテクチャの特性上、UPDATE や DELETE によって発生したデッドタプルはOSに即座に返還されず、断片化として残ります。pg_stat_user_tables ビューの n_dead_tup 指標と pgstattuple エクステンションを併用し、dead_tuple_ratio が20パーセントを超えるか、free_space の割合が30パーセントを占める場合にはディスク領域を回収する必要があります。放置するとI/Oブロックの読み取りが増加し、バッファプールの効率が崩壊します。
サービスを停止せずにバックグラウンドでストレージを回収するには、pg_repack ツールを使用します。第一に、対象の元テーブルのシャドウテーブルを作成し、ロギングトリガーを設定して変更事項を追跡します。第二に、有効なタプルをシャドウテーブルに大量コピーし、インデックスを非同期並列で再作成した後にシステムカタログを置き換えます。第三に、ターミナルからバックグラウンドプロセスのコマンドを直接実行します。
`bash
pg_repack
--dbname=production_db
--table=public.orders
--jobs=4
--wait-timeout=10
--no-superuser-check
`
ロック競合を防ぐには、セッションレベルで SET lock_timeout = '3s'; を設定し、3秒以内にロックを取得できない場合はキューに留まらず即座にエラーが発生するようにします。さらに --wait-timeout=10 オプションを付与し、後続クエリが連鎖的にブロックされる状況を断ち切ります。
統計情報の収集サイクルがずれ、クエリプランが突然変更されると、プロダクション障害に直結します。大容量の処理が集中する環境では、統計情報と実際のデータの分布が乖離し、プランフリップが発生します。オプティマイザがインデックススキャンの代わりにネステッドループ結合を選択すると、CPUスパイクが跳ね上がり、I/Oボトルネックが発生します。shared_preload_libraries に auto_explain モジュールを追加し、log_min_duration を500ミリ秒に設定して、遅いクエリの実際の実行プランをサーバーログに記録することで初めて診断が可能になります。
特定のクエリの実行パスを強制的に固定するには、ヒントモジュールを使用します。ログから問題のあるクエリを特定した後、pg_hint_plan エクステンションを有効化してSQL文の前にコメントを付与するか、hint_plan.hints カタログテーブルにクエリを登録します。ソースコードをデプロイしづらい状況であれば、カタログレイヤーに静的ヒント文字列を直接注入することで、再デプロイなしで即座にプランを固定できます。統計情報の再収集やインデックスの再作成に時間がかかる緊急時に、クエリの応答速度を維持する最も迅速な方法です。