PostgreSQLパフォーマンスチューニング: 10TBデータベースのクエリ最適化で学んだこと

去年の夏、私が担当しているSaaSプロダクトのPostgreSQLデータベースが10TBを超えた。それ自体は想定内だったんだけど、そのタイミングで突然いくつかのクエリが10秒以上かかるようになって、ユーザーからのクレームが一気に増えた。チームは私を含めて4人のバックエンドエンジニア。専任のDBAはいない。「とりあえず自分たちでなんとかするか」というやつだ。

この記事は、その2ヶ月間で実際に試して効いたこと、全然効かなかったこと、そして「なんでこんな動きするんだ」と深夜に頭を抱えたことをそのまま書いたものだ。PostgreSQL 15.3、AWS RDS on db.r6g.4xlarge、読み取り多め（書き込み:読み取り = 約1:8）という構成。

クエリプランナーが明らかにおかしな判断をしていた理由

最初に引っかかったのは、ある集計クエリだった。EXPLAIN ANALYZEで見ると、インデックスがちゃんと存在しているのにSeq Scanを選んでいる。「え、なんで？」となって調べ始めたのが泥沼への入口だった。

原因はテーブル統計情報の陳腐化だった。ANALYZEを最後に実行したのが3週間前で、その間にデータが約40%増加していた。プランナーは古い統計に基づいてコストを計算しているので、インデックススキャンよりSeq Scanの方が安いという誤った結論を出していた。

-- まずこれで統計の状態を確認した
SELECT schemaname, tablename, last_analyze, last_autoanalyze, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE n_live_tup > 1000000
ORDER BY last_analyze ASC NULLS FIRST;

autovacuumは動いていたんだけど、デフォルトの設定ではデータ増加のペースに追いつけていなかった。autovacuum_analyze_scale_factorがデフォルト0.2のままだったので、テーブルの20%が変化するまでANALYZEが走らない。10億行テーブルだと、2億行変わらないと統計が更新されない計算になる。

対処としてまずは手動でANALYZEを走らせたら、問題のクエリが3.2秒から0.08秒に落ちた。正直、こんなに単純な話だとは思っていなかった。もっと深いところに原因があると思い込んでいたので、拍子抜けする気持ち半分、「もっと早く気づけよ」という自己嫌悪半分。

その後、大きいテーブルには個別にautovacuum_analyze_scale_factor = 0.01を設定した。テーブルレベルのstorageパラメータで上書きできる。

pg_stat_statementsで犯人を見つけるのに3日かかった

統計情報の問題は解決したけど、それだけじゃなかった。全体的なクエリレイテンシは改善されたものの、ピーク時間帯のp99が依然として高い。

pg_stat_statementsは最初から有効にしていたんだけど、ちゃんと活用できていなかった。見るべき数字が多すぎて何に注目すればいいか分からなかった。3日間いろんな角度から見た結果、自分の中で「まずこれを見る」という順番ができた。

-- 総実行時間が多い順に並べる（呼び出し頻度ではなく）
SELECT
  query,
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round(stddev_exec_time::numeric, 2) AS stddev_ms,
  rows
FROM pg_stat_statements
WHERE calls > 100
ORDER BY total_exec_time DESC
LIMIT 20;

mean_exec_timeだけじゃなくてstddev_exec_timeも見るのが重要だった。平均は低いのに標準偏差が異常に大きいクエリが2本あって、そこに問題が隠れていた。条件によってはインデックスを使ったり使わなかったりするタイプのクエリで、「データ次第で実行計画が変わる」やつだった。

一番驚いたのは、ページネーション用のOFFSETクエリが問題の根源だったこと。OFFSET 50000とかになると、PostgreSQLは最初の50000行をスキャンして捨てるという動きをする。これ、ずっと知識としては知っていたんだけど、実際に自分のシステムでここまでボトルネックになるとは思っていなかった。

カーソルベースのページネーション（keyset pagination）に切り替えて、そのクエリは解決。ただし既存のAPIの仕様変更が必要になったので、フロントエンドチームとの調整に1週間かかった。こういう「技術的には明らかな答えがあるのに、調整コストが高い」という状況、データベースのチューニングあるあるだと思う。

インデックス設計を一から見直したら半分以上は不要だった

ここが本当に時間がかかった部分だ——というか、今でもまだ完全に終わっていない。

既存のインデックスが130本あった。4年分の「とりあえずインデックス貼れば速くなる」の積み重ねだ。まず未使用インデックスを洗い出した。

SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexname NOT LIKE '%pkey%'
  AND indexname NOT LIKE '%unique%'
ORDER BY pg_relation_size(indexrelid) DESC;

統計をリセットしてから2週間後に確認したら、idx_scan = 0のインデックスが41本あった。合計で約180GBのストレージ。そして書き込みのたびにこの41本が更新されていた。

全部を一気に消すのは怖かったので（ちゃんと理由があって作ったやつが混じっているかもしれないし）、月に10本ずつ消すことにした。2ヶ月で20本消した段階で書き込みのスループットが体感で分かるくらい上がった。

逆に、足りていなかったインデックスもあった。複合インデックスの列の順序が間違っていたケースが4件。WHERE user_id = ? AND created_at > ?というクエリに対して(created_at, user_id)という順序でインデックスを貼っていた。等値条件の列を先にするのが原則だけど、これが逆になっていた。

正直、インデックスの列順序は「分かっている」つもりだったのに実際には間違えていた。ちゃんと机上で考えると当たり前のことが、実際のコードレビューの流れの中だと見落とす。これはチームのコードレビューチェックリストに追加した。

パーティショニングで解決したこと、そうでなかったこと

10TBのうち最大のテーブルは単体で3.8TB。イベントログ系のデータで、created_atによる範囲検索が多い。「レンジパーティショニングを入れれば速くなるはず」と思って、これが一番時間を溶かした作業だった。

結論から言うと、パーティショニングで改善したのは古いデータの削除とアーカイブだけだった。クエリ速度への影響は、正直期待していたほどではなかった。

理由を調べていくと、パーティションプルーニングが機能するためにはWHERE句にパーティションキーが含まれている必要があって、私たちのクエリの半分はuser_idやstatus_idで検索していた。パーティションキー（created_at）を使っていないクエリは全パーティションをスキャンしてしまう。これ、ドキュメントを読めば分かる話なんだけど、実際にやってみるまで甘く見ていた。

移行作業も大変だった。pg_partman（PostgreSQL 10+対応のパーティション管理拡張）を使ったけど、本番に適用するためのダウンタイムゼロ移行の手順を組むのに相当な時間がかかった。pg_repackと組み合わせてロックを最小化しながらやったんだけど、金曜の午後に本番に投入したら予想外のロック競合が起きてヒヤリとした。作業は日曜の深夜にやるべきだった。次回への教訓。

パーティショニングが本当に効いたのは月次でパーティションをDETACHしてS3にアーカイブする運用を組んだとき。3ヶ月で約800GBのデータをアーカイブできて、その後のクエリ速度はむしろこっちの効果が大きかった。

設定値で実際に効果があったもの

postgresql.confの調整は最後にやった。「まずクエリとインデックスを直してから設定を触る」という順序は正しかったと思う。設定でごまかしても根本原因は変わらない。

ただ、いくつかは明確に効いた。

work_memは128MBから512MBに上げた。複雑なソートやハッシュ結合がディスクにスピルするのを減らすため。ただしこれ、接続数×work_memがメモリに乗るので雑に上げると危ない。私たちはPgBouncerでコネクションプーリングしていて、実際の同時クエリ数が把握できていたから上げられた。接続数の把握なしにwork_memを触るのは本当にやめた方がいい。

effective_cache_sizeはRAMの75%に設定した（デフォルトは4GB）。これはプランナーへのヒントであって実際にメモリを確保するわけじゃないけど、プランナーの判断が改善された。

random_page_costはSSDなので4.0から1.1に下げた。HDDを前提とした4.0のデフォルト値はSSD環境でインデックススキャンが過小評価される原因になる。これはRDSの公式ドキュメントでも推奨されている変更なんだけど、なぜかデフォルトのままになっていた。

あと、parallel_tuple_costとmax_parallel_workers_per_gatherの調整で大きいクエリのパラレルクエリが効くようになった。正直、ここは試行錯誤で追い込んだのでベストな値かどうか分からない。私たちの環境ではmax_parallel_workers_per_gather = 4が効果的だったけど、ワークロードによって全然違うと思う。

2ヶ月間の作業全体を振り返ると、効果が高かった順に並べるとこうなる: 統計情報の管理を正しくする → 不要インデックスを削除する → クエリそのものを直す → インデックス設計を見直す → 設定値を調整する。パーティショニングは「クエリ速度」ではなく「データ管理」の問題として位置づけた方がいい、というのが今の自分の考えだ。

一つだけ本当のことを言うと、一番効果があったのは「EXPLAIN ANALYZEをちゃんと読む時間を確保した」ことだった。日々の開発タスクに追われているとスキップしがちな作業だけど、ここに投資した時間は確実に回収できた。pgMustardというEXPLAIN ANALYZEのビジュアライザーも途中から使い始めて、これはチームの全員にとって読みやすくて良かった。