Schema Design Best Practices
Let the database enforce invariants your business depends on.
How to Use This List
- Review ER diagrams against constraint checklist.
- Block migrations missing FK or CHECK when required.
A - Integrity
- Declare FOREIGN KEY relationships. Document ON DELETE.
- Index foreign key columns. Join and cascade performance.
- Use CHECK for row rules. Not only app validation.
B - Keys
- Pick surrogate or natural deliberately. Document in ADR.
- Avoid composite PKs on wide hot tables. Unless reference data.
- Use IDENTITY for new id columns. Prefer over serial.
C - Evolution
- DDL in versioned migrations. No manual prod hotfix.
- Test rollback or forward-fix path. DDL may not rollback all objects.
- Comment non-obvious columns. Catalog is onboarding doc.
FAQs
Are constraints slow?
Small check cost; huge integrity win.
Related
- Primary & Foreign Keys - FK patterns
- Migrations Basics - shipping DDL
Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.