Skip to content

database.md

Latest commit

 

History

History
43 lines (37 loc) · 2.48 KB

database.md

File metadata and controls

43 lines (37 loc) · 2.48 KB

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.