Database queries are the most common source of production performance issues. Claude Code writes correct queries reliably. Writing queries that stay performant at scale requires more specific prompting.
The query context prompt
Before any query work, give Claude the scale context:
Database context:
- Table sizes: users (~1M rows), orders (~10M rows), order_items (~50M rows)
- Expected query frequency: [reads/second for this endpoint]
- Existing indexes: [list or describe]
- Acceptable query time: [SLA in ms]
Write queries assuming production scale, not dev scale.Without this, Claude writes queries that work correctly on a 100-row dev database and struggle on 10 million rows in production.
N+1 detection
Review this code for N+1 query problems. For each one:
1. Show me where the N+1 occurs
2. How many queries will this execute for a list of [N] items?
3. What's the fix (eager loading, join, or batch query)?
Don't fix yet — just find them.N+1 problems are almost never visible by reading the code — you only notice them when you see 200 queries in a request log. Claude finds them reliably when you ask explicitly.
Index recommendations
Review these queries. For each one:
1. What indexes would this query use?
2. What indexes are missing that would improve it?
3. Write the CREATE INDEX statement
Consider: query frequency, write frequency, index size tradeoffs.
Don't add indexes that would slow writes more than they speed reads.Query optimization
Optimize this query. It's currently slow on tables with [N] rows.
Show me:
1. What's making it slow (full table scan, sort on unindexed column, etc.)
2. The optimized version
3. What index is needed to support the optimization
4. How to verify it's using the index (EXPLAIN output)
Constraints: cannot change the data model, cannot add more than 2 indexes.Safe bulk operations
Write a query to update [condition]. This will affect [estimated rows].
Requirements:
- Batch in groups of [N] to avoid locking the table for long
- Log progress: updated count, estimated remaining
- Idempotent: safe to run twice
- Test the WHERE clause on a small subset before the full run
Include a dry run mode that shows affected count without changing data.Bulk updates that lock tables for minutes are one of the most common causes of production incidents. Batching and dry-run mode prevent both.
Query review checklist
Review this query before I run it in production:
- Is there a WHERE clause? (flag unbounded queries)
- Will it use an index?
- Could it return unexpectedly large result sets?
- Are there any implicit type coercions that would prevent index use?
- Any risk of locking rows other queries need?Database query patterns — N+1 fixes, index strategy, bulk operations — are in the Agent Prompt Playbook. $29.