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.