PostgreSQL Query Performance: Common N+1 and Batch Patterns

Application makes N+1 queries (one query per item) instead of batching, causing severe performance degradation as data grows.

Replace N+1 patterns with batch queries:

-- ANTI-PATTERN: N+1 (pseudocode)
-- orders = SELECT * FROM orders WHERE user_id = 1
-- for order in orders:
--   items = SELECT * FROM order_items WHERE order_id = order.id  -- N queries!

-- FIX 1: JOIN
SELECT o.*, oi.*
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.user_id = 1;

-- FIX 2: IN clause for separate queries
SELECT * FROM order_items
WHERE order_id IN (SELECT id FROM orders WHERE user_id = 1);

-- FIX 3: Lateral join for top-N per group
SELECT o.*, latest.*
FROM orders o
CROSS JOIN LATERAL (
  SELECT * FROM order_items oi
  WHERE oi.order_id = o.id
  ORDER BY oi.created_at DESC
  LIMIT 3
) latest;

-- FIX 4: Array aggregation (single row per order)
SELECT o.*,
  array_agg(oi.product_name) as products,
  sum(oi.price) as total
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id;

ORM tips: Use eager loading (Django: select_related/prefetch_related, SQLAlchemy: joinedload/selectinload).

Each database round trip adds latency. 100 items = 101 queries = 101 round trips. A single batch query reduces this to 1-2 round trips regardless of data size.

Optimizing database access patterns