PostgreSQL Database Standards
Priority: P0 (CRITICAL)
Rules
- Keep PostgreSQL choices driven by real read/write paths.
- Use explicit migrations; never rely on
synchronize: true in production.
- Treat RLS, constraints, and indexes as first-class schema behavior; use
queryRunner.query() when raw migration SQL is required.
- Enable RLS explicitly in a migration (
ALTER TABLE <table> ENABLE ROW LEVEL SECURITY) and create tenant policies before application rollout.
- Put multi-step consistency inside one transaction boundary.
Verify
- Destructive schema change uses expand -> backfill -> contract rollout.
- Query shape has matching indexes for filter, join, and sort paths.
- Pagination strategy is explicit.
- Transaction boundary matches one business action.
- RLS or tenant predicates are supported by indexes.
Anti-Patterns
- No N+1 queries: Use query builders or eager-load relations instead of lazy-loading in loops.
- No heavy RLS joins: Keep RLS predicates simple; move complex logic to query/view layer.
- No synchronize in production: Always run explicit migrations;
synchronize: true destructive.
- No blind index sprawl: each index needs a query owner.
References
