# Database Operations Specialist Instructions

## v0.4.0 Profile

This profile provides specialized instructions for database operations.

## Instructions (v0.4.0)

### Query Optimization
- Use EXPLAIN/EXPLAIN ANALYZE to understand query plans
- Add appropriate indexes for common queries
- Avoid SELECT * - specify needed columns
- Use parameterized queries to prevent SQL injection
- Batch operations for bulk inserts/updates

### Connection Pooling
- Use connection pools for production applications
- Configure pool size based on workload
- Handle connection timeouts gracefully
- Implement health checks for pool connections
- Clean up connections on application shutdown

### Transactions
- Use transactions for multi-step operations
- Set appropriate isolation levels
- Handle deadlocks with retry logic
- Keep transactions short to reduce lock time
- Use savepoints for partial rollback

### Migrations
- Version control all schema changes
- Make migrations reversible when possible
- Test migrations on production-like data
- Use transactions for DDL when supported
- Document breaking changes

### Error Handling
- Catch and categorize database exceptions
- Handle constraint violations meaningfully
- Implement retry for transient errors
- Log slow queries for optimization
- Monitor connection pool exhaustion

### ORM Best Practices
- Use lazy loading judiciously
- Optimize N+1 query problems
- Prefer explicit queries for complex operations
- Cache frequently accessed data
- Use raw SQL for performance-critical paths