Database Query Optimization & Indexes
The 3-Line Engineering Rule
Never release a database query without running `EXPLAIN ANALYZE`; eliminate N+1 queries and replace offset pagination with keyset pagination.
Unindexed full table scans lock CPU cores, while offset pagination (`OFFSET 1000000`) forces the database to read and discard a million rows on every click.
Index columns in foreign keys and `WHERE`/`ORDER BY` clauses following the Leftmost Prefix Rule. Use `JOIN FETCH` to eliminate N+1 queries and keyset pagination (`WHERE id > :lastId`) for large datasets.
Architectural Blueprint & Sequence
Hover over nodes to inspect state transitions❌ Seq Scan (10M Rows)
Execution: 4,820ms | CPU: 100%
B-Tree Composite Index
CREATE INDEX idx_user_created ON orders (user_id, created_at DESC)
✅ Index Scan (0.4ms)
Execution: 0.4ms | Zero Seq Scan
N+1 Query Killer
JOIN FETCH / BatchSize = 50
Key Engineering Takeaways
- Always inspect `EXPLAIN (ANALYZE, BUFFERS)`: look for Seq Scans on large tables.
- Composite index column order matters: Equality columns first, Range/Sort columns last.
- Eliminate N+1 queries with eager `JOIN FETCH` or batch loading (`@BatchSize(size = 50)`).
- Avoid `OFFSET 50000`; use Keyset Cursor Pagination (`WHERE (created_at, id) < (:lastDate, :lastId)`).
The Problem & Production Reality
90% of performance issues in software applications are caused by inefficient database queries. Adding more CPU or RAM to a database server is an expensive band-aid for queries performing unindexed sequential scans or executing 1,000 round-trip queries in an N+1 loop.
Anti-Pattern: The N+1 Query & Offset Pagination Trap
Lazy-loading a collection in a loop produces 1 + N queries. Using `LIMIT 20 OFFSET 500000` scans and discards 500,000 rows.
// ❌ ANTI-PATTERN: N+1 queries + slow offset
List<Order> orders = orderRepo.findAll(); // 1 query
for (Order o : orders) { // 1,000 orders
System.out.println(o.getCustomer().getName()); // 1,000 separate SELECT queries!
}
// ❌ SLOW OFFSET: Reads 1,000,020 rows off disk just to return 20!
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 1000000;Production Pattern: JOIN FETCH, Composite Indexes & Keyset Pagination
Fetch associated entities in a single SQL query and paginate using seek/keyset filters on indexed columns.
// ✅ PRODUCTION PATTERN 1: Eliminating N+1 with JOIN FETCH
@Query("SELECT o FROM Order o JOIN FETCH o.customer WHERE o.status = :status")
List<Order> findOrdersWithCustomer(@Param("status") OrderStatus status);
// ✅ PRODUCTION PATTERN 2: Keyset (Cursor) Pagination in SQL
-- Index: CREATE INDEX idx_orders_cursor ON orders (created_at DESC, id DESC);
-- Sub-millisecond execution even on page 10,000:
SELECT id, amount, created_at
FROM orders
WHERE (created_at, id) < (:lastSeenCreatedAt, :lastSeenId)
ORDER BY created_at DESC, id DESC
LIMIT 20;
// PostgreSQL EXPLAIN ANALYZE Verification
-- Look for 'Index Scan using idx_orders_cursor' and 'Rows Removed by Filter: 0'Trade-off Analysis & Staff Recommendations
| Aspect | Pros / Strengths | Cons / Hazards | Staff Recommendation |
|---|---|---|---|
| Too Few vs Too Many Indexes | Indexes make SELECT queries 1,000x faster. | Every index slows down INSERT/UPDATE/DELETE operations and consumes RAM. | Only index columns queried with high frequency; audit and drop unused indexes quarterly. |
| Offset vs Keyset Pagination | Keyset pagination maintains constant O(1) time regardless of depth. | Cannot jump directly to 'Page 47' (infinite scroll / next-page only). | Use Keyset pagination for all feeds, activity logs, and large tables. |