Design databases with confidence
Stop guessing. Use proven patterns for constraints, pagination, sorting, rollups, SCD, and more. This book translates production lessons into copy‑paste starters with trade‑offs explained.
By Alex Tan Hong Pin · Updated 2025
Who is this for?
Engineers and data‑minded developers who want to build reliable, comprehensible Postgres-backed systems. You’ll learn to encode business rules in the schema, choose the right indexes, and keep queries stable and fast.
What you’ll learn
- Schema patterns that prevent bad states
- Deterministic sorting and cursor pagination
- Rollups and materialized views for fast dashboards
- Slowly changing dimensions (intervals and snapshots)
- Error handling and app error codes in PL/pgSQL
- Polymorphism, trees (ltree), constraints, and more
Inside the book
Part I: Fundamentals
- Introduction
- Getting started
- Tools and environments
- Naming conventions
- Overview
- Schemas and tables
- Common columns
- Base types
- Advanced types
- Enum types
- Domain types
- Composite types
- Functions
- Aggregate functions
- Triggers
- Triggers: audit fields and bulk edits
- Constraints
- Generated columns
- Foreign keys
- Indexes
- Documentation
Part II: Reusable Data Patterns
- Patterns overview
- Constraints
- Status
- Soft delete
- Counter cache
- Categories and subcategories
- Tree paths (ltree)
- Polymorphism
- Money
- Config changesets
- Campaign scheduling
- Slowly changing dimensions
- Slow‑changing dimensions (snapshot approach)
- Sorting
- Cursor pagination
- Rollups
- Compaction
- Advisory locks
- Followers
- Friendship
- Optimistic locking
Part III–V: Application & Ops
- Application overview
- Dynamic WHERE filters
- Dynamic sort
- Pagination and keyset cursors
- Upsert and MERGE
- JSONB CRUD
- JSONB recordset and JSON_TABLE
- UNNEST inline staging
- Seeding data
- Testing strategies
- Fast fixtures and template DB
- Test settings
- Template database cloning
- Dates and time zones
- Changes overview
- Migrations and order
- Table lifecycle
- Column changes and backfills
- Type changes
- Add and validate constraints
- Roles and users
- Debugging overview
- Database and table size
- Slow query triage
- Query helper functions
- Locks and deadlocks
- Autovacuum and bloat
Why this book?
It focuses on decisions: what you gain, what you trade, and how to evolve safely. Examples are small and runnable. Patterns are practical and used in real teams.