「最近、なんかクエリが遅いんだよね」
現場でそんなぼやきを耳にしたら、たいていの場合、僕はまず「テーブルが太ってない？」と聞き返すことにしています。
PostgreSQLを使っていると避けて通れないのが、MVCC（多版型同時実行制御）と「テーブル肥大化（Bloat）」という宿命です。今回は、教科書には載っているけれど、現場でトラブルに直面した時にこそ役立つ「お掃除」の話をしようと思います。
—
なぜPostgreSQLは「太る」のか
PostgreSQLのMVCCは非常にエレガントです。レコードを更新（UPDATE）したり削除（DELETE）したりする際、古いデータは即座に物理削除されません。代わりに「古いデータは過去のものとして残し、新しい行を書き込む」という手法をとります。
これにより、誰かがデータを書き込んでいる最中でも、別の誰かがその瞬間のデータを読み取れるという強力な並行性が保たれるわけです。
しかし、ここで問題が起きます。「もう誰も参照しない古いタプル（Dead Tuple）」がテーブル内にゴミとして残ってしまうのです。これが蓄積すると、テーブルやインデックスが物理的に肥大化します。
想像してみてください。100件の有効なデータを探すために、100万件のゴミを読み飛ばさなきゃいけないとしたら……そりゃあクエリも遅くなりますよね。フルスキャン（Seq Scan）が重くなる最大の原因は、実はこの「ゴミ屋敷」状態なんです。
—
現場でまず確認すべきこと
「うちのDB、太ってるかな？」と思ったら、まずは以下のクエリを叩いてみてください。`pgstattuple` 拡張を使うのが一番確実ですが、とりあえず標準機能だけでサクッと確認するなら、こんなクエリが便利です。
— 現在の統計情報をベースにした概算の肥大化確認
SELECT
relname AS table_name,
n_dead_tup AS dead_tuples,
n_live_tup AS live_tuples,
(n_dead_tup::float / (n_live_tup + n_dead_tup + 1) 100)::numeric(5,2) AS dead_ratio
FROM
pg_stat_user_tables
WHERE
n_live_tup > 1000 — 小さなテーブルは無視
ORDER BY
dead_ratio DESC;
`dead_ratio` が高いテーブルがあったら要注意。そこが性能劣化のボトルネックになっている可能性が高いです。
—
救世主「VACUUM」との付き合い方
このゴミを片付けてくれるのが `VACUUM` です。
- VACUUM: 不要になった領域を「再利用可能」な状態にする。
- VACUUM FULL: テーブルを再構築して、空いた領域をOSに返却する。（ただし、実行中はテーブルに排他ロックがかかるので注意！）
基本的には `autovacuum` が自動で面倒を見てくれます。ですが、「更新頻度が高すぎるテーブル」や「バッチ処理で大量削除した直後」は、自動掃除が追いつかないことがあります。
そんな時は、手動で追い打ちをかけるのがベテランの作法です。
— 特定のテーブルを掃除する
VACUUM VERBOSE ANALYZE my_heavy_table;
ここでポイントなのが、`ANALYZE` を必ずセットで実行すること。VACUUMでゴミを掃除しても、統計情報が古いままではオプティマイザが間違った実行計画を立ててしまいます。
—
実践的なアドバイス：戦うべきは「肥大化」そのもの
「じゃあ、常に `VACUUM FULL` しまくればいいのか？」というと、それは違います。`VACUUM FULL` は強力ですが、テーブルの読み書きを止めてしまうため、本番環境で気軽に撃つのは命取りです。
もし「どうしても物理的にサイズを詰めたい！」という場合は、`pg_repack` のようなツールを検討してください。これを使えば、ロックを最小限に抑えながらインデックスとテーブルをオンラインで再構築できます。
最後に、心に留めておいてほしいこと
1. 「更新」は高くつく: 何も考えずに `UPDATE` を繰り返すと、驚くほど速く太ります。カラムを絞る、あるいは必要な時だけ更新するなど、設計段階で「ゴミの発生量」を意識するだけでもDBの寿命は伸びます。
2. autovacuumを過信しない: 設定はデフォルトのままですか？頻繁に更新されるテーブルがあるなら、そのテーブルだけ `autovacuum_vacuum_scale_factor` を小さく設定するなど、個別のチューニングが必須です。
3. 定期的な観測: 「遅い」と騒ぐ前に、`pg_stat_user_tables` を見て、ゴミが溜まっていないか確認する癖をつけてください。
DBのチューニングは、いわば「整理整頓」です。部屋を綺麗に保つように、データベースも定期的に掃除してあげてください。きっと、速くて安定したレスポンスで応えてくれるはずですよ。
それでは、良いDBライフを！
コメント