Database Query Optimization
GitHub Copilot.github/copilot-instructions.mdCoding
Rules for writing efficient SQL and ORM queries: indexing strategy, N+1 prevention, and query planning.
Get this skill
Add the skill file with the CLI, by hand, or over MCP.Save this to your project or home directory so GitHub Copilot can load it.
npx goodprompts add database-query-optimizationSaves to
./.github/copilot-instructions.mdnpx goodprompts add database-query-optimizationSaves to
./.github/copilot-instructions.mdSkill file
.github/copilot-instructions.md1,578 characters# Database Query Optimization
## Query Patterns
- Always use parameterized queries. Never interpolate user input into SQL.
- Select only the columns you need. Never use `SELECT *` in production code.
- Use `LIMIT` on all list queries. Default 20, max 100.
- Prefer cursor-based pagination over OFFSET (OFFSET rescans rows).
## Indexing
- Every foreign key column must have an index.
- Columns in WHERE, ORDER BY, and JOIN conditions need indexes.
- Composite indexes: put equality conditions first, range conditions last.
- Use partial indexes for queries that filter on a constant (e.g., `WHERE status = 'active'`).
- Don't over-index: each index slows writes and uses storage.
## N+1 Prevention
- Use `with` (Drizzle relations) or JOINs to eager-load related data.
- In loops, batch queries with `WHERE id IN (...)` instead of individual lookups.
- Use `Promise.all()` for independent async queries, but cap concurrency.
- Measure: enable query logging in development to count queries per request.
## Transactions
- Use transactions for operations that modify multiple tables atomically.
- Keep transactions short — don't do network calls inside transactions.
- Use serializable isolation only when required (e.g., balance updates).
- Always handle transaction rollback on error.
## Drizzle ORM Specific
- Use `db.query.*` with `with` for reads (relation-based eager loading).
- Use `db.insert/update/delete` for writes (builder pattern).
- Use `sql` template tag for complex queries Drizzle can't express.
- Run `EXPLAIN ANALYZE` on slow queries and add missing indexes.