SQL Academy · レッスン

MVCCと肥大化の原因

Q: 「MVCCと肥大化の原因」で何を学びますか？

多版型同時実行制御、不要なタプルが蓄積する理由、長時間トランザクションが肥大化を招く仕組みを理解します。 ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

多版型同時実行制御、不要なタプルが蓄積する理由、長時間トランザクションが肥大化を招く仕組みを理解します。

「MVCCと肥大化の原因」はCoddyKit上の無料SQL Academyレッスンです。これはレッスン1/4です。下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。これはSQL Academy学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Academyコースには全4レッスンが含まれています。

MVCCとは

Multi-Version Concurrency Control（多版型同時実行制御）のことです。PostgreSQLはロックを使う代わりに、行の複数のバージョンを保持します。読み取り側は一貫したスナップショットを参照し、書き込み側は読み取り側をブロックせずに新しいバージョンを作成します。

UPDATEの仕組み

UPDATEは行をその場で変更するわけではありません。

トランザクションTの時点で、古い行バージョンを「dead」としてマークする
新しいバージョンを書き込む
他のトランザクションは、自分のスナップショットで許可されるバージョンを参照する

なぜ肥大化するのか

不要になったバージョンが蓄積します。行数が変わらなくてもテーブルは大きくなります。クリーンアップしないと、クエリが次第に多くの不要な行をスキャンするようになります。

VACUUMが領域を再利用する仕組み

VACUUMは不要な行を再利用可能としてマークします（テーブルファイル内で再利用されます）。ファイル末尾が完全に空にならない限り、ファイルを縮小することはありません。VACUUM FULLはテーブルを書き直すため、排他ロックが必要で処理も遅くなります。

自動バキューム

PostgreSQLはバックグラウンドでautovacuumを実行します。不要な行がしきい値を超えると起動します。

autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.2
-- vacuum when dead_rows > 50 + 0.2 * total_rows

肥大化を引き起こすワークロード

小規模または頻繁に更新されるテーブルへの大量のUPDATE
大量のDELETE（領域を解放するにはvacuumが必要）
長時間実行されるトランザクションがスナップショットを保持し、vacuumを妨げる
アイドル状態のトランザクション中セッションが、負荷の高いテーブルで不要な行を蓄積させる

肥大化を診断する

pgstattuple拡張を使うと、正確な数値を取得できます。

CREATE EXTENSION pgstattuple;

SELECT * FROM pgstattuple('orders');
-- table_len, tuple_count, dead_tuple_count, free_space, etc.

SELECT * FROM pgstatindex('orders_user_id_idx');

長時間のトランザクションがVACUUMを妨げる

VACUUMがクリーンアップできるのは、最も古いアクティブなトランザクションより前にある行だけです。4時間アイドル状態のトランザクション中セッションがあると、4時間分の不要な行が回収されないまま残ります。

SELECT pid, state, xact_start, NOW() - xact_start AS duration
FROM pg_stat_activity
WHERE state IN ('active', 'idle in transaction')
ORDER BY duration DESC NULLS LAST;

ラップアラウンド対策

トランザクションIDは32ビットです。autovacuumが追いつかないと、クラスタは「ラップアラウンド」に直面し、安全モード（強制VACUUM）に移行します。次の項目を監視してください。

SELECT datname, age(datfrozenxid) FROM pg_database
ORDER BY age(datfrozenxid) DESC;

論理削除と物理削除は異なる

DELETEは行を不要としてマークするだけで、領域を再利用可能にするにはVACUUMが必要です。大量のDELETEの後にvacuumを実行しないと、大量の不要な行が残ります。

HOT更新

インデックス対象ではない列だけを更新し、同じページに空き領域がある場合、PostgreSQLはHOT（Heap-Only Tuple）更新を行います。インデックスを変更しないため、肥大化を抑えられます。

肥大化を抑える

トランザクションを短く保ちます
インデックス付きの列に対する広範囲な UPDATE を避けます（HOT が機能しません）
頻繁に更新されるテーブルでは autovacuum を積極的に調整します
長時間のロックなしで書き換えるには pg_repack を使用します

まとめ

MVCC は、デッド行が蓄積する代わりに並行性を実現します。

VACUUM はデッド行をクリーンアップします
Autovacuum は不可欠です — 無効にしないでください
長時間のトランザクションはクリーンアップを妨げます
pgstattuple で診断します

クイックチェック

1 つの列だけが変更された場合でも、UPDATE でテーブルが縮小しないのはなぜですか？

よくある質問

「MVCCと肥大化の原因」レッスンは無料ですか？

はい。「MVCCと肥大化の原因」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Academyコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Academyコースには全4レッスンが含まれています。

「MVCCと肥大化の原因」で何を学びますか？

多版型同時実行制御、不要なタプルが蓄積する理由、長時間トランザクションが肥大化を招く仕組みを理解します。ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

SQL Academyを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのSQL Academyは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。これはレッスン1/4です。

「MVCCと肥大化の原因」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このSQL Academyレッスンでコードを書いて実行できますか？

はい。すべてのSQL Academyレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

← SQL Academyに戻る