neon-postgres-egress-optimizer

まず：Neon の概要、Neon の利用開始方法、Neon 開発のベストプラクティスなどについては、親の neon スキルを使用してください。

neon スキルがインストールされていない場合は、https://neon.com/docs/ai/skills/neon/SKILL.md から取得するか、次のコマンドでインストールしてください。

neon skills -s neon -y

Postgres のエグレス最適化

Postgres データベースからの過剰なデータ転送（エグレス）の原因となる、アプリケーション側のクエリパターンを診断し、修正する手順をユーザーに案内します。エグレス料金が高くなる主な原因は、アプリケーションが使用する量を超えるデータを取得することです。

次の4つの手順を順番に進めてください。まず、最も多くのデータを転送するクエリを診断し、そのクエリに関連するコードベースを分析して、アンチパターンを修正します。最後に、問題が発生していないことと、実際に転送量が減少したことを検証します。

手順 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. 戻って、以下の診断クエリを実行します。

ユーザーが本番データベースの統計情報を持っている場合は、それを使用してください。本番環境の統計情報にアクセスできない場合は、手順 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;

結果を解釈する

推定されるエグレスへの影響に基づいて、調査結果に順位を付けます。

• 行数が多く、行の幅も広い ＝ エグレスが最も大きくなります。各行に 50KB の JSONB 列を含むクエリが 1,000 行を返す場合、呼び出しごとに約 50MB が転送されます。
• 呼び出し頻度が極端に高い場合は、小さなクエリでも積み重なります。1日に 50,000 回呼び出され、毎回 10 行を返すクエリでは、1日に 500,000 行が返されます。
• スキーマと照合して、幅の広い列を特定します。JSONB、TEXT、BYTEA、大きな VARCHAR 列を確認してください。

手順 2：コードベースを分析する

手順 1 で特定した各クエリについて、または統計情報を利用できない場合はコードベース内の各データベースクエリについて、次の点を確認します。

• レスポンスに必要な列だけを選択していますか？
• 返される行数に上限がありますか（LIMIT／ページネーション）？
• キャッシュの効果が得られるほど頻繁に呼び出されていますか？
• 生データを取得して、アプリケーションコードで集計していませんか？
• 子行ごとに親データが重複する JOIN を使用していませんか？

手順 3：修正する

見つかった問題に応じて適切な修正を適用します。以下に、よくあるエグレスのアンチパターンとその修正方法を示します。

未使用の列（SELECT *）

問題： クエリはすべての列を取得しますが、アプリケーションが使用するのはそのうちの一部だけです。大きな列（JSONB のデータ、TEXT フィールド）がネットワーク経由で転送されたあと、破棄されます。

修正： レスポンスに必要な列だけを指定します。

修正前：

SELECT * FROM products;

修正後：

SELECT id, name, price, image_urls FROM products;

ページネーションの欠如

問題： リストエンドポイントが、LIMIT を指定せずにすべての行を返しています。これは上限のないデータ送信によるリスクです。テーブルに行が増えるたびに、リクエストごとのデータ転送量も増加します。現在のテーブルサイズにかかわらず、問題として指摘してください。

データセットが小さければアプリケーションは問題なく動作するため、見落としやすい問題です。しかし、大規模になると、ページネーションのないエンドポイントが 10,000 行を返す場合、列幅がそれほど大きくなくても、1 日に数百メガバイトのデータを転送する可能性があります。

修正方法： ORDER BY と LIMIT／OFFSET を使用して、結果セットに上限を設けます。

修正前：

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 が 1 回のリクエストで送信されます。

これは SELECT * の問題とは異なります。必要な列だけを選択しても、JOIN では子の行ごとに親のデータが繰り返されます。修正には構造の変更が必要です。JOIN 自体を避けてください。

修正方法： JOIN を 2 つのクエリに分け、テーブルごとに 1 つずつ実行します。

修正前：

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;

JOIN を使う 1 つのクエリではなく、2 つのクエリを使います。商品データの取得は 1 回、レビューの取得も 1 回です。重複はありません。

ステップ 4：検証する

修正を適用した後、次のことを行います。

1. 既存のテストを実行し、問題が発生していないことを確認します。
2. レスポンスを確認し、API が引き続き同じデータ形式を返すことを確認します。列の選択やページネーションの変更は、特定のフィールドや結果セット全体に依存するクライアントに影響する可能性があります。
3. 改善を測定します。pg_stat_statements のデータが利用できる場合は、リセットし（SELECT pg_stat_statements_reset();）、トラフィックを流してから診断クエリを再実行し、変更前後を比較します。

Neon Infrastructure as Code（neon.ts）

上記の修正により、Postgres から外部に転送されるデータである エグレス が削減されます。非本番環境のコストを抑えるもう 1 つの大きな要素は コンピューティング です。これを neon.ts（Neon の Infrastructure as Code ファイル。詳しくは neon スキルを参照）に永続的に設定すれば、ブランチごとにフラグを指定しなくても、開発、プレビュー、CI の各ブランチをデフォルトで低コストに保てます。

npm i @neon/config

// neon.ts
import { defineConfig } from "@neon/config/v1";

export default defineConfig({
  branch: (branch) => {
    if (branch.exists || branch.isDefault) return {}; // don't touch prod
    return {
      ttl: "7d", // ephemeral branches auto-expire instead of accruing storage
      postgres: {
        computeSettings: {
          autoscalingLimitMinCu: 0.25, // scale to zero when idle
          autoscalingLimitMaxCu: 1, // cap autoscaling on throwaway branches
          suspendTimeout: "5m",
        },
      },
    };
  },
});

neon config apply   # apply to the current branch (neon deploy is an alias)

これは補完的な設定であり、置き換えではありません。エグレス料金を実際に削減するのはクエリパターンの修正です。一方、これらの設定は、非本番環境のコンピューティングとストレージが気づかないうちに同じ請求額を押し上げるのを防ぎます。neon checkout はブランチ作成時にポリシーを適用するため、新しい開発用／プレビュー用ブランチには、低コストのプロファイルが自動的に引き継がれます。

参考資料

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