Éliminer les requêtes N+1 et les sous-requêtes corrélées dans une API Postgres Node.js

Les points d’entrée lents sont souvent imputés à l’infrastructure, mais beaucoup d’entre eux ralentissent en raison de problèmes évidents dans le code du gestionnaire. Cette analyse examine deux points d’entrée Express gérés par Postgres : l’un qui renvoie les commandes d’un utilisateur en 2,24 secondes et un autre destiné aux rapports qui nécessite environ 29 secondes. Aucune solution ne requiert Redis, des serveurs supplémentaires ou une nouvelle architecture ; cinq erreurs courantes expliquent presque tout ce temps de réponse, et en les corrigeant, on parvient à faire passer les deux temps de réponse sous 200 ms. À la fin, vous devriez être capable d’identifier les mêmes schémas dans votre propre code et de savoir quelles formulations de requête utiliser à la place.

Si vous souhaitez une méthode générale pour identifier le goulot d’étranglement avant même de modifier du code, notre guide sur la manière de trouver le véritable goulot d’étranglement dans un endpoint Node.js lent aborde l’aspect du profilage. Ici, l’attention est portée sur les schémas de requêtes spécifiques.

Les deux endpoints examinés en détail

Le premier gestionnaire, GET /orders/:userId, charge les commandes d’un utilisateur, puis parcourt chaque commande pour récupérer ses articles, avant de parcourir chacun de ces articles pour obtenir le produit correspondant. Pour chaque article, il calcule également un hash SHA-256 et l’ajoute en tant que champ etag.

// GET /orders/:userId
app.get('/orders/:userId', async (req, res) => {
  const { userId } = req.params;

  const ordersResult = await pool.query(
    `SELECT * FROM orders WHERE user_id = ${userId} ORDER BY created_at DESC`
  );
  const orders = ordersResult.rows;

  const enrichedOrders = [];

  for (const order of orders) {
    const itemsResult = await pool.query(
      `SELECT * FROM order_items WHERE order_id = ${order.id}`
    );
    const items = itemsResult.rows;

    const enrichedItems = [];
    for (const item of items) {
      const productResult = await pool.query(
        `SELECT * FROM products WHERE id = ${item.product_id}`
      );
      const product = productResult.rows[0];

      const etag = crypto
        .createHash('sha256')
        .update(JSON.stringify({ item, product }))
        .digest('hex');

      enrichedItems.push({ ...item, product, etag });
    }

    enrichedOrders.push({ ...order, items: enrichedItems });
  }

  res.json(enrichedOrders);
});

Le deuxième gestionnaire crée un résumé de l’état des commandes. Il sélectionne chaque commande ainsi que le taux d’annulation par utilisateur, calculé à l’aide d’une sous-requête, puis parcourt les lignes résultantes en JavaScript pour compter le nombre d’ordres par état et stocker ce taux pour chaque utilisateur. Bien que le fragment contienne du SQL, le gestionnaire lui-même est écrit en JavaScript.

app.get('/reports/order-status-summary', async (req, res) => {
  const result = await pool.query(`
    SELECT
      o.id AS order_id,
      o.user_id,
      o.status,
      (
        SELECT round(
          count(*) FILTER (WHERE o2.status = 'cancelled')::numeric
            / NULLIF(count(*), 0),
          4
        )
        FROM orders o2
        WHERE o2.user_id = o.user_id
      ) AS cancellation_rate
    FROM orders o
  `);
  const rows = result.rows;

  const totalsByUser = {};
  const cancellationRateByUser = {};
  for (const row of rows) {
    totalsByUser[row.user_id] = totalsByUser[row.user_id] || {};
    totalsByUser[row.user_id][row.status] =
      (totalsByUser[row.user_id][row.status] || 0) + 1;
    cancellationRateByUser[row.user_id] = Number(row.cancellation_rate) || 0;
  }

  res.json({
    usersProcessed: Object.keys(totalsByUser).length,
    totalsByUser,
    cancellationRateByUser,
  });
});

À première vue, les deux solutions semblent raisonnables. Les problèmes ne deviennent visibles qu’avec des volumes de données réalistes. La base de test contient :

• utilisateurs : 5 000
• produits : 2 000
• commandes : 500 000
• éléments de commande : 1 500 000

Avec de telles dimensions, l’endpoint des commandes met 2,24 secondes à répondre, ce qui est bien trop long pour une simple page « Mes commandes », tandis que l’endpoint du résumé nécessite 29,26 secondes.

Pourquoi GET /orders/:userId met 2,24 secondes

Lorsqu’un utilisateur clique sur « Mes commandes », il ne devrait pas devoir attendre deux secondes devant un indicateur de chargement. Ce retard provient de trois facteurs de gaspillage qui s’accumulent les uns sur les autres.

La recherche initiale parcourt et trie toute la table

Déjà, la première requête présente plusieurs problèmes en seulement quelques lignes.

const ordersResult = await pool.query(
  `SELECT * FROM orders WHERE user_id = ${userId} ORDER BY created_at DESC`
);

• Aucun index n’existe sur orders.user_id, ce qui oblige Postgres à effectuer un parcours séquentiel de toutes les 500 000 lignes afin de trouver environ 102 lignes appartenant à l’utilisateur.
• Rien ne prend en charge non plus l’ordre ORDER BY created_at DESC ; par conséquent, après le parcours, les lignes correspondantes sont triées sans aucun index pour faciliter la tâche.
• La valeur de userId est insérée directement dans la chaîne SQL. Cela crée une faille de type injection SQL, car cette valeur provient directement de l’URL, et cela transforme également chaque requête en une requête textuellement unique.

N+1 imbriqué à deux niveaux

C’est dans les boucles que passe la majeure partie du temps.

for (const order of orders) {
  const itemsResult = await pool.query(`SELECT * FROM order_items WHERE order_id = ${order.id}`);
  ...
  for (const item of items) {
    const productResult = await pool.query(`SELECT * FROM products WHERE id = ${item.product_id}`);

• C’est le schéma classique N+1, imbriqué deux fois. Environ 102 commandes génèrent 102 requêtes pour les articles, et les quelque 500 articles produisent à leur tour environ 500 requêtes pour les produits. Cela représente plus de 600 allers-retours vers Postgres, exécutés les uns après les autres, car chaque await attend le précédent, ce qui entraîne une latence réseau totale à chaque fois. Les mêmes données peuvent être récupérées en une à trois requêtes en utilisant un JOIN ou WHERE id = ANY(...).
• order_items.order_id ne possède aucun index, ce qui oblige chacune des 102 requêtes pour les articles à parcourir 1,5 million de lignes séparément.

Hachage qui bloque inutilement le cycle d’événements

Le dernier problème concerne le travail du processeur plutôt que les opérations d’entrée/sortie.

const etag = crypto.createHash('sha256').update(JSON.stringify({ item, product })).digest('hex');

Le hachage est calculé de manière synchrone pour chaque élément, environ 500 fois par requête. Comme Node.js exécute JavaScript sur un seul thread, chacun de ces calculs bloque la boucle d’événements et retarde toutes les autres requêtes traitées par le processus. Pire encore, cette valeur n’est jamais utilisée pour le cache ou les requêtes conditionnelles, de sorte que ce travail ne sert à rien. Notre article sur la manière dont process.nextTick peut affamer la boucle d’événements de Node.js explique pourquoi bloquer ce thread est si coûteux.

En somme, la latence correspond à la somme séquentielle de plus de 600 allers-retours, de plusieurs scans complets de la table et de centaines de calculs de hachage.

Pourquoi le rapport de synthèse prend environ 29 secondes

Un utilisateur qui ouvre le résumé de sa commande et attend près d’une demi-minute en déduira raisonnablement que la page est défectueuse. La cause réside dans une seule instruction SQL.

SELECT
  o.id AS order_id, o.user_id, o.status,
  (
    SELECT round(
      count(*) FILTER (WHERE o2.status = 'cancelled')::numeric / NULLIF(count(*), 0),
      4
    )
    FROM orders o2
    WHERE o2.user_id = o.user_id
  ) AS cancellation_rate
FROM orders o

1. Une sous-requête corrélée s’exécute une fois par ligne externe. La requête externe renvoie les 500 000 commandes, et pour chacune d’elles, Postgres réexécute la sous-requête sur orders o2 filtrée par le user_id de cette ligne. Avec environ 100 commandes par utilisateur, le même taux d’annulation est recalculé à peu près 100 fois pour chaque utilisateur, ce qui représente au total environ 500 000 exécutions de sous-requête.
2. Aucun index n’est utilisé pour ces recherches répétées. En l’absence d’index sur orders.user_id, chacune de ces exécutions nécessite un balayage complet des données. Un index permettrait de réduire le coût de chaque exécution, mais il ne supprimerait pas la perte inutile liée au calcul du même résultat des centaines de fois.

Le résultat surdimensionné est agrégé une seconde fois en JavaScript. Les 500 000 lignes, chacune contenant le taux calculé, sont transmises par réseau à Node, qui les parcourt ensuite pour créer totalsByUser et cancellationRateByUser. Il s’agit d’une agrégation que Postgres aurait pu effectuer en une seule étape à l’aide de GROUP BY.

Le coût augmente en fonction du mauvais chiffre. Le travail qui devrait être proportionnel aux 5 000 utilisateurs l’est en réalité aux 500 000 commandes, soit un facteur d’environ 100 de plus. C’est pourquoi l’endpoint met entre 26 et 29 secondes pour fonctionner au lieu d’environ un dixième de seconde.

Étape un : ajouter des index correspondant au schéma d’accès

Les index sont généralement la solution la moins chère et la plus efficace en premier recours. Deux index sont nécessaires ici : un index composite sur orders (user_id, created_at DESC) afin que tant le filtrage que le tri soient gérés par l’index, et un index sur order_items (order_id) pour les recherches de produits. La commande ci-dessous crée ces deux index à l’intérieur du conteneur Postgres ; IF NOT EXISTS permet de la réexécuter en toute sécurité.

docker exec slow-api-postgres psql -U postgres -d shop -c "
CREATE INDEX IF NOT EXISTS idx_orders_user_id_created_at ON orders (user_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_order_items_order_id ON order_items (order_id);
"

Le ordre des colonnes dans l’index composite est important. En plaçant user_id en premier, Postgres peut accéder directement aux lignes d’un utilisateur donné, et comme ces lignes sont déjà stockées dans l’ordre created_at DESC au sein de l’index, l’étape de tri disparaît complètement. Sur une table en production très active, envisagez d’utiliser CREATE INDEX CONCURRENTLY afin que la création de l’index ne bloque pas les écritures.

Avec les index en place, les deux gestionnaires peuvent être réécrits pour récupérer des données en masse et laisser la base de données effectuer l’agrégation.

Réécriture de GET /orders/:userId avec des requêtes par lots

La nouvelle version effectue exactement trois requêtes. Elle récupère les commandes de l’utilisateur à l’aide d’une requête paramétrée, collecte leurs IDs et charge tous les articles associés avec order_id = ANY($1), puis élimine les doublons d’IDs de produits à l’aide d’un Set et charge ces produits dans une autre requête. Les résultats sont assemblés en mémoire à l’aide de deux recherches dans des Map, et un retour précoce gère les utilisateurs n’ayant pas de commandes.

app.get('/orders/:userId', async (req, res) => {
  const { userId } = req.params;

  const ordersResult = await pool.query(
    'SELECT * FROM orders WHERE user_id = $1 ORDER BY created_at DESC',
    [userId]
  );
  const orders = ordersResult.rows;

  if (orders.length === 0) {
    return res.json([]);
  }

  const orderIds = orders.map((o) => o.id);
  const itemsResult = await pool.query(
    'SELECT * FROM order_items WHERE order_id = ANY($1)',
    [orderIds]
  );
  const items = itemsResult.rows;

  const productIds = [...new Set(items.map((i) => i.product_id))];
  const productsResult = await pool.query(
    'SELECT * FROM products WHERE id = ANY($1)',
    [productIds]
  );
  const productsById = new Map(productsResult.rows.map((p) => [p.id, p]));

  const itemsByOrderId = new Map();
  for (const item of items) {
    const enrichedItem = { ...item, product: productsById.get(item.product_id) };
    if (!itemsByOrderId.has(item.order_id)) {
      itemsByOrderId.set(item.order_id, []);
    }
    itemsByOrderId.get(item.order_id).push(enrichedItem);
  }

  const enrichedOrders = orders.map((order) => ({
    ...order,
    items: itemsByOrderId.get(order.id) || [],
  }));

  res.json(enrichedOrders);
});

Les modifications, une par une :

1. L’index composite sur orders (user_id, created_at DESC) transforme la clause WHERE associée à ORDER BY en un balayage d’index au lieu d’un balayage séquentiel suivi d’un tri sur 500 000 lignes.

L’index sur order_items (order_id) signifie que la recherche des articles n’implique plus l’examen de 1,5 million de lignes.

La stratégie N+1 est remplacée par trois requêtes : une pour les commandes, une pour tous leurs articles, et une pour tous les produits distincts. Plus de 600 allers-retours sérialisés deviennent ainsi trois.

Des placeholders tels que $1 remplacent l’interpolation de chaînes, ce qui ferme la faille d’injection SQL. Notez que avec node-postgres, une requête paramétrée sans nom est toujours planifiée à chaque exécution ; si vous souhaitez que Postgres réutilise un plan, utilisez une instruction préparée nommée.

Le hash SHA-256 par article, qui n’était pas utilisé, a été supprimé, éliminant ainsi des tâches de blocage inutiles dans le boucle d’événements.

Une précaution pratique : la suppression du champ etag modifie la structure de la réponse. Vérifiez que aucun client ne dépend de ce champ avant de mettre en œuvre ce changement.

Réécriture du rapport avec GROUP BY

Le rapport optimisé exécute deux requêtes d’agrégation ciblées : l’une regroupe par user_id et status pour obtenir des comptages, l’autre regroupe par user_id pour calculer le taux d’annulation. Le JavaScript se contente de reformater les lignes déjà agrégées dans le format de réponse. Comme précédemment, le traitement est assuré par du JavaScript qui intègre du SQL.

// GET /reports/order-status-summary (OPTIMIZED / "after")
app.get('/reports/order-status-summary-optimized', async (req, res) => {
  const statusResult = await pool.query(`
    SELECT user_id, status, count(*)::int AS count
    FROM orders
    GROUP BY user_id, status
  `);

  const rateResult = await pool.query(`
    SELECT
      user_id,
      round(
        count(*) FILTER (WHERE status = 'cancelled')::numeric
          / NULLIF(count(*), 0),
        4
      ) AS cancellation_rate
    FROM orders
    GROUP BY user_id
  `);

  const totalsByUser = {};
  for (const row of statusResult.rows) {
    totalsByUser[row.user_id] = totalsByUser[row.user_id] || {};
    totalsByUser[row.user_id][row.status] = row.count;
  }

  const cancellationRateByUser = {};
  for (const row of rateResult.rows) {
    cancellationRateByUser[row.user_id] = Number(row.cancellation_rate) || 0;
  }

  res.json({
    usersProcessed: Object.keys(totalsByUser).length,
    totalsByUser,
    cancellationRateByUser,
  });
});

Qu’est-ce qui a changé et pourquoi c’est important :

1. La sous-requête corrélée est remplacée par un GROUP BY user_id. Postgres calcule le taux une fois par utilisateur lors d’une seule passe d’agrégation : 5 000 évaluations au lieu d’environ 500 000.
2. Le jeu de résultats passe de 500 000 lignes à environ 5 000 à 20 000. Postgres renvoie une ligne par paire (user_id, status) pour les comptages et une ligne par utilisateur pour le taux, sans colonne calculée redondante.

Deux requêtes indépendantes remplacent une seule requête multi-lignes. Chacune nécessite uniquement un seul balayage de orders, au lieu d’une requête imbriquée dans une autre pour chaque ligne.

Le nœud ne re-fait plus d’agrégation.Aucun cycle de comptage n’est exécuté en JavaScript ; le gestionnaire se contente de mapper les lignes groupées aux clés.

Le temps de traitement passe d’environ 26 à 29 secondes à environ 0,1 à 0,19 seconde, soit entre 150 et 260 fois plus rapide. De plus, le nouveau point de terminaison a été vérifié pour s’assurer qu’il renvoyait les mêmes valeurs totalsByUser et cancellationRateByUser que l’original pour l’ensemble des 5 000 utilisateurs. Cette vérification d’équivalence vaut la peine d’être retenue : chaque fois que vous réécrivez une requête pour améliorer sa vitesse, comparez les résultats avec la version lente avant de la remplacer.

Ces deux méthodes de regroupement lisent toujours l’ensemble de la table orders à chaque requête. Cela est acceptable à cette échelle, mais si la table continue de croître, une seule requête utilisant des clauses FILTER, une vue matérialisée ou une table de synthèse mise à jour périodiquement peut améliorer les performances du rapport.

Points clés

• Indexez les colonnes sur lesquelles vous effectuez des filtres et du tri, et adaptez l’ordre de l’index composite à la requête : d’abord les colonnes d’égalité, puis la colonne de tri.
• Considérez tout appel à await db.query() à l’intérieur d’une boucle comme un signal d’alerte ; remplacez-le par des requêtes en lot utilisant JOIN ou ANY($1).
• Utilisez toujours des requêtes paramétrées pour les valeurs provenant de la requête.
• Faites attention aux sous-requêtes corrélées qui recomptent pour chaque ligne ce qui pourrait être calculé une seule fois par entité.
• Laissez la base de données effectuer le regroupement et ne retournez que les lignes réellement nécessaires à la réponse.

Supprimez les tâches CPU synchrones qui n’ont pas de consommateur, et vérifiez que les points d’entrée optimisés renvoient des résultats identiques avant de procéder au changement.

Lectures complémentaires