backend-db-performance · diff
git:20260820.950107c to git:20260821.2a41031
34 added, 10 removed. Audit A to A.
---
name: backend-db-performance
description: >
Optimize slow queries, indexes, and N+1s. Use when "slow query",
"database performance", "add an index", or "N+1". Schema consistency
→ audit-db-schema. RLS access control → plan-rls-audit.
license: MIT
---
# Database Optimization Skill
+ **Degree of freedom: MIXED.** Which query/index/N+1 to fix `[HIGH freedom]`;
+ existing-index probes and EXPLAIN ANALYZE `[LOW freedom — run exactly]`.
+
+ ## How to reason
+
+ 1. **Observe** — EXPLAIN ANALYZE / `pg_stat_statements` / existing `pg_indexes`
+ 2. **Interpret** — seq scan vs N+1 vs over-fetch vs missing pagination
+ 3. **Classify** — add-index / eager-load / narrow-select / paginate / leave-alone
+ 4. **Severity** — write-path timeout outranks a 200ms list page
+
+ ## Worked example
+
+ > **Observe:** `/feed` p95 2.4s; Prisma logs 81 queries; `pg_indexes` has no `idx_posts_user_created`.
+ > **Interpret:** `findMany` posts then per-row `user.findUnique` — N+1; `ORDER BY created_at` is a seq scan.
+ > **Classify:** eager-load `include: { author }` + composite index `(user_id, created_at DESC)`.
+ > **Verify:** EXPLAIN ANALYZE → Index Scan; query count 2; p95 < 200ms. Did not add a duplicate index.
+
+ ## Self-critique before reporting
+
+ - **Existing first** — listed `pg_indexes` / migrations before `CREATE INDEX`
+ - **EXPLAIN** — the claimed winner has ANALYZE output, not intuition
+ - **No duplicate index** — the proposed name was queried and absent
+ - **Right owner** — schema consistency → `audit-db-schema`; RLS access → `plan-rls-audit`
+
Systematic approach to identifying and fixing database performance issues.
## When to Use
- Slow page loads (database bottleneck)
- Query timeout errors
- - N+1 query problems
+ - N+1 queries
- Schema design review
- Index optimization
- Migration planning
- ## CRITICAL: Check Existing First
+ ## CRITICAL: Check Existing First [LOW freedom — run exactly]
**Before ANY optimization, verify current state:**
1. **Check existing indexes:**
```sql
SELECT indexname, indexdef FROM pg_indexes
WHERE schemaname = 'public' AND tablename = 'your_table';
```
2. **Check existing migrations:**
```bash
ls -la supabase/migrations/ | grep -i "index\|optim\|perf"
```
3. **Check if index already exists:**
```sql
SELECT 1 FROM pg_indexes WHERE indexname = 'your_proposed_index';
```
4. **Check Supabase advisors for current issues:**
- Use `get_advisors` MCP tool for performance/security
- Don't re-fix already addressed issues
**Why:** Duplicate indexes waste storage and slow writes. Always verify before adding.
- ## Performance Investigation
+ ## Performance Investigation [HIGH freedom]
### 1. Identify Slow Queries
**Prisma - Enable query logging:**
```typescript
// lib/db.ts
import { PrismaClient } from '@prisma/client'
export const db = new PrismaClient({
log: [
{ emit: 'event', level: 'query' },
],
})
db.$on('query', (e) => {
if (e.duration > 100) { // Log queries > 100ms
console.log(`Slow query (${e.duration}ms):`, e.query)
}
})
```
**Supabase - Query analysis:**
```sql
-- Enable query stats
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- Find slow queries
SELECT
query,
calls,
total_time / calls as avg_time_ms,
rows / calls as avg_rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;
```
### 2. Common Performance Issues
| Issue | Symptom | Solution |
|-------|---------|----------|
| N+1 Queries | Many small queries | Use `include` / eager load |
| Missing Index | Slow WHERE/JOIN | Add index on filtered columns |
| Full Table Scan | Slow on large tables | Add index, limit results |
| Over-fetching | Slow response | Select only needed fields |
| No Pagination | Memory issues | Add cursor/offset pagination |
- ## N+1 Query Fix
+ ## N+1 Query Fix [HIGH freedom]
**Problem: Fetching related data in loop**
```typescript
// Bad - N+1 queries
const posts = await db.post.findMany()
for (const post of posts) {
const author = await db.user.findUnique({ where: { id: post.authorId } })
// 1 query for posts + N queries for authors
}
```
**Solution: Eager loading**
```typescript
// Good - 2 queries total
const posts = await db.post.findMany({
include: {
author: true,
},
})
// Or with select for specific fields
const posts = await db.post.findMany({
include: {
author: {
select: { id: true, name: true, avatar: true }
},
},
})
```
**Supabase equivalent:**
```typescript
// Single query with join
const { data: posts } = await supabase
.from('posts')
.select(`
*,
author:users(id, name, avatar)
`)
```
- ## Index Optimization
+ ## Index Optimization [HIGH freedom]
### When to Add Indexes
**Add index when column is used in:**
- `WHERE` clauses (filtering)
- `JOIN` conditions
- `ORDER BY` clauses
- Unique constraints
**Don't add index when:**
- Table is small (< 1000 rows)
- Column has low cardinality (few unique values)
- Column is rarely queried
- Table has heavy writes
### Index Types
```sql
-- Single column index
CREATE INDEX idx_posts_user_id ON posts(user_id);
-- Composite index (order matters!)
CREATE INDEX idx_posts_user_created ON posts(user_id, created_at DESC);
-- Unique index
CREATE UNIQUE INDEX idx_users_email ON users(email);
-- Partial index (index subset of rows)
CREATE INDEX idx_posts_published ON posts(created_at)
WHERE published = true;
-- GIN index for JSONB/array
CREATE INDEX idx_posts_tags ON posts USING GIN(tags);
-- Full-text search
CREATE INDEX idx_posts_search ON posts
USING GIN(to_tsvector('english', title || ' ' || content));
```
### Prisma Index Syntax
```prisma
model Post {
id String @id @default(cuid())
userId String
title String
status Status
createdAt DateTime @default(now())
user User @relation(fields: [userId], references: [id])
// Single column index
@@index([userId])
// Composite index
@@index([userId, createdAt(sort: Desc)])
// Unique constraint (creates unique index)
@@unique([userId, title])
}
```
- ## Query Optimization Patterns
+ ## Query Optimization Patterns [HIGH freedom]
### Select Only Needed Fields
```typescript
// Bad - fetches all columns
const users = await db.user.findMany()
// Good - fetches only needed
const users = await db.user.findMany({
select: {
id: true,
name: true,
email: true,
},
})
```
### Pagination
**Offset pagination (simple, but slow at high offsets):**
```typescript
const posts = await db.post.findMany({
skip: (page - 1) * limit,
take: limit,
orderBy: { createdAt: 'desc' },
})
```
**Cursor pagination (better for large datasets):**
```typescript
const posts = await db.post.findMany({
take: limit,
skip: cursor ? 1 : 0, // Skip cursor itself
cursor: cursor ? { id: cursor } : undefined,
orderBy: { createdAt: 'desc' },
})
// Return next cursor
const nextCursor = posts.length === limit ? posts[posts.length - 1].id : null
```
### Batch Operations
```typescript
// Bad - individual inserts
for (const item of items) {
await db.item.create({ data: item })
}
// Good - batch insert
await db.item.createMany({
data: items,
skipDuplicates: true,
})
// Good - transaction for related data
await db.$transaction([
db.order.create({ data: order }),
db.orderItem.createMany({ data: orderItems }),
db.inventory.updateMany({ where: {...}, data: {...} }),
])
```
### Count Optimization
```typescript
// Get count without fetching data
const count = await db.post.count({
where: { published: true },
})
// Combined with pagination
const [posts, count] = await db.$transaction([
db.post.findMany({ where, take: limit, skip: offset }),
db.post.count({ where }),
])
```
- ## Schema Design Best Practices
+ ## Schema Design Best Practices [HIGH freedom]
### Normalization vs Denormalization
**Normalize when:**
- Data changes frequently
- Data integrity is critical
- Storage is a concern
**Denormalize when:**
- Read performance is critical
- Data rarely changes
- Complex joins are slow
```sql
-- Normalized (separate table)
CREATE TABLE post_stats (
post_id UUID PRIMARY KEY REFERENCES posts(id),
view_count INT DEFAULT 0,
like_count INT DEFAULT 0
);
-- Denormalized (same table)
ALTER TABLE posts
ADD COLUMN view_count INT DEFAULT 0,
ADD COLUMN like_count INT DEFAULT 0;
```
### Efficient Data Types
```sql
-- Use appropriate types
id UUID DEFAULT gen_random_uuid() -- vs TEXT for IDs
status VARCHAR(20) -- vs unlimited TEXT
price DECIMAL(10,2) -- vs FLOAT for money
created_at TIMESTAMPTZ -- vs TIMESTAMP (include timezone)
-- Use enums for fixed values
CREATE TYPE status AS ENUM ('draft', 'published', 'archived');
```
### Soft Deletes
```prisma
model Post {
id String @id
deletedAt DateTime?
@@index([deletedAt]) // Index for filtering
}
// Query pattern
const posts = await db.post.findMany({
where: { deletedAt: null },
})
```
- ## Supabase-Specific Optimizations
+ ## Supabase-Specific Optimizations [HIGH freedom]
### RLS Performance
```sql
-- Bad: Function call in RLS (slow)
CREATE POLICY "slow_policy" ON posts
FOR SELECT USING (
user_id IN (SELECT user_id FROM team_members WHERE team_id = get_user_team())
);
-- Good: Direct comparison (fast)
CREATE POLICY "fast_policy" ON posts
FOR SELECT USING (user_id = auth.uid());
-- Good: Join-based (when needed)
CREATE POLICY "team_policy" ON posts
FOR SELECT USING (
EXISTS (
SELECT 1 FROM team_members
WHERE team_members.team_id = posts.team_id
AND team_members.user_id = auth.uid()
)
);
```
### Edge Functions for Complex Logic
```typescript
// Move complex aggregations to Edge Functions
// instead of multiple round trips
// supabase/functions/dashboard-stats/index.ts
Deno.serve(async (req) => {
const stats = await supabase.rpc('get_dashboard_stats', {
user_id: userId
})
return new Response(JSON.stringify(stats))
})
```
- ## Query Analysis
+ ## Query Analysis [LOW freedom — run exactly]
### EXPLAIN ANALYZE
```sql
EXPLAIN ANALYZE
SELECT * FROM posts
WHERE user_id = 'abc123'
ORDER BY created_at DESC
LIMIT 20;
-- Look for:
-- - Seq Scan (bad on large tables)
-- - Index Scan (good)
-- - Nested Loop (check if N+1)
-- - High actual time
```
### Key Metrics
| Metric | Target | Action if Exceeded |
|--------|--------|-------------------|
| Query time | < 100ms | Add index, optimize |
| Rows scanned | < 10x returned | Add index |
| Memory usage | < 256MB | Add LIMIT, pagination |
| Connection count | < pool size | Use connection pooling |
- ## Optimization Checklist
+ ## Optimization Checklist [LOW freedom — do not skip]
- [ ] Queries logged and monitored
- [ ] Indexes on filtered/joined columns
- [ ] No N+1 queries (eager loading)
- [ ] Pagination on all list endpoints
- [ ] Select only needed fields
- [ ] Batch operations where possible
- [ ] Connection pooling configured
- [ ] RLS policies optimized
- [ ] EXPLAIN ANALYZE on slow queries
- [ ] Appropriate data types used