Schema Design Patterns
6 min read
Good schema design: use TIMESTAMPTZ for all dates, TEXT over VARCHAR, NOT NULL where null is meaningless, and index all foreign key columns. Document tables with COMMENT ON TABLE and name indexes descriptively.
Schema Best Practices
- Always add a primary key to every table
- Use
SERIAL or UUID for IDs - Prefer
TIMESTAMPTZ over TIMESTAMP (timezone-aware) - Use
TEXT over VARCHAR unless you need a length constraint - Normalize to 3NF, then denormalize for performance
- Name indexes consistently:
idx_table_column - Use schemas to group related tables:
public, auth, billing