Postgres Egress Optimizer

アプリケーション側のクエリパターンを診断・修正し、Postgres データベースからの過度なデータ転送(エグレス)を解決します。高額なエグレス請求のほとんどは、アプリケーションが使用するより多くのデータを取得していることが原因です。

Step 1: 診断

どのクエリが最もデータを転送しているかを特定します。主なツールは pg_stat_statements 拡張機能です。

pg_stat_statements が利用可能かどうかを確認

SELECT 1 FROM pg_stat_statements LIMIT 1;

エラーが発生した場合は、拡張機能を作成する必要があります:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Neon ではデフォルトで利用可能ですが、この CREATE EXTENSION ステップが必要な場合があります。

統計情報が空の場合の対応

Neon コンピュートがゼロにスケールダウンして再起動すると、統計情報がクリアされます。統計情報が空または最近コンピュートが起動した場合:

1. 統計情報をリセットして、新しい測定期間を開始します: SELECT pg_stat_statements_reset();
2. アプリケーションを代表的なトラフィック環境で少なくとも 1 時間実行します。
3. 戻って、以下の診断クエリを実行します。

ユーザーが本番データベースから統計情報を取得している場合は、それを使用します。本番統計情報へのアクセスがない場合は、Step 2 に進んでコードベースを直接分析してください — コードレベルのパターン分析は、問題のあるクエリを特定するのに十分なことが多いです。

診断クエリ

これらを実行して、エグレスに最も貢献しているクエリを特定します。多くの行を返すクエリ、幅広い行(JSONB、TEXT、BYTEA カラム)を返すクエリ、または非常に頻繁に呼ばれるクエリに焦点を当てます。

最も多くの総行数を返すクエリ:

SELECT query, calls, rows AS total_rows, rows / calls AS avg_rows_per_call
FROM pg_stat_statements
WHERE calls > 0
ORDER BY rows DESC
LIMIT 10;

実行ごとに最も多くの行を返すクエリ(スコープが不適切な SELECT、ページネーション欠落):

SELECT query, calls, rows AS total_rows, rows / calls AS avg_rows_per_call
FROM pg_stat_statements
WHERE calls > 0
ORDER BY avg_rows_per_call DESC
LIMIT 10;

最も頻繁に呼ばれるクエリ(キャッシング候補):

SELECT query, calls, rows AS total_rows, rows / calls AS avg_rows_per_call
FROM pg_stat_statements
WHERE calls > 0
ORDER BY calls DESC
LIMIT 10;

実行時間が最も長いクエリ(直接的なエグレス測定ではありませんが、スパイク時の問題クエリの特定に役立ちます):

SELECT query, calls, rows AS total_rows,
  round(total_exec_time::numeric, 2) AS total_exec_time_ms
FROM pg_stat_statements
WHERE calls > 0
ORDER BY total_exec_time DESC
LIMIT 10;

結果の解釈

推定エグレス影響度で結果をランク付けします:

• 高い行数 + 幅広い行 = 最大のエグレス。1,000 行を返すクエリで、各行に 50KB の JSONB カラムが含まれる場合、呼び出しごとに約 50MB が転送されます。
• 極度な呼び出し頻度は、小さなクエリでも蓄積します。1 日 50,000 回呼ばれるクエリで 10 行返す場合 = 1 日 500,000 行。
• スキーマとの相互参照で幅広いカラムを特定します。JSONB、TEXT、BYTEA、および大きな VARCHAR カラムを探してください。

Step 2: コードベースの分析

Step 1 で特定された各クエリ、またはステップ 1 で統計情報が利用できない場合はコードベース内の各データベースクエリについて、以下をチェックします:

• レスポンスが必要とするカラムのみを選択していますか?
• 返される行数が制限されていますか(LIMIT/ページネーション)?
• キャッシングの利点を得られるほど頻繁に呼ばれていますか?
• 生データを取得して、アプリケーションコードで集約していますか?
• 親データを子行全体に複製する JOIN を使用していますか?

Step 3: 修正

見つかった各問題に対して、適切な修正を適用します。以下は、最も一般的なエグレスアンチパターンと修正方法です。

未使用カラム(SELECT *)

問題: クエリはすべてのカラムを取得しますが、アプリケーションはいくつかのみを使用します。大きなカラム(JSONB ブロブ、TEXT フィールド)がネットワーク経由で転送され、破棄されます。

修正前:

SELECT * FROM products;

修正後:

SELECT id, name, price, image_urls FROM products;

ページネーションの欠落

問題: リストエンドポイントが LIMIT なしですべての行を返します。これは無制限のエグレスリスク — テーブル内の新しい行が増えるたびに、すべてのリクエストでデータ転送が増加します。現在のテーブルサイズに関わらず、これにフラグを付けてください。

アプリケーションは小さなデータセットでは正常に動作するため、見落としやすいです。しかしスケールでは、ページネーションなしのエンドポイントが 10,000 行を中程度のカラム幅で返すと、1 日あたり数百メガバイト転送する可能性があります。

修正前:

SELECT id, name, price FROM products;

修正後:

SELECT id, name, price FROM products
ORDER BY id
LIMIT 50 OFFSET 0;

ページネーションを追加するときは、消費クライアントがすでにページネーション付きレスポンスをサポートしているかどうかを確認します。そうでない場合は、適切なデフォルトを選択し、API のページネーションパラメータをドキュメント化します。

静的データに対する高頻度クエリ

問題: クエリが 1 日に数千回呼ばれていますが、データはほとんど変わりません。呼び出しのたびに同じ行がデータベースから転送されます。このパターンは pg_stat_statements からのみ見えます — コード自体は正常に見えます。

他のクエリと比べて呼び出し回数が極度に高いクエリを探してください。一般的な例: 設定テーブル、カテゴリリスト、フィーチャーフラグ、ユーザーロール定義。

修正: アプリケーションとデータベース間にキャッシングレイヤを追加して、すべてのリクエストでデータベースへのアクセスを避けます。

アプリケーション側の集約

問題: アプリケーションがテーブルのすべての行を取得してから、アプリケーションコード内で集約(平均、カウント、合計、グループ化)を計算します。結果は小さな概要であるにもかかわらず、完全なデータセットがネットワーク経由で転送されます。

修正: 集約を SQL にプッシュします。

修正前: アプリケーションがテーブル全体を取得してコード内でループや .reduce() で集約します。

修正後:

SELECT p.category_id,
       AVG(r.rating) AS avg_rating,
       COUNT(r.id) AS review_count
FROM reviews r
INNER JOIN products p ON r.product_id = p.id
GROUP BY p.category_id;

JOIN の重複

問題: 幅広い親テーブルと子テーブル間の JOIN は、すべての親カラムを子行全体に複製します。製品に 200 件のレビューがあり、製品行に 50KB の JSONB カラムが含まれている場合、JOIN はその 50KB × 200 = 単一のリクエストで約 10MB を送信します。

これは SELECT * の問題とは異なります。選択するカラムが必要なもののみであっても、JOIN は親データをすべての子行で繰り返します。修正は構造的です: JOIN 全体を避けます。

修正前:

SELECT * FROM products
LEFT JOIN reviews ON reviews.product_id = products.id
WHERE products.id = 1;

修正後(2 つの個別クエリ):

SELECT id, name, price, description, image_urls FROM products WHERE id = 1;
SELECT id, user_name, rating, body FROM reviews WHERE product_id = 1;

1 つの JOIN ではなく 2 つのクエリ。製品データは 1 度だけ取得されます。レビューは 1 度だけ取得されます。重複はありません。

Step 4: 検証

修正を適用した後:

1. 既存テストを実行 — 何も壊れていないことを確認します。
2. レスポンスをチェック — API がまだ同じデータ形状を返すことを確認します。カラム選択とページネーション変更は、特定のフィールドまたは完全な結果セットに依存するクライアントを破損させる可能性があります。
3. 改善を測定 — pg_stat_statements データが利用できる場合は、リセットしてください(SELECT pg_stat_statements_reset();)、トラフィックを実行してから診断クエリを再実行して修正前後を比較します。

さらに読む

• https://neon.com/docs/introduction/network-transfer.md
• https://neon.com/docs/introduction/cost-optimization.md