Software Engineering

PostgreSQL & Data Modeling

Moving from SQLite to PostgreSQL, the features you get for it, reading real query plans, and data modeling in the depth that decides whether a schema survives its second year.

The course project

The Production Schema

A modeled, migrated, indexed PostgreSQL database with an entity relationship diagram and query plans to justify every index.

course

9 modules · 18 lessons

Each module ends with a practice test, a graded build, and a step of the course project.

Part I: The Database

Why PostgreSQL

What you gain by outgrowing the file on disk.

Types That Mean Something

Choosing a column type is a modeling decision, not a formality.

Migrating to PostgreSQL

The day your data gets audited whether you wanted it or not.

Section project · coming soon

Stand Up a Database

Create a PostgreSQL database, a role and your first tables.

Part II: Modeling

Normalization, Properly

The forms, and what each one actually prevents.

Relationships and Diagrams

Cardinality is a decision about the world, not about the database.

Time, History and Deletion

Three modeling decisions people make by accident.

Section project · coming soon

Model and Normalize

Design a schema, justify it, and draw its ERD.

Part III: Production

Query Plans for Real

EXPLAIN ANALYZE gives you timings, not intentions.

Transactions and Concurrency

What happens when two people do the same thing at the same moment.

Running a Database

Backups you have restored, and migrations that do not lock the table.

Section project · coming soon

Migrate and Back Up

Evolve the schema with migrations and prove a restore works.

All courses