Skip to content
PostgreSQL in Production: Data Modeling, SQL & Performance
Model trustworthy data, write correct SQL, read query plans and coordinate concurrent orders. Take one PostgreSQL system through migrations, TypeScript integration and a verified restore.
Make the database part of the correctness story
Build a database you can explain under real change: customers with no orders, catalog prices that move, queries that slow down, writers that overlap, schema migrations and recovery after failure. Work directly with PostgreSQL and SQL, then connect the same command to TypeScript.
Build Harbor Orders across six modules
Harbor Orders is a small multi-store order and inventory system. You model customers, catalog products, historical line prices and available stock, then build reports, measure a larger workload, implement a repeatable single-item order command, evolve delivery state and restore the database. Each module leaves evidence that the next one uses.
What you will be able to do
Translate business statements into tables, keys and explicit rules, then demonstrate the boundary of each guarantee.
State what one output row means, predict the result from fixtures, and explain why a query remains correct when relationships multiply.
Improve one bounded query with evidence while preserving its result and accounting for write and maintenance overhead.
Explain the transaction boundary and reproduce both a stock race and an idempotent replay under concurrent connections.
Plan a schema transition as several observable releases with explicit completion checks and a realistic rollback boundary.
Demonstrate that a recovered Harbor database contains the expected records and still supports the order contract.
A practical reading-and-code course
Work through 30 substantive readings, 18 implementation labs, six debugging cases and one cumulative capstone. Complete SQL and shell examples are provided inline with filenames and execution order. Each reading connects a mental model to an experiment, an expected observation, a deliberate defect and a repair.
Plan approximately 20–28 hours for reading, SQL experiments, lab extensions and a careful capstone. This is an estimate of hands-on effort, not recorded video duration. A local Docker environment supports the included reference experiments; a paid cloud database is not required.
A PostgreSQL 18 baseline you can reproduce
The reference experiments were executed on PostgreSQL 18.4 and the TypeScript bridge on Bun 1.3.14 on September 9, 2026. Verification included the schema and query scripts, a 200000-row plan workload, competing reservations, simultaneous identical-key replay, schema evolution, logical dump/restore, business reconciliation and a restored write probe. Exact plans and timing vary with versions, data and hardware.
The learning path
1. Model Data and Enforce Invariants
Build the Harbor Orders schema, choose truthful types, protect store boundaries, and prove which invalid states the database rejects.
2. Write Queries That Keep Their Meaning
Use joins, aggregation, windows, NULL-aware predicates and stable pagination to produce trustworthy Harbor reports.
3. Explain Plans and Design Useful Indexes
Measure a reproducible workload, read row estimates and buffers, choose indexes deliberately, and explain their operational cost.
4. Coordinate Transactions and Concurrent Orders
Build a single-item order command, prove retry behavior, observe lock waits and prevent two writers from reserving the same last unit.
5. Evolve the Schema Without Breaking Writers
Add delivery state through expand, backfill and enforcement, verify concurrent index validity, and test old/new writer compatibility.
6. Operate, Restore and Prove Recovery
Diagnose database symptoms, separate privileges, restore a real archive, reconcile business data and connect a bounded TypeScript transaction.
Evidence you keep
An annotated schema, deterministic seed data and an invariant matrix with passing and failing SQL cases.
A report catalog with explicit row grain, expected results and stable cursor contracts.
Before/after plans, a query-and-index inventory and an evidence-based performance decision.
A tested order command, a two-session timeline and a retry/failure policy.
An expand/backfill/enforce migration sequence, a compatibility matrix and a rollout/rollback decision record.
An operational runbook, a restored database, a reconciliation report and the final Harbor release packet.
Assessment built around decisions
Each module includes six scenario questions. The final module adds a 24-question review bank with 15 scenarios per attempt: 60 authored questions across the course. The capstone has a transparent six-dimension manual self-review rubric, acceptance criteria and reference approaches for the labs.