Claude Coder Database Query Optimization Rules

# Claude Coder Database Query Optimization Rules

## Query Generation Standards
1. ALWAYS use parameterized queries — NEVER string concatenation
2. SELECT only needed columns — no SELECT * in production code
3. Use CTEs for readability; convert to subqueries only if measurably faster
4. JOIN order: smallest table first, filtered table first
5. LIMIT results when the full result set isn't needed
6. Use UNION ALL instead of UNION unless deduplication is required

## Index Recommendations
- For every query you write, suggest appropriate indexes
- Composite index column order: equality conditions first, then range, then sort
- Consider partial indexes for queries with constant WHERE conditions
- Use covering indexes (INCLUDE columns) for frequently accessed columns
- GIN indexes for JSONB, array, and full-text search columns

## Performance Requirements
- Queries returning < 100 rows: target < 10ms
- Queries returning 100-10K rows: target < 100ms
- Analytical queries: target < 5s
- If a query exceeds targets, provide EXPLAIN ANALYZE and optimization steps

## Anti-Patterns (NEVER DO)
- N+1 queries — always use JOINs or subqueries
- Unbounded queries without LIMIT
- Functions on indexed columns in WHERE clauses (kills index usage)
- LIKE '%prefix' searches (use trigram indexes or full-text search)
- Recursive CTEs without termination conditions
- Correlated subqueries when JOINs suffice

## Migration Safety
- All migrations must be reversible (provide UP and DOWN)
- No locking operations on large tables during peak hours
- Use concurrent index creation (CREATE INDEX CONCURRENTLY)
- Test migrations on production-size dataset before applying