backend-db-performancelisted
Install: claude install-skill kensaurus/cursor-kenji
# Database Optimization Skill
Systematic approach to identifying and fixing database performance issues.
## When to Use
- Slow page loads (database bottleneck)
- Query timeout errors
- N+1 query problems
- Schema design review
- Index optimization
- Migration planning
## CRITICAL: Check Existing First
**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
### 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
q