PostgreSQL

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 つです。 列 役割 xmin その行バージョンを作ったトランザクション ID xmax その行バージョンを削除または更新で見えなくしたトランザクション ID。何もなければ 0 ctid その行バージョンがテーブル内のどこにあるかを示す物理位置 トランザクション ID は、PostgreSQL が書き込みトランザクションを識別するために付ける番号です。記事中では XID とも書きます。xmin は「この XID が作った」、xmax は「この XID が死亡印を付けた」と読むと理解しやすいです。 ...