PostgreSQLのUPDATEは上書きしない：MVCCと行バージョンをpsqlで見る

UPDATE しかしていないのに、なぜテーブルは太るのか

PostgreSQL を運用していると、「DELETE はほとんどしていないのにテーブルの物理サイズが増え続ける」「夜間バッチで大量 UPDATE が走っているのに、別画面の SELECT は思ったほど待たされない」という現象に出会います。

この不思議は、PostgreSQL の UPDATE を「今ある行の値を書き換える操作」だと思っていると説明しにくくなります。入口で先に図にすると、実際の動きは次のようになります。

PostgreSQL の UPDATE は上書きではなく追記です。新しい行バージョンを追加し、古い行バージョンには「このトランザクションで見えなくなった」という死亡印を付けます。DELETE も物理的にその場で消すのではなく、対象の行バージョンに死亡印を付ける操作です。

この記事では、この一点から実務でよく見る挙動をつなげます。SELECT / UPDATE など SQL の基本操作そのものを確認したい場合は、先に PostgreSQLと向き合うための 現場で使えるデータベース操作・SQLノート を見ておくと、以降の psql ログを追いやすくなります。

行バージョンを見分ける xmin と xmax

PostgreSQL は MVCC、つまり Multi-Version Concurrency Control という仕組みで同時実行を制御します。日本語にすると「複数バージョンを使った同時実行制御」です。ひとつの論理的な行に対して、必要に応じて複数の行バージョンを持ち、読み取り側は自分のスナップショットから見えるバージョンだけを読みます。

スナップショットとは、ある時点でどのトランザクションが完了済みで、どのトランザクションがまだ実行中だったかを判断するための見取り図です。どの範囲を一つのスナップショットで通すかは、トランザクション分離レベルという設定で決まります。既定の READ COMMITTED では SQL 文ごとに新しいスナップショットを取り直すため、実行中に別のトランザクションが確定した変更は次の文から見えます。一段上の REPEATABLE READ にすると、トランザクションが最初に読んだ時点のスナップショットが最後まで固定され、同じ SELECT を何度実行しても結果が変わりません。この記事の後半では、この固定が効く様子も実際に見ます。

行バージョンには、通常の id や name のような列とは別に、PostgreSQL が内部的に持つシステム列があります。この記事で見るのは次の 3 つです。

トランザクション ID は、PostgreSQL が書き込みトランザクションを識別するために付ける番号です。記事中では XID とも書きます。xmin は「この XID が作った」、xmax は「この XID が死亡印を付けた」と読むと理解しやすいです。

正確には xmax は行ロックでも使われるため、あらゆる場面で単純な削除印だけを意味するわけではありません。ただし、この記事のように UPDATE / DELETE の流れを見る範囲では、「古い行バージョンに死亡印が入る」と捉えると全体像をつかめます。

psql で行バージョンを直接見る

ここからは手を動かして確認します。使い捨ての PostgreSQL 17 環境を Docker で立ち上げるなら、次のコマンドで十分です。

docker run --rm -d --name pg17 -e POSTGRES_PASSWORD=pass -p 5432:5432 postgres:17
docker exec -it pg17 psql -U postgres

作業が終わったら、別のシェルから docker stop pg17 で止めればコンテナは削除されます。以下の xmin や xmax の実数値、ctid、テーブルサイズは環境や実行順によって変わります。見るべきところは、値そのものではなく変化の方向です。

なお、ここで見る仕組みは PostgreSQL の中核なので、手元の環境が 13 や 14 でも挙動は変わりません。バージョンを問わず、同じ SQL で同じ変化を確認できます。

実験1: UPDATE のたびに ctid が動く

まずは 1 行だけのテーブルを作ります。xmin、xmax、ctid は明示的に SELECT すると見ることができます。

postgres=# DROP TABLE IF EXISTS mvcc_item;
NOTICE:  table "mvcc_item" does not exist, skipping
DROP TABLE
postgres=# CREATE TABLE mvcc_item (
postgres(#   id integer PRIMARY KEY,
postgres(#   name text NOT NULL,
postgres(#   point integer NOT NULL
postgres(# );
CREATE TABLE
postgres=# INSERT INTO mvcc_item VALUES (1, 'apple', 100);
INSERT 0 1
postgres=# SELECT xmin, xmax, ctid, id, name, point FROM mvcc_item;
 xmin | xmax | ctid  | id | name  | point
------+------+-------+----+-------+-------
  737 |    0 | (0,1) |  1 | apple |   100
(1 row)

xmin = 737 は、この行バージョンが XID 737 によって作られたことを表します。xmax = 0 は、まだ削除も更新もされていないという意味です。ctid = (0,1) はテーブル内の物理位置です。

次に、同じ行を UPDATE します。

postgres=# UPDATE mvcc_item SET point = 120 WHERE id = 1;
UPDATE 1
postgres=# SELECT xmin, xmax, ctid, id, name, point FROM mvcc_item;
 xmin | xmax | ctid  | id | name  | point
------+------+-------+----+-------+-------
  738 |    0 | (0,2) |  1 | apple |   120
(1 row)

論理的には id = 1 の行を更新しただけです。しかし、見えている行バージョンの xmin は 738 に変わり、ctid も (0,1) から (0,2) に変わりました。同じ場所の値が塗り替えられたのではなく、新しい行バージョンが別の位置に書かれています。

もう一度 UPDATE すると、同じことが繰り返されます。

postgres=# UPDATE mvcc_item SET point = 150 WHERE id = 1;
UPDATE 1
postgres=# SELECT xmin, xmax, ctid, id, name, point FROM mvcc_item;
 xmin | xmax | ctid  | id | name  | point
------+------+-------+----+-------+-------
  739 |    0 | (0,3) |  1 | apple |   150
(1 row)

通常の SELECT では、古い (0,1) や (0,2) の行バージョンは見えません。見えないだけで、すぐに物理削除されたわけではありません。

実験2: 未コミット UPDATE 中でも SELECT は古い値を読める

次は psql を 2 枚開いて確認します。最初にどちらか片方でテーブルを準備します。

postgres=# DROP TABLE IF EXISTS mvcc_lock_demo;
NOTICE:  table "mvcc_lock_demo" does not exist, skipping
DROP TABLE
postgres=# CREATE TABLE mvcc_lock_demo (
postgres(#   id integer PRIMARY KEY,
postgres(#   status text NOT NULL
postgres(# );
CREATE TABLE
postgres=# INSERT INTO mvcc_lock_demo VALUES (1, 'ready');
INSERT 0 1
postgres=# SELECT xmin, xmax, ctid, id, status FROM mvcc_lock_demo;
 xmin | xmax | ctid  | id | status
------+------+-------+----+--------
  743 |    0 | (0,1) |  1 | ready
(1 row)

セッション A でトランザクションを開始し、UPDATE します。ただし、まだ COMMIT しません。

-- セッション A
postgres=# BEGIN;
BEGIN
postgres=*# UPDATE mvcc_lock_demo SET status = 'processing' WHERE id = 1;
UPDATE 1
postgres=*# SELECT txid_current();
 txid_current
--------------
          744
(1 row)

postgres=*# SELECT xmin, xmax, ctid, id, status FROM mvcc_lock_demo;
 xmin | xmax | ctid  | id |   status
------+------+-------+----+------------
  744 |    0 | (0,2) |  1 | processing
(1 row)

セッション A から見ると、自分が作った新しい行バージョン (0,2) が見えます。

この状態で、セッション B から SELECT します。ここではスナップショットが固定されることも見たいので、REPEATABLE READ で始めます。

-- セッション B
postgres=# BEGIN ISOLATION LEVEL REPEATABLE READ;
BEGIN
postgres=*# SELECT xmin, xmax, ctid, id, status FROM mvcc_lock_demo;
 xmin | xmax | ctid  | id | status
------+------+-------+----+--------
  743 |  744 | (0,1) |  1 | ready
(1 row)

セッション B の SELECT は待たされません。新しい (0,2) は未コミットの XID 744 が作った行バージョンなので、セッション B のスナップショットからは見えません。その代わり、古い (0,1) が見えます。

ここで重要なのは、セッション B から見えている古い行バージョンの xmax が 744 になっていることです。古い行にはすでに死亡印が付いています。それでも、XID 744 はセッション B のスナップショット時点では未コミットなので、古い行はまだ有効なものとして読めます。

セッション A を COMMIT します。

-- セッション A
postgres=*# COMMIT;
COMMIT

それでも、セッション B は REPEATABLE READ の同じトランザクション内にいる限り、最初に見たスナップショットを保ちます。

-- セッション B
postgres=*# SELECT xmin, xmax, ctid, id, status FROM mvcc_lock_demo;
 xmin | xmax | ctid  | id | status
------+------+-------+----+--------
  743 |  744 | (0,1) |  1 | ready
(1 row)

セッション B を COMMIT して、新しい SQL 文として読み直すと、今度は更新後の行バージョンが見えます。

-- セッション B
postgres=*# COMMIT;
COMMIT
postgres=# SELECT xmin, xmax, ctid, id, status FROM mvcc_lock_demo;
 xmin | xmax | ctid  | id |   status
------+------+-------+----+------------
  744 |    0 | (0,2) |  1 | processing
(1 row)

SELECT は更新中の行のロック解除を待つのではなく、自分のスナップショットから見える古い行バージョンを読めます。これが、PostgreSQL で読み取りと書き込みが衝突しにくい大きな理由です。

実験3: UPDATE だけで dead tuple と物理サイズが増える

最後に、UPDATE を繰り返すと dead tuple が溜まることを数字で見ます。dead tuple は、もう新しいスナップショットからは不要になった古い行バージョンです。

postgres=# DROP TABLE IF EXISTS mvcc_size_demo;
NOTICE:  table "mvcc_size_demo" does not exist, skipping
DROP TABLE
postgres=# CREATE TABLE mvcc_size_demo (
postgres(#   id integer PRIMARY KEY,
postgres(#   payload text NOT NULL
postgres(# );
CREATE TABLE
postgres=# INSERT INTO mvcc_size_demo
postgres-# SELECT i, repeat('x', 100)
postgres-# FROM generate_series(1, 50000) AS g(i);
INSERT 0 50000
postgres=# ANALYZE mvcc_size_demo;
ANALYZE
postgres=# SELECT
postgres-#   pg_size_pretty(pg_relation_size('mvcc_size_demo')) AS table_size,
postgres-#   n_live_tup,
postgres-#   n_dead_tup
postgres-# FROM pg_stat_user_tables
postgres-# WHERE relname = 'mvcc_size_demo';
 table_size | n_live_tup | n_dead_tup
------------+------------+------------
 6880 kB    |      50000 |          0
(1 row)

n_live_tup は生きている行の推定数、n_dead_tup は dead tuple の推定数です。作った直後なので、50,000 行が生きていて dead tuple はありません。

続けて、全行 UPDATE を 5 回実行します。

postgres=# DO $$
postgres$# BEGIN
postgres$#   FOR i IN 1..5 LOOP
postgres$#     UPDATE mvcc_size_demo
postgres$#     SET payload = repeat(chr(96 + i), 100);
postgres$#   END LOOP;
postgres$# END $$;
DO
postgres=# ANALYZE mvcc_size_demo;
ANALYZE
postgres=# SELECT
postgres-#   pg_size_pretty(pg_relation_size('mvcc_size_demo')) AS table_size,
postgres-#   n_live_tup,
postgres-#   n_dead_tup
postgres-# FROM pg_stat_user_tables
postgres-# WHERE relname = 'mvcc_size_demo';
 table_size | n_live_tup | n_dead_tup
------------+------------+------------
 40 MB      |      50000 |     250000
(1 row)

論理的な行数はずっと 50,000 行のままです。しかし、UPDATE のたびに古い行バージョンが dead tuple になり、テーブルの物理サイズも増えました。pg_relation_size はテーブル本体のサイズを見る関数なので、ここではインデックスサイズを除いたテーブル部分だけを見ています。

n_live_tup と n_dead_tup はどちらも統計情報にもとづく推定値なので、実行タイミングやテーブルの状態で多少ずれます。生きている行が本当は 50,000 のままでも、n_live_tup が一時的に大きく見えることがあります。それでも、UPDATE だけで dead tuple が増える方向は変わりません。

ロールバックしても、後片付けは残る

ここまでは更新を確定してきましたが、ロールバックした場合も見ておきます。取り消したのだから何も残らない、と思いたくなるところです。実験1で使った mvcc_item をそのまま使い、更新してからロールバックします。

postgres=# BEGIN;
BEGIN
postgres=*# UPDATE mvcc_item SET point = 999 WHERE id = 1;
UPDATE 1
postgres=*# ROLLBACK;
ROLLBACK
postgres=# SELECT xmin, xmax, ctid, id, point FROM mvcc_item;
 xmin | xmax | ctid  | id | point
------+------+-------+----+-------
  739 |  751 | (0,3) |  1 |   150
(1 row)

point は 150 のままで、999 への更新はなかったことになっています。行を作った xmin は実験1の最後と同じ 739 のまま、つまり有効な行バージョンは変わっていません。ところが xmax を見ると、この UPDATE を試みたトランザクション ID の 751 が入ったままです。ロールバックしても xmax は 0 には戻りません。代わりに PostgreSQL は「この死亡印を付けた XID 751 はロールバックで失敗した」と判断し、その死亡印を無効なものとして扱います。だから古い行バージョンはそのまま有効に読めます。

見落としがちなのは、UPDATE が書きかけた point = 999 の新しい行バージョンです。これは物理的にはテーブルに書き込まれていて、ロールバック後は誰からも見えない dead tuple として残ります。ロールバックは論理的には「なかったこと」ですが、掃除すべきゴミは同じように生まれる、ということです。

実務で見る現象は、同じ仕組みから出ている

ここまで見た行バージョンの仕組みを前提にすると、冒頭の不思議はひとつずつ説明できます。

大量 UPDATE 中でも SELECT が待たされにくいのは、読み取り側が更新中の新しい行バージョンを無理に読みに行かず、自分のスナップショットから見える古い行バージョンを読めるからです。同じ行を UPDATE しようとする書き込み同士は競合しますが、読み取りと書き込みは分離しやすくなります。

UPDATE や DELETE のあとにテーブルが太るのは、古い行バージョンがすぐには消えないからです。その行バージョンをまだ読む可能性があるスナップショットが残っている間、PostgreSQL は勝手に回収できません。

そこで必要になるのが VACUUM です。VACUUM は、もうどのトランザクションからも見えない dead tuple を回収し、その領域を再利用できるようにする回収係です。通常の VACUUM は空いた領域をテーブル内で再利用できるようにしますが、ファイルサイズを必ず小さくするわけではありません。

長時間開きっぱなしのトランザクションが嫌われる理由も同じです。ここでいう「開きっぱなし」は、接続したまま座っていることではありません。psql に入っているだけ、あるいは普通に SQL を1文ずつ実行しているだけなら、PostgreSQL は文ごとにトランザクションを自動で開いて閉じているので、古いスナップショットは残りません。問題になるのは、BEGIN を実行したあと COMMIT も ROLLBACK もせずに止まっている状態で、pg_stat_activity を覗くと idle in transaction と表示されます。接続とトランザクションは別物で、線がつながっていることと、作業のかたまりが開いたままであることは違う、という区別がここで効いてきます。

この状態のトランザクションが居座ると、古いスナップショットが解放されず、PostgreSQL は「この古い行バージョンをまだ読むかもしれない」と判断せざるを得ません。その結果、VACUUM が回収できる dead tuple が減り、テーブルやインデックスの肥大につながります。

解放されるのは、そのトランザクションが COMMIT か ROLLBACK を実行したとき、あるいは接続そのものが閉じたときです。接続が切れた場合、開いたままだったトランザクションは自動的にロールバックされます。放置を防ぐために、idle_in_transaction_session_timeout を設定して一定時間で接続を強制的に切る運用もよく使われます。アプリケーション側でも、更新後の COMMIT の呼び忘れや、コネクションプールへ未確定のまま接続を返す作りが、同じ idle in transaction を生みます。

PostgreSQL の運用で「なぜそうなるのか」に迷ったら、まずテーブルの中に複数の行バージョンが並んでいる絵に戻ると、かなりの現象を同じ言葉で説明できます。