Database Query Optimization

# 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.