1---
2title: Eliminate N+1 Queries with Batch Loading
3impact: MEDIUM-HIGH
4impactDescription: 10-100x fewer database round trips
5tags: n-plus-one, batch, performance, queries
6---
7 
8## Eliminate N+1 Queries with Batch Loading
9 
10N+1 queries execute one query per item in a loop. Batch them into a single query using arrays or JOINs.
11 
12**Incorrect (N+1 queries):**
13 
14```sql
15-- First query: get all users
16select id from users where active = true;  -- Returns 100 IDs
17 
18-- Then N queries, one per user
19select * from orders where user_id = 1;
20select * from orders where user_id = 2;
21select * from orders where user_id = 3;
22-- ... 97 more queries!
23 
24-- Total: 101 round trips to database
25```
26 
27**Correct (single batch query):**
28 
29```sql
30-- Collect IDs and query once with ANY
31select * from orders where user_id = any(array[1, 2, 3, ...]);
32 
33-- Or use JOIN instead of loop
34select u.id, u.name, o.*
35from users u
36left join orders o on o.user_id = u.id
37where u.active = true;
38 
39-- Total: 1 round trip
40```
41 
42Application pattern:
43 
44```sql
45-- Instead of looping in application code:
46-- for user in users: db.query("SELECT * FROM orders WHERE user_id = $1", user.id)
47 
48-- Pass array parameter:
49select * from orders where user_id = any($1::bigint[]);
50-- Application passes: [1, 2, 3, 4, 5, ...]
51```
52 
53Reference: [N+1 Query Problem](https://supabase.com/docs/guides/database/query-optimization)
54