Postgres Egress Optimizer

Guidez l'utilisateur à travers le diagnostic et la correction des patterns de requêtes au niveau applicatif qui causent un transfert de données (egress) excessif depuis sa base de données Postgres. La plupart des factures d'egress élevées proviennent de l'application qui récupère plus de données qu'elle n'en utilise.

Step 1: Diagnose

Identifiez quelles requêtes transfèrent le plus de données. L'outil principal est l'extension pg_stat_statements.

Check if pg_stat_statements is available

SELECT 1 FROM pg_stat_statements LIMIT 1;

Si cela produit une erreur, l'extension doit être créée :

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Sur Neon, elle est disponible par défaut mais peut nécessiter cette étape CREATE EXTENSION.

Handle empty stats

Les stats sont effacées quand une compute Neon passe à zéro et redémarre. Si les stats sont vides ou la compute a récemment démarré :

1. Réinitialisez les stats pour commencer une nouvelle fenêtre de mesure : SELECT pg_stat_statements_reset();
2. Laissez l'application tourner sous un trafic représentatif pendant au moins une heure.
3. Revenez et exécutez les requêtes de diagnostic ci-dessous.

Si l'utilisateur a des stats d'une base de données de production, utilisez celles-ci. S'il n'a pas accès aux stats de production, passez à Step 2 et analysez le codebase directement — les patterns au niveau du code sont souvent suffisants pour identifier les plus gros contrevenants.

Diagnostic queries

Exécutez celles-ci pour identifier les principaux contributeurs d'egress. Concentrez-vous sur les requêtes qui retournent beaucoup de lignes, retournent des lignes larges (colonnes JSONB, TEXT, BYTEA), ou sont appelées très fréquemment.

Queries returning the most total rows:

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;

Queries returning the most rows per execution (poorly scoped SELECTs, missing pagination):

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;

Most frequently called queries (candidates for caching):

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;

Longest running queries (not a direct egress measure, but helps identify problem queries during a spike):

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;

Interpret the results

Classez les résultats par impact d'egress estimé :

• High row count + wide rows = egress maximal. Une requête retournant 1 000 lignes où chaque ligne inclut une colonne JSONB de 50 KB transfère ~50 MB par appel.
• Extreme call frequency même sur de petites requêtes s'accumule. Une requête appelée 50 000 fois/jour retournant 10 lignes chacune = 500 000 lignes/jour.
• Cross-reference with the schema pour identifier quelles colonnes sont larges. Cherchez les colonnes JSONB, TEXT, BYTEA, et les grandes VARCHAR.

Step 2: Analyze codebase

Pour chaque requête identifiée à Step 1, ou pour chaque requête de base de données du codebase si aucune stat n'est disponible, vérifiez :

• Does it select only the columns the response needs?
• Does it return a bounded number of rows (LIMIT/pagination)?
• Is it called frequently enough to benefit from caching?
• Does it fetch raw data that gets aggregated in application code?
• Does it use a JOIN that duplicates parent data across child rows?

Step 3: Fix

Appliquez la correction appropriée pour chaque problème trouvé. Ci-dessous se trouvent les anti-patterns d'egress les plus courants et comment les corriger.

Unused columns (SELECT *)

Problem: La requête récupère toutes les colonnes mais l'application n'en utilise que quelques-unes. Les grandes colonnes (blobs JSONB, champs TEXT) sont transférées sur le fil et jetées.

Before:

SELECT * FROM products;

After:

SELECT id, name, price, image_urls FROM products;

Missing pagination

Problem: Un endpoint de liste retourne toutes les lignes sans LIMIT. C'est un risque d'egress non borné — chaque nouvelle ligne du tableau augmente le transfert de données à chaque requête. Signalez cela indépendamment de la taille actuelle du tableau.

C'est facile à rater parce que l'application peut fonctionner correctement avec de petits datasets. Mais à grande échelle, un endpoint non paginé retournant 10 000 lignes avec même des largeurs de colonne modérées peut transférer des centaines de mégaoctets par jour.

Before:

SELECT id, name, price FROM products;

After:

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

Quand vous ajoutez la pagination, vérifiez si le client consommateur supporte déjà les réponses paginées. Si non, choisissez des valeurs par défaut sensées et documentez les paramètres de pagination dans l'API.

High-frequency queries on static data

Problem: Une requête est appelée des milliers de fois par jour mais retourne des données qui changent rarement. Chaque appel transfère les mêmes lignes depuis la base de données. Ce pattern n'est visible que depuis pg_stat_statements — le code lui-même semble normal.

Cherchez les requêtes avec des comptes d'appel extrêmement élevés par rapport à autres requêtes. Exemples courants : tables de configuration, listes de catégories, feature flags, définitions de rôles utilisateur.

Fix: Ajoutez une couche de caching entre l'application et la base de données pour que celle-ci évite de frapper la base de données à chaque requête.

Application-side aggregation

Problem: L'application récupère toutes les lignes d'un tableau puis calcule les agrégats (moyennes, comptes, sommes, regroupements) dans le code applicatif. Le dataset complet est transféré sur le fil alors que le résultat est un petit résumé.

Fix: Poussez l'agrégation dans SQL.

Before: L'application récupère des tableaux entiers et agrège dans le code avec des boucles ou .reduce().

After:

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 duplication

Problem: Une JOIN entre un tableau parent large et un tableau child duplique toutes les colonnes parent à travers chaque ligne child. Si un produit a 200 avis et la ligne produit inclut une colonne JSONB de 50 KB, la join envoie ce 50 KB × 200 = ~10 MB pour une seule requête.

Cela est distinct du problème SELECT *. Même si vous sélectionnez seulement les colonnes nécessaires, une JOIN répète toujours les données parent pour chaque ligne child. La correction est structurelle : évitez entièrement la join.

Before:

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

After (two separate queries):

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

Deux requêtes au lieu d'une JOIN. Les données du produit sont récupérées une fois. Les avis sont récupérés une fois. Pas de duplication.

Step 4: Verify

Après avoir appliqué les corrections :

1. Run existing tests pour confirmer que rien n'a cassé.
2. Check the responses — assurez-vous que l'API retourne toujours la même forme de données. Les changements de sélection de colonnes et de pagination peuvent casser les clients qui dépendent de champs spécifiques ou de jeux de résultats complets.
3. Measure the improvement — si des données pg_stat_statements sont disponibles, réinitialisez-les (SELECT pg_stat_statements_reset();), laissez le trafic s'exécuter, puis relancez les requêtes de diagnostic pour comparer avant et après.

Neon Infrastructure as Code (neon.ts)

Les corrections ci-dessus réduisent egress (données transférées hors de Postgres). L'autre grand levier de coût hors production est compute, et vous pouvez le codifier durablement dans neon.ts — le fichier infrastructure-as-code de Neon (voir la skill neon pour la référence complète) — pour que les branches dev, preview, et CI restent bon marché par défaut au lieu de compter sur des flags par branche :

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)

Cela est complémentaire, pas un substitut : les corrections des patterns de requête sont ce qui réduit réellement les frais d'egress, tandis que ces paramètres maintiennent le compute et le stockage non-production sans augmenter silencieusement la même facture. Parce que neon checkout applique la politique quand elle crée une branche, les nouvelles branches dev/preview héritent automatiquement du profil bon marché.

Further reading

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