Tech Learning Daily — UPDATE しただけなのに、なぜ Postgres のテーブルはどんどん太っていくのか？

DB 🌿 基礎 ⏱ 約 7 分

UPDATE しただけなのに、なぜ Postgres のテーブルはどんどん太っていくのか？

DELETE FROM したのに \dt+ で見たテーブルサイズが全然減らない——そんな経験はないだろうか。逆に、大量の一括 UPDATE を回した翌日にテーブルが妙に重くなっていることもある。PostgreSQL が行を書き換えたり消したりするとき、実際にディスク上で起きているのは、思っているような「その場での上書き」ではない。

🎯 3 行まとめ

PostgreSQL の UPDATE/DELETE は既存の行を書き換えず、新しい行バージョンを追加し古いバージョンに「無効」の印をつけるだけ。これは MVCC という仕組みで、読み取りと書き込みが互いをブロックしないための設計だ。
無効になった古い行バージョン（dead tuple）は自動では消えず、VACUUM（多くは autovacuum）が後から回収する。既定では更新・削除件数が「50 行＋テーブル行数の 20%」を超えると autovacuum が動く。
通常の VACUUM は回収した空間をテーブル内で再利用可能にするだけで、OS にディスクを返さない。ファイルサイズそのものを縮めるには VACUUM FULL が要るが、これはテーブルをロックして読み書きを止める。

なぜ「上書き」ではなく「追加」なのか

PostgreSQL が UPDATE/DELETE を実行するとき、素朴には「該当行を書き換える・消す」ように見える。しかし実際にディスク上で起きているのは追記だ。この仕組みは MVCC（Multi-Version Concurrency Control、複数バージョンによる同時実行制御）と呼ばれ、各行には見えない管理列 xmin（その行バージョンを作ったトランザクション ID）と xmax（削除・更新したトランザクション ID。まだ有効なら 0）が付いている。INSERT は xmin だけをセットして行を作る。UPDATE は元の行はそのままに新しい内容の行バージョンをもう 1 つ追加してそこに新しい xmin を刻み、同時に元の行の xmax にこの更新を行ったトランザクション ID を書き込んで「この時点から無効」の印をつける。DELETE も同様に xmax を立てるだけで、その場では行を物理的に消さない。

なぜこんな回りくどいことをするのか。理由は「読み取りが書き込みをブロックせず、書き込みが読み取りをブロックしない」ようにするためだ。あるトランザクションは自分が読み取りを始めた瞬間のスナップショットを基準に、それより後に作られた行（xmin が未来）は見えないものとして無視し、それより前に削除された行（xmax が過去）も無視する。この判定だけで「読み取り中に他のトランザクションが書き換えた行の、中途半端な状態」に出くわす事故を防げる。行を直接上書きしてしまうと、読み取り中のトランザクションは「今まさに書き換えられている途中の行」を避けるためにロック待ちを強いられることになる。

UPDATE 前          UPDATE 後
┌─────────────┐  ┌─────────────┐
│xmin:100 v:"A"│  │xmin:100 v:"A"│(dead)
│xmax:0        │  │xmax:105      │
└─────────────┘  ├─────────────┤
                  │xmin:105 v:"B"│(live)
                  │xmax:0        │
                  └─────────────┘

🍱 たとえるなら

図書館の改訂版棚を思い浮かべてほしい。図書館は同じ本の内容を紙の上で書き換えたりしない。改訂版が出たら新しい版を棚に追加し、旧版の背表紙に「貸出停止」のシールを貼るだけで、その場では回収しない。すでに旧版を借りて読んでいる利用者がいるかもしれないからだ。閉館後、誰にも借りられていない旧版だけをまとめて回収して棚の空きスペースに戻すのが夜間清掃係の仕事であり、彼らは毎晩ではなく「回収待ちの本が一定量たまったら」出動する。

消えたはずの行はどこへ行くのか

UPDATE/DELETE によって「もう誰からも見えない」と確定した古い行バージョンを dead tuple（デッドタプル）と呼ぶ。PostgreSQL はこれを即座には掃除せず、VACUUM というメンテナンスコマンドが後から回収する。多くの環境ではこれを autovacuum が自動的に担っており、あるテーブルの更新・削除件数が「50 行＋そのテーブルの行数の 20%」を超えると自動的に起動する（autovacuum_vacuum_threshold の既定値 50 と autovacuum_vacuum_scale_factor の既定値 0.2 による計算式）。このしきい値を大きくすると起動頻度は下がるが dead tuple がテーブル内に溜まりやすくなり、小さくすると頻繁に掃除される代わりに VACUUM 自体の I/O 負荷が増える。

ただし普通の VACUUM が回収した領域は OS には返らない。回収した空間は「そのテーブル内で次の INSERT/UPDATE が使い回せる空き領域」としてマークされるだけで、ファイルサイズ自体は縮まない。ディスク上のファイルを実際に小さくするには VACUUM FULL が必要で、これはテーブル全体を新しいファイルへ丸ごと書き直す処理のため ACCESS EXCLUSIVE ロックを取り、実行中はそのテーブルへの読み書きが全部止まる。一方、普通の VACUUM（autovacuum を含む）はロックを取らず通常の読み書きと並行して走る。テーブルが太ったなら毎回 VACUUM FULL で縮めればいいように思えるかもしれない。しかし実際には VACUUM FULL は本番のトラフィックをロックで止めてしまうため、定常運用では普通の VACUUM に空き領域の使い回しを任せ、ファイルサイズが「使用量とほぼ釣り合う一定の大きさ」に落ち着くのを待つのが基本になる。

# 特定テーブルだけ autovacuum を積極的にする例
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.05
);

放置するとどうなるのか

dead tuple の回収が追いつかないと、テーブルは「生きている行の数」以上にファイルサイズが膨張していく現象（bloat）が起きる。インデックスも古いエントリを保持し続けるため、Seq Scan でも Index Scan でも余計なページを読む分だけ遅くなる。特に大量行の一括 UPDATE や一括インポート後の一括更新は短時間に大量の dead tuple を生むため、autovacuum の通常の巡回間隔に対して bloat が急激に積み上がりやすい。

もう一つ見落とされがちなのが トランザクション ID 周回（wraparound）の問題だ。xmin/xmax に使うトランザクション ID は 32 ビットで循環する数値で、上限に達すると値が巻き戻る。ある行の xmin が「未来の ID に見えてしまう」ほど古いまま放置されると、その行が突然すべて不可視になるという致命的な事故につながりかねない。これを防ぐため VACUUM は古い行に特別な「常に最古扱い」の ID を焼き込んで無効化する freeze 処理も兼ねており、autovacuum_freeze_max_age（既定 2 億トランザクション）を超えたテーブルは通常のしきい値を無視して強制的に VACUUM される。

💼 実務でどう出会うか

大量の UPDATE/DELETE を伴うバッチ処理の直後に DB が妙に遅くなったら、pg_stat_user_tables の n_dead_tup/n_live_tup や last_autovacuum を見て、autovacuum が実際に走っているか、bloat が積み上がっていないかを確認するのが定石だ。マイグレーションで大量行を更新する前後に手動で VACUUM (ANALYZE) を挟む、対象テーブルだけ autovacuum_vacuum_scale_factor を下げて掃除の頻度を上げるといった調整が要る場面もある。

⌨️ 手を動かす（5 分）

行が「上書き」ではなく新しいバージョンとして追加されること、そして VACUUM が dead tuple を回収する様子を、ローカルの Docker で確かめる。

docker run -d --rm --name pgdemo -e POSTGRES_PASSWORD=pass -p 5433:5432 postgres:16
until docker exec pgdemo pg_isready -U postgres >/dev/null 2>&1; do sleep 1; done

docker exec pgdemo psql -U postgres -c "
CREATE TABLE t (id int primary key, v text);
INSERT INTO t VALUES (1, 'A');
SELECT xmin, xmax, v FROM t;"

docker exec pgdemo psql -U postgres -c "UPDATE t SET v = 'B' WHERE id = 1;"
docker exec pgdemo psql -U postgres -c "UPDATE t SET v = 'C' WHERE id = 1;"
docker exec pgdemo psql -U postgres -c "SELECT xmin, xmax, v FROM t;"

docker exec pgdemo psql -U postgres -c "VACUUM VERBOSE t;"

docker stop pgdemo

最初の SELECT では xmin が INSERT のトランザクション ID になっている。2 回の UPDATE のあとに見る xmin は最後の UPDATE のトランザクション ID に変わっており、行が上書きではなく新しいバージョンとして作られたことがわかる。VACUUM VERBOSE の出力には「removed ... dead row versions」といった行が現れ、直前の 2 回の UPDATE で生まれた不要な旧バージョンがちょうど回収されたことを確認できる。

🙅 よくある誤解

DELETE すればすぐディスク使用量が減る — 実際には行は dead tuple になるだけで、空間はテーブル内で再利用されるように印がつくのみ。ファイルサイズはその場では縮まず、物理的に縮小するには VACUUM FULL が要る。
VACUUM はテーブルをロックして止める重い処理だ — 通常の VACUUM（autovacuum を含む）は ACCESS EXCLUSIVE ロックを取らず、読み書きと並行して動く。ロックを取って本番影響が出るのは VACUUM FULL のほうだ。
VACUUM をサボっても遅くなるだけで実害はない — bloat による速度低下に加えて、長期間 VACUUM されないテーブルはトランザクション ID 周回のリスクが高まり、最悪の場合データベースへの書き込みが強制的に止まる。

📖 用語ミニ辞典

MVCC: 複数バージョンの行を使って読み取りと書き込みが互いをブロックしないようにする同時実行制御方式。
xmin / xmax: 行バージョンごとに持つ隠し列。作成したトランザクション ID(xmin)と削除・更新したトランザクション ID(xmax、無効なら0)を記録する。
dead tuple: どのトランザクションからも見えなくなった古い行バージョン。VACUUM が回収するまでディスク上に残る。
autovacuum: 更新・削除件数がしきい値を超えたテーブルを自動的に VACUUM するバックグラウンドプロセス。
トランザクションID周回: 32bitのトランザクションIDが上限で巻き戻る現象。放置すると古い行が突然不可視になりうる。

🔗 もっと深く

PostgreSQL: Documentation — 13.1. Introduction (MVCC) — 読み取りが書き込みをブロックしない設計思想を説明する一次資料。
PostgreSQL: Documentation — 24.1. Routine Vacuuming — dead tuple の回収・autovacuum のしきい値計算式・トランザクションID周回の仕組みを解説する公式ドキュメント。
PostgreSQL: Documentation — VACUUM — 通常の VACUUM と VACUUM FULL のロック挙動の違いを定義したコマンドリファレンス。
Every UPDATE Leaves a Ghost: MVCC, Bloat, and VACUUM in PostgreSQL — PlanetScale — xmin/xmax と bloat の関係を実例つきで平易に解説したエンジニアリングブログ。