Database Postgresql

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

• Framework Map
• Implementation Examples
• SQL Gotchas (UPDATE FROM)