PostgreSQLのMVCCは悪い。他のすべてのもそうだ。

目次 PostgreSQLに対する非難 非難1: 書き込み増幅 非難2: テーブルは事故現場 非難3: アイドル状態のトランザクションが井戸を汚染する 非難4: 32ビットの負債 アンドゥキャンプ: OracleとInnoDB SQL Server: バージョニングはオプションの追加機能 MongoDB: キャッシュ内のバージョン LSM世代: ライフスタイルとしてのガベージコレクション RDBMSの外側: etcd PostgreSQL自体の修正 スコアカード Postgresについて最初に学ぶことの一つは、Postgresを好まない人々をフォローしていれば、MVCCは悪いということでしょう。40年前のデザインミスです。その痕跡は至る所にあります。2倍に肥大化したテーブル、32ビットのトランザクションカウンターの制限、VACUUMとの終わりのない戦い、デッドタプル（無効な行）の悪夢。これには証拠もあります。Uberは2016年に書き込み増幅を測定し、それゆえMySQLに移行しました。Andy Pavloのデータベースグループは、PostgreSQLの中で最も嫌いな部分としてMVCCを挙げています。これは現実です。Postgresは最悪の部類に入ります。 これらのどれも誇張されているわけではありませんが、それは実際の設定によるものです。肥大化、書き込みの増幅、バキュームの世話：すべての非難は欠陥ではなく設定に起因しており、私たちはこれをライブのPostgreSQL 19ベータ2インスタンスで一つずつ再現します。しかし、コミュニティからコミュニティへと広がる評価は、常に一つの問いを途中で止めてしまいます。何と比較してか？他のすべてのエンジンは何をしており、そのコストはいくらか？MVCCはオプションではないからです。読者が書き込みをブロックしないようにしたいデータベースは、どこかに複数バージョンの行を保持する必要があります。そうするすべてのエンジンは、同じ4つの質問に答えます：古いバージョンはどこに保存されるのか？テーブル自体か、それとも別の構造か？バージョンの連鎖はどちらを指すのか？古いものから新しいものへ、それとも新しいものから古いものへ？インデックスは何を指すのか？物理的な行の位置か、それとも論理的なキーか？誰が、いつクリーンアップするのか？後でバックグラウンドプロセスか、それともトランザクション自体か？PostgreSQLの回答：テーブル内、古いものから新しいものへ、物理的な位置、後でバックグラウンドプロセス。批評家がリストするすべてのコストは、これらの4つの回答から派生します。そして、すべての代替案は異なる回答のセットであり、その請求書は他の誰か、書き込み手、履歴の読者、tempdb、キャッシュ、コンパクターに送られます。それらのうちの1つは、PostgreSQLのデザインが最初から無料で持っていた唯一の特性を購入するために、何年もエンジニアリングを費やしました。トランザクションが昼食を挟んで開いたままだと、すべてが異なる形で失敗します。 PostgreSQLに対する非難 完全なメカニズムを知りたい場合は、PostgreSQL MVCC, Byte by Byte を pageinspect を使って見てください。短いバージョン：PostgreSQLでのUPDATEは行を決して変更しません。それは行の完全な新しいコピーをヒープに書き込み、古いバージョンのt_xmaxをスタンプし、両方のバージョンをディスク上に残します。可視性は読み取り時にタプルごとに決定されます。クリーンアップは誰か他の人の問題、具体的にはVACUUMの問題です。4つの非難が以下に続きます。 非難1: 書き込み増幅 Uberの苦情の中心です。テーブル上のすべてのインデックスは行の物理的な場所（ページ番号とスロット、ctid）を指しており、UPDATEは新しい物理行を作成するため、すべてのインデックスは新しい場所を指す新しいエントリを必要とします。触れていない列のインデックスでさえもです。同じ100万行のテーブルを2つ用意します。1つは主キーのみ、もう1つは4つの追加のセカンダリインデックスを持つもの： CREATE TABLE accounts ( id bigint PRIMARY KEY, email text NOT NULL, status text NOT NULL, balance numeric NOT NULL, created_at timestamptz NOT NULL, last_seen timestamptz ); INSERT INTO accounts SELECT g, 'user' || g || '@example.com', 'active', 100, now(), now() FROM generate_series(1, 1000000) g; CREATE INDEX ON accounts (email); CREATE INDEX ON accounts (status); CREATE INDEX ON accounts (balance); CREATE INDEX ON accounts (created_at); CREATE TABLE accounts_lean (LIKE accounts); ALTER TABLE accounts_lean ADD PRIMARY KEY (id); INSERT INTO accounts_lean SELECT * FROM accounts; 次に10万行を更新します。last_seenのみを触ります。last_seenはどちらのテーブルのどのインデックスにも含まれていません。WAL（Write-Ahead Log）の生成量を測定します（CHECKPOINT後、pg_stat_wal、pg_stat_reset_shared('wal')でクリーンなウィンドウを取得）： UPDATE accounts_lean SET last_seen = now() WHERE id > 100000 AND id <= 200000; wal_records | wal_fpi | wal_bytes | wal_pretty ------------+---------+-----------+------------ 302510 | 1419 | 38218531 | 36 MB UPDATE accounts SET last_seen = now() WHERE id > 100000 AND id <= 200000; wal_records | wal_fpi | wal_bytes | wal_pretty ------------+---------+-----------+------------ 709440 | 1981 | 72394573 | 69 MB 同じ論理的な変更、10万個のタイムスタンプ。leanテーブルは約3.0 WALレコード/行を生成しました。インデックス付きテーブルは7.1を生成しました。ヒープの更新、主キーの新しいエントリ1つ、4つのセカンダリインデックスのそれぞれに新しいエントリ1つ。変更した列はどれもインデックスされていません。行が移動し、すべてが物理的な場所を指しているため、すべてが書き直されました。これがUberが測定した増幅であり、それは累積します。より多くのWALは、より多くのチェックポイント作業、より多くのフルページ書き込み、そしてすべてのレプリカに送信されるバイト数を意味します。インデックスが多いテーブルでの単一列の更新は、有用な変更バイト数あたりの最も高価な操作の1つです。PostgreSQLの緩和策はHOT更新（Heap-Only Tuples）です。インデックス付き列が変更されず、新しいバージョンが同じページに収まる場合、インデックスはそのままにされます。上記の条件は両方とも満たされませんでした。ページは満杯だったので、新しいバージョンはすべて別のページに着地しました。テーブルに余裕を持たせて再度試します： ALTER TABLE accounts SET (fillfactor = 70); VACUUM FULL accounts; UPDATE accounts SET last_seen = now() WHERE id > 200000 AND id <= 300000; wal_records | wal_bytes | n_tup_hot_upd (this batch) ------------+-----------+---------------------------- 455812 | 51 MB | 41,996 of 100,000 改善されましたが、数に注目してください。42%がHOTであり、100%ではありません。各ページには30%の空きスペースと約80行があります。最初の約30回の更新が予約スペースを使い切ると、そのページの残りの行はインデックスを含めて他の場所に溢れ出します。機会的なプルーニングがページ内のデッドバージョンをリサイクルした後、同じバッチをもう一度実行します： wal_records | wal_bytes | n_tup_hot_upd (this batch) ------------+-----------+---------------------------- 304990 | 22 MB | 66,314 of 100,000 66%がHOTで、行あたり3.0 WALレコード、ほぼleanテーブルのコストに戻りました。それが緩和策の正直な形です。HOTは機会的であり、オンデマンドではなく定常状態で到着し、予約スペースとしてテーブルサイズの30%を静かに犠牲にします。これは機能し、適切に設定されたfillfactorを持つ本番環境のOLTPテーブルは、通常90%以上のHOT比率を達成します。しかし、デフォルトの動作は行あたり7.1レコードであり、批評家はデフォルトを説明しています。 非難2: テーブルは事故現場 すべてのUPDATEはINSERTと遅延DELETEです。何もしないと、デッドバージョンが蓄積します： VACUUM (FULL, ANALYZE) accounts_lean; -- reset: 89 MB, 0% dead \timing on BEGIN; UPDATE accounts_lean SET balance = balance + 1; -- all 1M rows ROLLBACK; SELECT pg_size_pretty(pg_relation_size('accounts_lean')); SELECT tuple_count, dead_tuple_count, round(dead_tuple_percent) FROM pgstattuple('accounts_lean'); BEGIN Time: 0.032 ms UPDATE 1000000 Time: 1590.112 ms (00:01.590) ROLLBACK Time: 0.124 ms pg_size_pretty ---------------- 178 MB tuple_count | dead_tuple_count | dead_pct ------------+------------------+---------- 1000000 | 1000000 | 47 1つのステートメント、コミットさえしなかったトランザクションでテーブルが2倍になりました。100万のデッドタプルがバキュームを待っており、VACUUM Is a Lieで述べられているように、その過程で増加したインデックスは、それ自体では決して縮小しません。これが肥大化のトレッドミルです。ストレージ設計は、書き込み速度でゴミが作成され、バキューム速度で収集されることを保証し、それらの2つの速度を一致させ続けることは、PostgreSQLがあなたにアウトソースする運用ジョブであり、autovacuumのデフォルトが出発点となります。 非難3: アイドル状態のトランザクションが井戸を汚染する 実験を続けます。