backend-db-performance · diff
git:20260912.33d48b1 to git:20261006.39320f8
35 added, 260 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 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:**
```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 [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;
+ - 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`.
- -- 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;
- ```
+ Logging snippet and the `pg_stat_statements` query: [references/queries.md](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: 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
- }
- ```
+ - **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.
- **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 }
- },
- },
+ 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)
- `)
- ```
+ Bad/good Prisma and the Supabase equivalent: [references/queries.md](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
- ```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);
+ - 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.
- -- Full-text search
- CREATE INDEX idx_posts_search ON posts
- USING GIN(to_tsvector('english', title || ' ' || content));
+ ```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
```
- ### 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])
- }
- ```
+ All six index forms and the Prisma model: [references/schema-and-indexes.md](references/schema-and-indexes.md) §Index types, §Prisma index syntax.
## 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' },
- })
- ```
+ - **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`.
- **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' },
+ take: limit,
+ skip: cursor ? 1 : 0, // skip the cursor row 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 }),
- ])
- ```
+ Select, both pagination styles, batching and count: [references/queries.md](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
- ```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
- }
+ - 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`.
- // Query pattern
- const posts = await db.post.findMany({
- where: { deletedAt: null },
- })
- ```
+ SQL for both shapes, the data-type list and the soft-delete model: [references/schema-and-indexes.md](references/schema-and-indexes.md) §Normalization, §Efficient data types, §Soft deletes.
## Supabase-Specific Optimizations [HIGH freedom]
- ### RLS Performance
+ - **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.
```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()
- )
- );
+ CREATE POLICY "fast_policy" ON posts FOR SELECT USING (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))
- })
- ```
+ Slow/fast/join policies and the Edge Function: [references/schema-and-indexes.md](references/schema-and-indexes.md) §RLS performance, §Edge Functions.
## 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
+ 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](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