The N+1 Insert Loop That Slowed Our API to a Crawl

Source: Dev.to

The Problem

// ❌ The pattern that killed our performance
async function importUsers(users) {
  for (const user of users) {
    await pool.query(
      'INSERT INTO users (name, email) VALUES ($1, $2)',
      [user.name, user.email]
    );
  }
}

For 1 000 users:

• 1 000 round trips to the database
• ~50 ms per query
• ≈ 50 seconds total

Why It Matters

The Correct Pattern: Bulk Insert

// ✅ Single query, any number of rows
async function importUsers(users) {
  const values = users
    .map((_, i) => `($${i * 2 + 1}, $${i * 2 + 2})`)
    .join(', ');

  const params = users.flatMap(u => [u.name, u.email]);

  await pool.query(`INSERT INTO users (name, email) VALUES ${values}`, params);
}

Or Even Better with unnest()

// ✅ PostgreSQL unnest pattern
async function importUsers(users) {
  await pool.query(
    `INSERT INTO users (name, email)
     SELECT * FROM unnest($1::text[], $2::text[])`,
    [users.map(u => u.name), users.map(u => u.email)]
  );
}

The Rule: pg/no-batch-insert-loop

This pattern is detected by the pg/no-batch-insert-loop rule from eslint-plugin-pg.

Let ESLint Catch This

npm install --save-dev eslint-plugin-pg

Use Recommended Config (All Rules)

import pg from 'eslint-plugin-pg';
export default [pg.configs.recommended];

Enable Only This Rule

import pg from 'eslint-plugin-pg';

export default [
  {
    plugins: { pg },
    rules: {
      'pg/no-batch-insert-loop': 'error',
    },
  },
];

What You’ll See

When N+1 loops are detected ESLint reports:

src/import.ts
  5:3  error  ⚡ CWE-1049 | Database query loop detected. | HIGH
                 Fix: Batch queries using arrays and "UNNEST" or a single batched INSERT. |
                 https://use-the-index-luke.com/sql/joins/nested-loops-join-n1-problem

Detection Patterns

The pg/no-batch-insert-loop rule catches:

• query('INSERT...') inside for, for…of, for…in loops
• query('INSERT...') inside while and do…while loops
• query('INSERT...') inside forEach, map, reduce, filter callbacks
• query('UPDATE...') inside any loop construct
• query('DELETE...') inside any loop construct

Other Bulk Patterns

Bulk Update

// ✅ Update with unnest
await pool.query(
  `
  UPDATE users SET status = data.status
  FROM unnest($1::int[], $2::text[]) AS data(id, status)
  WHERE users.id = data.id
  `,
  [ids, statuses]
);

Bulk Delete

// ✅ Delete with ANY
await pool.query('DELETE FROM users WHERE id = ANY($1)', [userIds]);

Quick Install

npm install --save-dev eslint-plugin-pg

import pg from 'eslint-plugin-pg';
export default [pg.configs.recommended];

Turn 50‑second imports into ~100 ms operations.

📦 npm: eslint-plugin-pg
📖 Rule docs: pg/no-batch-insert-loop
⭐ GitHub: https://github.com/ofri-peretz/eslint