ハクソク

世界を動かす技術を、日本語で。

PostgresのLISTEN/NOTIFYはスケールしない

322日前原文(recall.ai)

概要

  • Recall.aiで 大量の会議データ をPostgresに同時書き込みするユースケース
  • Postgresの LISTEN/NOTIFY 機能によるスケール問題の発見
  • グローバルロック によるパフォーマンス低下とダウンタイム発生
  • 問題の再現と原因特定のプロセス
  • 回避策 と今後の教訓

Postgres LISTEN/NOTIFYによるスケーラビリティ問題の発覚

  • Recall.aiでは、 1ヶ月あたり数百万時間分の会議データ をリアルタイムでPostgresに書き込み
  • データは ビデオ・音声・構造化データ（文字起こし・イベント・メタデータ） に分類
  • 数万の “meeting bot” が同時にデータを書き込み
  • Postgresの LISTEN/NOTIFY 機能を利用し、botの設定更新などの通知を実装
  • 非常に高い同時書き込み負荷で Postgresが停止 する事象が発生

問題の発見と調査プロセス

  • 2025年3月19日～22日にかけて 3回の大規模ダウンタイム を経験
  • 症状：
    • データベース負荷と待機セッションが 急増
    • クエリスループットが激減
    • CPU・ディスクI/O・ネットワークトラフィックが 著しく低下
  • lock contention（ロック競合） を疑い、log_lock_waitsを有効化して調査
  • ログ解析により、 updateエンドポイント （bot設定更新時NOTIFY発行）が負荷増加と相関

ロックの詳細と根本原因

  • ログに AccessExclusiveLock on object 0 of class 1262 of database 0 という異常なロックが多数記録
  • Postgresのソースコード調査で、 NOTIFYを含むトランザクションのCOMMIT時 に「データベース全体のグローバルロック」を取得する仕様を確認
  • このロックは 全てのCOMMITを直列化 し、 大量の同時書き込み時にボトルネック となる
  • LISTEN/NOTIFYはスケーラビリティに向かない ことが判明

問題の再現と比較検証

  • NOTIFY有効時：
    • トランザクションコミットが直列化 され、スループットが著しく低下
  • NOTIFY無効時：
    • 通常通り高い並列性 で処理可能、パフォーマンス回復

回避策と教訓

  • LISTEN/NOTIFYの利用を停止 し、別の通知手段（例：外部メッセージキュー）へ移行
  • Postgresを 多数の同時書き込みが必要なワークロードでLISTEN/NOTIFYに依存しない 設計の重要性
  • 高負荷環境でのPostgres運用時は、内部ロック機構の理解が不可欠

まとめ

  • Postgresの LISTEN/NOTIFYは便利だが、グローバルロックによるスケール制約 が存在
  • 大量同時書き込み環境では利用を避ける べき
  • 問題発見のためには 詳細なロギングとソースコードレベルの調査 が有効
  • 通知設計は外部キュー利用等で分離 し、データベース本体のスケーラビリティを守ることが推奨

Hackerたちの意見

面白いね。トランザクションの外で、別の接続で NOTIFY を実行したらどうなるんだろう？

└

NOTIFY用に別のTCPストリームを持つのが標準的なやり方じゃないの？それとも俺が間違ってる？

└

俺もそう思う。トランザクション中に一時テーブルに通知コマンドを追加して、トランザクションが成功裏にコミットした後にその一時テーブルの各行でNOTIFYを実行するっていうのはどう？

└

ただ、トランザクションの外で通知すると、トランザクション保証が失われるから注意してね。

RLSやトリガーもスケールしないよね。

└

うん、次のPOSシステムのデプロイでトリガーを削除するつもりだよ。各挿入に10〜50msも追加されちゃうからね。order_itemsテーブルに40アイテム挿入する場合は問題になる。

└

シャーディングが必要になった瞬間、外部キーも役に立たなくなるよ。データベースに「秘密の追加作業」を頼むと、ユーザーには透明なはずなのに、タダでは済まないってことが分かった。

そうだね、あと文字数制限（カラムサイズ）もあるし。だから、データベースの変更をPostgresのWALで聞くのが好きなんだ。https://github.com/cpursley/walex?tab=readme-ov-file#walex （ここにはいくつか役立つリンクがあるよ）

└

最近、実際のテーブルに書き込まずに、トランザクション保証付きでWALに直接書き込めることを見つけたよ。これってキューやアウトボックス目的には素晴らしいと思う。普通の方法でテーブルにデータを挿入するとリソースをたくさん使うからね（オートバキュームがこの用途では大きな問題）。その機能がどれか見つけられないし、まだ実際に使われてるのを見たこともないけど、何か注意点があるのかな。編集: 見つけた、pg_logical_emit_messageだよ。

└

node.jsユーザー向けには、PostgresのWALを監視して、アプリケーションコードで扱えるノードイベントを発行するpostgres.jsがあるよ。

└

そうだね、ベンダーがPostgresのレプリケーションの挙動をめちゃくちゃにして、他のツールにこれらの機能を統合する一般的な方法を妨げるまではね。AWSを見てるよ。

この投稿には二つの理由で感謝してる。* Postgresの機能がブロッカーになる前に、どれくらい成長する必要があるかの指標を示してくれる。* 将来的にこの問題や混乱するログラインに直面する人たちが、この投稿を見つけて問題をすぐに理解できるようになる。

リスン・ノティファイには一つの問題があったけど、それは妥当なものだった。でも今はHTTPのレイテンシ、ネットワークの問題、DNS、リトライ、自分でDDoSするような問題が出てきてるね。

└

LISTEN/NOTIFYのスケーリング問題の影響が、実際に行われているタスクの負荷や範囲よりもDB全体のパフォーマンスに対してずっと大きかったみたいだね（記事の最後に基づいて）。そして、もしそのオフロードされたタスクにもっとパフォーマンスが必要なら、選択肢があることもわかってるみたい（Redis経由のpub/subとか）。

LISTEN/NOTIFYは単なるロックフリーのトリガーじゃないんだ。負荷がかかると同時実行性を危うくする可能性がある。小規模では無害に見える機能も、大規模になると全てが壊れることがあるよ。

PostgresのLISTEN/NOTIFYは、Oban（Elixir用のバックグラウンドジョブ処理フレームワーク）にとってしばらくの間、一貫した痛みのポイントだった。ペイロードサイズの制限やコネクションプーラーの問題だけでも微妙に壊れちゃうんだよね。特に皮肉なのは、Elixirは分散Erlangのおかげで素晴らしい分散とpubsubのストーリーを持っていること。今はアプリの40〜50%がクラスター化されていなかった5年前に比べて、ずっと一般的に使われるようになった。Flyのようなプラットフォームの登場で簡単になったし、Herokuの衰退でほぼ不可能になったからね。

└

これをどう解決したの？WALを聞くことを考えた？

└

オーバンがMnesia（Erlangの組み込み機能）を使ってないなんて気づかなかったわ。

もし私の理解が正しければ、グローバルロックは通知イベントが順番に発信されるためのものなんだよね。それが気にならないなら、順序保証をしないバリアントを持つのはどうかな？そうすれば、全体をロックせずにトランザクション内で「通知」できるようになるよ。

└

可能性はあるけど、その時点ではビジネスロジックをデータベースの外に移した方が良さそうだね。成功したコミットを待ってから、元のアプリを通じて外部プロセスをトリガーしたり、外部のpub/subシステムでWALを監視したり、もっと賢い方法を考えるのもありかも。

ここでのダムなポーリング（FOR UPDATE SKIP LOCKEDメソッド）と比較するとどうなるのか興味あるな。いつかベンチマークを設定するつもりなんだけど、こういうことは証拠があまりないのにみんなよく議論するからね。このAccessExclusiveLockの挙動には気づかなかったな - Postgresのロックがどう相互作用するかのリマインダー（そして恥ずかしげもなく宣伝2）だね。

└

その宣伝、受け入れるよ。これをまとめてくれてありがとう！pgロックの非常に役立つ概要だね。

└

pgmqを触ったことある？結構面白いよ： https://github.com/pgmq/pgmq

└

私は最大1分までのバックオフでポーリングを使ってる。だから、ワークロードが終わったらすぐに次の作業をポーリングするんだ。何も見つからなかったら5秒待って、まだ何もなければ10秒、…って感じで1分まで待って、それ以降はまた1分ごとにポーリングして、作業が見つかるまで続けるよ。バックオフタイマーはまた0にリセットされる。

└

ポーリングは良い方法だけど、正しくやるのは結構難しいよね。特に、トランザクションがオープンのままでタプルをクリーンアップできないときに、信頼性のあるキューを作るのは簡単じゃない。例えば「最初に利用可能なタプルを取得する」っていうのは、何千ものデッドタプルをスキップしなきゃいけないかもしれない。トランザクションをオープンにしておくのは確かにアンチパターンだけど、時には役に立つこともある。例えば、pg_repackは実行中にトランザクションをオープンにしておくし、vacuumもその時の一部でオープンなトランザクションを持ってると思う。こういうことが偶然に起こっても、データベースが壊れないのはいいよね。

ちょっと気になったんだけど、他にPostgresユーザーが気をつけるべきこと、例えばAccessExclusiveLockみたいなのがあったら教えてほしいな。私が知ってることは、ユニークインデックスは挿入を遅くするから、DBがテーブル全体のロックを取得しなきゃいけないってこと。あとは、WHERE句の中のCASE文はクエリプランナー/オプティマイザーを壊しちゃって、フルテーブルスキャンが必要になること。読み取り専用のPostgres関数はSTABLE PARALLEL SAFEとしてマークするべきだよね。

└

もっと詳しく教えてもらえる？ユニークインデックスでの挿入はテーブルをロックしないよ。WHERE句の中のCASE文は問題ないし、式インデックスを使ってインデックスを作ればいいよ。