PostgreSQL 運用・トラブル解決ガイド｜「遅い・詰まる・肥大化」を仕組みから切り分ける

PostgreSQLは「入れたら動く」データベースですが、運用に入ってから効いてくる癖が多いのも事実です。特にMVCCとVACUUM、統計情報とオプティマイザ、そしてロックの粒度は、Oracleや商用DBの常識をそのまま持ち込むとどこかで食い違います。

このガイドは、個別のノウハウを並べる代わりに「症状 → 内部で何が起きているか → 確認するビュー・パラメータ → 対処」という順番で運用の全体像を地図にしています。目の前の障害に対応するときは該当する章だけを読み、落ち着いたら前半の仕組みの章に戻ってくると、次から切り分けが速くなります。

なお、パラメータのデフォルト値や制限値はメジャーバージョンで変わります。本文では判断の目安として値を挙げますが、最終的な確認は使用中のバージョンの公式ドキュメントでお願いします。

🗺️ PostgreSQL運用でつまずくのはどこか（このガイドの地図）

トラブルは「遅い」「詰まる」「増える」「止まる」の4系統に分けて考える

障害報告は「システムが重い」という粒度で来ることが多く、そのまま調べ始めると迷子になります。最初にやるのは、症状を次の4系統のどれかに分類する作業です。

• 遅い。SQLの応答時間が伸びている。CPUやI/Oは動いている。実行計画・統計情報・インデックス設計の問題であることが多い。
• 詰まる。特定のSQLやセッションが応答を返さない。CPUはむしろ低い。ロック待ちやレプリケーション待ちを疑う。
• 増える。テーブル・インデックスのサイズ、ディスク使用量、WAL、デッドタプルが増え続ける。VACUUMが回っていない、WALが消えない系統。
• 止まる。接続できない、書き込みが拒否される、プロセスが落ちる。接続数枯渇、XID周回の緊急停止、ディスクフル、OOM Killerが候補。

💡 この4系統は、最初に見るビューも違います。「遅い」ならpg_stat_statementsとEXPLAIN、「詰まる」ならpg_locksとpg_blocking_pids()、「増える」ならpg_stat_user_tablesとpg_replication_slots、「止まる」ならサーバログとpg_stat_activityです。分類を間違えると見るビューも間違うので、ここだけは急がず決めます。

障害発生時に最初の5分で見るもの：pg_stat_activity・待機イベント・サーバログ

まずは現在動いているセッションの全体像です。

SELECT pid,
       usename,
       application_name,
       state,
       wait_event_type,
       wait_event,
       now() - xact_start  AS xact_age,
       now() - query_start AS query_age,
       left(query, 120)    AS query
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND state <> 'idle'
ORDER BY xact_start NULLS LAST;

見るポイントは3つです。

1. wait_event_typeがLockのセッションが並んでいれば「詰まる」系統。ブロック元をpg_blocking_pids()で辿ります。
2. wait_event_typeがIO（DataFileReadなど）中心なら、読みすぎ＝実行計画かバッファ不足の疑い。NULL（実行中）中心ならCPUバウンド。
3. state = 'idle in transaction'でxact_ageが長いものがあれば、それ自体が原因になります。VACUUMを止め、ロックを握り続けます。

同時にサーバログを時系列で見ます。deadlock detected、canceling statement due to statement timeout、could not resize shared memory segment、out of memory、database ... must be vacuumed withinといった文字列は、それだけで原因がほぼ特定できるキーワードです。ログに何も出ていないのに遅い場合は、log_min_duration_statementが未設定でスローログが取れていない可能性があります（後述）。

バージョンによって挙動が変わる箇所（VACUUM、パラレル、統計情報まわり）

「ネットで見た対処法が効かない」の多くはバージョン差です。運用で影響が大きいのは次のあたりです😇

インデックスの並列バキュームはPG13で入り、PG14ではXID周回に近づくと自動でインデックス処理などを省略する緊急動作（vacuum_failsafe_age）が加わりました。PG17ではVACUUMがTIDを保持する仕組みが変わり、maintenance_work_mem不足による複数回スキャンが起きにくくなっています。

パラレル実行まわりでは、JITがPG12からデフォルト有効で、短時間クエリでかえってオーバーヘッドになるケースがあります。パラレル対応のノード種類もバージョンごとに増えています。

統計情報については、拡張統計（CREATE STATISTICS）が列の組み合わせの相関を扱えます。ただし式に対する統計が扱えるようになったのはPG14で、それ以前は式インデックスを作って統計を得る回避策が必要でした。

CTEはPG12からデフォルトでインライン展開されるようになりました。PG11以前の「CTEは最適化の壁として使える」という前提のSQLは、アップグレード後に計画が変わります。

💡 まずSELECT version();を確認し、対処法の前提バージョンと一致しているかを見るのを癖にしてください。

自前構築とRDS/Auroraで、触れる範囲がどう変わるか

マネージドサービスでは、OSにログインできない前提で手順を組み直す必要があります。

• パラメータはpostgresql.confではなくパラメータグループで変更し、静的パラメータは再起動が必要です。pg_settings.pending_restartで確認できます。
• スーパーユーザー権限はなく、rds_superuserロールでの代替になります。COPY ... FROM '/path'のようなサーバ側ファイルアクセスや、任意の拡張の導入は制限されます。
• サーバログはCloudWatch Logsへ出す設定にしておかないと、後から時系列を追えません。
• Auroraは共有ストレージ層を持つため、WALアーカイブやベースバックアップの運用は自前構築と同じようには行いません。バックアップは基本的にスナップショットとバックトラック／PITR機能に寄せる前提になります。

一方で、本ガイドで扱う「MVCC・実行計画・ロック」の内部挙動はどの環境でも共通です。切り分けの考え方は流用でき、変わるのは操作手段だけだと理解しておくと混乱しません。

⚙️ 仕組みを先に押さえる：MVCC・WAL・共有バッファ

MVCCとxmin/xmax — 更新しても古い行が残る理由と可視性の判定

PostgreSQLのUPDATEは「その場で書き換える」操作ではありません。新しいバージョンの行を追記し、古い行に「このトランザクションIDで削除された」という印を付けます。各行（タプル）はシステム列としてxmin（挿入したXID）とxmax（削除したXID）を持っており、あるセッションから行が見えるかどうかは、自分のスナップショットとxmin/xmax、そしてコミット状態を記録したpg_xact（旧clog）を突き合わせて判定されます。

-- 行のバージョン情報を覗く
SELECT xmin, xmax, ctid, * FROM accounts WHERE id = 1;

ここから運用上の帰結が3つ出ます。

1. DELETEやUPDATEしても、すぐには容量が空きません。古いバージョン（デッドタプル）はVACUUMが回収するまで残ります。
2. 参照している側は書き込みをブロックしません。読み取りは過去のバージョンを見るため、SELECTとUPDATEが待ち合いません（この点はOracleと同様ですが、実現方法はUNDO領域ではなくテーブル内の追記です）。
3. 更新が多い行はテーブル内に散らばります。同一ページ内に収まり、更新列がインデックス対象でなければHOT更新となりインデックス更新を省けますが、fillfactorが詰まっていると別ページへ移り、インデックスも更新されます。更新頻度の高いテーブルでfillfactorを下げるチューニングはこの性質に基づいています。

WALとチェックポイント — コミットが速い理由、クラッシュリカバリで起きること

コミット時にデータファイル本体へ書き戻していたら、ランダムI/Oが大量に発生して遅くなります。PostgreSQLは変更内容をまずWAL（先行書き込みログ）へシーケンシャルに書き、WALのfsyncが完了した時点でコミット成功とします。データファイルへの反映は、共有バッファ上の汚れたページをチェックポインタとバックグラウンドライタが後追いで書き出します。

つまり、コミットのレイテンシを決めるのはWAL書き込み先のfsync性能です。「INSERTが遅い」原因がWALデバイスにあることは珍しくありません。pg_stat_activityでWALSyncやWALWriteの待機が目立つ場合はここを疑います。

クラッシュ後の起動時は、最後のチェックポイント位置からWALを再適用（REDO）して整合性を回復します。ここで運用上のトレードオフが出ます。checkpoint_timeoutを長く、max_wal_sizeを大きくするとチェックポイント頻度が減ってI/Oの山は小さくなりますが、リカバリに読むWALが増えて復旧時間が伸びます。逆に短くすると復旧は速いがフルページイメージの書き込みが増え、WAL量も膨らみます。checkpoint_completion_target（PG14以降はデフォルト0.9）で書き出しを平滑化し、log_checkpointsを有効にして実際の所要時間と書き出しページ数を記録しておくのが基本です。

共有バッファとOSキャッシュの二段構え — shared_buffersの決め方

⚠️ PostgreSQLは共有バッファ（shared_buffers）にページをキャッシュしますが、その下にOSのページキャッシュがあり、実質二段構えです。このためshared_buffersを大きくしすぎると、同じデータを二重にキャッシュしてメモリを無駄にし、チェックポイント時の書き出し量も増えます。

一般的な出発点は物理メモリの25%程度、そしてeffective_cache_sizeをOSキャッシュを含めた実効キャッシュ量の見積もり（50〜75%程度）として設定します。effective_cache_sizeはメモリを確保するパラメータではなく、オプティマイザがインデックススキャンのコストを見積もるための情報です。ここが小さすぎると、本来インデックスが有利な場面でシーケンシャルスキャンが選ばれやすくなります。

効き方の確認はヒット率とpg_buffercacheで行います。

-- データベース単位のバッファヒット率
SELECT datname,
       blks_hit,
       blks_read,
       round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS hit_pct
FROM pg_stat_database
WHERE datname = current_database();

ヒット率は高いほど良いというより、「急に下がったら何かが変わった」というトレンド指標として使います。ワーキングセットが増えたのか、悪い実行計画で余計なページを読んでいるのかを、後述のpg_stat_statementsと合わせて判断します。

autovacuumとFREEZE — トランザクションID周回という時間制限

autovacuumには2つの仕事があります。1つはデッドタプルの回収とpg_statisticの更新（ANALYZE）、もう1つが凍結（FREEZE）です。

トランザクションIDは32ビットの循環値で、有効に比較できる範囲は約21億トランザクション分しかありません。古い行のxminを放置すると、いずれ「未来のXID」と誤認されて可視性判定が壊れます。これを防ぐため、十分に古い行は「常に可視」を意味する凍結状態にマークされ、テーブルごとにrelfrozenxidが進められます。autovacuum_freeze_max_age（デフォルト2億）を超えたテーブルには、autovacuumが無効設定でも強制的にバキュームが走ります。

残り余裕は次のSQLで見られます。

SELECT datname,
       age(datfrozenxid) AS xid_age,
       2^31 - age(datfrozenxid) AS remaining_approx
FROM pg_database
ORDER BY xid_age DESC;

⚠️ 余裕が尽きると、まずログに警告が出て、最終的には新規トランザクションの発行を拒否して読み取り専用同然になります。ここまで来ると計画停止してシングルユーザーモードでバキュームするしかなくなるため、age(datfrozenxid)の監視は必須項目です。

🎈 テーブルとインデックスが膨らむ（VACUUM関連のトラブル）

デッドタプルが回収されない典型パターン：長時間トランザクション、放置されたレプリケーションスロット、prepared transaction

VACUUMは「誰からも見えなくなった行」しか回収できません。したがって、古いスナップショットやXIDを保持し続ける存在が1つでもあると、autovacuumが正常に動いていても回収は進みません。原因はほぼ次の4つです。

1. 長時間トランザクション。idle in transactionのまま放置されたセッション、巨大なバッチ、REPEATABLE READで長く走る集計処理。
2. レプリケーションスロット。スタンバイを削除したのにスロットを消し忘れた、論理レプリケーションのサブスクライバが停止している。スロットはWALも保持するため、ディスクフルの原因にもなります。
3. prepared transaction（2相コミットの未決着）。PREPARE TRANSACTIONしたままCOMMIT PREPAREDされていないもの。アプリからは見えないため気づきにくい典型です。
4. スタンバイからの参照。hot_standby_feedback = onのスタンバイで長時間クエリが走っていると、プライマリ側の回収が抑制されます。

まとめて確認するSQLを1つ用意しておくと便利です。

-- 古いXIDを押さえている犯人候補を一覧する
SELECT 'backend' AS kind, pid::text AS id, age(backend_xmin) AS xmin_age, state, left(query,80) AS info
  FROM pg_stat_activity WHERE backend_xmin IS NOT NULL
UNION ALL
SELECT 'slot', slot_name, age(xmin), active::text, restart_lsn::text
  FROM pg_replication_slots
UNION ALL
SELECT 'prepared', gid, age(transaction), '', prepared::text
  FROM pg_prepared_xacts
ORDER BY xmin_age DESC NULLS LAST;

💡 対処は原因ごとに、セッションのキャンセル、pg_drop_replication_slot()、ROLLBACK PREPAREDと分かれますが、いずれも「先に犯人を止める。VACUUMを打つのは後」が順番です。犯人が生きているうちに手動VACUUMを走らせても回収できず、I/Oだけ消費します。

肥大化を数字で確認する：pg_stat_user_tables、pgstattuple、pg_relation_size

まずは軽量なpg_stat_user_tablesで当たりを付けます。

SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum,
       last_autoanalyze,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

⚠️ last_autovacuumが古いままn_dead_tupが積み上がっているテーブルが要注意です。ただしこれらは統計情報に基づく推定値なので、実際の空き領域を知りたい場合はpgstattuple拡張を使います。

CREATE EXTENSION IF NOT EXISTS pgstattuple;

-- 正確だが全ページを読む。大きなテーブルでは approx を使う
SELECT * FROM pgstattuple('public.orders');
SELECT * FROM pgstattuple_approx('public.orders');

-- インデックス側の断片化
SELECT * FROM pgstatindex('public.orders_pkey');

💡 判断の目安は、free_percent（あるいはダイジェスト系の推定bloat率）が20〜30%を超え、かつサイズが数GB以上で運用に影響が出ている場合です。テーブルサイズが小さければ、肥大化していても実害はほとんどありません。数字を見る目的は「詰め直す価値があるか」の判断で、bloat率そのものを下げること自体は目的ではありません。

autovacuumの調整値と、手動VACUUM・VACUUM FULL・REINDEXを選ぶ判断基準

autovacuumの起動条件は、おおまかに「デッドタプル数 > autovacuum_vacuum_threshold（既定50）+ autovacuum_vacuum_scale_factor（既定0.2）× 行数」です。この0.2は、1億行のテーブルでは2000万行のデッドタプルが溜まるまで起動しないことを意味します。大きなテーブルには個別設定を入れるのが定石です。

-- 大きく更新の多いテーブルは閾値を絞り、コスト遅延を緩める
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold    = 1000,
  autovacuum_analyze_scale_factor= 0.01,
  autovacuum_vacuum_cost_delay   = 0
);

全体設定では、autovacuum_max_workers（既定3）を増やすだけでは効果が薄いことに注意します。コスト制限（autovacuum_vacuum_cost_limit）はワーカー間で分配されるため、ワーカーを増やすと1ワーカーあたりの速度は落ちます。「autovacuumが追いつかない」場合は、まずautovacuum_vacuum_cost_delayを小さく（I/Oに余裕があれば0）し、maintenance_work_memを十分に（数百MB〜1GB程度）与えるほうが効きます。進行状況はpg_stat_progress_vacuumで確認できます。

コマンドの選択基準は次のとおりです。

• VACUUM（通常）はページ内の空き領域を再利用可能にします。ロックはSHARE UPDATE EXCLUSIVEで読み書きを止めません。ファイルサイズは基本的に縮まない（末尾の空きのみ切り詰め）。日常運用はこれ。
• VACUUM FULLはテーブルを作り直してサイズを縮めます。ACCESS EXCLUSIVEロックなので読み書きともに止まります。元サイズ相当の追加ディスクも必要。計画停止が取れるときの最終手段です。
• pg_repackはオンラインで実質的な再編成を行う拡張です。短時間の排他ロックだけで済むため、止められない環境ではこちらを検討します。
• REINDEXはインデックスのみの膨張に有効です。REINDEX CONCURRENTLY（PG12以降）ならオンラインで実行できますが、時間とディスクを要し、失敗すると無効なインデックスが残るので後処理を手順に入れます。

「肥大化しているからVACUUM FULL」と反射的に決めず、回収を阻む要因を取り除いたうえで、サイズを縮める必要が本当にあるかを判断します。原因が残っていれば、縮めてもすぐ元に戻ります。

XID周回の警告が出たときに行う対応の順番

ログにdatabase "xxx" must be vacuumed within N transactionsが出たら、残り時間との競争です。順番を守ります😱

1. XIDを押さえている要因を止めます。前述のクエリで長時間トランザクション、スロット、prepared transactionを特定し、排除する。これをやらないと、どれだけVACUUMしてもrelfrozenxidは進みません。
2. 最も古いテーブルを特定します。

SELECT c.oid::regclass AS table_name,
       age(c.relfrozenxid) AS xid_age,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','m','t')
ORDER BY age(c.relfrozenxid) DESC
LIMIT 20;

3. 凍結バキュームを対象を絞って走らせます。VACUUM (FREEZE, VERBOSE) 対象テーブル;。全DBにVACUUM FREEZEを一括で流すと時間が読めなくなるため、古い順に処理します。急ぐ場合はVACUUM (INDEX_CLEANUP OFF, FREEZE)でインデックス処理を省き、凍結を優先する判断もあります。
4. 並列で処理速度を上げます。vacuumdb -jで複数テーブルを同時に処理する。vacuum_cost_delayを一時的に0にする。
5. 新規トランザクションが拒否される状態まで来たら、シングルユーザーモードでの復旧手順が必要になります。ここは事前に手順を読んでおく領域で、当日調べ始めると時間が足りません😰

再発防止としては、age(datfrozenxid)を監視項目に入れ、閾値（例えばautovacuum_freeze_max_ageの1.5倍程度）でアラートを上げるようにします。

🐢 クエリが遅い：実行計画とオプティマイザから切り分ける

EXPLAIN (ANALYZE, BUFFERS) の読み方 — 見積もり行数と実測行数のズレを起点にする

実行計画を読むとき、最初から最下層のノードを眺めても原因は見えません。見るべきは見積もりと実測のズレです。

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT ...;

各ノードにはrows=（見積もり）とactual rows=（実測）が並びます。ここが1桁以上乖離しているノードを、下から上に向かって最初に見つけた地点が調査の起点です。そこより上のノードの選択ミス（ネステッドループを選んでしまった、ハッシュテーブルが小さすぎた）は、たいてい下の見積もり誤差の結果でしかありません。

loopsも重要です。actual rows=10 loops=50000は、そのノードが50000回実行され、合計50万行を返していることを意味します（表示されるactual rowsは1ループあたりの平均）。ネステッドループの内側でこれが起きていると、内側が速く見えても総コストは大きくなります。

BUFFERSではshared hit（キャッシュヒット）とshared read（ディスク読み）を見ます。readが多ければI/O待ち、hitが異常に多ければ「キャッシュには載っているが読みすぎている」＝不要な行を大量に走査している状態です。Rows Removed by Filterが大きいのは、インデックスで絞れていないサインです。

Sort Method: external merge Disk: ...やBuckets: ... Batches: 4（ハッシュの分割）が出たらwork_mem不足です。

統計情報・拡張統計・相関 — 見積もりを直すためにできること

見積もりがずれる原因は大きく3つです。

1つは統計が古い、あるいは粗いこと。まずANALYZEを実行し、pg_stat_user_tables.last_autoanalyzeを確認します。値の分布が偏っている列は、ヒストグラムの精度（既定default_statistics_target = 100）を上げます。

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

2つめは列間の相関。オプティマイザは既定で列の独立性を仮定するため、「都道府県 = ‘東京都’ AND 市区町村 = ‘渋谷区’」のような従属関係がある条件は選択率を掛け算して過小に見積もります。ここは拡張統計で改善できます。

CREATE STATISTICS st_city (dependencies, ndistinct, mcv)
  ON pref_code, city_code FROM addresses;
ANALYZE addresses;

3つめは式や結合の見積もり限界。WHERE date_trunc('month', created_at) = ...のような式は、PG14以降なら式に対する拡張統計、それ以前は式インデックスを作ることで統計が得られます。

⚠️ 見積もりを正した結果として計画が変わるのが正しい直し方です。enable_nestloop = offのようなスイッチは、原因調査時の切り分け（「本当にその結合方式が原因か」を確認する）には有効ですが、恒久設定にすると別のクエリを壊します。

インデックスが使われない典型ケース：型の不一致、関数適用、前方一致以外のLIKE、ORDER BY + LIMIT

「インデックスを作ったのに使われない」の原因はパターン化できます。

• 型の不一致。bigint列に文字列リテラルやnumericを渡すと暗黙キャストの方向によってインデックスが使えません。character varyingとtextは問題になりませんが、char(n)との比較は注意が必要です。プレースホルダの型をドライバ側で確認します。
• 列に関数・演算を適用している。WHERE lower(email) = $1やWHERE created_at + interval '9 hour' > $1は通常のインデックスを使えません。式インデックス（CREATE INDEX ON users (lower(email))）を作るか、条件側を書き換えます。
• LIKEの前方一致以外。LIKE '%abc%'はB-treeでは絞れません。pg_trgmのGINインデックスを検討します。また、ロケールがC以外のデータベースでは前方一致LIKE 'abc%'でも通常のインデックスが使えないため、text_pattern_opsを指定した専用インデックスが必要です。
• ORDER BY + LIMIT。ORDER BY created_at DESC LIMIT 10は、ソート順に一致するインデックスがあれば上位数件で打ち切れます。しかしWHERE条件の列とORDER BY列が別インデックスに分かれていると、オプティマイザは「大量に絞ってからソート」か「インデックス順に読んで条件で捨てる」の二択になり、後者を選んで遅くなることがあります。複合インデックス（絞り込み列 → ソート列の順）で解決するケースが多いです。
• 選択率が悪い。テーブルの大部分がヒットする条件では、シーケンシャルスキャンのほうが実際に速く、オプティマイザの判断が正しい場合もあります。件数を確認してから疑ってください。

なお、使われていないインデックスの棚卸しも定期的に行います。

SELECT relname, indexrelname, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS idx_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

不要なインデックスはUPDATE時のオーバーヘッドとHOT更新の阻害要因になります。ただしidx_scan = 0でも、統計リセット以降に使われていないだけの可能性や、制約のために必要な場合があるので、削除前に用途を確認します。

work_mem・random_page_cost・パラレル実行が効く場面と、効かない場面

work_memはソートやハッシュ1つあたりに使えるメモリで、接続数×ノード数だけ多重に確保され得ます。全体設定で大きくするとOOMのリスクがあるため、既定は控えめにして、重いバッチのセッションだけ引き上げる運用が安全です。

-- セッション単位・トランザクション単位で引き上げる
SET LOCAL work_mem = '256MB';

random_page_cost（既定4.0）は、ランダムI/Oがシーケンシャルの4倍高いという前提です。SSDやEBSなど実質的な差が小さいストレージでは1.1程度に下げると、インデックススキャンが適切に選ばれるようになります。ただしこれは全クエリに影響するため、変更前後で主要クエリの計画を比較してください。effective_cache_sizeの設定漏れと合わせて、「インデックスが選ばれない」問題の根本原因になりがちな2つです😇

パラレル実行は、大量行を走査して集約するような分析系クエリで効きます。効かないのは、返す行数が少ない検索系（起動コストで負ける）、パラレル非対応の関数や書き込みを含む処理、max_parallel_workers_per_gather（既定2）やワーカープール枯渇時です。EXPLAIN ANALYZEでWorkers Planned: 2 / Workers Launched: 0となっていれば、ワーカーが確保できておらず期待した並列度が出ていません。OLTPワークロードが主体のサーバでは、パラレルが業務クエリのCPUを奪う側面もあるため、上限設定は全体のワークロードを見て決めます。

🔒 詰まる・つながらない：ロックと接続まわり

pg_locksとpg_blocking_pidsでブロック元セッションを特定する手順

ロック待ちの調査は、pg_locksを単体で眺めるよりpg_blocking_pids()で連鎖を辿るほうが速いです。

SELECT a.pid,
       a.wait_event_type,
       a.wait_event,
       pg_blocking_pids(a.pid) AS blocked_by,
       now() - a.query_start   AS waiting_for,
       left(a.query, 100)      AS query
FROM pg_stat_activity a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
ORDER BY waiting_for DESC;

出てきたblocked_byのPIDをpg_stat_activityで引き直し、さらにそのPIDがブロックされていないかを確認して、チェーンの根元に到達します。根元のセッションがidle in transactionであれば、アプリ側でコミット／ロールバックが漏れています。

どのオブジェクトの、どのモードのロックで待っているかはpg_locksで確認します。

SELECT l.pid, l.locktype, l.mode, l.granted,
       coalesce(l.relation::regclass::text, l.transactionid::text) AS target
FROM pg_locks l
WHERE NOT l.granted
ORDER BY l.pid;

locktype = 'transactionid'で待っているのは行ロック競合（同じ行を更新しようとしている）、locktype = 'relation'はテーブルレベルのロック競合で、DDLやVACUUM FULLが絡んでいる可能性が高いです。緊急停止はpg_cancel_backend(pid)（実行中クエリのキャンセル）を先に試し、それでも解消しなければpg_terminate_backend(pid)でセッションを切ります。順番を守るのは、キャンセルのほうが副作用が小さいからです。

DDLが全体を止める理由 — ACCESS EXCLUSIVE ロックとlock_timeoutを併用した安全な流し方

ALTER TABLEは原則としてACCESS EXCLUSIVEロックを取ります。これはSELECTとも競合するロックです。問題は、取得を待っている間もキューの後ろの新規クエリが待たされる点です。長いSELECTが1本走っているだけで、その後ろの全アクセスがDDL待ちで詰まり、一瞬で接続数が枯渇します。「軽いDDLを流したらサービスが止まった」のはほぼこれです😱

対策は「短時間で取れなければ諦める」設計です。

BEGIN;
SET LOCAL lock_timeout = '3s';        -- 3秒で取れなければエラーで抜ける
ALTER TABLE orders ADD COLUMN memo text;
COMMIT;

✅ 失敗したらリトライします。これでキューを長時間ふさぐ事故を防げます。

加えて、DDLの種類ごとの重さを知っておく必要があります。

• テーブル全体の書き換えが起きないもの。ADD COLUMN（デフォルトなし、またはPG11以降の非volatileなデフォルト）、DROP COLUMN、RENAME。ロックは強いが一瞬で終わります。
• 全行スキャンが必要なもの。SET NOT NULL（PG12以降は等価なCHECK制約があればスキップ可）、型変更、ADD CONSTRAINT ... CHECK。NOT VALIDで先に追加し、VALIDATE CONSTRAINT（弱いロック）で後から検証する二段構えにします。
• インデックス作成。CREATE INDEXは書き込みをブロックします。CREATE INDEX CONCURRENTLYを使えば読み書きを止めませんが、トランザクション内では使えず、失敗するとINVALIDなインデックスが残るため後始末が必要です。

接続数の枯渇とidle in transaction — タイムアウト設定とコネクションプーラの役割

FATAL: sorry, too many clients alreadyは結果であって原因ではありません。多くの場合、裏で何かが詰まってセッションが滞留しています。まず接続の内訳を見ます。

SELECT state, count(*), max(now() - state_change) AS max_age
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state ORDER BY count DESC;

idle in transactionが多ければアプリのトランザクション管理、activeが多ければ性能問題、idleが多ければプール設定過大です。

保険として次のタイムアウトを入れておきます。

-- 例：グローバル方針として設定（値は業務要件に合わせる）
ALTER SYSTEM SET statement_timeout = '60s';
ALTER SYSTEM SET lock_timeout = '5s';
ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
ALTER SYSTEM SET idle_session_timeout = '30min';  -- PG14以降

💡 statement_timeoutをグローバルに掛けるとバッチやメンテナンスが落ちるので、ロール単位（ALTER ROLE app SET statement_timeout = ...）で分けるのが現実的です。

PostgreSQLは接続ごとにプロセスを起こすため、接続数の増加はメモリとコンテキストスイッチのコストに直結します。アプリ側が大量の短命接続を張る構成なら、PgBouncer等のプーラを挟んでDBへの実接続を絞るほうが安定します。トランザクションプーリングモードではプリペアドステートメントやセッション変数の扱いに制約が出るため、アプリの使い方と合うかは事前に確認が必要です。RDS ProxyやAurora側の接続管理機能を使う選択肢もあります。

デッドロックのログを読み解き、アクセス順の設計で再発を防ぐ

デッドロックはdeadlock_timeout（既定1秒）経過後の検出処理で見つかり、片方が自動的にエラー（SQLSTATE 40P01）になります。ログには関与したプロセス、待っていたロック、そしてそれぞれのSQLが出力されます。

読むときのポイントは「どのオブジェクトを、どの順番でロックしようとしたか」です。プロセスAが行1→行2、プロセスBが行2→行1の順でロックを取れば、必然的に発生します。したがって根本対策は、ロック取得順をアプリ全体で統一することです。

• 複数行を更新するときは、主キーなど一定のキーでソートしてから更新する。
• 明示的にロックを取るならSELECT ... FOR UPDATE ... ORDER BY idで順序を固定する。
• 親子テーブルを更新する処理は、常に親→子の順に固定する。
• トランザクションを短くする。処理の途中でユーザー入力や外部API呼び出しを挟まない。

ログが足りない場合はlog_lock_waits = on（deadlock_timeoutを超えた待機をログ出力）を有効にしておくと、デッドロックに至らないロング待ちも記録され、傾向が見えます。なお、外部キーがある場合の更新では参照側にFOR KEY SHARE相当のロックが取られるため、外部キーを含む更新順序も設計対象になります。

📊 性能劣化に気づける監視とログ設計

最低限そろえるメトリクス：キャッシュヒット率、コミット/ロールバック、レプリケーション遅延、待機イベント

「遅くなった」という報告に答えるには、比較できる過去のデータが必要です。最低限これだけは時系列で取ります。

観点	取得元	見る理由
キャッシュヒット率	pg_stat_database.blks_hit / blks_read	ワーキングセット増加や計画悪化の兆候
コミット/ロールバック数	pg_stat_database.xact_commit / xact_rollback	スループット、エラー急増の検知
デッドタプル数・最終バキューム	pg_stat_user_tables	肥大化の予兆
XID余裕	age(pg_database.datfrozenxid)	周回停止の予防（必須）
レプリケーション遅延	pg_stat_replication、pg_replication_slots	遅延とWAL滞留
待機イベント分布	pg_stat_activityのサンプリング	ボトルネックがCPU/IO/Lockのどれか
一時ファイル	pg_stat_database.temp_bytes	work_mem不足の検知
デッドロック数	pg_stat_database.deadlocks	設計問題の顕在化

レプリケーション遅延は、バイト差と時間差の両方を見ます。

SELECT client_addr, state, sync_state,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

待機イベントは瞬間値なので、1秒間隔でサンプリングしてwait_event_type別に集計すると、時間帯ごとのボトルネックが見えます。これはAWSのPerformance Insightsが行っていることと本質的に同じです。

pg_stat_statementsで重いSQLを継続的に順位づけする

個別のスロークエリログだけでは、「1回5秒のバッチ」と「10ms×100万回のクエリ」のどちらが本当の負荷かが判断できません。pg_stat_statementsは正規化されたSQLごとに累積値を持つため、総時間で順位づけできます。

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;  -- shared_preload_libraries への追加が必要

SELECT round(total_exec_time::numeric, 0) AS total_ms,
       calls,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       rows,
       shared_blks_hit, shared_blks_read,
       left(query, 100) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

運用のコツは、累積値そのものではなく差分を見ることです。定期的にスナップショットを保存し、前回との差分でランキングを作ると「昨日から急に重くなったSQL」が浮かびます。pg_stat_statements_reset()でリセットする運用もありますが、他の利用者と競合するので基本はスナップショット方式が安全です。

track_io_timing = onにするとblk_read_timeが取れてI/O起因かどうかが判別しやすくなります（計測オーバーヘッドは環境依存なので、本番投入前に確認してください）。pg_stat_statements.max（既定5000程度）を超えるとエントリが追い出されるため、SQLの種類が多い環境では増やす検討をします。

log_min_duration_statementとauto_explainの設定値の考え方

スローログの閾値は「多すぎて読めない」と「重要なものが記録されない」の間を取ります。OLTPなら数百ms〜1秒から始め、出力量を見て調整するのが現実的です。

log_min_duration_statement = 500ms
log_lock_waits = on
log_temp_files = 0          # 一時ファイル生成を全て記録
log_autovacuum_min_duration = 0
log_checkpoints = on
log_line_prefix = '%m [%p] %q%u@%d app=%a '

log_autovacuum_min_duration = 0はVACUUMの実績（回収できたか、スキップされたか、何秒かかったか）が残るため、肥大化調査で役立ちます。

さらに実行計画まで残したい場合はauto_explainです。

shared_preload_libraries = 'pg_stat_statements,auto_explain'
auto_explain.log_min_duration = '3s'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_nested_statements = on
auto_explain.log_timing = off      # 計測負荷を抑える

log_analyze = onは実測値が取れる代わりに計測コストがかかります。log_timing = offにすると行数は取れて時間は取れませんが、負荷は下がります。閾値を高めに設定し、本当に遅いものだけ記録するのが安全な入り方です。「再現しない遅延」の原因を後追いするには、これが最も確実な手段になります。

RDS/AuroraのPerformance InsightsとCloudWatchをどう組み合わせるか

マネージド環境では役割分担を意識します。

• Performance InsightsはDBロード（平均アクティブセッション）を待機イベント別・SQL別に時系列で見るものです。「いつから、何を待って重くなったか」の特定に使い、pg_stat_activityのサンプリングを自前で組む代わりになります。
• CloudWatchメトリクスではCPU、FreeableMemory、ReadIOPS/WriteIOPS、DiskQueueDepth、DatabaseConnections、ReplicaLag、TransactionLogsDiskUsage（WAL滞留＝スロット放置の検知に有用）などインフラ側の指標を見ます。
• CloudWatch LogsにはPostgreSQLのサーバログを集約します。スローログやデッドロック、autovacuumの記録をここに集め、Logs Insightsで検索できるようにしておきます。
• 拡張モニタリングはOSレベルのプロセス単位の情報で、CPU高騰時にどのバックエンドが食っているかの確認に使います。

💡 切り分けの流れとしては、Performance Insightsで「待機イベントがIO系→CloudWatchのIOPSとバーストクレジット枯渇を確認」「Lock系→pg_blocking_pids()で連鎖を追う」「CPU系→SQLと実行計画へ」と進むと迷いません。インスタンスのストレージ性能上限（gp2のバーストやgp3のIOPS設定値）に当たっているだけというケースもあるので、DB内部を掘る前にインフラ側の上限を確認するのは有効です。

💾 壊れたときに戻せるか：バックアップ・リカバリとレプリケーション

論理バックアップ（pg_dump）と物理バックアップ（ベースバックアップ＋WAL）の使い分け

2種類は代替関係ではなく、役割が違います。

論理バックアップ（pg_dump）は、SQL文またはアーカイブ形式でデータを取り出します。

# 圧縮・並列リストア可能な形式で取得
pg_dump -Fc -Z6 -d appdb -f appdb.dump
# 巨大DBはディレクトリ形式＋並列
pg_dump -Fd -j 4 -d appdb -f /backup/appdb_dir

# リストア（並列、対象を絞ることも可能）
pg_restore -d appdb_new -j 4 /backup/appdb_dir
pg_restore -d appdb_new -t orders appdb.dump

特徴は、テーブル単位で戻せる、異なるメジャーバージョンや異なるアーキテクチャへ移せる、一方でサイズに比例して取得・復元時間が長く、任意の時点には戻せない（ダンプ取得時点のみ）ことです。ロールやテーブルスペースといったクラスタ全体のオブジェクトは含まれないため、pg_dumpall -gを併用します。

物理バックアップは、データディレクトリのコピー（ベースバックアップ）とWALアーカイブの組み合わせです。

# ベースバックアップ
pg_basebackup -D /backup/base -Ft -z -Xnone -c fast -P

# WALアーカイブ（アーカイブ先は別デバイス・別リージョンへ）
archive_mode = on
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'
wal_level = replica

⚠️ こちらは大規模DBでも取得が速く、PITRができます。ただし戻せるのはクラスタ単位で、バージョン間の移行はできません。実運用では、pgBackRestやBarmanのような専用ツールで世代管理・圧縮・検証を任せるほうが安全です（自作のarchive_commandは、アーカイブ失敗時にWALが溜まり続けてディスクフルを招く事故が起きやすい）。

日常のDR基盤は物理＋WAL、論理は「特定テーブルの誤更新を戻す」「移行」用途、という組み合わせが基本形です。

PITRの前提条件と、復旧時間を実測しておくリハーサル

PITR（Point-In-Time Recovery）が成立する前提は次のとおりです。

1. 復旧したい時点より前のベースバックアップがある。
2. そのベースバックアップから目標時点までのWALが欠損なく残っている。
3. WALアーカイブがデータ領域と別の故障単位にある。

復旧手順の骨格は、ベースバックアップを展開し、リカバリ設定を書いてrecovery.signalを置いて起動するという流れです（PG12以降、リカバリ設定はpostgresql.confに書きます）。

restore_command = 'cp /archive/%f %p'
recovery_target_time = '2026-09-20 14:35:00+09'
recovery_target_action = 'promote'

ここで押さえておきたいのは、復旧時間（RTO）を実測しておくことです。WALの再適用は基本的に単一プロセスの逐次処理で、蓄積量が多ければ数時間かかることもあります。「バックアップは取れている」と「目標時間内に戻せる」は別の話です。リハーサルでは少なくとも、😨

• 展開＋リカバリの所要時間
• 目標時点の指定が意図どおりに効くか
• アーカイブに欠損がないか
• 復旧後にアプリが接続して正常動作するか

を確認します。あわせてpg_verifybackup（PG13以降）でバックアップの完全性を検証できるようにしておくと、取得そのものの信頼性が上がります。RDS/Auroraでは自動バックアップとPITRが提供されますが、復元は新しいインスタンスの作成になるため、その所要時間と接続先切り替え手順を含めて測っておく必要があります。

ストリーミングレプリケーションの遅延と、hot_standby_feedbackで起きるトレードオフ

ストリーミングレプリケーションでは、スタンバイがWALを受信（write）、ディスク同期（flush）、適用（replay）の3段階を進めます。遅延の場所によって原因が違います。write/flushが遅れるならネットワーク帯域・遅延、あるいはスタンバイのディスク性能。replayだけが遅れるなら、適用は単一プロセスなのでプライマリの並列更新に追いつけていない、もしくはスタンバイ上の長いクエリとの競合です。

スタンバイで参照クエリを流す場合、プライマリのVACUUMが消した行をスタンバイのクエリがまだ見ている、という競合が起きます。これを解決する2つの方向にトレードオフがあります。

• max_standby_streaming_delayを長くする。クエリを守れますが、適用が止まり遅延が伸びます。フェイルオーバー時のデータロス幅（実質的なRPO）も悪化します。既定値のままなら、競合したクエリがERROR: canceling statement due to conflict with recoveryでキャンセルされます。
• hot_standby_feedback = onにする。スタンバイの最古スナップショットをプライマリへ伝え、プライマリ側の回収を抑制します。クエリは守られますが、プライマリのテーブルが肥大化します。スタンバイで長時間クエリを流すと、前章で述べた「VACUUMが効かない」問題を自分で作ることになります😇

どちらを選ぶかは、「スタンバイの参照クエリを落としたくないか」「プライマリの肥大化を許容できるか」の優先度で決めます。hot_standby_feedback = onにする場合は、スタンバイ側にもstatement_timeoutを設定して長時間クエリを抑えるのが実務的な折衷案です。

また、レプリケーションスロットを使うと必要なWALが確実に保持される代わりに、スタンバイ停止時にプライマリのWALが溜まります。max_slot_wal_keep_size（PG13以降）で上限を設けて、プライマリのディスクフルを避ける設計にします（上限を超えるとそのスロットは無効化され、スタンバイは再構築が必要になるという代償があることも理解しておきます）。

フェイルオーバー後に発生しがちな問題（シーケンス、接続先、統計情報）

昇格が成功しても、その後に問題が出ることがあります。

• シーケンス。物理レプリケーションではシーケンスもWAL経由で複製されるため基本的に問題ありませんが、論理レプリケーションではシーケンスの現在値が複製されません（対応状況はバージョンで異なります）。論理レプリケーションで切り替える構成では、setval()による引き上げを切り替え手順に必ず入れます。ID重複は後から気づくと修復が厄介です🫠
• 接続先の切り替え漏れ。アプリ、バッチ、BIツール、監視、ETL。接続元の棚卸しができていないと、一部が旧プライマリを向いたまま残り、最悪スプリットブレインになります。切り替えは接続先の抽象化（DNS、仮想IP、RDSのエンドポイント）で一箇所に集約し、旧プライマリは確実に停止させます。
• 統計情報とキャッシュ。物理レプリケーションではpg_statisticも複製されるので実行計画は維持されますが、共有バッファは空なので昇格直後は一時的にI/Oが増えます。一方、論理バックアップからリストアした環境では統計情報が空のため、ANALYZEを実行するまで実行計画が大きく悪化します。リストア手順の最後に必ずANALYZE（またはvacuumdb --analyze-in-stages -j N）を入れてください。
• pg_stat_statements等の累積値リセット。監視のベースラインが消えるため、切り替え直後の「遅い」判断が難しくなります。

🔄 Oracleから移行した環境で起きる問題

移行直後の「データが合わない」：NULLと空文字、DATE型、識別子の大文字小文字

Oracleから移行した直後に出る問題は、性能より先にデータの意味の違いとして現れます。

空文字とNULLの扱い。OracleではVARCHAR2の空文字がNULLとして扱われますが、PostgreSQLでは空文字とNULLは別の値です。そのため、OracleでIS NULLにヒットしていた行がPostgreSQLではヒットせず、集計件数がずれます。NVLをCOALESCEに置き換えただけでは解決せず、coalesce(nullif(col, ''), 'default')のような扱いや、移行時のデータ変換方針そのものを決める必要があります。

DATE型。OracleのDATEは時刻を含みますが、PostgreSQLのdateは日付のみです。そのままdateにマッピングすると時刻が切り捨てられ、日次の境界処理が変わります。基本的にはtimestamp（タイムゾーンを扱うならtimestamptz）に対応させます。timestamptzは内部的にUTCで保持し、TimeZone設定に応じて表示・入力を変換する型で、timestampはタイムゾーンを持たない型です。この違いを理解せずに混在させると、比較や日付切り出しで微妙なずれが生じます。

💡 識別子の大文字小文字。Oracleは未クォートの識別子を大文字に畳み、PostgreSQLは小文字に畳みます。移行ツールが"EMP_NO"のようにダブルクォート付きの大文字で作ると、以後すべてのSQLでクォートが必要になり、ORMやツールとの相性問題が出ます。移行時にすべて小文字へ正規化しておくのが、後の運用コストを最小にする選択です。

症状別の切り分けと書き換え方は「Oracle から PostgreSQL 移行でハマるポイント」で詳しく解説

ここで挙げたもの以外にも、ROWNUMとLIMIT/ウィンドウ関数、DECODE、階層問い合わせ（CONNECT BY）と再帰CTE、MERGEとUPSERT、暗黙の型変換の差、||とNULLの連結結果、ソート順とロケールの違いなど、移行後に表面化するポイントは多数あります。症状ごとの切り分け手順と具体的な書き換え方はOracle から PostgreSQL 移行でハマるポイント｜症状別の切り分けと対処にまとめているので、移行案件を担当する場合はこちらも合わせて確認してください。

REDO/UNDOとWAL/MVCCの違いが、運用手順にもたらす差

OracleはUNDO表領域に旧バージョンを保持し、REDOログで再実行情報を記録します。PostgreSQLはUNDO領域を持たず、旧バージョンをテーブル内に残すMVCCで、WALがREDOに相当します。この構造差から運用手順が変わります。

• ⚠️ 長時間クエリの失敗の仕方が違う。OracleではSnapshot too old（ORA-01555）でUNDOが上書きされてクエリが失敗しますが、PostgreSQLでは旧バージョンがテーブル内に残るため長時間クエリ自体は成功しやすく、その代償としてテーブルが肥大化します。エラーが出ない代わりに、後で容量問題として跳ね返る点が要注意です。
• 定期メンテナンスの対象が違う。Oracleの自動統計収集やUNDO保持期間の管理に相当する「PostgreSQL固有の必須運用」がVACUUM（とFREEZE）です。UNDOがない代わりに、回収を阻む要因の監視が運用の中心になります。
• ロールバックのコスト。PostgreSQLではロールバックは「そのトランザクションのXIDをabortとして記録する」だけで済むため高速ですが、書いた行はデッドタプルとして残ります。巨大なバッチを失敗させた後にテーブルが膨らむのはこのためです。
• XID周回という時間制限がある。Oracleには対応する概念がありません。前述のage(datfrozenxid)監視は、Oracle経験者が見落としやすい項目です。

ヒント句や自動統計収集に頼れない前提での、性能設計の考え方

PostgreSQL本体にはOracleのようなヒント句がありません（pg_hint_planという拡張はありますが、マネージド環境では使えない場合があります）。またSQL計画の固定（SQL Plan Management相当）も標準機能にはありません。したがって、性能設計は「オプティマイザに正しい情報を与えて、正しい選択をさせる」方向に寄せる必要があります。

具体的には、

1. 統計情報を正しく保つ。autoanalyzeの閾値を大きなテーブルで調整し、大量データ投入直後は明示的にANALYZEを打つ。
2. 見積もり誤差を拡張統計で埋める。列間の相関、式に対する統計を明示的に作る。
3. コストパラメータを環境に合わせる。effective_cache_size、random_page_costをストレージ実態に合わせる。
4. インデックス設計で選択肢を与える。複合インデックスの列順、部分インデックス（WHERE付き）、カバリングインデックス（INCLUDE）で、オプティマイザが選べる良い選択肢を用意する。
5. どうしても制御したい場合はSQLの構造で誘導する。MATERIALIZED付きCTEでの区切り、結合順の明示的な分割、集合演算への書き換えなど。ただし将来のバージョンアップで挙動が変わり得るため、なぜそう書いたかをコメントで残します。

また、autoanalyzeは「テーブル全体の変更量」で起動するため、パーティションの親テーブルや、追記のみのテーブル（PG13以降はautovacuum_vacuum_insert_thresholdで改善）では狙ったタイミングで走らないことがあります。バッチ処理の最後にANALYZEを明示的に組み込むのは、移行環境での定石です。

❓ よくある質問

autovacuumは動いているのにデッドタプルが減らないのはなぜ？

VACUUMは「誰からも見えなくなった行」しか回収できないため、古いスナップショットやXIDを保持する存在が1つでもあると回収が進みません。典型的な原因は、idle in transactionなどの長時間トランザクション、放置されたレプリケーションスロット、COMMIT PREPAREDされていないprepared transaction、hot_standby_feedback = onのスタンバイでの長時間クエリの4つです。pg_stat_activity・pg_replication_slots・pg_prepared_xactsをUNIONして犯人を洗い出し、先にそれを止めてからVACUUMを打つのが正しい順番です。犯人が生きたまま手動VACUUMを走らせても回収できず、I/Oを消費するだけになります。

テーブルが肥大化したらVACUUM FULLを実行すべき？

反射的にVACUUM FULLを選ぶのは避けます。通常のVACUUMはSHARE UPDATE EXCLUSIVEロックで読み書きを止めませんが、VACUUM FULLはACCESS EXCLUSIVEロックで読み書きともに止まり、元サイズ相当の追加ディスクも必要になるため計画停止時の最終手段です。止められない環境ではpg_repack、インデックスのみの膨張ならREINDEX CONCURRENTLY（PG12以降）が選択肢になります。判断の目安はfree_percentが20〜30%超かつ数GB以上で運用に影響が出ている場合で、そもそも回収を阻む原因が残っていれば縮めてもすぐ元に戻ります。

shared_buffersは大きくすれば大きいほど速くなる？

そうとは限りません。PostgreSQLは共有バッファの下にOSのページキャッシュがある二段構えのため、大きくしすぎると同じデータを二重にキャッシュしてメモリを無駄にし、チェックポイント時の書き出し量も増えます。一般的な出発点は物理メモリの25%程度で、あわせてeffective_cache_sizeを実効キャッシュ量の見積もり（50〜75%程度）として設定します。effective_cache_sizeはメモリを確保するパラメータではなくオプティマイザのコスト見積もり用の情報で、小さすぎるとインデックスが有利な場面でシーケンシャルスキャンが選ばれやすくなります。

軽いALTER TABLEでサービス全体が止まるのを防ぐには？

ALTER TABLEは原則ACCESS EXCLUSIVEロックを取り、SELECTとも競合します。厄介なのは取得を待っている間に後続の新規クエリもキューで待たされる点で、長いSELECTが1本走っているだけで全アクセスが詰まり接続数が枯渇します。対策は「短時間で取れなければ諦める」設計で、トランザクション内でSET LOCAL lock_timeout = ’3s’を設定し、失敗したらリトライします。あわせて、全行スキャンが必要なDDLはNOT VALIDで追加して後からVALIDATE CONSTRAINTする、インデックスはCREATE INDEX CONCURRENTLYを使うなど、種類ごとの重さを把握しておきます。

🧪 学習の進め方：手を動かして再現できるようにする

Dockerとpgbenchで肥大化・ロック競合を自分で再現する練習メニュー

障害対応は、初見では確実に遅くなります。落ち着いた環境で「症状を意図的に作る」練習をしておくと、本番での切り分け速度が変わります。ローカルならDockerで十分です。

docker run --name pg -e POSTGRES_PASSWORD=pass -p 5432:5432 -d postgres:17
docker exec -it pg psql -U postgres -c "CREATE DATABASE bench;"
docker exec -it pg pgbench -U postgres -i -s 100 bench   # 初期データ投入
docker exec -it pg pgbench -U postgres -c 20 -j 4 -T 120 -P 5 bench

練習メニューの例です。

1つめ、肥大化を作る。autovacuum = offにしてpgbenchを走らせ、pg_stat_user_tables.n_dead_tupとpg_total_relation_size()の増加を観察する。その後、別セッションでBEGIN; SELECT 1;を張ったままVACUUMしても回収されないことを確認し、COMMITしてから回収されるのを見る。MVCCの「回収できる条件」が体感で理解できます。

2つめ、ロック競合を作る。セッションAでBEGIN; UPDATE pgbench_accounts SET abalance = abalance + 1 WHERE aid = 1;を実行して放置し、セッションBで同じ行を更新。pg_blocking_pids()で特定する練習をする。続いてセッションCからALTER TABLE pgbench_accounts ADD COLUMN memo text;を流し、その後のSELECTまで詰まることを確認する（DDLのキュー詰まりの再現）。

3つめ、デッドロックを作る。2セッションで逆順に2行を更新し、ログに出るデッドロックメッセージを実際に読む。

4つめ、実行計画の変化を作る。ANALYZE前後でEXPLAINの見積もりが変わるのを見る。SET enable_indexscan = offで計画を切り替え、コストの差を確認する。work_memを小さくしてexternal merge Diskを発生させる。

5つめ、XIDの進行を見る。SELECT txid_current();（PG13以降はpg_current_xact_id()）とage(datfrozenxid)の関係を観察する。

いずれも数分で試せて、読むだけの場合とは記憶への残り方が違います。

公式ドキュメントのどの章から読むと運用に直結するか

PostgreSQLの公式ドキュメントは分量がありますが、運用に直結する章は限られています。優先順位をつけるなら次の順です。

1. 「Routine Database Maintenance Tasks」（定常的なデータベース保守作業）。VACUUM、統計情報、XID周回、ログ管理。運用の必読章で、ここを読んでいないとVACUUM系の障害に対応できません。
2. 「Server Configuration」（サーバ設定）。各パラメータの意味と既定値。ネットの記事で見た設定値の根拠をここで確認する癖をつけます。
3. 「Performance Tips」（性能向上のヒント）。EXPLAINの読み方、プランナが使う統計情報、明示的な結合順制御。
4. 「Monitoring Database Activity」（データベース活動状況の監視）。pg_stat_*ビューの各列の意味と、待機イベントの一覧。切り分けの語彙がここで揃います。
5. 「Concurrency Control」（同時実行制御）。トランザクション分離レベル、ロックモードの競合表。DDLを安全に流すための根拠になります。
6. 「Backup and Restore」（バックアップとリストア）。PITRの手順。
7. 「Internals」の関連節とリリースノート。アップグレード時は該当バージョンのリリースノートで挙動変更を確認します。

特に「ロックモードの競合表」と「待機イベント一覧」は、障害対応中に参照するリファレンスとしてブックマークしておく価値があります。

OSS-DBやAWS認定を、実務の理解を深める道具として使う

資格は目的ではなく、体系を強制的に埋めるための道具として使うと効率が良いです。

OSS-DB Silver/GoldはPostgreSQLに特化しており、特にGoldの範囲（運用管理、性能チューニング、障害対応）は本ガイドで扱った内容と重なります。「VACUUMの内部動作」「実行計画」「バックアップとリカバリ」を試験範囲として一通り押さえると、断片的だった知識がつながります🎉

AWS認定（Database系・Solutions Architect系）は、RDS/Auroraの機能（マルチAZ、リードレプリカ、PITR、Performance Insights、パラメータグループ）とアーキテクチャ選択の観点を整理するのに役立ちます。PostgreSQLの内部よりも「どのマネージド機能で要件を満たすか」の視点が身につきます。

学習の進め方として現実的なのは、資格の出題範囲を「自分が説明できないこと」の棚卸しリストとして使うことです。たとえば「hot_standby_feedbackをonにするとどうなるか」「VACUUM FULLとpg_repackの違い」「PITRに必要な前提」を口頭で説明できるか確認し、詰まったところだけ手を動かして検証する。この往復が、試験対策と実務の両方に効きます。

⚠️ なお、試験範囲・出題形式・対象バージョンは改定されます。学習計画を立てる前に、各認定の公式サイトで最新の試験ガイドを確認してください。

---

最後に、運用担当として最初に整えるべきものを3つ挙げます。1つはage(datfrozenxid)とレプリケーションスロットの監視で、放置すると止まる系のリスクを消せます。2つめはpg_stat_statementsとスローログ。遅くなったときに比較できる材料を持つためです。3つめはリストアのリハーサル記録で、戻せることの証明になります。この3つが揃っていれば、それ以外のチューニングは落ち着いてから順番に取り組めます。逆に、この3つが欠けたままの高度なチューニングは、土台のないところに積み上げる作業になりがちです。