Postgresキューを健全に保つ

(planetscale.com)

13 ポイント投稿者 GN⁺ 12 일 전 | 1件のコメント | WhatsAppで共有

Postgresで job queue を運用する際に発生する慢性的な問題である dead tuple の蓄積 と、それに伴う テーブルの bloat、性能低下の原因と解決方法を整理
キューテーブルは大半の行が 挿入-読み取り-削除 で短く循環するため、サイズは一定でも 累積スループットが非常に大きい
Postgresの MVCC 構造上、削除された行は即座に除去されず dead tuple として残って整理される必要があり、autovacuum がこれを担う
長時間実行トランザクションや重なって実行される分析クエリが MVCC horizon を固定すると、autovacuumがdead tupleを整理できずキュー性能が低下する
PlanetScaleの Traffic Control（Insights拡張）機能が、クエリクラス別のリソース制限 によってこの問題を解決する実用的な手段として提示される

キューワークロードの特性

キューテーブルの固有の特徴は、大半の行が 一時的（transient） であること — 挿入され、1回読まれ、削除される
テーブルサイズはほぼ一定だが、累積スループットは莫大
Postgresにjob queueを置く主な利点は、jobの状態と他のDBロジックを同一トランザクションで同期 できること
- jobが失敗するとトランザクション全体がロールバック
- 外部キューサービスを使うと、アプリケーションのトランザクション状態との同期が複雑になる

例となるキューテーブルとワーカーの動作

本文で示された基本スキーマ

CREATE TABLE jobs (  
  id BIGSERIAL PRIMARY KEY,  
  run_at TIMESTAMPTZ DEFAULT now(),  
  status TEXT DEFAULT &#039;pending&#039;,  
  payload JSONB  
);  
CREATE INDEX idx_jobs_fetch ON jobs (run_at) WHERE status = &#039;pending&#039;;

ワーカーはトランザクションを開き、最も古い pending job を FOR UPDATE SKIP LOCKED でロックして重複処理を防ぐ
作業成功時は DELETE の後に COMMIT、失敗時はロールバックされ、その行は再び別のワーカーから見えるようになる
このトランザクションは 可能な限り短く保つ必要がある — 長く開いているほどvacuumを妨げるため（本文の例はサブミリ秒ワーカー基準）

性能そのものが問題ではない

Postgresが 大規模な job queue を処理できることはすでに文書化されており、能力そのものは問題ではない
本当の問題は、同じDB上で競合する他のワークロードとの共存
キューテーブルの健全性はその設定だけでなく、同じPostgresインスタンスで動くすべてのトランザクションの振る舞い に左右される
本文はプライマリにおける 競合クエリトラフィック に焦点を当てている（レプリカ・replication slotの影響は別）

本当の問題: dead tuple の整理

Postgresは MVCC により同一行の複数バージョンを保持する — 削除された行は即座には消えず、「削除済みとしてマーク」された後、新しいトランザクションには不可視な状態 で残る
このように残った行が dead tuple であり、vacuum処理 によって整理される
dead tuple は SELECT の結果には現れないが、それでも コストを発生させる
- Sequential scan: 実行器がheap page上のdead tupleを読み、可視性を確認して破棄する
- Index scan（キューで ORDER BY run_at LIMIT 1 に使う方式）: B-treeインデックスがdead tupleへの参照を蓄積し、もはや見えない行を指す項目まで走査することになる
各dead index entryは 追加のI/Oを発生 させ、アプリケーションからは見えなくてもdead tuple数に応じてコストが大きく増加する
整理周期は autovacuum_naptime（デフォルト1分）で、実行可否は autovacuum_vacuum_threshold と autovacuum_vacuum_scale_factor によって決まる

dead tuple の内部メカニズム

行メタデータの3つが重要
- ctid: heap内タプルの物理位置 (page, offset)
- xmin: この行を挿入したトランザクションID（XID）
- xmax: この行を削除/ロックしたトランザクションIDで、0 なら削除マークなし
pending 3件を取得する場合でも、実行器が以前に削除された6件のdead tupleをすべて走査した後、3件だけ返す 状況が起こりうる
インデックスでもleaf entryがdeadなheap tupleを指している場合、scan過程で無駄な作業 が蓄積する
DBが dead tuple生成速度より整理速度のほうが遅くなると、失敗スパイラルに入る
適切にチューニングされたPostgresクラスタは、秒間数万件のキュー処理量を支えられる

autovacuumが無力化される状況

autovacuumがdead tuple整理に失敗する主な原因
- 特定の テーブル lock がcleanupを妨げる
- 不適切な autovacuum設定
- 最も一般的なのは、アクティブなトランザクションがdead tupleの回収を妨げる場合
Postgresは アクティブなトランザクションからまだ見える可能性のあるdead tupleはvacuumしない
- 最も古いアクティブトランザクションがcutoffを設定 → MVCC horizon
- そのトランザクションが終わるまで、そのスナップショット以後のdead tupleはすべて維持される
2分間の単一トランザクション1本で 2分間horizonが固定 される
同じ失敗パターンは 重なり合う中程度の長さのクエリ群 でも起こる
- 例: 40秒の分析クエリ3本を20秒間隔でずらして実行すると、個々のクエリはタイムアウトしなくても常に1本はアクティブなため、horizonが前進できない
「Just use Postgres」の思想で複数ワークロードを1つのDBに置くと、速いjob処理そのものではなく、重なった遅いクエリのせいでdead tuple整理が遅れること が問題になる

既存ツールと限界

autovacuumチューニングオプション: autovacuum_vacuum_cost_delay, autovacuum_vacuum_cost_limit
長時間実行クエリを制限するためのタイムアウト
- statement_timeout（Postgres 7.3）: 指定時間を超えた個別SQL文を終了
- idle_in_transaction_session_timeout（9.6）: トランザクション内でアイドル状態が続くセッションを終了
- transaction_timeout（17.0）: アクティブ・非アクティブを問わず、トランザクションが指定時間を超えると終了
これらのタイムアウトは 単一クエリの実行時間だけを対象 としており、同時実行性や実行コストは制限できないため、MVCC horizonを継続的に固定するワークロード を防ぐには不向き
必要なのは トラフィッククラスごとの識別 であり、高優先度ワークロードはそのままに、低優先度ワークロードのリソース使用率だけを調整できる手段

Database Traffic Control™

PlanetScaleが開発した Insights拡張 の一部で、PlanetScale Postgres専用 の機能
個別クエリの性能とリソース使用量を細かく制御する際に使う
Resource Budget により対象クエリへリソース上限を与え、超過時にブロックできる
解決戦略は、重なり合う遅いクエリの同時実行数と頻度を制限 し、autovacuumが適切な速度でdead tupleを整理する余地を確保すること
ブロックされたクエリは永久拒否ではなく 再試行 されるため、アプリケーション側のretryロジックが必須
総仕事量を維持しつつ、実行速度を平準化 するアプローチ

デモ構成と背景

この文書の着想元は2015年のBrandur Leachのブログ "Postgres Job Queues & Failure By MVCC"
- Postgresベースのjob queueにおける致命的な失敗モードを記録
- 閉じられないトランザクションがMVCC horizonを固定し、cleanupを妨げる現象を実証するテストベンチを含む
元のテストベンチは brandur/que-degradation-test として公開されている

問題の再現（Postgres 18基準）

元のテストはRuby + Que gem v0.x + Postgres 9.4ベース
著者はSQLレベルの動作だけを切り分けて検証するため、TypeScript + Bun で書き直した
Queと同じ recursive CTEパターン、同じschema、producer rate、work duration、worker数、long-runnerパターンを維持
PlanetScale PS-5クラスタ で実行（月額5ドルから）
結果: 目に見えるが管理可能なレベルの性能低下
- 元のテストでは15分以内にDBがdeath spiralに陥ったが、PS-5では15分間ワーカーキューを0付近に維持
- しかしdead tupleは 線形に増加 しており、さらに時間が経てば同じ問題が再発する兆候がある
- B-treeインデックス整理の改善（バージョン churn に対する bottom-up deletion、scanベースのdead index tuple除去など）により 緩和はされたが解消はされていない

改善の試み: SKIP LOCKED + バッチ処理

2015年にはなかった2つの現代的改善
1. FOR UPDATE SKIP LOCKED — recursive CTE全体を 単一の SELECT で置き換え、他のワーカーがロックした行はスキップ
2. Batch processing（トランザクションあたり10 jobs） — 1回のlock取得で10件処理し、インデックススキャンのコストを分散
条件は同一: worker 8、producer 50 jobs/sec、work 10ms、long-runnerは45秒後に開始
主な結果

指標	original (recursive CTE)	enhanced (SKIP LOCKED + batch)
Baseline lock time	2–3ms	1.3–3.0ms
End lock time (typical)	10–34ms	9–29ms
Worst spike	84.5ms (dead tuple 33k)	180ms (dead tuple 24k)
Queue depth	0–100 (oscillating)	0 (ほとんど)
Dead tuples at end	42,400	42,450
Throughput	~89/s	~50/s

degradation曲線はほぼ同一 — どちらの方式も同じB-treeインデックスを走査し、同じdead tupleに遭遇するため
throughputの差はlock戦略ではなく テスト設計 の影響（CTEワーカーはproducerより速くjobを取っていく一方、batchワーカーはキューを空にしてbackoff sleepする）
結論: 10年前には15分でDBを死なせていたキュー設計も、今ではより長く耐えるが、根本問題は残っている — 500 jobs/secに上げれば問題はさらに早く再現する

Traffic Controlで解決

Resource Budgetが提供する制御手段
- Server share & burst limit: サーバーリソースの比率と消費速度
- Per-query limit: サーバー使用量基準で秒単位にクエリ実行可能時間を制限
- Maximum concurrent workers: 利用可能ワーカープロセスに対する比率
クエリ対象の指定は主に SQLCommenterタグのメタデータ で行う（例: action=analytics）
idle_in_transaction_session_timeout で捕捉されるlong-runnerの代わりに、より現実的なシナリオである アクティブに処理中の重なり合う分析クエリ によってdegradationを発生させる（セッションタイムアウトでは捕まえられないケース）
action=analytics クエリの Maximum concurrent workers を1ワーカー（max_worker_processes の25%）に制限 → 分析クエリは同時に1本だけ実行
15分の枠内でdeath spiralを誘発するため、producerを 800 jobs/sec に増加
EC2から同一PlanetScale DBに対して "enhanced" ワークロードを2回実行
- 800 jobs/sec
- 120秒の分析クエリ3本を同時実行し、常に重なるようずらして配置
- 15分継続
結果比較

指標	Traffic Control無効	Traffic Control有効
Queue backlog	155,000 jobs	0 jobs
Lock time	300ms+	2ms
Dead tuples at end	383,000	0–23,000 (cycling)
Analytics queries	3 concurrent, overlapping	1 at a time, 2 retrying
VACUUM effectiveness	Blocked (horizon固定)	Normal (クエリ間に整理ウィンドウを確保)
Outcome	Death spiral	Completely stable

Traffic Controlは 特定ワークロードの同時実行性を直接制限 し、autovacuumのチューニングやタイムアウトでは不可能だった制御を提供する
分析レポートは容量の許す範囲で継続実行され、15分間で 15件完了、キューは終始健全な状態を維持

まとめ

Postgresベースのキューにおける MVCC dead tuple問題 は2015年の遺物ではない
現代のPostgresは B-tree改善と SKIP LOCKED によってかなりの余裕を提供するが、根本メカニズムは同じ
- VACUUMがdead tupleを整理できなければ蓄積する
- 長時間実行・重複トランザクションがMVCC horizonを固定すると、VACUUMは整理できない
「Just use Postgres」でキュー・分析・アプリロジックを1つのDBに置く環境では、これは 理論上のリスクではなく日常的な運用条件 である
危険な形は劇的なクラッシュではなく、静かに劣化していく均衡状態 — lock timeが徐々に増え、jobが遅くなり、アラートは鳴らない
Postgresのタイムアウト手段では ワークロードクラスの識別や同時実行性の制限ができない
キューを他のワークロードと一緒に動かすなら、最も効果的な対策は VACUUMが追いつけることを保証すること であり、Traffic Controlはそれを単純化する

1件のコメント

GN⁺ 12 일 전

Hacker Newsのコメント

Postgres には依然として vacuum horizon 問題がある。これは長時間実行されるクエリが、頻繁に変更されるテーブルの vacuum を妨げる現象だ。この問題は 2015 年以来すでによく知られている。標準の Postgres にはこれを解決する良い手段がないが、筆者の会社のカスタム版にはこれを解決する機能がある。要するに、OLAP スタイルの長い処理とキュースタイルの高速な処理を同じ Postgres インスタンスで混在させるのは、今でも賢明ではない。要件次第では、0MQ や RMQ のようなメッセージキューを使う方が簡単な解決策かもしれない
- データベースがキャッシュやバッファプールを細かく制御できない限り、このような異なる負荷を混在させるのは常にあまり良い選択ではない。分析用テーブルがキャッシュ全体を汚染するのを防ぐ方法がないからだ
記事は良かったが、いくつか指摘したい点がある。
1. MVCC horizon の説明が互いに矛盾して見える。トランザクションが異なる時点で開始されるならスナップショットも異なり、最初のトランザクションが終われば vacuum は進められるはずだ
2. SELECT * FROM jobs WHERE status='pending' ORDER BY run_at LIMIT 1 FOR UPDATE SKIP LOCKED; クエリの性能問題は実際に存在するが、単調増加するカラムを追加してインデックスを張れば緩和できる。こうすれば dead tuple を考慮しなくてよくなり、単に領域を浪費するだけで、読み取り性能の低下は小さくなる。ただし、同時書き込みの状況で単調増加をどう保証するかはアプリケーション設計次第だ
3. 要するに、「Postgres で非常に長いトランザクションと非常に高頻度のトランザクションを一緒に使うな」という教訓だ
- インデックスを追加しても、Postgres は dead tuple を完全に掃除するまでそれらを保持しなければならない。多少は高速化するだろうが、いずれディスクはいっぱいになり、他のテーブルの vacuum も妨げられる可能性がある
筆者です。質問があればいつでもどうぞ
- ブログのキュー実装は、ジョブの処理中ずっとトランザクションを保持しているように見える。できるだけ短く保てという助言は理解できるが、状態カラムを processing に更新することで、長いトランザクション自体を避けられないのか気になる
- キューテーブルの fillfactor を 100 未満に設定して試したのか気になる。HOT update を使えば、状態変更時にインデックス項目を新しく作らずに dead space を再利用できるので、問題が起きる時点を遅らせられるように思える
- この解決策が pg_squeeze を使う場合と比べてどう違うのか気になる。私たちもキューシステムで同じ問題に直面していて pg_squeeze を試しているが、かなりうまく動いているように見える
広告のようにも見えるが、技術的な解決方法についてもう少しでも説明があればよかった
- トラフィック制御の方式については別のブログ記事がある: https://planetscale.com/blog/behind-the-scenes-how-traffic-c...
- InnoDB の同時実行制御の仕組みに似ているように聞こえる。InnoDB は、クエリが実行できる最大作業量を制限するトークンベースのアプローチを使っている: https://dev.mysql.com/doc/refman/8.4/en/innodb-performance-t...
Postgres は本当に多くのことができる。人々は Kafka や SQS を選ぶが、実際には Graphile Worker で十分に処理できる仕事も多い
- 「すべてを Postgres で処理しよう」という哲学は、小規模から中規模ではシンプルさを保つのに向いているが、規模が大きくなると SQL データベースにはできるだけ少ない仕事だけを任せる方がよい。たいていのシステムではそこがボトルネックになるからだ
- SQS は非常にシンプルで、AWS 環境では簡単に統合できる。Kafka ははるかに複雑だが、必要な機能があるなら素晴らしい選択肢だ。ただし運用負荷が大きく、履歴書のために導入されたようなプロジェクトも多い。一方で SQS は実用的なツールとして使われている。ただし AWS から離れようとするなら、その依存性が問題になることもある
Postgres では、行を更新すると問題はずっと深刻になる。挿入と削除だけを使うなら、かなり長く持ちこたえられる
- Postgres での UPDATE は、実質的には INSERT と DELETE の組み合わせだ

Postgresキューを健全に保つ

キューワークロードの特性

例となるキューテーブルとワーカーの動作

性能そのものが問題ではない

本当の問題: dead tuple の整理

dead tuple の内部メカニズム

autovacuumが無力化される状況

既存ツールと限界

Database Traffic Control™

デモ構成と背景

問題の再現（Postgres 18基準）

改善の試み: SKIP LOCKED + バッチ処理

Traffic Controlで解決

まとめ

関連記事

1件のコメント

Hacker Newsのコメント