Buổi 12: Database & Performance — Query Optimization 📊

Thành quả: Detect và fix N+1 queries, add caching, benchmark trước/sau với evidence

---

🎯 Mục Tiêu

1. Detect N+1 queries và eager loading với Prisma
2. Database indexing strategy
3. Redis caching pattern
4. API response time benchmarking
5. Frontend performance (lazy loading, code splitting, memoization)

---

📖 Phần 1: N+1 Query Problem

The Problem

// ❌ N+1: 1 query for posts, N queries for authors
const posts = await prisma.post.findMany();
for (const post of posts) {
  post.author = await prisma.user.findUnique({ where: { id: post.authorId } });
  // 100 posts = 101 queries!
}

// ✅ Eager loading: 1 query with JOIN
const posts = await prisma.post.findMany({
  include: {
    author: { select: { id: true, name: true, email: true } },
    tags: true,
    _count: { select: { comments: true } },
  },
});
// 100 posts = 1 query!

Detection

// Enable Prisma query logging
const prisma = new PrismaClient({
  log: [
    { emit: 'event', level: 'query' },
    { emit: 'stdout', level: 'info' },
    { emit: 'stdout', level: 'warn' },
  ],
});

prisma.$on('query', (e) => {
  console.log(`Query: ${e.query}`);
  console.log(`Duration: ${e.duration}ms`);
});

---

📖 Phần 2: Indexing Strategy

-- Columns to index:
-- 1. Foreign keys (always)
-- 2. Columns in WHERE clauses
-- 3. Columns in ORDER BY
-- 4. Columns in JOIN conditions

-- Prisma auto-creates indexes for @id and @unique
-- You need to add for common queries:

-- Posts by status + date (common listing query)
CREATE INDEX idx_posts_status_created ON posts(status, created_at DESC);

-- Posts by author (user's posts page)
CREATE INDEX idx_posts_author ON posts(author_id);

-- Comments by post (post detail page)
CREATE INDEX idx_comments_post ON comments(post_id);

-- Full-text search
CREATE INDEX idx_posts_search ON posts USING GIN(to_tsvector('english', title || ' ' || content));

Prisma Index Definition

model Post {
  // ...
  @@index([authorId])
  @@index([status, createdAt(sort: Desc)])
}

Query Analysis

-- EXPLAIN ANALYZE cho biết query plan
EXPLAIN ANALYZE
SELECT * FROM posts
WHERE status = 'PUBLISHED'
ORDER BY created_at DESC
LIMIT 20;

-- Kết quả:
-- Index Scan (GOOD ✅) vs Sequential Scan (BAD ❌)
-- Actual time: 0.2ms (GOOD) vs 150ms (BAD)

---

📖 Phần 3: Redis Caching

Cache Pattern

// services/cache.service.ts
import { createClient } from 'redis';

const redis = createClient({ url: process.env.REDIS_URL });

export const cache = {
  async get<T>(key: string): Promise<T | null> {
    const data = await redis.get(key);
    return data ? JSON.parse(data) : null;
  },

  async set(key: string, data: any, ttlSeconds: number = 300): Promise<void> {
    await redis.setEx(key, ttlSeconds, JSON.stringify(data));
  },

  async invalidate(pattern: string): Promise<void> {
    const keys = await redis.keys(pattern);
    if (keys.length) await redis.del(keys);
  },
};

// Usage in service
async function getPosts(params: PostQueryParams) {
  const cacheKey = `posts:${JSON.stringify(params)}`;
  
  // Check cache first
  const cached = await cache.get<PaginatedResponse<Post>>(cacheKey);
  if (cached) return cached;

  // Query DB
  const result = await prisma.post.findMany(/* ... */);
  
  // Cache for 5 minutes
  await cache.set(cacheKey, result, 300);
  
  return result;
}

// Invalidate on mutation
async function createPost(data: CreatePostInput) {
  const post = await prisma.post.create({ data });
  await cache.invalidate('posts:*'); // Clear all post caches
  return post;
}

Cache Strategy

Strategy	Use Case	TTL
Cache-Aside	Read-heavy data	5-15 min
Write-Through	Consistency critical	N/A
Write-Behind	High write volume	Async
Cache-Busting	On mutation, clear related	Instant

---

📖 Phần 4: API Benchmarking

Using autocannon

npm install -g autocannon

# Benchmark GET /posts
autocannon -c 100 -d 10 http://localhost:3000/api/v1/posts

# Results:
# Stat    Avg       Min       Max
# Req/s   2,450     1,800     3,200
# Latency 40ms      12ms      180ms

Before/After Comparison

## Performance Report

| Metric | Before | After | Improvement |
|--------|--------|-------|-------------|
| GET /posts avg | 150ms | 12ms | **12.5x** ✅ |
| GET /posts p99 | 800ms | 45ms | **17.8x** ✅ |
| GET /posts/:slug | 80ms | 5ms | **16x** ✅ |
| Requests/sec | 300 | 2,450 | **8.2x** ✅ |

Changes made:
1. Added index on (status, createdAt) → 10x on listing
2. Eager loading → eliminated N+1 (100 queries → 1)
3. Redis cache → 12x on cached responses
4. Select only needed fields → 2x less data transfer

---

📖 Phần 5: Frontend Performance

Lazy Loading Routes

// App.tsx
import { lazy, Suspense } from 'react';

const PostList = lazy(() => import('@/features/posts/PostList'));
const PostDetail = lazy(() => import('@/features/posts/PostDetail'));
const Dashboard = lazy(() => import('@/features/dashboard/Dashboard'));

function App() {
  return (
    <Suspense fallback={<PageSkeleton />}>
      <Routes>
        <Route path="/posts" element={<PostList />} />
        <Route path="/posts/:slug" element={<PostDetail />} />
        <Route path="/dashboard" element={<Dashboard />} />
      </Routes>
    </Suspense>
  );
}

React Memoization

// Expensive list rendering
const PostCard = React.memo(({ post }: { post: Post }) => {
  return (
    <div className="card">
      <h3>{post.title}</h3>
      <p>{post.excerpt}</p>
    </div>
  );
});

// Expensive computation
function useFilteredPosts(posts: Post[], filters: Filters) {
  return useMemo(() => {
    return posts
      .filter(p => !filters.tag || p.tags.includes(filters.tag))
      .sort((a, b) => new Date(b.createdAt) - new Date(a.createdAt));
  }, [posts, filters]);
}

---

🧪 Lab: Performance Optimization Sprint

Task: Measure → Optimize → Measure (60 min)

Step 1: Baseline (10 min)
→ autocannon GET /posts → record avg, p99, req/s
→ Check Prisma query logs → count queries

Step 2: Fix N+1 (10 min)
→ Add include/select to Prisma queries
→ Re-measure → compare

Step 3: Add Indexes (10 min)
→ Add missing indexes
→ EXPLAIN ANALYZE → verify Index Scan
→ Re-measure

Step 4: Add Caching (15 min)
→ Implement Redis cache for GET /posts
→ Re-measure cached vs uncached

Step 5: Document (15 min)
→ Create performance-report.md
→ Include before/after metrics
→ Include evidence (screenshots/output)

---

🎓 Tóm Tắt

Optimization	Impact	Effort
Fix N+1 queries	10-100x	Low
Add DB indexes	5-20x	Low
Redis caching	10-50x	Medium
Select specific fields	2-5x	Low
Frontend code splitting	2-3x initial load	Low

⏭️ Buổi tiếp theo

Buổi 13: CI/CD Pipeline — Automated Testing & Deployment 🚀