Modeling for Postgres

Normalization is assumed knowledge here. What this topic covers is everything the engine offers after the tables are drawn: values the database computes so that no code path can get them wrong, a three-valued logic that empties a query's result set without raising anything, a physical row layout in which column order costs real disk, and the row-store shape that makes adding a column nearly free and reading two columns of forty surprisingly expensive.

Cartwheel has three of these live right now. order_items has no line total, so three code paths multiply qty by unit_price and one of them applies a promotional discount the other two have never heard of. The unique index on the new orders.public_id column currently holds four rows whose value is null, which the word "unique" was taken to rule out. And delivery_events is on its way to 1.2 billion rows in a column order that was never chosen on purpose.

Generated Columns, Stored and Virtual

A generated column is a column whose value is an expression over other columns of the same row. Postgres computes it and refuses to let anything write to it, which removes the entire class of bug where two services disagree about what a derived value means. The expression must be immutable, may not contain a subquery, may not reference another table, and may not reference another generated column.

ALTER TABLE order_items
  ADD COLUMN line_total numeric(10,2)
      GENERATED ALWAYS AS (qty * unit_price) STORED;

-- leaving the keyword off means VIRTUAL since 18,
-- which is not what a 17-era migration meant by it
ALTER TABLE order_items
  ADD COLUMN line_total_v numeric(10,2)
      GENERATED ALWAYS AS (qty * unit_price);

The keyword at the end decides everything, and omitting it in 18 selects the opposite of what it selected before. Virtual is now the default: the value occupies no storage and is computed on every read, like a one-column view welded to the table. Stored computes the value on write and keeps it on disk. In 18 a virtual generated column cannot be indexed, and since a unique constraint is implemented by an index, it cannot be unique either; its expression is also restricted to built-in functions and types. Anything you intend to filter, sort or constrain on has to say STORED, deliberately and in writing.

The two forms also differ at the moment you add them. Adding a virtual generated column never rewrites the table — it is a catalogue entry and nothing more. Adding a stored one, which the last topic of this chapter does on a live table, rewrites the entire table and every index on it.

NULL Is Not a Value

Null is not zero, not an empty string, and not a value at all. It is the absence of one, and comparing anything to it produces NULL rather than true or false. That third truth value propagates: qty <> 5 is NULL for a row whose qty is null, and a WHERE clause keeps only rows that evaluate to true, so those rows drop out of a predicate that reads as though it should include them.

SELECT count(*) FROM customers
 WHERE id NOT IN (SELECT customer_id FROM orders);
 0

SELECT count(*) FROM orders WHERE customer_id IS NULL;
 3      -- guest checkouts from the prototype, never cleaned up

Three null rows in a 40-million-row table are enough to zero the whole answer. For any customer, NOT IN has to establish that the id differs from every value the subquery produced, and a comparison against null is unknown, so the predicate is never true for anyone. A count of zero comes back, nothing is raised, and the churn report built on it reads like a business result rather than a bug. Postgres is doing exactly what the SQL standard requires; the standard is what surprises people.

IS DISTINCT FROM compares two values treating null as an ordinary comparable, so it returns true or false and never unknown. COALESCE substitutes a real value before the comparison happens. And NOT EXISTS is the safe rewrite of NOT IN — it asks whether a matching row exists rather than whether a value equals every member of a set, so a null row simply fails to match instead of poisoning the predicate. It usually plans better too, because the planner can execute it as an anti-join.

NULL in Unique Indexes

The same rule reaches uniqueness. Two null values are not considered equal in a unique comparison, so a unique constraint permits any number of rows carrying null in the constrained column. That is standard behaviour and it is almost never what the person who typed UNIQUE had in mind.

CREATE UNIQUE INDEX orders_public_id_key
    ON orders (public_id);                -- any number of NULL rows allowed

ALTER TABLE orders
  ADD CONSTRAINT orders_public_id_uq
      UNIQUE NULLS NOT DISTINCT (public_id);   -- at most one NULL row

Since 15, NULLS NOT DISTINCT makes nulls compare equal for that constraint, so at most one row may be missing a value. Cartwheel's four null public_id rows arrived during the backfill that gave existing orders their customer-facing identifier: the column was nullable while the backfill ran, the unique index raised nothing, and the team read the constraint as a guarantee it had never made. The four rows are still there.

Column Order Costs Bytes

Postgres stores each fixed-width value on its natural alignment boundary: 8 bytes for bigint, timestamptz and double precision, 4 for integer and date, 2 for smallint, 1 for boolean. When a wide type follows a narrow one, the gap between them is padding — bytes written to disk, shipped to the replica, and read back into shared_buffers forever, carrying nothing.

-- 32 bytes of column data: 7 wasted after each boolean
CREATE TABLE a (flag_a boolean, id bigint, flag_b boolean, ts bigint);

-- 18 bytes of column data, rounded up to 24 by the row's own alignment
CREATE TABLE b (id bigint, ts bigint, flag_a boolean, flag_b boolean);

Declaring the wide columns first and letting the narrow ones share the tail turns 32 bytes of column data into 18, which the tuple's own alignment rounds to 24. Ordering fixed-width columns widest-first is free at design time, and fixing it afterwards is a full table rewrite. Eight bytes per row is invisible on a lookup table and it is 9.6 GB on delivery_events at 1.2 billion rows — the same 9.6 GB in every base backup, on every replica, and in the portion of the table that has to compete for cache.

Wide Tables, Narrow Reads

Postgres is a row store: the whole row lives together in one 8 KB page. A query that reads two columns of a forty-column table still pulls every one of those pages through the buffer cache, because the two columns it wants are interleaved with the thirty-eight it does not. The width of the table sets the cost of reading any part of it.

Splitting a genuinely cold set of columns into a side table keyed by the same id is a legitimate physical optimization rather than a normalization failure, and it is worth doing when the cold part is large and read rarely. Postgres already does a version of it for you — the TOAST mechanism Chapter 5 opens up — so delivery_events.payload costs less on a narrow scan than its size suggests.

Designing for the Access Pattern

Cartwheel's three interesting tables want three different physical designs from identical modeling advice. inventory is a small hot set under constant read-modify-write, so it cares about update behaviour and contention and almost nothing else. orders is insert-once, read-by-customer, with a status that changes twice early in a row's life and never again. delivery_events is append-only and scanned by time, which means it will want partitioning long before it wants a clever index.

The model is the same in all three cases: keys, foreign keys, no duplicated facts. The physical decisions are not, and a house style applied uniformly to all three guarantees that two of them are wrong. Chapter 8 chooses each table's indexes on that basis, and Chapter 11 partitions delivery_events, the one that has outgrown being a single relation.