Eliminate N+1 Queries with Batch Loading

Why This Matters

The N+1 query problem occurs when you fetch a list of records, then loop through them to fetch related data. This results in 1 query for the list plus N queries for related data - hence "N+1".

This pattern is extremely common and causes:

• Slow API responses: Loading 100 posts with their authors triggers 101 queries
• Database overload: Hundreds of small queries instead of a few efficient ones
• Poor scalability: Performance degrades linearly with data size
• Connection pool exhaustion: Too many concurrent queries exhaust available connections

The Problem

Looping through results and querying related data for each item creates N+1 queries:

-- Query 1: Get all posts
SELECT * FROM posts WHERE published = true;
-- Returns 100 posts

-- Application code loops through posts:
for (post in posts) {
  -- Query 2-101: Get author for each post
  SELECT * FROM users WHERE id = post.author_id;
}

-- Result: 1 + 100 = 101 queries!
-- Time: ~2 seconds for 100 posts

Each iteration triggers a separate database query, creating massive overhead. With 1000 posts, this becomes 1001 queries.

The Solution

Use JOIN to fetch related data in a single query:

-- Single query fetches posts and authors together
SELECT 
  p.*,
  u.id AS author_id,
  u.name AS author_name,
  u.email AS author_email
FROM posts p
JOIN users u ON u.id = p.author_id
WHERE p.published = true;

-- Result: 1 query for all data
-- Time: ~20ms for 100 posts

When you can't use JOIN (e.g., loading data conditionally), use ANY(array) to batch load:

-- Step 1: Get all posts
SELECT * FROM posts WHERE published = true;
-- Returns 100 posts with author_ids: [1, 2, 3, ..., 100]

-- Step 2: Batch load all authors in one query
SELECT * FROM users
WHERE id = ANY(ARRAY[1, 2, 3, ..., 100]);

-- Result: 2 queries total (instead of 101)
-- Time: ~25ms for 100 posts

Common Mistakes

• Using ORMs without eager loading: Many ORMs default to lazy loading, which causes N+1 queries. Always use eager loading or select_related/prefetch_related.
• Not using JOINs when possible: JOINs are faster than multiple queries with ANY(array). Only use ANY(array) when JOINs aren't feasible.
• Loading too much data: When using JOINs, only select the columns you need. Avoid SELECT * to reduce data transfer.
• Forgetting to index foreign keys: JOINs require indexes on foreign key columns. Without indexes, JOINs become slow.

Implementation Examples

Here's how to implement batch loading in common scenarios:

-- Example: Loading posts with comments count
SELECT 
  p.*,
  COUNT(c.id) AS comment_count
FROM posts p
LEFT JOIN comments c ON c.post_id = p.id
WHERE p.published = true
GROUP BY p.id;

-- Example: Loading multiple relationships with ANY(array)
-- Step 1: Get posts
SELECT id, author_id, category_id FROM posts;

-- Step 2: Batch load authors
SELECT * FROM users
WHERE id = ANY(ARRAY[1, 2, 3]);

-- Step 3: Batch load categories
SELECT * FROM categories
WHERE id = ANY(ARRAY[10, 20, 30]);

-- Result: 3 queries total instead of 1 + N + M queries