Database Schema Design
PostgreSQL schema patterns, indexing strategies, and migration workflows that scale from MVP to production.
Schema Conventions
- Use UUID v7 for primary keys (time-sortable, no sequential exposure)
- Every table gets created_at and updated_at timestamps with defaults
- Soft deletes via deleted_at column (nullable timestamp), never hard delete
- Snake_case for all column and table names
- Use enums sparingly -- prefer check constraints for small value sets
- Add NOT NULL constraints by default, make nullable only when needed
- Foreign keys always have ON DELETE behavior defined explicitly
Indexing Strategy
B-tree (Default)
Standard index for equality and range queries. Use for foreign keys, timestamps, and common WHERE clauses.
GIN (Generalized Inverted)
For JSONB fields and full-text search. Essential when querying inside JSON objects or arrays.
Partial Indexes
Create indexes with WHERE conditions (e.g., WHERE deleted_at IS NULL) to reduce index size and speed up common queries.
Composite Indexes
Multi-column indexes for common query patterns. Put equality columns first, range columns last.
Migration Workflow
Generate a new migration file with a timestamp prefix. Each migration has an up() and down() function.
All migrations go through code review. Check for backward compatibility, data loss risks, and lock time.
Run migrations against a copy of production data. Verify performance on realistic table sizes.
Run migrations before code deployment. Use zero-downtime patterns (add column, backfill, then add constraint).
Watch query performance after migration. Check for new slow queries and unexpected lock contention.
Need help designing a database schema? Get in touch and we will help you build a solid foundation.