Database

# Database

## Query Patterns
- Use parameterized queries for all database operations. Never interpolate user input into SQL.
- Select only needed columns. Avoid `SELECT *` in production queries.
- Use CTEs (Common Table Expressions) for complex queries instead of nested subqueries.
- Batch inserts and updates using `INSERT INTO ... VALUES (...), (...)` or `unnest` patterns.
- Use database-level constraints (NOT NULL, UNIQUE, CHECK, FK) as the source of truth for data integrity.

## N+1 Prevention
- Detect N+1 queries by enabling query logging in development.
- Use eager loading or `JOIN` when fetching parent-child relationships.
- In ORMs, use `include`/`with`/`joinedload` rather than lazy loading in loops.
- For GraphQL, use DataLoader or equivalent batching to collapse repeated queries.
- Add automated N+1 detection in tests using query counting assertions.

## Indexing
- Create indexes on all foreign key columns.
- Create indexes on columns used in `WHERE`, `ORDER BY`, and `JOIN` clauses.
- Use composite indexes for queries that filter on multiple columns. Column order matters.
- Use partial indexes for queries that filter on a fixed condition (e.g., `WHERE deleted_at IS NULL`).
- Monitor slow query logs and add indexes for queries exceeding 100ms.
- Remove unused indexes. They slow down writes and waste storage.

## Migrations
- Migrations are forward-only in production. Never edit a deployed migration.
- Each migration must be reversible with a corresponding down migration.
- Use non-locking migration strategies for large tables (add column, backfill, then add constraint).
- Test migrations against a copy of production data before deploying.
- Name migrations descriptively: `20260115_add_orders_status_index.sql`.

## Schema Design
- Use UUIDs (v7 for sortability) as primary keys for public-facing entities.
- Add `created_at` and `updated_at` timestamps to all tables with database-level defaults.
- Use soft deletes (`deleted_at` timestamp) for user-facing data. Hard delete only for system data.
- Normalize to third normal form by default. Denormalize intentionally with a documented reason.
- Use `ENUM` types or reference tables for fixed value sets, not free-text columns.

## Connection Management
- Use connection pooling (PgBouncer, HikariCP, or ORM-level pooling).
- Set pool size to `(2 * CPU cores) + number of disks` as a starting point.
- Set query timeouts: 5 seconds for web requests, 30 seconds for background jobs.
- Handle connection failures with retry logic and exponential backoff.