Topic

N+1 & loading strategy

Definition

An N+1 query pattern issues one query to fetch a list of rows and then one additional query per row to fetch each row’s related data, where a single query with an explicit join or batched load would return the same result.

In simpler words

Fetching 20 tickets and then separately fetching each ticket’s assignee, one at a time, means 21 database round trips instead of one.

Center this on a Ticket→assignee relation that a list query does not join — a natural place N+1 could creep in.

Open official example (opens in new tab)

After this you can

Recognize an N+1 pattern in a code sample
Rewrite a per-row lookup as a single joined query
Explain why the fix scales better as list size grows

Where this pattern would appear here

Definition

TicketsService.list currently selects tickets without their assignee relation, so any code that later loops over the returned rows to fetch each assignee individually would reintroduce the query for every row instead of loading the relation once.

In simpler words

The risk is not the Ticket–User relation itself; it is loading it one row at a time after the fact.

Ticket has a ManyToOne assignee relation to User with a nullable assignee_id foreign key — well suited to a single joined fetch, risky as a per-row lookup.

A naive implementation might call a repository lookup per ticket inside a .map — 20 tickets means 20 extra queries for one list response.

The N+1 mistake

// Wrong — one query per ticket
const tickets = await qb.getMany();
const withAssignee = await Promise.all(
  tickets.map(async (t) => ({
    ...t,
    assignee: t.assigneeId ? await usersRepo.findOne({ where: { id: t.assigneeId } }) : null,
  })),
);

20 tickets means 20 extra round trips, and the count grows with every additional row on the page.

Loading the relation deliberately

Definition

A deliberate loading strategy fetches related rows in the same query as their parent — through a join such as leftJoinAndSelect, or through TypeORM’s relations option — so the total query count stays fixed regardless of how many parent rows are returned.

In simpler words

One query, or one join, for the whole page — not one per row.

qb.leftJoinAndSelect("ticket.assignee", "assignee") adds the relation to the same SQL statement TicketsService.list already builds, with no extra round trip.

Select only the columns the response actually needs when a joined relation gets wide — toResponse() already narrows the ticket shape; do the same for a hydrated assignee.

Right — one query for the page

const qb = this.ticketsRepo
  .createQueryBuilder('ticket')
  .leftJoinAndSelect('ticket.assignee', 'assignee');
// ...same status/q filters, skip/take...
const [rows, total] = await qb.getManyAndCount();

Assignee data now arrives with the tickets in a single round trip, however many rows the page contains.

How to prove N+1 in practice

Definition

You diagnose N+1 by counting SQL statements for a single list request, not by reading TypeScript and hoping. Enable query logging or use a DB/proxy that shows statement counts.

In simpler words

If one GET /tickets produces 1 + pageSize queries, you found it.

Turn on TypeORM logging (or Postgres log_min_duration) in local dev while hitting the list endpoint once.

Compare: without join → many SELECTs on users; with leftJoinAndSelect → one (or two with count) statements.

DataLoader-style batching (conceptual): collect ids during a request, then fetch once — useful in GraphQL; in REST Nest services, prefer an explicit join or In(ids) batch.

Measurement checklist

1. Hit GET /tickets?limit=20 once
2. Count SELECT statements in logs
3. If count ≈ 1 + 20 on users → N+1
4. Add leftJoinAndSelect / relations / In(ids)
5. Re-hit; count should stay ~constant as limit grows

Intermediate habit: measure before and after, do not argue from intuition alone.

Keep in mind

Spot N+1 by counting queries in dev logs against one list response, not by guessing.
Prefer a join or a single relations fetch over a per-row lookup.
Re-check loading strategy whenever a list endpoint gains a new relation.
Prove the fix by re-counting queries after the change.

Test

Check your understanding

At least 10 questions — mix of concept, syntax, practical, and logic. Score ≥80% (enforced by the API) to save progress.

Checking your session…

15 questions · concept 5 · syntax 3 · practical 4 · logic 3

1. What is the N+1 problem?

Concept

Always running exactly two queriesA performance win from independent per-row fetchesFetching a list once then querying each row’s relation separatelyA bug that only affects write endpoints

2. How can TicketsService.list avoid N+1 for assignees?

Syntax

leftJoinAndSelect (or equivalent) the assignee in the same queryLoop findOneBy for each ticket’s assigneeId after the listAdd a database index on assignee_id and keep the loopCache each assignee in Redis after the per-row fetch

3. Why is N+1 painful?

Practical

Each row returns duplicate columns, bloating the payloadIt locks the table for the duration of the listThe single join query becomes too large to parseQuery count grows with page size and latency stacks

4. Does N+1 only affect writes?

Logic

Yes — only endpoints that write related rows are affectedYes — reads use a single join, so they are immuneYes — it only appears inside transactionsNo — list/read endpoints are classic victims

5. Is running exactly two queries always N+1?

Concept

No — two queries can be fine; N+1 grows with N rowsYes — any query beyond the first is an N+1 queryYes — N+1 means exactly N plus one, so two countsYes — unless both queries run in one transaction

6. What does leftJoinAndSelect do?

Practical

Join the related rows but leave the relation property emptyRun a second query to load the relation after the main oneJoin related rows and hydrate them on the entities in one goFilter out parent rows that have no related row

7. When might DataLoader/batching help?

Syntax

When you need to write many rows in a single transactionWhen resolving relations across many parents without per-row queriesWhen a single join already loads every relationWhen caching the entire table in memory at startup

8. How do you notice N+1 in logs?

Logic

A single query containing a LEFT JOINMany INSERT statements after a SELECTOne list query followed by many identical per-id relation queriesOne query that returns duplicated joined rows

9. Does pagination make N+1 harmless?

Concept

Yes — limiting to one page eliminates the extra queriesIt reduces N but the pattern remains; still join/batchYes — offset pagination joins relations automaticallyYes — as long as the page size stays under ten

10. Which fix is wrong?

Practical

Joining the relation with leftJoinAndSelectSelecting only the columns the response needsAdding an index on the foreign key columnTreating per-row relation fetches as a performance win because data stays independent

11. What performance problem is this?

Conceptadvanced

const tickets = await repo.find();
for (const t of tickets) {
  t.assignee = await userRepo.findOneBy({ id: t.assigneeId });
}

N+1 queries: one list query plus one user query per ticketA single joined query, which is optimalA Cartesian product from the loopOnly two queries regardless of ticket count

12. How many queries does this run to load tickets with assignees?

Syntaxintermediate

repo.createQueryBuilder('t')
  .leftJoinAndSelect('t.assignee', 'u')
  .getMany();

One query, because the assignee is joined and hydrated in the same statementOne per ticket, same as the loopTwo: one for tickets, one for all usersZero, because it only builds SQL

13. What does the relations option do here?

Practicalintermediate

repo.find({ relations: { assignee: true } });

Loads the assignee relation in the same query, avoiding N+1Creates the FK column if missingRuns a separate query per relation alwaysFilters out tickets without an assignee

14. Is this the N+1 problem?

Logicadvanced

const tickets = await repo.find();
const users = await userRepo.find();

Yes — any two queries are N+1Yes — because it loads users separatelyNo — it is two queries total regardless of row count, not one-per-rowYes — unless you use a transaction

15. What pattern do these query logs show?

Conceptadvanced

SELECT * FROM tickets;
SELECT * FROM users WHERE id = $1;
SELECT * FROM users WHERE id = $1;
SELECT * FROM users WHERE id = $1;

N+1: a list query followed by repeated per-id relation lookupsA healthy single join queryA Cartesian productA transaction rollback

Checking your session…