backend-db-performance

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 queries
• Schema design review
• Index optimization
• Migration planning

CRITICAL: Check Existing First [LOW freedom — run exactly]

Before ANY optimization, verify current state:

1. Check existing indexes:

SELECT indexname, indexdef FROM pg_indexes
WHERE schemaname = 'public' AND tablename = 'your_table';

2. Check existing migrations:

ls -la supabase/migrations/ | grep -i "index\|optim\|perf"

3. Check if index already exists:

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 [HIGH freedom]

1. Identify Slow Queries

Prisma - Enable query logging:

// 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:

-- 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

N+1 Query Fix [HIGH freedom]

Problem: Fetching related data in loop

// 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

// 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:

// Single query with join
const { data: posts } = await supabase
 .from('posts')
 .select(`
 *,
 author:users(id, name, avatar)
 `)

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

-- 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

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 [HIGH freedom]

Select Only Needed Fields

// 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):

const posts = await db.post.findMany({
 skip: (page - 1) * limit,
 take: limit,
 orderBy: { createdAt: 'desc' },
})

Cursor pagination (better for large datasets):

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

// 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

// 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 [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

-- 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

-- 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

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 [HIGH freedom]

RLS Performance

-- 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

// 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 [LOW freedom — run exactly]

EXPLAIN ANALYZE

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

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