Postgres Egress Optimizer

Guide l'utilisateur à travers le diagnostic et la correction des motifs de requête côté application qui causent un transfert de données excessif (egress) 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

Identifie les requêtes qui 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 vidées quand un compute Neon passe à zéro et redémarre. Si les stats sont vides ou si le compute vient de se réveiller :

1. Réinitialise les stats pour démarrer une nouvelle fenêtre de mesure : SELECT pg_stat_statements_reset();
2. Laisse l'application s'exécuter sous un trafic représentatif pendant au moins une heure.
3. Reviens et exécute les requêtes de diagnostic ci-dessous.

Si l'utilisateur a des stats provenant d'une base de données production, utilise-les. S'il n'a pas accès aux stats de production, passe à l'étape 2 et analyse la base de code directement — les motifs au niveau du code sont souvent suffisants pour identifier les plus gros contrevenants.

Diagnostic queries

Exécute celles-ci pour identifier les plus gros contributeurs d'egress. Concentre-toi 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.

Requêtes retournant le plus de lignes au total :

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;

Requêtes retournant le plus de lignes par exécution (SELECT mal délimités, pagination manquante) :

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;

Requêtes appelées le plus fréquemment (candidates pour le 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;

Requêtes les plus longues (pas une mesure directe d'egress, mais aide à identifier les requêtes problématiques lors d'un pic) :

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

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

• Nombre de lignes élevé + lignes larges = plus gros egress. Une requête retournant 1 000 lignes où chaque ligne inclut une colonne JSONB de 50 Ko transfère ~50 Mo par appel.
• Fréquence d'appel extrême 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.
• Croise avec le schéma pour identifier quelles colonnes sont larges. Cherche les colonnes JSONB, TEXT, BYTEA, et les grandes colonnes VARCHAR.

Step 2: Analyze codebase

Pour chaque requête identifiée à l'étape 1, ou pour chaque requête de base de données dans la base de code si aucune stat n'est disponible, vérifie :

• Sélectionne-t-elle uniquement les colonnes dont la réponse a besoin ?
• Retourne-t-elle un nombre limité de lignes (LIMIT/pagination) ?
• Est-elle appelée assez fréquemment pour bénéficier du caching ?
• Récupère-t-elle des données brutes qui sont agrégées dans le code application ?
• Utilise-t-elle un JOIN qui duplique les données parentes sur toutes les lignes enfants ?

Step 3: Fix

Applique la correction appropriée pour chaque problème trouvé. Voici les anti-motifs 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 réseau 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 illimité — chaque nouvelle ligne du tableau augmente le transfert de données à chaque requête. Signale cela quel que soit la taille actuelle du tableau.

C'est facile à manquer car l'application peut fonctionner correctement avec de petits ensembles de données. Mais à grande échelle, un endpoint non paginé retournant 10 000 lignes avec même une largeur de colonne modérée 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 tu ajoutes la pagination, vérifie si le client consommateur supporte déjà les réponses paginées. Si non, choisis des valeurs par défaut sensées et documente 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 motif n'est visible que depuis pg_stat_statements — le code lui-même semble normal.

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

Fix: Ajoute une couche de caching entre l'application et la base de données pour éviter 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, comptages, sommes, groupements) dans le code application. L'ensemble complet du dataset transfère sur le réseau même si le résultat est un petit résumé.

Fix: Pousse l'agrégation dans SQL.

Before: L'application récupère les tables entières 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: Un JOIN entre une table parente large et une table enfant duplique toutes les colonnes parentes sur chaque ligne enfant. Si un produit a 200 avis et la ligne produit inclut une colonne JSONB de 50 Ko, le join envoie 50 Ko × 200 = ~10 Mo pour une seule requête.

C'est distinct du problème SELECT *. Même si tu sélectionnes uniquement les colonnes nécessaires, un JOIN répète quand même les données parentes pour chaque ligne enfant. La correction est structurelle : évite le join complètement.

Before:

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

After (deux requêtes séparées):

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'un JOIN. Les données 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. Exécute les tests existants pour confirmer que rien ne s'est cassé.
2. Vérifie les réponses — assure-toi 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 d'ensembles complets de résultats.
3. Mesure l'amélioration — si les données pg_stat_statements sont disponibles, réinitialise-les (SELECT pg_stat_statements_reset();), laisse le trafic s'exécuter, puis réexécute les requêtes de diagnostic pour comparer avant et après.

Further reading

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