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

Related

• Primary & Foreign Keys - FK patterns
• Migrations Basics - shipping DDL

External Related Links

• Official PostgreSQL Documentation - Central hub for all manuals.
• Awesome Postgres - Curated collection of tools, libraries, extensions, and resources.
• PostgreSQL Wiki - Community FAQ, howtos, and technical notes.
• Learn PostgreSQL (2nd Edition) - Luca Ferrari & Enrico Pirozzi.
• Just Use Postgres! - Denis Magda.

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+.