Posted inDatabase

PostgreSQLがディスクを圧迫？Bloat（肥大化）の解消とAutovacuum最適化の秘策

Database tutorial - IT technology blog
Database tutorial - IT technology blog

5分でできるディスクの確認と「応急処置」

深夜にサーバーのディスク使用率が90〜95%に達したという警告を受け取ったとしましょう。実際に確認してみると、データ（行）自体は約20GBしかないのに、ディスク上のデータベースファイルが100GBにも膨れ上がっています。これは典型的なBloat（肥大化）の兆候です。焦ってすぐに VACUUM FULL を実行してはいけません。このコマンドはテーブル全体をロックするため、アプリケーションからのすべてのクエリが停止してしまいます。

まずは、以下のSQL文を使用して、どのテーブルに最も多くの「ゴミ」（デッドタプル）が含まれているかを特定することから始めましょう。

SELECT 
    relname AS table_name, 
    n_dead_tup AS dead_tuples, 
    last_autovacuum, 
    last_vacuum 
FROM pg_stat_user_tables 
ORDER BY n_dead_tup DESC;

もし、あるテーブルに数百万もの dead_tuples があり、last_autovacuum の最終実行日が「先週」などになっている場合、それが原因です。システムを停止させずに一時的に対処するには、以下のコマンドを実行します。

VACUUM (ANALYZE) your_table_name;

注意：このコマンドはテーブル内の新しいデータのために空きスペースを整理するだけで、OSに対してすぐにディスク容量を返却するわけではありません。

データを削除してもディスク容量が減らないのはなぜか？

MVCC（多版型同時実行制御）はPostgreSQLの核心となる仕組みですが、同時にBloatの原因でもあります。行を UPDATE する際、Postgresは元の行を直接書き換えるのではなく、古い行を「期限切れ」（デッドタプル）としてマークし、新しい行を挿入します。DELETE も同様で、物理的にすぐに削除するのではなく、削除フラグを立てるだけです。

この手法により、他のトランザクションは古いデータを引き続き参照でき、一貫性が保たれます。しかし、ゴミの生成速度がクリーニング速度を上回ると、Bloatが発生します。データファイルは無駄な空き領域で膨れ上がり、データベースは実際のデータを探すために「ゴミの山」をスキャンしなければならず、結果として SELECT 文が著しく低速化します。

実体験：デフォルトのAutovacuumが「消極的」すぎる場合

私は以前、3,000 TPS（Transactions Per Second）という高頻度のログシステムを扱ったことがあります。Postgresには Autovacuum が備わっていますが、デフォルト設定は安全性を重視しすぎています。CPU負荷を抑えるために動作が遅く、すぐに停止してしまいます。その結果、60GBのテーブルのうち40GBがBloatという事態に陥りました。

書き込み強度の高いシステムやAWS RDSでのデータベース管理においては、デフォルト値のままにしてはいけません。postgresql.conf を調整して、Autovacuumがより積極的に動作するようにしましょう。

効率的なクリーニングのためのAutovacuum設定

以下は、私がよく優先的にチューニングするパラメータです：

# クリーニング作業用のメモリを増やす（例：16GB RAMのサーバーで512MB）
maintenance_work_mem = 512MB 

# 同時に実行されるワーカー数を増やす
autovacuum_max_workers = 5 

# データの5%が変更されたらクリーニングを開始（デフォルトの20%は高すぎる）
autovacuum_vacuum_scale_factor = 0.05 

# ゴミ掃除を速めるためにコスト制限を緩和する
autovacuum_vacuum_cost_limit = 1000

保存後、以下のコマンドで新しい設定を適用します：

SELECT pg_reload_conf();

pg_repackでダウンタイムなしに容量を回収する

通常、VACUUM はそのテーブル内での再利用のためにゴミをまとめるだけです。OSに容量を完全に返却するには VACUUM FULL が必要ですが、前述の通りテーブルを長時間ロックします。200GBのテーブルであれば、システムが数時間「仮死状態」になる可能性があります。このような大規模なデータセットには、テーブルパーティショニングの導入を検討するのも一つの手です。

最適な解決策は pg_repack です。これは長時間ロックをかけずにテーブルとインデックスを再構築できるエクステンションです。一時テーブルを作成してデータをコピーし、最後に名前を瞬時にスワップします。

pg_repackのインストール方法 (Ubuntu/Debian)

sudo apt-get install postgresql-15-repack
# その後、データベースに接続してエクステンションを作成
CREATE EXTENSION pg_repack;

特定のテーブルに対してクリーニングを実行するには：

pg_repack -d your_db_name -t your_table_name

pg_repack の実行中も、アプリケーションは通常通り INSERT や UPDATE を行えます。これは24時間365日の稼働が求められる本番環境における救世主です。

インデックスの肥大化（Index Bloat）も忘れずに

テーブルだけでなく、インデックスも肥大化します。実際、インデックスが実際のデータサイズの3倍に膨れ上がることもあります。安全にインデックスの肥大化を解消する方法は REINDEX CONCURRENTLY を使うことです：

REINDEX INDEX CONCURRENTLY idx_customer_email;

Share:
Tags: