Persistence Responsibilities - Learning Module

Loading content...

0/246

Query Optimization

When Correct Code Becomes Slow Code

Your code works perfectly in development with 1,000 test records. You deploy to production with 10 million records, and suddenly that 50ms API response takes 30 seconds. Users leave. Servers struggle. The on-call engineer's phone buzzes at 3 AM.

This is the reality of query performance at scale. Code that's functionally correct can be operationally catastrophic. The persistence layer's responsibility extends beyond simply retrieving data—it must retrieve data efficiently. This means understanding how databases execute queries, how indexes accelerate access, and how subtle code patterns can trigger devastating performance problems.

What You Will Learn

By the end of this page, you will understand how databases execute queries and use indexes, how to design indexes that dramatically improve query performance, how to identify and fix the notorious N+1 query problem, and the techniques for efficient pagination at scale. You'll develop the intuition to recognize and resolve performance issues before they reach production.

Understanding Query Execution

Before optimizing queries, you must understand how databases execute them. When you submit a query, the database doesn't simply "look up" the data—it constructs and executes a query plan.

Query Execution Pipeline:

Parsing: SQL text is validated and converted to an internal representation
Planning/Optimization: The query planner evaluates possible execution strategies and picks the most efficient one
Execution: The chosen plan is executed, accessing storage and applying operations
Result Processing: Results are formatted and returned to the client

The query planner is the most critical component for performance. Given a query, there may be dozens of ways to execute it. The planner uses statistics about table sizes, value distributions, and available indexes to estimate which approach will be fastest.

explain-analyze
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
-- EXPLAIN shows the query plan WITHOUT executing
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;
 
-- EXPLAIN ANALYZE shows the plan AND actual execution statistics
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;
 
-- Example output (PostgreSQL):
/*
Seq Scan on orders  (cost=0.00..458.00 rows=100 width=244)
                    (actual time=0.012..4.523 rows=87 loops=1)
  Filter: (customer_id = 123)
  Rows Removed by Filter: 9913
Planning Time: 0.089 ms
Execution Time: 4.562 ms
*/
 
-- This tells us:
-- "Seq Scan" = Sequential (full table) scan - no index used!
-- "cost=0.00..458.00" = Estimated cost (relative units)
-- "rows=100" = Planner estimated 100 rows (actual was 87)
-- "Rows Removed by Filter: 9913" = Examined 10,000 rows, discarded 9,913
 
-- Now add an index and try again:
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
 
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123;
/*
Index Scan using idx_orders_customer_id on orders 
    (cost=0.29..8.31 rows=100 width=244)
    (actual time=0.015..0.089 rows=87 loops=1)
  Index Cond: (customer_id = 123)
Planning Time: 0.152 ms
Execution Time: 0.119 ms
*/
 
-- Now using "Index Scan" - 38x faster (4.5ms → 0.12ms)
-- Only examined the 87 matching rows, not all 10,000

Common Query Plan Operations
Operation	Description	Performance Implication
Seq Scan	Read every row in the table	Slow for large tables, fast for small ones
Index Scan	Use index to find rows, then fetch from table	Fast for selective queries
Index Only Scan	Satisfy query entirely from index (covering index)	Fastest - no table access needed
Bitmap Index Scan	Build a bitmap of matching rows, then fetch	Good for medium selectivity
Nested Loop Join	For each row in outer, scan inner	Efficient for small result sets
Hash Join	Build hash table of one side, probe with other	Efficient for equality joins
Merge Join	Merge two sorted inputs	Efficient for large, sorted datasets
Sort	Sort rows in memory or on disk	Can be expensive for large sets

EXPLAIN ANALYZE is Your Best Friend

Before tuning any query, run EXPLAIN ANALYZE to understand what the database is actually doing. Don't guess—measure. The difference between what you think the query does and what it actually does can be dramatic. Make reading query plans a regular habit.

Index Design Principles

Indexes are data structures that enable fast data lookup without scanning entire tables. They work like a book's index—instead of reading every page to find a topic, you look in the index, find the page number, and go directly there.

The most common index type is the B-Tree index, which maintains sorted values in a balanced tree structure. B-Trees excel at:

Equality comparisons: WHERE id = 123
Range queries: WHERE created_at > '2024-01-01'
Prefix searches: WHERE name LIKE 'John%'
ORDER BY and GROUP BY operations

Index Design Guidelines

•Index columns used in WHERE, JOIN, ORDER BY — These are the primary candidates. If you filter by status frequently, index it.
•Put high-selectivity columns first in composite indexes — A column is high-selectivity if it has many distinct values. Put it first in the index.
•Consider covering indexes — If an index contains all columns a query needs, the database can satisfy the query from the index alone (index-only scan).
•Mind the write penalty — Every index must be updated on INSERT/UPDATE/DELETE. More indexes = slower writes.
•Use partial indexes for skewed data — Index only the rows that matter. CREATE INDEX ON orders(id) WHERE status = 'pending' if most queries filter for pending.
•Match index order to query order — For ORDER BY created_at DESC, id ASC, the index should be (created_at DESC, id ASC).

index-examples
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- Single column index: Basic lookup optimization
CREATE INDEX idx_orders_status ON orders(status);
-- Speeds up: WHERE status = 'pending'
 
-- Composite index: Multiple columns, order matters!
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);
-- Speeds up: WHERE customer_id = 123
-- Speeds up: WHERE customer_id = 123 AND status = 'shipped'
-- Does NOT speed up: WHERE status = 'shipped' (leftmost column missing)
 
-- Covering index: Includes all columns needed by query
CREATE INDEX idx_orders_covering ON orders(customer_id, status) 
    INCLUDE (total, created_at);
-- For: SELECT total, created_at FROM orders 
--      WHERE customer_id = 123 AND status = 'shipped'
-- Index-only scan: no table access needed!
 
-- Partial index: Only index subset of rows
CREATE INDEX idx_pending_orders ON orders(created_at) 
    WHERE status = 'pending';
-- Small index, very fast for: 
-- SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at
 
-- Expression index: Index on computed value
CREATE INDEX idx_orders_year ON orders(EXTRACT(YEAR FROM created_at));
-- Speeds up: WHERE EXTRACT(YEAR FROM created_at) = 2024
 
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- Speeds up case-insensitive: WHERE LOWER(email) = 'john@example.com'
 
-- Unique index: Enforces uniqueness + provides fast lookup
CREATE UNIQUE INDEX idx_users_email ON users(email);
-- Both constraint enforcement and query optimization
 
-- Text search: GIN index for full-text search / JSONB
CREATE INDEX idx_products_tags ON products USING GIN(tags);
-- For JSONB column with array: WHERE tags @> '["electronics"]'
 
CREATE INDEX idx_articles_search ON articles USING GIN(to_tsvector('english', title || ' ' || body));
-- For full-text search: WHERE to_tsvector('english', ...) @@ to_tsquery('database')

The Index Selection Anti-Pattern

Don't create indexes speculatively "just in case." Each index consumes storage, slows down writes, and must be maintained during operations. Instead, analyze actual query patterns, identify slow queries, and add targeted indexes. Monitor index usage—databases can tell you which indexes are never used and should be dropped.

The N+1 Query Problem

The N+1 query problem is one of the most common and devastating performance anti-patterns in persistence layers. It occurs when code fetches a collection and then makes one additional query for each item in the collection.

The Pattern:

1 query: Get all 100 orders
100 queries: For each order, get the customer
= 101 queries total (1 + N)

With 100 items, this might take 500ms. With 10,000 items, it takes 50 seconds. The problem scales linearly with data size—exactly the opposite of what you want.

n-plus-one-example
TypeScript
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
// ❌ N+1 PROBLEM: 101 queries for 100 orders
async function getOrdersWithCustomersBAD(): Promise<OrderWithCustomer[]> {
    // Query 1: Get all orders
    const orders = await db.query('SELECT * FROM orders LIMIT 100');
    
    // Queries 2-101: Get customer for each order (N queries)
    const results = [];
    for (const order of orders) {
        // This runs 100 times = 100 queries!
        const customer = await db.query(
            'SELECT * FROM customers WHERE id = $1',
            [order.customer_id]
        );
        results.push({ ...order, customer: customer[0] });
    }
    
    return results; // 101 total queries
}
 
// ✅ SOLUTION 1: JOIN - Single query with all data
async function getOrdersWithCustomersJOIN(): Promise<OrderWithCustomer[]> {
    const results = await db.query(`
        SELECT 
            o.*,
            c.id as customer_id,
            c.name as customer_name,
            c.email as customer_email
        FROM orders o
        JOIN customers c ON o.customer_id = c.id
        LIMIT 100
    `);
    
    return results.map(row => ({
        // ... map row to OrderWithCustomer
    }));
}
// 1 query total!
 
// ✅ SOLUTION 2: Batch loading with IN clause
async function getOrdersWithCustomersBATCH(): Promise<OrderWithCustomer[]> {
    // Query 1: Get all orders
    const orders = await db.query('SELECT * FROM orders LIMIT 100');
    
    // Query 2: Get ALL customers at once using IN clause
    const customerIds = [...new Set(orders.map(o => o.customer_id))];
    const customers = await db.query(
        'SELECT * FROM customers WHERE id = ANY($1)',
        [customerIds]
    );
    
    // Build lookup map for O(1) access
    const customerMap = new Map(customers.map(c => [c.id, c]));
    
    // Combine in memory (no more queries)
    return orders.map(order => ({
        ...order,
        customer: customerMap.get(order.customer_id)
    }));
}
// 2 queries total, regardless of order count!
 
// ✅ SOLUTION 3: DataLoader pattern (batches within a request)
import DataLoader from 'dataloader';
 
// DataLoader batches multiple individual requests into single batch query
const customerLoader = new DataLoader<string, Customer>(async (ids) => {
    const customers = await db.query(
        'SELECT * FROM customers WHERE id = ANY($1)',
        [ids]
    );
    
    // Return in same order as requested IDs
    const customerMap = new Map(customers.map(c => [c.id, c]));
    return ids.map(id => customerMap.get(id));
});
 
// Usage: Looks like individual loads, but batches automatically
async function getOrdersWithCustomersDATALOADER(): Promise<OrderWithCustomer[]> {
    const orders = await db.query('SELECT * FROM orders LIMIT 100');
    
    // These 100 load calls are batched into 1 query!
    const customersPromises = orders.map(o => customerLoader.load(o.customer_id));
    const customers = await Promise.all(customersPromises);
    
    return orders.map((order, i) => ({
        ...order,
        customer: customers[i]
    }));
}

When to Use JOINs

•Related data always needed together
•One-to-one or many-to-one relationships
•Need to filter on related table columns
•Complex sorting across relationships
•Aggregations involving multiple tables

When to Use Batch Loading

•Related data only sometimes needed
•One-to-many relationships with many items
•Data from different databases/services
•Need caching at entity level
•GraphQL resolvers (DataLoader pattern)

ORMs and N+1

ORMs can both cause and solve N+1 problems. Lazy loading (fetching related entities on access) easily triggers N+1 if you iterate over a collection. Most ORMs provide eager loading options (e.g., include in Prisma, Include() in Entity Framework, @Fetch(FetchMode.JOIN) in Hibernate) that generate proper JOINs or batch loads. Learn your ORM's loading strategies!

Efficient Pagination Techniques

When result sets are large, you must paginate—returning data in manageable chunks. However, naive pagination approaches break down at scale. The persistence layer should provide pagination that remains efficient regardless of how deep into the result set a user navigates.

Pagination Approaches Compared
Approach	How It Works	Performance at Scale	Best For
OFFSET/LIMIT	`LIMIT 20 OFFSET 1000`	❌ Degrades linearly - must scan all skipped rows	Small datasets, early pages only
Keyset/Cursor	`WHERE id > last_seen_id LIMIT 20`	✅ Constant time - uses index directly	Large datasets, infinite scroll, APIs
Seek Method	Like keyset with composite keys	✅ Constant time even with sorting	Complex sort orders, reliable pagination

pagination-techniques
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
// ❌ OFFSET PAGINATION: Gets slower on later pages
async function getOrdersOffset(page: number, pageSize: number) {
    // Page 1: OFFSET 0 - fast
    // Page 100: OFFSET 1980 - database scans and discards 1980 rows!
    // Page 10000: OFFSET 199980 - painfully slow
    
    const offset = (page - 1) * pageSize;
    return db.query(`
        SELECT * FROM orders 
        ORDER BY created_at DESC 
        LIMIT $1 OFFSET $2
    `, [pageSize, offset]);
}
 
// ✅ KEYSET PAGINATION: Constant speed on any page
async function getOrdersKeyset(
    cursor: string | null, 
    pageSize: number
): Promise<{ orders: Order[]; nextCursor: string | null }> {
    let query: string;
    let params: any[];
    
    if (cursor === null) {
        // First page - no cursor constraint
        query = `
            SELECT * FROM orders 
            ORDER BY created_at DESC, id DESC 
            LIMIT $1
        `;
        params = [pageSize + 1]; // Fetch one extra to detect hasMore
    } else {
        // Subsequent pages - use cursor as boundary
        const { created_at, id } = decodeCursor(cursor);
        query = `
            SELECT * FROM orders 
            WHERE (created_at, id) < ($1, $2)
            ORDER BY created_at DESC, id DESC 
            LIMIT $3
        `;
        params = [created_at, id, pageSize + 1];
    }
    
    const results = await db.query(query, params);
    const hasMore = results.length > pageSize;
    const orders = hasMore ? results.slice(0, pageSize) : results;
    
    const nextCursor = hasMore 
        ? encodeCursor({
            created_at: orders[orders.length - 1].created_at,
            id: orders[orders.length - 1].id
          })
        : null;
    
    return { orders, nextCursor };
}
 
// Cursor encoding: Make opaque to clients
function encodeCursor(data: { created_at: Date; id: string }): string {
    return Buffer.from(JSON.stringify(data)).toString('base64');
}
 
function decodeCursor(cursor: string): { created_at: Date; id: string } {
    return JSON.parse(Buffer.from(cursor, 'base64').toString());
}
 
// Required index for keyset pagination to be fast:
// CREATE INDEX idx_orders_keyset ON orders(created_at DESC, id DESC);
 
// Usage:
// First request: getOrdersKeyset(null, 20)
// Next page: getOrdersKeyset("eyJ...", 20) // Using returned cursor

When OFFSET is Acceptable

OFFSET pagination is fine for small datasets (< 10,000 rows), admin interfaces with few users, or cases where users rarely go past page 5. The simplicity of OFFSET (users understand 'Page 3 of 50') has value. Switch to keyset pagination when you have large datasets, high traffic, or API consumers who may paginate deeply.

Query Optimization Techniques

Beyond indexes and pagination, several techniques help optimize query performance in the persistence layer:

Optimization Strategies

•Projection — Select only the columns you need. SELECT id, name is faster than SELECT *, especially with wide tables or large TEXT/BLOB columns.
•Filtering Early — Apply WHERE conditions as early as possible. Filter before JOIN when feasible (though the query planner often does this automatically).
•Avoiding Function Calls on Indexed Columns — WHERE YEAR(created_at) = 2024 can't use an index on created_at. Use WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01' instead.
•Using EXISTS vs. IN for Subqueries — WHERE EXISTS (SELECT 1 FROM ...) often outperforms WHERE id IN (SELECT ...) for large subquery results.
•Denormalization for Read Performance — Store pre-computed or redundant data to avoid JOINs in read-heavy scenarios. Trade write complexity for read speed.
•Materialized Views — Pre-compute and store complex query results. Refresh periodically. Excellent for dashboards and reports.
•Query Result Caching — Cache frequently-run query results in Redis/Memcached. Invalidate on data changes.
•Connection Pooling — Reuse database connections instead of creating new ones for each query. Dramatic latency reduction.

optimization-examples
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
-- Projection: Select only what you need
-- ❌ Slow: Fetches entire row including large description column
SELECT * FROM products WHERE category = 'electronics';
 
-- ✅ Fast: Only needed columns
SELECT id, name, price FROM products WHERE category = 'electronics';
 
--------------------------------------------------------------
 
-- Sargable predicates (can use index) vs. Non-sargable
-- ❌ Non-sargable: Function prevents index use
SELECT * FROM orders WHERE YEAR(created_at) = 2024;
 
-- ✅ Sargable: Range condition uses index
SELECT * FROM orders 
WHERE created_at >= '2024-01-01' 
  AND created_at < '2025-01-01';
 
-- ❌ Non-sargable: Calculation on column
SELECT * FROM products WHERE price * 1.1 > 100;
 
-- ✅ Sargable: Calculation on constant
SELECT * FROM products WHERE price > 100 / 1.1;
 
--------------------------------------------------------------
 
-- EXISTS vs IN for subqueries
-- Context: Find customers who have placed orders
 
-- ❌ IN: Builds full list, then checks membership
SELECT * FROM customers 
WHERE id IN (SELECT customer_id FROM orders);
 
-- ✅ EXISTS: Stops at first match per customer
SELECT * FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.id
);
 
--------------------------------------------------------------
 
-- Materialized View: Pre-compute expensive aggregations
CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT 
    DATE_TRUNC('day', created_at) as day,
    COUNT(*) as order_count,
    SUM(total) as revenue,
    AVG(total) as avg_order_value
FROM orders
GROUP BY DATE_TRUNC('day', created_at);
 
-- Create index on the materialized view
CREATE INDEX idx_daily_sales_day ON daily_sales_summary(day);
 
-- Query the materialized view (instant!)
SELECT * FROM daily_sales_summary WHERE day >= '2024-01-01';
 
-- Refresh when data changes (can be scheduled)
REFRESH MATERIALIZED VIEW daily_sales_summary;
-- Or concurrently (doesn't lock reads):
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary;

Avoid Premature Optimization

Optimize based on measurements, not assumptions. Use EXPLAIN ANALYZE to confirm actual query behavior. Profile your application to find which queries actually consume time. The query you think is slow may not be the problem. The query you never suspected may be executing 10,000 times per request.

Monitoring and Continuous Analysis

Query optimization isn't a one-time task—it's an ongoing process. As data grows, query patterns change, and new features are added, previously fast queries may become slow. The persistence layer should support continuous monitoring and analysis.

Monitoring Best Practices

•Enable Slow Query Logging — Database configuration to log queries exceeding a threshold (e.g., 100ms). Review periodically.
•Track Query Metrics — Measure query count, duration percentiles (p50, p95, p99), and error rates per endpoint or operation.
•Monitor Index Usage — Track which indexes are used (and which aren't). Unused indexes waste space and slow writes.
•Analyze Lock Contention — Identify queries waiting on locks. Long lock waits indicate concurrency problems.
•Set Up Alerts — Alert on query latency spikes, connection pool exhaustion, and slow query volume increases.
•Regular Query Review — Periodically review top resource-consuming queries. Patterns change as data grows.

monitoring-setup
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
-- PostgreSQL: Enable slow query logging
-- In postgresql.conf:
-- log_min_duration_statement = 100  -- Log queries > 100ms
 
-- Find most time-consuming queries (requires pg_stat_statements)
SELECT 
    query,
    calls,
    total_exec_time / 1000 as total_seconds,
    mean_exec_time as avg_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
 
-- Find unused indexes
SELECT 
    schemaname || '.' || relname AS table,
    indexrelname AS index,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size,
    idx_scan as index_scans
FROM pg_stat_user_indexes
WHERE idx_scan = 0  -- Never used
AND indexrelname NOT LIKE '%_pkey'  -- Exclude primary keys
ORDER BY pg_relation_size(indexrelid) DESC;
 
-- Check table bloat (dead rows needing VACUUM)
SELECT 
    relname AS table,
    n_dead_tup AS dead_rows,
    n_live_tup AS live_rows,
    ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;
 
-- Monitor active locks and waiting queries
SELECT 
    l.pid,
    l.mode,
    l.granted,
    a.query,
    a.wait_event,
    age(now(), a.xact_start) AS transaction_age
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE NOT l.granted  -- Waiting for lock
ORDER BY a.xact_start;

Query Performance Regression Testing:

Consider adding query performance tests to your CI/CD pipeline:

Baseline measurements: Record typical query times on a representative dataset
Threshold checks: Fail builds if new code significantly increases query times
EXPLAIN plan validation: Ensure critical queries use expected indexes
N+1 detection: Count queries per request; alert on unusual increases

Tools like jest-db-profiler, database-specific profiling extensions, or APM solutions (Datadog, New Relic) can automate this.

Summary: Query Optimization

Query optimization transforms correct code into fast code. Let's consolidate the essential techniques:

Key Takeaways

•EXPLAIN ANALYZE reveals truth — Don't guess about query performance. Measure actual execution plans and times.
•Indexes are the primary optimization tool — Proper indexes can make queries 10-100x faster. Design them for your actual query patterns.
•N+1 queries devastate performance at scale — Use JOINs, batch loading, or DataLoader to eliminate them.
•Keyset pagination stays fast — OFFSET pagination degrades linearly. Keyset/cursor pagination is constant time.
•Write sargable predicates — Avoid functions on indexed columns in WHERE clauses.
•Monitor continuously — Slow query logs, metrics, and regular review catch problems before users do.

Module Complete:

You've now completed the first module on Persistence Layer Responsibilities. You understand the four core responsibilities:

Data Storage and Retrieval — The fundamental CRUD operations and their complexities
Abstracting Storage Details — Hiding implementation behind clean interfaces
Transaction Management — Ensuring data consistency with ACID properties
Query Optimization — Making data access fast through indexes, efficient patterns, and monitoring

These foundations prepare you for the next module, where we'll dive deeper into the Repository Pattern—the most common abstraction for implementing persistence layers.

Module Complete

Congratulations! You've mastered the fundamental responsibilities of the persistence layer. These concepts form the foundation for all subsequent modules on persistence patterns, ORM usage, and data modeling. You now have the mental framework to design and evaluate persistence layers in real-world systems.