Database Indexing & Query Optimization

1// ============================================
2// Database Indexing — Practical Examples
3// ============================================
4
5// ---------- Index Impact Demonstration ----------
6
7// Table: orders (10 million rows)
8// Columns: id, user_id, product_id, amount, status, created_at
9
10// ❌ WITHOUT INDEX: Full table scan
11const slowQuery = `
12  -- Find all orders for user 12345 in the last 30 days
13  -- Without index: scans ALL 10M rows → ~30 seconds
14  SELECT * FROM orders
15  WHERE user_id = 12345
16  AND created_at > NOW() - INTERVAL '30 days'
17  ORDER BY created_at DESC;
18
19  -- EXPLAIN ANALYZE output:
20  -- Seq Scan on orders
21  --   Filter: (user_id = 12345 AND created_at > '2024-01-01')
22  --   Rows Removed by Filter: 9,999,950
23  --   Execution Time: 28,543.21 ms  ← 28 SECONDS!
24`;
25
26// ✅ WITH COMPOSITE INDEX: B-Tree traversal
27const createIndex = `
28  -- Create a composite index matching the query pattern
29  CREATE INDEX idx_orders_user_date
30  ON orders (user_id, created_at DESC);
31
32  -- Same query WITH index:
33  -- Index Scan using idx_orders_user_date on orders
34  --   Index Cond: (user_id = 12345 AND created_at > '2024-01-01')
35  --   Execution Time: 2.34 ms  ← 2 MILLISECONDS! (12,000x faster)
36`;
37
38// ---------- Index Types in Practice ----------
39
40const indexExamples = `
41  -- 1. SINGLE COLUMN INDEX
42  -- Good for: Simple WHERE clauses
43  CREATE INDEX idx_users_email ON users (email);
44  -- Speeds up: SELECT * FROM users WHERE email = 'jane@example.com';
45
46  -- 2. COMPOSITE (MULTI-COLUMN) INDEX
47  -- Good for: Queries filtering on multiple columns
48  CREATE INDEX idx_orders_user_status
49  ON orders (user_id, status, created_at DESC);
50  -- Speeds up: SELECT * FROM orders
51  --            WHERE user_id = 123 AND status = 'active'
52  --            ORDER BY created_at DESC;
53  -- ⚠️ Column order matters! This index does NOT help with:
54  -- WHERE status = 'active' (user_id must come first)
55
56  -- 3. COVERING INDEX (includes all needed columns)
57  CREATE INDEX idx_orders_covering
58  ON orders (user_id, created_at DESC)
59  INCLUDE (amount, status);
60  -- The DB never needs to read the main table!
61  -- This is called an "index-only scan" — fastest possible
62
63  -- 4. PARTIAL INDEX
64  -- Only index rows matching a condition
65  CREATE INDEX idx_orders_active
66  ON orders (user_id, created_at DESC)
67  WHERE status = 'active';
68  -- Smaller index, faster lookups, only for active orders
69  -- Great when most queries filter by status = 'active'
70
71  -- 5. UNIQUE INDEX
72  CREATE UNIQUE INDEX idx_users_email_unique ON users (email);
73  -- Enforces uniqueness AND provides fast lookups
74
75  -- 6. GIN INDEX (for JSONB / full-text search)
76  CREATE INDEX idx_products_metadata ON products USING GIN (metadata);
77  -- Speeds up: SELECT * FROM products
78  --            WHERE metadata @> '{"color": "red"}';
79
80  -- 7. BRIN INDEX (Block Range Index — for time-series)
81  CREATE INDEX idx_events_time ON events USING BRIN (created_at);
82  -- Very small index, perfect for naturally ordered data
83  -- 1000x smaller than B-Tree for time-series tables
84`;
85
86// ---------- Query Optimization Patterns ----------
87
88// Pattern 1: Use EXPLAIN ANALYZE to understand query performance
89const explainQuery = `
90  EXPLAIN ANALYZE
91  SELECT u.name, COUNT(o.id) as order_count, SUM(o.amount) as total
92  FROM users u
93  JOIN orders o ON o.user_id = u.id
94  WHERE o.created_at > NOW() - INTERVAL '90 days'
95  GROUP BY u.id, u.name
96  HAVING COUNT(o.id) > 5
97  ORDER BY total DESC
98  LIMIT 20;
99
100  -- Look for:
101  -- ❌ Seq Scan (full table scan — needs index)
102  -- ❌ Hash Join on large tables (consider index for join column)
103  -- ❌ Sort with high cost (consider index with matching ORDER BY)
104  -- ✅ Index Scan or Index Only Scan
105  -- ✅ Bitmap Index Scan (for multiple conditions)
106`;
107
108// Pattern 2: Connection pooling for performance
109// ❌ BAD: New connection per request (200ms overhead per connection)
110async function badQueryHandler(req, res) {
111  const conn = await createNewConnection(); // 200ms TCP + TLS + auth
112  const result = await conn.query('SELECT * FROM users WHERE id = $1', [req.params.id]);
113  await conn.close();
114  res.json(result);
115}
116
117// ✅ GOOD: Connection pool (reuse existing connections)
118const { Pool } = require('pg');
119const pool = new Pool({
120  host: 'localhost',
121  database: 'mydb',
122  max: 20,                // Maximum connections in pool
123  idleTimeoutMillis: 30000,
124  connectionTimeoutMillis: 2000,
125});
126
127async function goodQueryHandler(req, res) {
128  // Gets an existing connection from pool (< 1ms)
129  const result = await pool.query(
130    'SELECT * FROM users WHERE id = $1',
131    [req.params.id]
132  );
133  // Connection automatically returned to pool
134  res.json(result.rows[0]);
135}
136
137// Pattern 3: Avoiding N+1 queries
138// ❌ BAD: N+1 query problem
139async function getPostsWithAuthorsBad() {
140  const posts = await db.query('SELECT * FROM posts LIMIT 20'); // 1 query
141  for (const post of posts) {
142    // N additional queries!
143    post.author = await db.query(
144      'SELECT name FROM users WHERE id = $1', [post.user_id]
145    );
146  }
147  return posts; // Total: 21 queries!
148}
149
150// ✅ GOOD: JOIN in a single query
151async function getPostsWithAuthorsGood() {
152  const posts = await db.query(`
153    SELECT p.*, u.name as author_name, u.avatar as author_avatar
154    FROM posts p
155    JOIN users u ON u.id = p.user_id
156    ORDER BY p.created_at DESC
157    LIMIT 20
158  `); // Total: 1 query!
159  return posts;
160}
161
162console.log("Indexing examples configured.");