Усунення запитів N+1 та корелюованих підзапитів у API Node.js з Postgres

Повільні кінцеві точки часто звинувачують у проблемах інфраструктури, проте багато з них працюють повільно через причини, які очевидні в самому коді обробника. У цьому аналізі розглядаються дві кінцеві точки Express, які працюють з базою даних Postgres: одна повертає інформацію про замовлення користувача за 2,24 секунди, а інша — звіти — за приблизно 29 секунд. Для виправлення проблем не потрібні Redis, додаткові сервери чи нова архітектура; майже весь час затримки спричиняє п’ять поширених помилок, а їх усунення дозволяє скоротити час відповіді обох кінцевих точок до менше ніж 200 мс. До кінця ви зможете розпізнавати такі ж закономірності у власному коді та знати, які формули запитів використовувати замість цього.

Якщо ви шукаєте загальний метод для виявлення «вузького місця» ще до того, як почнете працювати з кодом, наш посібник з пошуку справжнього вузького місця у повільному Node.js ендпоїнті розглядає аспекти профілювання. Тут увага зосереджена на конкретних шаблонах запитів.

Два ендпоїнти під мікроскопом

Перший обробник, GET /orders/:userId, завантажує замовлення користувача, потім проходить по кожному замовленню, щоб отримати його елементи, а далі проходить по кожному елементу, щоб отримати відповідний продукт. Для кожного елемента також обчислюється хеш SHA-256, який додається як поле 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);
});

Другий обробник створює підсумок статусу замовлень. Він вибирає кожне замовлення разом із коефіцієнтом скасування на користувача, обчисленим у підзапиті, а потім перебирає отримані рядки за допомогою JavaScript, щоб підрахувати кількість замовлень за кожним статусом та зберегти цей коефіцієнт для кожного користувача. Хоча у фрагменті коду є SQL, сам обробник написаний на 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,
  });
});

На перший погляд обидва рішення здаються розумними. Проблеми проявляються лише при реальних обсягах даних. У тестовій базі даних є:

• користувачі: 5,000
• товари: 2,000
• замовлення: 500,000
• елементи замовлень: 1,500,000

При такому обсязі ендпоїнт замовлень виконується протягом 2,24 секунди, що є занадто довгим терміном для простого екрана „Мої замовлення“, а ендпоїнт з підсумками — протягом 29,26 секунд.

Чому GET /orders/:userId виконується протягом 2,24 секунд

Користувач, який натискає „Мої замовлення“, не повинен чекати дві секунди під індикатором завантаження. Ця затримка виникає через три окремі причини, які накопичуються одна на одну.

Початковий пошук сканує та сортує всю таблицю

Вже перше запитання містить кілька проблем у лише кількох рядках.

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

• Не існує індексу на orders.user_id, тому Postgres виконує послідовний скан усіх 500 000 рядків, щоб знайти приблизно 102 рядки, які належать користувачеві.
• Також немає нічого, що б підтримувало ORDER BY created_at DESC, тому після сканування відповідні рядки сортуються без жодної допомоги індексу.
• Значення userId вставляється безпосередньо у рядок SQL. Це створює вразливість до втручання через SQL, оскільки значення надходить безпосередньо з URL, а також це перетворює кожен запит на унікальний за текстом запит.

Подвійно вкладений N+1

Саме у циклах витрачається більшість часу.

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

• Це класичний шаблон N+1, вкладений двічі. Приблизно 102 замовлення генерують 102 запити до елементів, а близько 500 елементів створюють ще близько 500 запитів до продуктів. Це означає понад 600 поїздок до Postgres, виконаних одна за одною, оскільки кожен await чекає на попередній, і кожна з них оплачує повну затримку мережі. Ті самі дані можна отримати за допомогою одного–трьох запитів з використанням JOIN або WHERE id = ANY(...).
• order_items.order_id не має індексу, тому кожен з 102 запитів до елементів змушений самостійно прочитати 1,5 мільйона рядків.

Хешування, яке марно блокує цикл подій

Остання проблема полягає у роботі процесора, а не у введенні-виведенні даних.

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

Хеш обчислюється синхронно для кожного елемента, приблизно 500 разів на запит. Оскільки Node.js виконує JavaScript у єдиному потоці, кожне з цих обчислень блокує цикл подій та затримує всі інші запити, якими займається процес. Ще гірше те, що отримане значення ніколи не використовується для кешування чи умовних запитів, тож виконана робота не приносить жодної користі. Наша стаття на тему того, як process.nextTick може блокувати цикл подій пояснює, чому блокування цього потоку є настільки дорогим з точки зору ресурсів.

У сукупності затримка складається із послідовної суми понад 600 поїздок туди-назад, кількох повних сканувань таблиць та сотень обчислень хешів.

Чому звіт підсумків формується приблизно за 29 секунд

Користувач, який відкриває резюме свого замовлення та чекає майже півхвилини, цілком може припустити, що сторінка не працює. Причина криється у одному 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. Корелювана підзапит виконується по одному разу для кожного зовнішнього рядка. Зовнішній запит повертає всі 500 000 замовлень, і для кожного з них Postgres перевиконує підзапит над orders o2, відфільтрований за user_id цього рядка. Якщо у кожного користувача приблизно 100 замовлень, той самий показник скасувань перераховується близько 100 разів для кожного користувача, що в сумі дає близько 500 000 виконань підзапиту.
2. Відсутній індекс для частих пошуків. Без індексу на orders.user_id кожне з цих виконань вимагає повного сканування даних. Індекс міг би зробити кожне виконання дешевшим, але не усунув би надмірних витрат на обчислення однієї й тієї самої відповіді сотні разів.

Результат з надмірним розміром об’єднується ще раз у JavaScript. Усі 500 000 рядків, кожен з яких містить обчислений показник, надсилаються через мережу до Node, який потім проходить їх по черзі, щоб створити totalsByUser та cancellationRateByUser. Це той самий процес об’єднання даних, який Postgres міг би виконати один раз за допомогою GROUP BY.

Витрати зростають пропорційно неправильному числу. Робота, яка мала б бути пропорційною 5 000 користувачам, насправді пропорційна 500 000 замовленням, що дає множник приблизно у 100 разів. Саме тому обробка запиту займає від 26 до 29 секунд замість приблизно десятої частки секунди.

Перший крок: додати індекси, які відповідають шаблону доступу

Індекси зазвичай є найдешевшим та найефективнішим першим рішенням. Тут потрібні два індекси: композитний індекс на orders (user_id, created_at DESC), щоб як фільтрація, так і сортування виконувалися за допомогою цього індексу, та індекс на order_items (order_id) для пошуку елементів. Наведена нижче команда створює обидва індекси всередині контейнера Postgres; оператор IF NOT EXISTS забезпечує можливість безпечного повторного виконання.

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

Порядок стовпців у композитному індексі має значення. Якщо поставити user_id на перше місце, Postgres зможе безпосередньо перейти до рядків певного користувача, а оскільки ці рядки вже зберігаються у порядку created_at DESC всередині індексу, крок сортування зовсім не потрібен. У активній таблиці виробничого середовища розгляньте використання команди CREATE INDEX CONCURRENTLY, щоб процес створення індексу не блокував записи.

Після створення індексів обидва обробники можна переписати так, щоб вони отримували дані пакетно та дозволяли базі даних виконувати агрегацію.

Переписування GET /orders/:userId за допомогою пакетних запитів

Нова версія виконує рівно три запити. Спочатку за допомогою параметризованого запиту отримуються замовлення користувача, збираються їхні ідентифікатори, а потім завантажуються всі пов’язані елементи за умовою order_id = ANY($1). Далі ідентифікатори продуктів усуваються за допомогою структури Set, і ці продукти завантажуються ще одним запитом. Результати об’єднуються в пам’яті за допомогою двох пошуків у структурі Map, а для користувачів без замовлень використовується передчасне повернення.

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

Зміни по черзі:

1. Композитний індекс на orders (user_id, created_at DESC) перетворює умову WHERE разом із ORDER BY на сканування за допомогою індексу замість послідовного сканування та сортування 500 000 рядків.

Індекс на order_items (order_id) означає, що пошук елементів більше не вимагає обробки 1,5 мільйона рядків.

Патерн N+1 перетворюється на три запити: один для замовлень, один для всіх їхніх елементів та один для всіх унікальних продуктів. Більше 600 серіалізованих операцій замінюються на три.

Замінники, такі як $1, замінюють вставку рядків, що закриває можливості для SQL-ін’єкцій. Зауважте, що з node-postgres за кожну виконання все одно планується неназваний параметризований запит; якщо ви хочете, щоб Postgres повторно використовував цей план, скористайтеся названим підготовленим запитом.

Непотрібний хеш SHA-256 для кожного елемента було видалено, що усуває марні операції заблокування з циклу подій.

Одна практична порада: видалення поля etag змінює структуру відповіді. Переконайтеся, що жоден клієнт від нього не залежить, перш ніж впроваджувати цю зміну.

Переписування звіту за допомогою GROUP BY

Оптимізований звіт виконує два цілеспрямовані запити на агрегацію: один групує дані за user_id та status для підрахунку кількостей, інший — за user_id для обчислення коефіцієнта скасувань. JavaScript лише перетворює вже агреговані рядки у формат відповіді. Як і раніше, обробник — це JavaScript, який містить 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,
  });
});

Що змінилося та чому це важливо:

1. Корелюваний підзапит тепер є частиною оператора GROUP BY user_id. Postgres обчислює коефіцієнт один раз на користувача під час одного проходження агрегації: 5 000 обчислень замість приблизно 500 000.
2. Кількість рядків у результаті зменшилася з 500 000 до приблизно 5 000–20 000. Postgres повертає один рядок для кожної пари (user_id, status) для підрахунку кількостей та один рядок на користувача для коефіцієнта скасувань, без дубльованих обчислених стовпців.

Дві незалежні запити замінюють один запит із множенням рядків. Кожен з них потребує лише одного сканування таблиці orders, замість того щоб для кожного рядка виконувати запит, вкладений у інший.

Вузол більше не агрегує дані. У JavaScript не виконується жоден цикл підрахунку; обробник просто відносить груповані рядки до ключів.

Час виконання скоротився з приблизно 26 до 29 секунд до близько 0,1–0,19 секунди — це від 150 до 260 разів швидше. Також було перевірено, що новий ендпоїнт повертає такі самі значення totalsByUser та cancellationRateByUser, як і оригінальний, для всіх 5 000 користувачів. Ця перевірка еквівалентності варта наслідування: щоразу, коли ви переписуєте запит для прискорення, порівнюйте результати з повільною версією перед її заміною.

Обидва способи обробки все ще зчитують усю таблицю orders при кожному запиті. Це прийнятно на такому рівні масштабування, але якщо таблиця продовжуватиме зростати, один запит із використанням конструкцій FILTER, матеріалізованого перегляду чи таблиці підсумків, оновлюваної періодично, може покращити ефективність звіту.

Ключові висновки

• Створюйте індекси для стовпців, за допомогою яких ви фільтруєте та сортуєте дані, та відповідно до цього формуйте складний індекс: спочатку стовпці для порівняння, потім — стовпець сортування.
• Будь-який виклик await db.query() усередині циклу слід вважати ознакою проблеми; замініть його на запити типу JOIN чи ANY($1) у блоці обробки.
• Завжди використовуйте параметризовані запити для значень, які надходять з запиту.
• Стежте за корелюваними підзапитами, які перераховують дані для кожного рядка заново, тоді як їх можна об’єднати лише один раз на елемент.
• Дозвольте базі даних виконувати агрегацію та повертайте лише ті рядки, які дійсно потрібні у відповіді.

Видаліть синхронну роботу CPU, яка не має споживача, та переконайтеся, що оптимізовані кінцеві точки повертають ідентичні результати, перш ніж здійснювати перемикання.