PostgreSQL Deep Dive
by Sergey Okinchuk
Identity and UUIDsModeling for Postgres

Chapter Three · Schema Design and Evolution

Schema Design and Evolution

The types are right; the rules are not. Stock has gone negative twice, the status column holds three spellings of "delivered", and every team derives yesterday's revenue differently. Five topics turn Cartwheel's model into one the engine enforces — and then change a live 40-million-row table without taking checkout down.

5 topics

Chapter 2 fixed what each column means. This chapter is about what the database is willing to guarantee about them, and what it costs to change its mind later. Those two questions are the same question, because a schema that cannot be altered safely is a schema that stops improving — the rule that would have prevented last quarter's incident does not get added if adding it means a maintenance window.

Nadia has a list. inventory.on_hand has gone negative twice, both times from a stock-correction script that talks to the database directly and has never seen the API's validation. orders.status is free text and currently contains delivered, Delivered and deliverd. The analytics team re-derives "yesterday's revenue" in three places and gets three answers. Chapter 2 left one item explicitly open on top of that: orders.id is still a 4-byte integer whose sequence has issued 1,352,914,698 of its 2,147,483,647 values, with just under four years of headroom at the current rate.

The first four topics build the guarantees: generated columns and the null semantics that break a query without failing it, constraints added to live tables without a long lock, the analytics schema that Chapter 1 promised the dashboard, and the line between logic that belongs inside the database and logic that only looks like it does. The last topic is the one the rest of the chapter was preparing for. It explains which lock every schema change takes, why a three-millisecond migration can cause a twenty-minute outage, and how a primary key gets widened on a live table over days of elapsed time and milliseconds of lock.

What this chapter does to Cartwheel's schema
The types are rightand the rules are not
→
Guarantees the engine makesgenerated columns · constraints · the analytics schema
→
Which lock, and for how longwhy a three-millisecond migration causes a twenty-minute outage
→
orders.id widened on a live tabledays of elapsed time, milliseconds of lock

Topics in This Chapter

Topic 12
Modeling for Postgres
Everything the engine offers after the tables are drawn: generated columns that compute themselves, three-valued logic that empties a result set without an error, and column order that costs real gigabytes. Why virtual is the default since 18, and what a row store charges for reading two columns of forty.
Schema Design
Topic 13
Constraints as Guardrails
The only rule that survives a second service and a psql session at midnight. The foreign-key index Postgres never creates, adding constraints to a 40-million-row table with NOT VALID, reaching SET NOT NULL without a full scan, and what deferring a constraint costs you.
Data Integrity
Topic 14
Views and Materialized Views
A view is a rewrite the planner sees through, so a filter written outside it lands on an index inside it. A materialized view is a cache with SQL syntax — including the refresh that locks readers out, the unique index CONCURRENTLY demands, and the fact that nothing refreshes itself.
Derived Data
Topic 15
Functions, Triggers, and PL/pgSQL
Volatility labels are contracts the planner acts on, and mislabelling one puts stale answers in an index. Statement triggers with transition tables, the jobs a trigger genuinely earns, and why a SECURITY DEFINER function without a pinned search_path is a privilege-escalation hole.
In-Database Code
Topic 16
Migrations Without Downtime
ACCESS EXCLUSIVE is the only lock that blocks a SELECT, and a queued request for it blocks everyone behind it. Which ALTER TABLE forms are free, which rewrite 40 million rows, and the expand-and-contract procedure that widens orders.id from integer to bigint while checkout keeps running.
Schema Change