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

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: new PrismaClient({ log: [{ emit: 'event', level: 'query' }] }) and db.$on('query', ...); log anything over 100 ms with its duration.
• Postgres/Supabase: CREATE EXTENSION IF NOT EXISTS pg_stat_statements, then order by total_time DESC, reading total_time / calls and rows / calls.

Logging snippet and the pg_stat_statements query: references/queries.md §Identify slow queries.

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

• Problem: findMany then a per-row findUnique in a loop — 1 + N queries.
• Fix: eager-load the relation; narrow it with a nested select so you do not over-fetch the join.
• Supabase: one .select('*, author:users(id, name, avatar)') call does the join.

// Good - 2 queries total
const posts = await db.post.findMany({
  include: { author: { select: { id: true, name: true, avatar: true } } },
})

Bad/good Prisma and the Supabase equivalent: references/queries.md §N+1 query fix.

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 for one filter; composite when WHERE a = ? ORDER BY b (column order matters: equality first, then sort).
• Unique index for constraints; partial index (WHERE published = true) for a hot subset; GIN for JSONB, arrays and to_tsvector search.
• Prisma: @@index([userId, createdAt(sort: Desc)]), @@unique([...]) mirror the SQL.

CREATE INDEX idx_posts_user_created ON posts(user_id, created_at DESC);  -- composite, order matters
CREATE INDEX idx_posts_published ON posts(created_at) WHERE published = true;  -- partial

All six index forms and the Prisma model: references/schema-and-indexes.md §Index types, §Prisma index syntax.

Query Optimization Patterns [HIGH freedom]

• Select only needed fields — select: { id, name, email } instead of the whole row.
• Pagination — offset (skip/take) is fine for small pages; cursor (cursor: { id }, skip: 1) for large or infinite lists; return nextCursor from the last row.
• Batch — createMany({ skipDuplicates }) instead of a create loop; $transaction([...]) for related writes.
• Count — db.post.count({ where }); pair it with the page query inside one $transaction.

const posts = await db.post.findMany({
  take: limit,
  skip: cursor ? 1 : 0,                        // skip the cursor row itself
  cursor: cursor ? { id: cursor } : undefined,
  orderBy: { createdAt: 'desc' },
})
const nextCursor = posts.length === limit ? posts[posts.length - 1].id : null

Select, both pagination styles, batching and count: references/queries.md §Select only needed fields through §Count optimization.

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: a post_stats(post_id PK → posts, view_count, like_count) table; denormalized: counters as columns on posts.

• Data types — UUID ids, VARCHAR(n) for bounded strings, DECIMAL(10,2) for money, TIMESTAMPTZ always, ENUM for fixed sets.

• Soft deletes — nullable deletedAt with @@index([deletedAt]); every read filters deletedAt: null.

SQL for both shapes, the data-type list and the soft-delete model: references/schema-and-indexes.md §Normalization, §Efficient data types, §Soft deletes.

Supabase-Specific Optimizations [HIGH freedom]

• RLS — a function call or sub-select per row (get_user_team()) is slow; prefer user_id = auth.uid(), or an EXISTS join against the membership table when the rule needs it.
• Edge Functions — move multi-query aggregations into one supabase.rpc('get_dashboard_stats') call behind Deno.serve instead of several client round trips.

CREATE POLICY "fast_policy" ON posts FOR SELECT USING (user_id = auth.uid());

Slow/fast/join policies and the Edge Function: references/schema-and-indexes.md §RLS performance, §Edge Functions.

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 for N+1), high actual time. Annotated version: references/queries.md §EXPLAIN ANALYZE.

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