PostgreSQLがディスクを食い始めたとき（そしてVACUUMでは不十分な理由）
数年前、クライアントの本番サーバーでパフォーマンスが低下したPostgreSQLインスタンスのトラブルシューティングをしていました。ミリ秒で終わるはずのクエリが遅々として進まない。pg_relation_size()では40 GBのテーブルと表示されているのに、実際の行数から見積もると8 GBほどしかないはずです。残りの32 GBは無駄な重荷——ブロートです。
MySQL、PostgreSQL、MongoDBをさまざまなプロジェクトで使ってきましたが、それぞれに強みがあります。ただ、PostgreSQLのMVCC（Multi-Version Concurrency Control）モデルは最大の特長であると同時に、この問題の根本原因でもあります。UPDATEやDELETEを実行しても、行がその場で上書きされるわけではありません。古いバージョンは「死んだ」とマークされ、新しいバージョンが書き込まれます。特に書き込みの多いテーブルでは、時間が経つにつれてこのデッドタプルが積み重なり、テーブルとインデックスのサイズが膨らんでいきます。
一般的な解決策はVACUUMとAUTOVACUUMです。デッドタプルの領域を回収して再利用可能にしてくれますが、ディスク上の物理ファイルは縮小されません。完全に領域を回収する唯一の方法はVACUUM FULLですが、これはテーブル全体を書き直すため、排他ロックを取得します。つまり、実行中はテーブル全体がオフラインになります。数百万行のある本番データベースでは、数分から数時間のダウンタイムが発生することもあります。
そこでpg_repackの出番です。肥大化したテーブルとインデックスをライブで再構築し、最後に古いバージョンと新しいバージョンを入れ替える際だけ、ごく短時間のロックを取得します。本番環境はずっと稼働したまま。ユーザーは何も気づきません。
修正前にブロートを把握する
何かインストールする前に、本当にブロートの問題があるかどうかを確認しましょう。次のクエリを実行して、データベース全体のテーブルブロートを見積もります：
SELECT
schemaname,
tablename,
pg_size_pretty(pg_relation_size(schemaname || '.' || tablename)) AS table_size,
pg_size_pretty(
pg_relation_size(schemaname || '.' || tablename) -
(reltuples * 8192 / fillfactor::float)::bigint
) AS estimated_bloat
FROM pg_stat_user_tables
JOIN pg_class ON relname = tablename
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(schemaname || '.' || tablename) DESC
LIMIT 20;より正確な方法はpgstattuple拡張機能を使うことです：
CREATE EXTENSION IF NOT EXISTS pgstattuple;
SELECT
table_len,
tuple_count,
dead_tuple_count,
dead_tuple_percent,
free_space,
free_percent
FROM pgstattuple('public.orders');dead_tuple_percentやfree_percentが20〜30%を超えている場合、そのテーブルはリパックの候補です。
pg_repackのインストール
pg_repackはPostgreSQLの拡張機能です。リパック処理中にサーバーサイドのコードを実行します。サーバー側の拡張機能とクライアントのCLIツールの両方をインストールする必要があります。
Ubuntu / Debianの場合
sudo apt update
sudo apt install postgresql-16-repack # 16をお使いのPGバージョンに置き換えてくださいRHEL / CentOS / Rocky Linuxの場合
sudo dnf install pg_repack_16 # PGDGリポジトリからソースからビルド（パッケージが利用できない場合）
git clone https://github.com/reorg/pg_repack.git
cd pg_repack
make
sudo make installインストール後、対象データベースで拡張機能を有効にします：
-- データベースに接続する
CREATE EXTENSION IF NOT EXISTS pg_repack;正しく読み込まれたか確認します：
SELECT * FROM pg_extension WHERE extname = 'pg_repack';pg_repackの設定と実行
pg_repackは、テーブルのシャドウコピーを作成し、コピー中に発生したすべての変更をトリガーを使ってリプレイした後、短時間のACCESS EXCLUSIVEロックでテーブルを入れ替えることで動作します。ほとんどのテーブルで1秒未満です。
単一の肥大化したテーブルをリパックする
pg_repack \
--host=localhost \
--port=5432 \
--username=postgres \
--dbname=mydb \
--table=public.ordersテーブルのインデックスのみをリパックする（テーブルの書き直しなし）
インデックスだけが肥大化している場合もあります。この方法はより高速で、影響も少なくて済みます：
pg_repack \
--host=localhost \
--port=5432 \
--username=postgres \
--dbname=mydb \
--table=public.orders \
--only-indexesデータベース内のすべてのテーブルをリパックする
pg_repack \
--host=localhost \
--port=5432 \
--username=postgres \
--dbname=mydb \
--all--dry-runフラグを使うと、実際には実行せずにpg_repackが何をするかを確認できます：
pg_repack --dbname=mydb --table=public.orders --dry-runリソース使用量のコントロール
負荷の高い本番サーバーでは、pg_repackがI/Oを酷使しないようにしたいものです。2つのフラグが役立ちます：
pg_repack \
--dbname=mydb \
--table=public.orders \
--wait-timeout=10 \
--no-kill-backend--wait-timeout: 競合するロックを待つ秒数（諦めるまで）（デフォルト: 60秒）--no-kill-backend: 他の接続を強制終了しない——待つかアボートするだけ
書き込みトラフィックの多いテーブルには、最もトラフィックが少ない時間帯（通常は深夜3〜5時）にpg_repackを実行し、シェルスクリプトでまとめることを検討してください：
#!/bin/bash
# repack_bloated.sh — オフピーク時にcronで実行する
DB="mydb"
PG_USER="postgres"
TABLES=("public.orders" "public.events" "public.logs")
for TABLE in "${TABLES[@]}"; do
echo "[$(date)] $TABLE をリパック中..."
pg_repack \
--dbname=$DB \
--username=$PG_USER \
--table=$TABLE \
--wait-timeout=30 \
--no-kill-backend
echo "[$(date)] 完了: $TABLE"
done週次メンテナンスのためにcrontabに追加します：
crontab -e
# 毎週日曜日の午前3時に実行
0 3 * * 0 /usr/local/bin/repack_bloated.sh >> /var/log/pg_repack.log 2>&1検証とモニタリング
pg_repackを実行した後、ブロートが実際になくなったことを確認しましょう。実行前後のテーブルサイズを比較します：
SELECT
pg_size_pretty(pg_relation_size('public.orders')) AS table_size,
pg_size_pretty(pg_indexes_size('public.orders')) AS indexes_size,
pg_size_pretty(pg_total_relation_size('public.orders')) AS total_size;pgstattuple を再度実行して、デッドタプルが消えたことを確認します：
SELECT
dead_tuple_count,
dead_tuple_percent,
free_percent
FROM pgstattuple('public.orders');適切にリパックされたテーブルではdead_tuple_count = 0、free_percentは0に近い値（または設定したfillfactorに近い値）が表示されるはずです。
時間経過によるブロートの増加を監視する
ブロートはまた増えてきます——これは正常なことです。目標は管理可能な状態に保つことです。週次で実行したり、Grafana/Datadogに連携したりできるモニタリングクエリを設定しましょう：
SELECT
schemaname || '.' || relname AS table_name,
n_dead_tup,
n_live_tup,
ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
last_autovacuum,
last_vacuum
FROM pg_stat_user_tables
WHERE n_live_tup > 10000
ORDER BY dead_pct DESC NULLS LAST
LIMIT 20;定期的にautovacuumが実行されているにもかかわらず、特定のテーブルでdead_pctが上がり続ける場合、通常はautovacuumが書き込み量に追いつけていないことを意味します。テーブルごとに設定をチューニングできます：
ALTER TABLE public.orders SET (
autovacuum_vacuum_scale_factor = 0.01, -- デッドタプルが1%に達したらトリガー
autovacuum_analyze_scale_factor = 0.005
);本番ログで確認すべきこと
pg_repackは進捗をstdoutに記録します。正常に完了したことを確認するための主要な出力行：
INFO: repacking table "public"."orders"
INFO: repacking index "orders_pkey"
INFO: repacking index "orders_user_id_idx"
INFO: applying last changes on the target table
INFO: cleaning up temprary objectsERROR: query was canceled due to conflict with recoveryが表示される場合、スタンバイレプリカ上で実行しています——pg_repackはプライマリでのみ動作します。ERROR: lock wait timeout exceededが表示される場合は、--wait-timeoutを増やすか、トラフィックが少ない時間帯を待ちましょう。
苦労して学んだいくつかのこと
pg_repackは、再構築中にテーブルの完全なコピーを保持するための十分な空きディスク領域が必要です。40 GBのordersテーブルをリパックする場合、少なくとも40 GBの空き容量が必要です。開始前に確認しましょう：
df -h /var/lib/postgresql/また、pg_repackはコピーフェーズ中の変更を追跡するために、ソーステーブルに一時的なトリガーをインストールします。これらのトリガーは若干の書き込みオーバーヘッドをもたらします。非常にスループットの高いテーブル（1分あたり数百万回の書き込み）では、まずステージング環境でテストして、オーバーヘッドが許容範囲内であることを確認してください。
最後に——pg_repackはプライマリキーのないテーブルをリパックできません。PKのないHEAPテーブルがある場合は、先にPKを追加するか、代替手段としてCLUSTERを使用してください（こちらはロックを取得しますが、メンテナンスウィンドウ中であれば許容できるかもしれません）。
週次のpg_repack実行、書き込みの多いテーブルごとに適切にチューニングされたautovacuum、そしてブロートモニタリングクエリの組み合わせ——これが複数の本番PostgreSQL環境で実績のある構成です。派手ではありませんが、クエリを高速に保ち、ディスクコストを予測可能にするための運用習慣です。
