N+1 Query: The Code Smell Hiding Behind Clean Code

It started with an integration ticket — connect one system to another, pull data from one backend, then push it out to a third party. From the code's point of view, nothing looked off. Variable names were clear, the function structure made sense, there was no five-level nesting, nothing you could immediately point at and call "dirty code". If someone had reviewed that PR, it would most likely have been approved on the spot.

Once the feature launched, two symptoms showed up almost at the same time: sometimes the data being sent was incomplete, and certain endpoints kept timing out.

Checking the logs, what turned up wasn't a business-logic error — it was line after line of database connection errors, far more than any reasonable amount, all hitting the same table, over and over, for one and the same endpoint.

From there it was clear: the problem wasn't in what the code said, it was in how that code talked to the database.

01A Smell You Can't See by Reading

Most people associate a code smell with messy code — duplication, giant functions, nested conditionals. The N+1 query is different. Semantically, the code can be spotless. The problem only appears when the code is executed, not when it's read.

The definition is simple: instead of fetching what it needs in one or two queries, the code runs one query to get a list of records, then N additional queries — one per item — to fetch the related data. With a thousand rows, that's a thousand and one round-trips to the database, when one or two would have been enough.

Three things make this smell dangerous. First, it's invisible in code review, because a single line that touches a relation looks trivial. Second, the cost only shows up once the data grows, and in some cases the damage to the connection pool is far worse than merely being "slow". Third — and this is the part that gets misread most often — the smell is treated as a purely read-side problem, which isn't always the case.

02The Most Common Shape: N+1 on the Read Side

This is the version the internet talks about most, so it's worth a quick mention as a point of comparison. With an ORM like Sequelize, the pattern looks roughly like this:

const vouchers = await Voucher.findAll(); // 1 query

for (const voucher of vouchers) {
  const vendor = await Vendor.findByPk(voucher.vendorId); // 1 extra query per row
  console.log(vendor.name);
}

The fix is common knowledge too: eager loading, so the relation is pulled in the same query through a JOIN.

const vouchers = await Voucher.findAll({
  include: [{ model: Vendor }], // the relation is fetched in the same query
});

for (const voucher of vouchers) {
  console.log(voucher.Vendor.name);
}

That small change cuts N+1 queries down to one or two total. Simple, but the effect is significant — this is the kind of small step that keeps a server calm.

03The Shape Nobody Talks About: N+1 on the Write Side

This part actually matters more, because most searches for "N+1 query" only lead to the read context. The exact same pattern can happen when updating or inserting data in batches — and that's much closer to what caused the integration incident above.

Look at this code for a mass employee update:

const employees = [
  { id: 1, name: "John Doe", salary: 6000000 },
  { id: 5, name: "Jane Smith", salary: 7500000 },
  { id: 8, name: "Andi Wijaya", salary: 5200000 },
  { id: 10, name: "Siti Rahma", salary: 6800000 },
];

for (const emp of employees) {
  await db.query(
    `UPDATE employee SET name = $1, salary = $2 WHERE id = $3`,
    [emp.name, emp.salary, emp.id]
  );
}

At a glance, nothing is wrong. But if employees holds a million rows, that's a million query round-trips to the database, one at a time, sequentially. Every round-trip carries its own overhead — network latency, query planning, and the riskiest one, a connection held for the whole duration. If this runs alongside other traffic hitting the same endpoint, the connection pool can drain fast — and that's exactly what triggered the "incomplete data" and "timeout" symptoms above.

The Fix: Batch Updates with UNNEST

Instead of updating one row at a time, the whole batch is sent in a single query, using PostgreSQL's UNNEST to "unpack" arrays into temporary rows, which are then joined against the target table.

const employees = [
  { id: 1, name: "John Doe", salary: 6000000 },
  { id: 5, name: "Jane Smith", salary: 7500000 },
  { id: 8, name: "Andi Wijaya", salary: 5200000 },
  { id: 10, name: "Siti Rahma", salary: 6800000 },
  // ... can run into the thousands or millions of rows
];

const batchSize = 1000;

for (let i = 0; i < employees.length; i += batchSize) {
  const batch = employees.slice(i, i + batchSize);

  const ids = batch.map((e) => e.id);
  const names = batch.map((e) => e.name);
  const salaries = batch.map((e) => e.salary);

  await db.query(
    `UPDATE employee AS e
     SET name = data.name,
         salary = data.salary
     FROM (
       SELECT * FROM UNNEST($1::int[], $2::text[], $3::numeric[])
         AS t(id, name, salary)
     ) AS data
     WHERE e.id = data.id`,
    [ids, names, salaries]
  );
}

With a batchSize of 1000, a million rows only needs a thousand query round-trips instead of a million. The difference isn't just "faster" — it can be the difference between a server that holds up and one that falls over under load.

There's a trade-off worth naming, so this doesn't read like a magic cure with no strings attached. A batch that's too large can extend lock duration on the table and drive up memory usage, both in the application and in the database, so the optimal batchSize needs to be tested, not guessed at a round thousand. If the stack isn't PostgreSQL, the same principle is reachable other ways — INSERT ... ON DUPLICATE KEY UPDATE in MySQL, or bulkCreate with the updateOnDuplicate option in Sequelize.

04The Numbers Worth Recording

(This section is strongest when filled with real figures from the incident itself — query count before and after, endpoint response time in each condition. The table below works as a skeleton:)

05The Root Cause Isn't Just "Not Optimized Enough"

Stepping back, what happened in this integration wasn't merely a slow-query problem. There was something more fundamental underneath — the solution that got built was never fitted to the real needs of the system consuming it.

In the same case, there was also an API structure sending plenty of fields the receiving system never used at all. That isn't N+1 — that's API contract design — but the root is similar: something was built without genuinely asking what the other side actually needed.

A small snippet like a one-row-at-a-time update loop looks harmless. But without picturing the "what if this runs over a million records, alongside other traffic" scenario, that small snippet is exactly what blows the server up.

One habit worth holding onto from this: before a loop that touches the database gets merged, check how many times it will run once the data is large. The answer often matters more than how neatly that loop is written.

The N+1 query is one of the easiest smells to miss, because it doesn't show up in the code — only in production. If your product is showing similar symptoms — endpoints that suddenly go slow, or data that occasionally arrives incomplete — feel free to get in touch to take a closer look.