Das Eliminieren von N+1-Anfragen und korrelierten Unterabfragen in einer Node.js-Postgres-API

Schlecht performante Endpunkte werden oft der Infrastruktur zugeschrieben, doch bei vielen liegt die Ursache für die Langsamkeit direkt im Code des Handlers. In dieser Analyse werden zwei Express-Endpunkte untersucht, die von Postgres unterstützt werden: Ein Endpunkt liefert die Bestellungen eines Benutzers in 2,24 Sekunden zurück, während ein Berichts-Endpunkt etwa 29 Sekunden benötigt. Keine der Lösungen erfordert Redis, zusätzliche Server oder eine neue Architektur; fünf häufige Fehler sind für fast den gesamten Zeitverlust verantwortlich, und ihre Behebung bringt beide Antwortzeiten unter 200 Millisekunden. Am Ende sollten Sie in Ihrem eigenen Code dieselben Muster erkennen können und wissen, welche Abfragen anstelle dessen verwendet werden sollten.

Falls Sie eine allgemeine Methode suchen, um den Engpass zu ermitteln, bevor Sie Code anfassen, behandelt unser Leitfaden zur Ermittlung des eigentlichen Engpasses in einem langsamen Node.js-Endpoint den Aspekt der Profilierung. Hier liegt der Fokus auf spezifischen Abfragemustern.

Die beiden Endpunkte im Fokus

Der erste Handler, GET /orders/:userId, lädt die Bestellungen eines Benutzers, durchläuft anschließend jede Bestellung, um deren Einzelpositionen abzurufen, und durchläuft danach jede Position, um das entsprechende Produkt zu holen. Für jede Position wird außerdem ein SHA-256-Hash berechnet und als etag-Feld hinzugefügt.

// 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);
});

Der zweite Handler erstellt eine Zusammenfassung des Bestellstatus. Er wählt jede Bestellung zusammen mit einer pro Benutzer berechneten Stornierungsrate aus, die in einer Unterabfrage ermittelt wird, und durchläuft anschließend die resultierenden Zeilen in JavaScript, um die Statuszahlen zu zählen und die Rate pro Benutzer zu speichern. Obwohl der Auszug SQL enthält, ist der Handler selbst in JavaScript geschrieben.

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,
  });
});

Beide Lösungen erscheinen auf den ersten Blick vernünftig. Die Probleme werden erst bei realistischen Datenvolumina sichtbar. Die Testdatenbank enthält:

• Benutzer: 5.000
• Produkte: 2.000
• Bestellungen: 500.000
• Bestellpositionen: 1.500.000

Bei diesem Umfang benötigt das Endpunkt für Bestellungen 2,24 Sekunden – was für ein einfaches „Meine Bestellungen“-Bildschirm viel zu lang ist – und der Zusammenfassungsendpunkt benötigt 29,26 Sekunden.

Warum GET /orders/:userId 2,24 Sekunden dauert

Ein Benutzer, der auf „Meine Bestellungen“ klickt, sollte nicht zwei Sekunden lang vor einem Ladeindikator warten müssen. Die Verzögerung entsteht durch drei verschiedene Faktoren, die sich übereinander addieren.

Die anfängliche Abfrage durchsucht und sortiert die gesamte Tabelle

Bereits die allererste Abfrage weist in nur wenigen Zeilen mehrere Probleme auf.

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

• Es gibt keinen Index auf orders.user_id, weshalb Postgres eine sequenzielle Durchsuchung aller 500.000 Zeilen durchführt, um etwa 102 Zeilen zu finden, die dem Benutzer gehören.
• Auch für ORDER BY created_at DESC gibt es keine Unterstützung, sodass die passenden Zeilen nach der Durchsuchung ohne jeglichen Index zur Unterstützung sortiert werden.
• Der userId wird direkt in den SQL-String eingefügt. Das stellt ein Sicherheitsloch für SQL-Injection dar, da der Wert direkt aus der URL stammt, und es führt außerdem dazu, dass jede Anfrage zu einer textlich einzigartigen Abfrage wird.

Ein doppelt verschachteltes N+1-Problem

Die Schleifen sind der Hauptgrund für die hohe Rechenzeit.

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}`);

• Es handelt sich um das klassische N+1-Muster, das zweimal verschachtelt ist. Etwa 102 Bestellnummern erzeugen 102 Abfragen zu den Artikeln, und die rund 500 Artikel erzeugen weitere etwa 500 Abfragen zu den Produkten. Das bedeutet mehr als 600 Hin- und Rückreisen zum Postgres, die nacheinander ausgeführt werden müssen, da jedes await auf das vorherige wartet und dabei die volle Netzwerklatenz entsteht. Die gleichen Daten können mit einer bis drei Abfragen unter Verwendung von JOIN oder WHERE id = ANY(...) abgerufen werden.
• order_items.order_id verfügt über keinen Index, wodurch jede der 102 Abfragen zu den Artikeln allein 1,5 Millionen Zeilen durchlesen muss.

Hashing, das den Event-Loop unnötig blockiert

Das letzte Problem ist eine CPU-Auslastung statt einer I/O-Belastung.

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

Der Hash wird für jedes Element synchron berechnet, etwa 500 Mal pro Anfrage. Da Node.js JavaScript in einem einzigen Thread ausführt, blockiert jede dieser Berechnungen den Event-Loop und verzögert alle anderen Anfragen, die der Prozess verarbeitet. Noch schlimmer ist, dass der erzeugte Wert niemals zur Caching oder für bedingte Anfragen verwendet wird, wodurch die ganze Arbeit sinnlos ist. Unser Artikel zu wie process.nextTick den Event-Loop lahmlegen kann erklärt, warum das Blockieren dieses Threads so kostspielig ist.

Insgesamt ergibt sich die Latenz aus der seriellen Summe von mehr als 600 Hin- und Rückwegen, mehreren vollständigen Tabellenscans sowie Hunderten von Hash-Berechnungen.

Warum der Zusammenfassungsbericht etwa 29 Sekunden dauert

Ein Benutzer, der die Bestellübersicht öffnet und fast eine halbe Minute wartet, wird zu Recht annehmen, dass die Seite fehlerhaft ist. Die Ursache liegt in einem einzigen SQL-Ausdruck.

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. Eine korrelierte Unterabfrage wird pro äußerer Zeile einmal ausgeführt. Die äußere Abfrage gibt alle 500.000 Bestellungen zurück, und für jede davon führt Postgres die Unterabfrage über orders o2 erneut aus, wobei gefiltert wird nach dem user_id dieser Zeile. Bei etwa 100 Bestellungen pro Benutzer wird derselbe Stornierungsgrad etwa 100 Mal für jeden Benutzer neu berechnet, was insgesamt rund 500.000 Ausführungen der Unterabfrage ergibt.
2. Es gibt keinen Index für die wiederholten Abfragen. Ohne Index auf orders.user_id erfolgt bei jeder dieser Ausführungen eine vollständige Suche. Ein Index würde jede Ausführung kostengünstiger machen, doch er würde den zugrunde liegenden Verschwendungseffekt, denselben Ergebniswert hundertmal zu berechnen, nicht beseitigen.

Das überdimensionierte Ergebnis wird in JavaScript ein zweites Mal aggregiert. Alle 500.000 Zeilen, bei denen jeweils der berechnete Wert wiederholt wird, werden über das Netzwerk an Node gesendet, welches sie anschließend durchläuft, um totalsByUser und cancellationRateByUser zu erstellen. Das ist eine Aggregation, die Postgres bereits mit GROUP BY durchführen könnte.

Die Kosten steigen mit der falschen Zahl an. Die Arbeit, die proportional zu den 5.000 Benutzern sein sollte, ist proportional zu den 500.000 Bestellungen – das entspricht etwa einem Faktor von 100. Deshalb dauert die Verarbeitung 26 bis 29 Sekunden anstelle von etwa einem Zehntel Sekunde.

Schritt eins: Fügen Sie Indizes hinzu, die dem Zugriffsverhalten entsprechen.

Indizes sind in der Regel die günstigste und effektivste erste Lösung. Hier sind zwei erforderlich: ein komplexer Index auf orders (user_id, created_at DESC), damit sowohl das Filtern als auch das Sortieren über den Index abgewickelt werden können, sowie ein Index auf order_items (order_id) für die Abfrage der Artikel. Der folgende Befehl erstellt beide Indizes innerhalb des Postgres-Kontainers; IF NOT EXISTS sorgt dafür, dass er sicher wiederholt werden kann.

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);
"

Die Reihenfolge der Spalten im komplexen Index ist wichtig. Wenn user_id zuerst steht, kann Postgres direkt zu den Zeilen eines bestimmten Benutzers springen, und da diese Zeilen bereits in der Reihenfolge created_at DESC im Index gespeichert sind, entfällt der Sortiervorgang ganz. Bei einer stark genutzten Produktivtabelle sollte man CREATE INDEX CONCURRENTLY verwenden, damit die Erstellung der Indizes keine Schreibvorgänge blockiert.

Sobald die Indizes vorhanden sind, können beide Handler umgeschrieben werden, um Daten in großen Mengen abzurufen und die Aggregation der Datenbank zu überlassen.

Umschreiben von GET /orders/:userId mit gebündelten Abfragen

Die neue Version stellt genau drei Abfragen aus. Zunächst werden die Bestellungen des Benutzers mit einer parametrisierten Abfrage abgerufen, ihre IDs gesammelt und alle damit verbundenen Artikel mit order_id = ANY($1) geladen. Anschließend werden die Produkt IDs mit einem Set entdupliziert, und diese Produkte werden in einer weiteren Abfrage geladen. Die Ergebnisse werden im Speicher mithilfe von zwei Map-Abfragen zusammengefügt, und ein vorzeitiger Rückgabewert kümmert sich um Benutzer ohne Bestellungen.

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);
});

Die Änderungen, eine nach der anderen:

1. Der zusammengesetzte Index auf orders (user_id, created_at DESC) verwandelt die Kombination aus WHERE und ORDER BY in einen Indexscan anstelle eines sequenziellen Scans sowie Sortierens von 500.000 Zeilen.

Durch den Index auf order_items (order_id) müssen Suchvorgänge nach Artikeln nicht mehr 1,5 Millionen Zeilen durchscannen.

Der N+1-Problemfall reduziert sich auf drei Abfragen: eine für die Bestellungen, eine für alle darin enthaltenen Artikel und eine für alle eindeutigen Produkte. Mehr als 600 serialisierte Hin- und Rückwegabfragen werden somit zu drei.

Platzhalter wie $1 ersetzen die Zeichenketteninterpolation und schließen so das SQL-Injection-Sicherheitsloch. Beachten Sie, dass bei node-postgres pro Ausführung weiterhin eine namenlose parametrisierte Abfrage geplant wird; wenn Sie möchten, dass Postgres einen Plan wiederverwendet, verwenden Sie eine benannte vorbereitete Anweisung.

Der nicht genutzte SHA-256-Hash pro Artikel wurde entfernt, wodurch sinnlose Blockierarbeiten im Event-Loop beseitigt werden.

Eine praktische Vorsichtsmaßnahme: Das Entfernen des etag-Feldes ändert die Struktur der Antwort. Stellen Sie sicher, dass kein Client davon abhängt, bevor Sie diese Änderung einpflegen.

Überarbeitung des Berichts mit GROUP BY

Der optimierte Bericht führt zwei gezielte Aggregierungsabfragen aus: Eine gruppiert nach user_id und status, um Zählwerte zu erzeugen, die andere gruppiert nach user_id, um den Stornierungsgrad zu berechnen. Die JavaScript-Logik formatiert lediglich die bereits aggregierten Zeilen in das gewünschte Antwortformat um. Wie zuvor handelt es sich beim Handler um JavaScript, das SQL-Code enthält.

// 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,
  });
});

Was hat sich geändert und warum ist das wichtig:

1. Die korrelierte Unterabfrage wird zu einer GROUP BY user_id. Postgres berechnet den Stornierungsgrad einmal pro Benutzer in einem einzigen Aggregationsvorgang – 5.000 Auswertungen anstelle von etwa 500.000.
2. Die Ergebnismenge verringert sich von 500.000 Zeilen auf etwa 5.000 bis 20.000. Postgres gibt pro (user_id, status)-Paar eine Zeile für die Zählwerte sowie pro Benutzer eine Zeile für den Stornierungsgrad aus, ohne duplizierte berechnete Spalten.

Zwei unabhängige Abfragen ersetzen eine Abfrage mit mehrfacher Auswertung einer Zeile. Jede benötigt lediglich einen einzigen Scan von orders, anstatt für jede Zeile eine Abfrage in einer anderen zu verwenden.

Der Node aggregiert nicht mehr erneut. Es läuft keine Zählungsschleife in JavaScript; der Handler wandelt lediglich die gruppierten Zeilen in Schlüssel um.

Die Laufzeit verringert sich von etwa 26 auf 29 Sekunden auf rund 0,1 bis 0,19 Sekunden – das sind etwa 150 bis 260 Mal schnellere Ergebnisse. Zudem wurde überprüft, dass der neue Endpunkt für alle 5.000 Benutzer dieselben Werte für totalsByUser und cancellationRateByUser wie die ursprüngliche Version zurückgibt. Diese Überprüfung der Äquivalenz lohnt sich: Immer wenn Sie eine Abfrage aus Geschwindigkeitsgründen umschreiben, vergleichen Sie die Ergebnisse mit der langsamen Version, bevor Sie sie ersetzen.

Sowohl die Aggregationen lesen bei jeder Anfrage weiterhin die gesamte orders-Tabelle. Bei dieser Größenordnung ist das in Ordnung, doch wenn die Tabelle weiter wächst, kann eine einzige Abfrage mit FILTER-Klauseln, einer materialisierten Ansicht oder einer periodisch aktualisierten Zusammenfassungstabelle die Berichterstellung verbessern.

Wichtigste Erkenntnisse

• Indizieren Sie die Spalten, nach denen Sie filtern und sortieren, und passen Sie die Reihenfolge des komplexen Indexes der Abfrage an: zuerst Spalten mit Gleichheitsbedingungen, anschließend die Sortierspalte.
• Betrachten Sie jedes await db.query() innerhalb einer Schleife als Warnsignal; ersetzen Sie es durch JOIN- oder ANY($1)-Batchabfragen.
• Verwenden Sie immer parametrisierte Abfragen für Werte, die aus der Anfrage stammen.
• Achten Sie auf korrelierte Unterabfragen, die pro Zeile erneut berechnen, was eigentlich einmal pro Entity gruppiert werden könnte.
• Lassen Sie die Datenbank aggregieren und geben Sie nur die Zeilen zurück, die tatsächlich für die Antwort benötigt werden.

Entfernen Sie die synchronen CPU-Aufgaben, für die es keinen Empfänger gibt, und überprüfen Sie, dass die optimierten Endpunkte vor dem Umstieg identische Ergebnisse liefern.