The Shape of Data

Consider a rule as boring as they come: a cellar entry's quantity is never negative. In a working product, rows arrive in that table from at least three places — the web handler when a user records a purchase, the batch importer when someone uploads a spreadsheet from their old cellar app, and the admin script an engineer runs to fix a support ticket at 11pm. Application-layer validation protects the paths where somebody remembered to call it. That is not a slight against your team; it is arithmetic. Three code paths, written at different times by different people (and increasingly by different models), each carrying its own copy of the rule.

A constraint collapses that arithmetic. Every one of those paths issues an INSERT, and every INSERT passes through the same gate. The rule is stated once, in the schema, and there is no path around it — not the ORM, not the raw psql session, not the migration script, not the code your assistant generated at 2am while you reviewed the diff at speed.

A primary key is the enforced answer to "which row is this?" — unique and not null, checked on every write. The interesting decisions start immediately after. This guide's running schema is a wine-cellar product: users, wines, vintages, cellar entries, tasting notes, recommendations. Its wines table could plausibly be keyed by (producer, name), which is what the domain actually considers identity. Instead it carries a generated bigint:

CREATE TABLE wines (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  producer text NOT NULL,
  name     text NOT NULL,
  region   text NOT NULL,
  country  text NOT NULL,
  UNIQUE (producer, name)
);

Both keys are present, and that is the point. The surrogate key exists for referencing: it is small, immutable, and cheap to carry in a foreign key on 2.1 million cellar entries. The natural key — declared as UNIQUE (producer, name) — is the actual invariant, the statement that Château Montrose the Saint-Estèphe label appears once in this table and not four times because four importers spelled it differently. Teams that adopt surrogate keys and then drop the natural key have not simplified anything; they have deleted the rule and kept the plumbing. You can find this failure in the wild by looking for tables where the only unique constraint is on id: every row is unique in the least useful possible sense.

The same pattern holds one level down. Vintages carry UNIQUE (wine_id, year): Montrose 2016 is one row (id 3187), not one row per person who entered it. This is where identity modeling starts to earn its keep, because "a wine" and "a wine of a particular year" are genuinely different things — a subject m2 takes up in full.

A foreign key encodes one invariant: every reference resolves. In the running schema, tasting_notes.vintage_id REFERENCES vintages(id) means there is no such thing as a note about a vintage that does not exist. Without the constraint, that sentence becomes aspirational, and the failure mode is quieter than people expect. Orphaned rows do not throw errors. They join to nothing, so an inner join drops them — your tasting-activity report simply returns a smaller number, and the number looks plausible, and nobody notices until an auditor asks why the count in the export disagrees with the count in the product.

The teams that skip foreign keys rarely say "we do not want referential integrity." They say the ORM manages relationships, or that foreign keys slow down deletes, or that they will add them later once the model settles. The first is a claim that application code will do perfectly what it does approximately; the second is true and almost always irrelevant at product scale; the third has an observable half-life, because by the time the model settles the data already violates the constraint you now want to add.

Declaring the constraint forces a decision you were making anyway, just implicitly: what happens to children when the parent goes away?

Notice that the cascade choice on user deletion is not a database preference; it is your data-retention policy, written in DDL. That is the recurring theme: the schema is where a policy stops being a document and becomes a mechanism.

Four tools cover nearly everything, and the useful way to learn them is not by definition but by the anomaly each one makes unrepresentable. Here is the running schema's cellar_entries, the table that records that a user owns bottles:

CREATE TABLE cellar_entries (
  id                   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id              bigint  NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  vintage_id           bigint  NOT NULL REFERENCES vintages(id) ON DELETE RESTRICT,
  quantity             integer NOT NULL CHECK (quantity >= 0),
  purchase_price_cents integer NOT NULL CHECK (purchase_price_cents > 0),
  purchased_on         date    NOT NULL,
  rack                 text,
  slot                 smallint
);

NOT NULL on user_id says an entry that belongs to nobody is not a fact. CHECK (quantity >= 0) is the oversell backstop — whatever the reservation code does, the row cannot record negative bottles, which means a concurrency bug in m6's territory degrades into a failed transaction rather than corrupted accounting. CHECK (purchase_price_cents > 0) catches the data-entry class of error where a form submits an empty string and the ORM helpfully coerces it to zero. And UNIQUE, which this table does not use but wines and vintages do, is the one people most often try to reimplement.

Postgres also offers exclusion constraints, which generalize uniqueness to any comparison operator — the canonical use is preventing overlapping ranges, such as two bookings of the same rack slot over overlapping date ranges via EXCLUDE USING gist. You will not need it often; know it exists so you recognize the problem shape when it appears.

The lens is only honest if it has a boundary. Constraints are row-scoped: a CHECK can see the row being written and nothing else. So an invariant like "a user on the free plan may hold at most 50 bottles in total" is outside the toolbox — it depends on the sum of other rows, which may be changing concurrently. Same for "a recommendation must reference a vintage the user does not already own" and "at least one cellar member must have the owner role." These are real invariants, and pretending a constraint covers them is worse than knowing it does not.

The options for cross-row invariants are: a transaction that reads and writes atomically at a sufficient isolation level (m6 makes this precise, including exactly how the naive version fails); a trigger that runs inside the writing transaction; a materialized helper column protected by its own constraint; or application logic that you accept is best-effort. Each is a real answer. "We assume the API layer handles it" is not one, because that is a statement about the code paths that exist today.

Which leads to the review discipline this module exists to install. For every business rule your product believes, write down the layer that enforces it. The output looks like this, and "nobody" is a finding, not an omission:

Here is the entire requirement the cellar product started from, as it was actually written: "Users track the bottles they own, identified by producer and vintage year, with what they paid and where in the cellar it sits. They record tasting notes with a score, and receive recommendations of wines to buy next." Modeling begins by mining that paragraph for nouns and then rejecting most of them.

The test for entity-hood is two questions. First, does the thing have identity independent of other rows — can you point at it and say "that one" without reference to its context? Second, do invariants attach to it in its own right? A user passes both: an account exists whether or not it owns anything, and "emails are unique" is a rule about users. A bottle count does not pass either: it has no independent existence and no rules of its own; it is an attribute of an ownership fact.

Run the paragraph through the test and six nouns survive: users, wines, vintages, cellar_entries, tasting_notes, recommendations. "Producer" does not survive today — no invariants attach to it yet, so it is a text column on wines. "Score" does not survive — it is an attribute of a note. "Cellar" does not survive either, which is a genuinely close call: today a user has exactly one implicit cellar, so it would be a table with an id and nothing else. The section on attributes returns to what would change that.

Once you have entities, cardinality is a purely mechanical question with a mechanical consequence. Ask it in both directions — can one X relate to many Y, and can one Y relate to many X — and the answer places the foreign key for you.

For 1:N, the key goes on the many side. One wine has many vintages; one vintage belongs to one wine; therefore vintages.wine_id. The tell that you have it backwards is immediate and worth memorizing: if the column would need to hold multiple values, it is on the wrong table. wines.vintage_id could only ever name one of Montrose's forty vintages, and teams that go down this path end up either with a comma-separated list or with a duplicate wine row per year — which is to say, with the redundancy m3 is about.

For M:N, neither side can hold the key, so the relationship becomes a table of its own. One user owns bottles of many vintages; one vintage is owned by many users; therefore cellar_entries, holding user_id and vintage_id. For 1:1, default to one table. A separate table for a strict 1:1 buys you a join in exchange for almost nothing — the exceptions being genuinely optional sections of data, or columns with a different security or access profile that you want isolated on purpose.

Here is the complete schema, Postgres 16, with the decisions defended. Every SQL statement in the rest of this guide runs against exactly this.

CREATE TABLE users (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email        citext NOT NULL UNIQUE,
  display_name text   NOT NULL,
  created_at   timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE wines (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  producer text NOT NULL,
  name     text NOT NULL,
  region   text NOT NULL,
  country  text NOT NULL,
  UNIQUE (producer, name)
);

CREATE TABLE vintages (
  id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  wine_id bigint   NOT NULL REFERENCES wines(id) ON DELETE RESTRICT,
  year    smallint NOT NULL CHECK (year BETWEEN 1900 AND 2100),
  abv     numeric(3,1),
  UNIQUE (wine_id, year)
);

CREATE TABLE cellar_entries (
  id                   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id              bigint  NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  vintage_id           bigint  NOT NULL REFERENCES vintages(id) ON DELETE RESTRICT,
  quantity             integer NOT NULL CHECK (quantity >= 0),
  purchase_price_cents integer NOT NULL CHECK (purchase_price_cents > 0),
  purchased_on         date    NOT NULL,
  rack                 text,
  slot                 smallint
);

CREATE TABLE tasting_notes (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    bigint   NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  vintage_id bigint   NOT NULL REFERENCES vintages(id) ON DELETE RESTRICT,
  rating     smallint NOT NULL CHECK (rating BETWEEN 50 AND 100),
  note       text,
  tasted_on  date     NOT NULL
);

CREATE TABLE recommendations (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id      bigint       NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  vintage_id   bigint       NOT NULL REFERENCES vintages(id) ON DELETE RESTRICT,
  score        numeric(4,3) NOT NULL CHECK (score BETWEEN 0 AND 1),
  reason       text         NOT NULL,
  generated_at timestamptz  NOT NULL DEFAULT now()
);

Why cellar_entries is a junction table with payload. It realizes the M:N between users and vintages, but it carries quantity, purchase_price_cents, purchased_on, and location. Those attributes belong to neither user nor vintage — they are facts about this ownership. That is the general rule: when a relationship has its own attributes, the junction table is where they live, and the schema is telling you the relationship was really an entity all along (a purchase event).

Why tasting_notes references vintage, not cellar entry. The tempting design hangs a note off the entry, since you usually taste what you own. But you can taste a wine at a friend's house, at a shop, or from a bottle you finished last year and no longer have an entry for. Referencing the vintage models the domain; referencing the entry models the happy path and quietly makes the other cases unrepresentable — the exact failure this guide keeps returning to, in the opposite direction from the usual one.

Why purchase_price_cents lives on the entry. It is the price paid, on purchased_on, by that user — a historical fact of the purchase, not a lookup of what Montrose 2016 costs today. Moving it to a shared price table would look like removing duplication and would actually destroy information, since a later price update would silently rewrite what Elena paid in March 2024. Module 3 gives this distinction its name and its test.

The hardest modeling calls are the ones between a column and a table. region is a text column on wines today, and that is correct today, because nothing in the product has an invariant about regions — no rule says Saint-Estèphe must exist before a wine can claim it, and nothing hangs off a region except the string itself. The day requirements arrive that attach facts to regions — appellation rules, a country's tax treatment, a canonical hierarchy of Bordeaux subregions — region becomes an entity, gets a table, and wines.region becomes wines.region_id. Promoting it before that day is over-modeling: you pay a join and a maintenance burden for a lookup table whose only column is the name you already had.

The rule generalizes cleanly. Promote an attribute to an entity when invariants attach to it, when it needs attributes of its own, or when the same value must be referenced identically from several places. Two of those three are satisfied by "we keep misspelling it," which is why the promotion usually happens sooner than teams expect but later than the pattern-matchers want.

Time deserves its own paragraph, because it is where modeling most often quietly loses information. purchased_on is a date because the domain fact is a calendar day — nobody cares that the purchase was recorded at 14:32, and a timestamp would invite time-zone questions the domain does not have. created_at and generated_at are timestamptz because they are moments in the life of the system, compared against other moments across users in different zones. And the deeper discipline: model change as new rows rather than overwritten columns wherever the history is a domain fact. If a bottle's rack location matters historically, moving it should insert a movement row, not update a column — an UPDATE is a small, silent act of deletion.

Now the anti-pattern this module exists to name. Ask an assistant to build the cellar product and you will frequently get a schema that is an excellent transcription of the request and a poor model of the domain. It has a table per screen — dashboard_items, my_cellar_rows — because the request described screens. It has columns named after form fields. And because the prompt said "users can favorite wines," it has users.favorites jsonb holding an array of wine ids.

That last one is the diagnostic case. The array works: you can read it, render it, and demo it. What it forfeits is everything m1 established. There is no foreign key, so a favorited wine can be deleted and the array now points at nothing, silently. There is no uniqueness, so the same wine appears twice the moment a double-click gets through. There is no join, so "which users favorited this wine" — the first question the recommendations feature will ask — requires scanning every user. And there is nowhere to put favorited_at, which somebody will want within the quarter, so it gets nested into the array elements and now you have an undocumented schema inside a column.

The corrective is the junction table you would have written from the cardinality question: user_favorites (user_id, wine_id, favorited_at, PRIMARY KEY (user_id, wine_id)). Note what the composite primary key does for free — it is the uniqueness invariant, so double-clicks are impossible rather than deduplicated.

Which yields the review checklist for any proposed schema, generated or otherwise. Does every table represent something with independent identity? Is every relationship's cardinality declared and its key on the correct side? Does every plural stored inside a single column have a defended reason to not be a table? Is every natural key declared UNIQUE? For each of the three or four questions the product will obviously ask next, can this schema answer it without a migration — and if not, is that acceptable? Four minutes of checklist, applied to a diff you would otherwise skim, is the highest-leverage review in this guide.

Imagine the cellar product had shipped with one table instead of six — a cellar_log, one row per bottle event, everything on the row:

It works. You can query it, render it, and demo it. Three specific failures are already inevitable.

The update anomaly. The region was recorded as Saint-Estèphe, which is right, but suppose an editor corrects a mis-entered region across the catalog — or that Montrose's region string needs standardizing from "Saint-Estèphe" to "Saint-Estèphe, Médoc". The fact lives in 44 rows. The update statement matches 41 of them, because three rows spell the producer with a different accent or a trailing space. Now the database holds two answers to one question, and every subsequent report picks one arbitrarily. This is the update anomaly: the same fact stored many times, updated once, disagreeing with itself thereafter. Note what makes it vicious — nothing failed. No error, no alert, just a slow divergence discovered by a user who sees one region in search and another on the detail page.

The insert anomaly. You cannot record that Montrose 2019 exists until somebody owns a bottle of it, because every row requires a user and a quantity. The catalog and the ownership are welded together, so facts about wines can only enter the system through the act of buying one. Recommendations — which by definition are about wines the user does not own — have nowhere to come from.

The delete anomaly. Elena drinks her last 2015 and the row is deleted. The system has now forgotten that Montrose 2015 exists, along with its region and her rating of it. Deleting one fact destroyed three, because they shared a row.

A functional dependency is the whole apparatus in one sentence: A → B means that if you know A, B is determined. In the log table, producer, wine_name → region — knowing the wine determines the region, absolutely, regardless of who owns it or when. That dependency is a fact about the domain, and the trouble comes from storing it in a table whose key is something else entirely.

First normal form requires atomic values: no repeating groups, no lists in a cell. The violation you will meet most often is grapes text holding "Cabernet Sauvignon, Merlot, Petit Verdot". What atomicity buys is concrete — you can index a value, reference it with a foreign key, and count occurrences correctly. What the string costs is equally concrete: WHERE grapes LIKE '%Merlot%' also matches "Merlot Noir" and misses "merlot", and no index helps a leading wildcard.

Second and third normal form are one idea applied twice: every non-key attribute must depend on the key, the whole key, and nothing but the key. 2NF is violated when an attribute depends on only part of a composite key — in the log, region depends on the wine alone, not on the (user, wine, year) triple that identifies a row. 3NF is violated when a non-key attribute depends on another non-key attribute — if the log also stored country, determined by region rather than by the row's key, that is the transitive dependency 3NF forbids.

Boyce-Codd normal form, honestly: it tightens 3NF for the case where a table has overlapping candidate keys and a non-trivial dependency runs from a non-superkey to a key attribute. It is real, and in ten years of ordinary product work you may meet it twice. Know that 3NF is not the last word; do not go looking for BCNF violations in schemas that do not have overlapping candidate keys.

Now break the rules, on purpose. Denormalization is reintroducing redundancy to buy read performance, and it is a legitimate move when the read path is genuinely hot and the alternative is a five-way join on every page load. The failure is not denormalizing — it is denormalizing without knowing which kind of redundancy you just created.

There are two kinds, and confusing them causes damage in both directions.

A snapshot records what was true at a moment. cellar_entries.purchase_price_cents is the price Elena paid for Montrose 2016 on 2024-03-15: 21,500 cents. It looks like duplication of a wine's price, but it is not duplication at all — it is a distinct fact about a distinct event. "Normalizing" it into a shared price table keyed by vintage would replace a historical fact with a current lookup, so the day the price is updated, Elena's purchase history quietly changes to say she paid something she did not pay. Snapshots must not be normalized away, and the same reasoning covers the shipping address on an order, the tax rate applied to an invoice, and the terms in force when a contract was signed.

A cache is derivable. A bottle_count column on users is exactly SELECT sum(quantity) FROM cellar_entries WHERE user_id = ..., precomputed. It contains no information the base tables lack, which is precisely why it can be wrong: any write path that changes quantities without updating the cache creates drift, and drift is undetectable without an explicit comparison.

Hence the rule this module enforces: every cache gets a written maintenance plan before it ships. The plan names three things — what updates the value (a trigger, or a transactional co-update alongside every write to the base table), when it is allowed to be stale and by how much, and what detects drift (a reconciliation query that runs on a schedule and alerts rather than silently repairing). A cache with a maintenance plan is engineering. A cache without one is a second source of truth that nobody has agreed is a source of truth.

When someone proposes a redundant column — in a design doc, a pull request, or a schema an assistant generated — three questions settle it, and they take about ninety seconds.

Is it a snapshot or a cache? Apply the recomputation test. Snapshots are approved on the spot and, importantly, annotated so a future refactor does not "clean them up." Caches proceed to question two.

What maintains it? Name the mechanism, not the intention. A trigger on the base table is the most robust answer because it holds regardless of which code path writes. A transactional co-update in application code is acceptable if — and only if — every write path performs it, which means you must enumerate the write paths: the web handler, the importer, the admin tooling, the backfill scripts. "The service layer handles it" is not an answer; it is a hope about a code structure that will change.

What detects drift? A reconciliation query with an owner and a schedule, alerting on mismatch. If nobody can write the reconciliation query, that usually means the value's definition is not agreed on, which is a finding of its own — you cannot maintain a number nobody can define.

When you send SQL to Postgres, four things happen in order, and knowing where each one ends explains most planner behavior that otherwise looks arbitrary.

Parse turns the text into a tree; this is where syntax errors live and nothing else. Rewrite expands views, applies rules, and inlines CTEs where it can. Plan is the interesting stage: the query planner enumerates ways to get the answer — sequential scan, index scan, bitmap heap scan, several join orders and join strategies — assigns each an estimated cost using table statistics, and picks the cheapest. Execute runs the winner.

Two consequences fall out immediately. First, the planner's decision is made from statistics, not from your data — it consults a stored sample (histograms, most-common values, distinct-value estimates, collected by ANALYZE) and reasons about how many rows each predicate will keep. When those statistics are stale, the plan is a confident answer to the wrong question. Second, indexes only matter during planning. An index the planner does not choose costs you write throughput and buys you nothing; it does not sit there being helpful in the background.

A B-tree keeps keys in sorted order and lets you descend to any key in a few page reads. That single property is the source of everything it can do: equality lookup, range scans, ordered retrieval without a sort, and — crucially — prefix behavior on multi-column keys.

A composite index on (user_id, purchased_on) sorts by user_id first, then by purchased_on within each user. Picture a phone book ordered by surname then first name. Finding everyone named Vasquez is one descent. Finding Vasquez, Elena is one descent. Finding everyone named Elena requires reading the whole book, because the Elenas are scattered across every surname — the ordering simply does not group them.

That is the entire leftmost-prefix rule. The index on cellar_entries (user_id, purchased_on) serves:

• WHERE user_id = 1042 — the leading column alone, one descent to a contiguous range.
• WHERE user_id = 1042 AND purchased_on >= '2024-01-01' — descend on the prefix, then range-scan within it.
• WHERE user_id = 1042 ORDER BY purchased_on DESC LIMIT 20 — descend, then walk the already-sorted range backwards; no sort node at all, which is often the larger win.

And it does not usefully serve WHERE purchased_on >= '2024-01-01', because the qualifying entries sit under 50,000 different user_id values, scattered across the whole leaf level. Postgres may still fall back on a full index scan if the index is much narrower than the table, but it is reading everything either way — the ordering is not helping.

Here is the scenario that produces the most confused bug reports. You add exactly the right index, you re-run the query, and EXPLAIN shows a sequential scan. The index is not broken and the planner is not confused. It has decided that using the index would be slower, and it is usually right.

Selectivity is the fraction of rows a predicate keeps. An index scan is not free per row: for each matching entry the executor reads the index page, then jumps to the heap page holding the actual row — and those heap jumps are effectively random access. A sequential scan reads pages in order, which storage and the operating system's readahead both reward heavily. So there is a crossover. Reading 0.1% of a table through an index is enormously cheaper than scanning it. Reading 40% through an index means doing 40% of the table's worth of random page fetches plus the index traversal, which loses to simply reading the table straight through.

In the running schema, WHERE user_id = 1042 keeps roughly 42 rows out of 2.1 million — selectivity around 0.002%, and the index wins by orders of magnitude. Now suppose someone adds an index on a hypothetical is_archived boolean where 88% of rows are false. Every query filtering is_archived = false will sequentially scan regardless, because the index would identify 1.85 million rows and then fetch each of their heap pages. Low-cardinality columns are index dead weight in isolation — they earn their place only as the trailing column of a composite index, or in a partial index (WHERE is_archived = true) that indexes just the rare side.

Stop guessing and read the plan. EXPLAIN ANALYZE runs the query and reports both what the planner expected and what actually happened. Here is Elena's cellar page — her entries with wine names, most recent first — against the schema with no supporting index:

EXPLAIN ANALYZE
SELECT ce.id, w.producer, w.name, v.year, ce.quantity, ce.purchased_on
  FROM cellar_entries ce
  JOIN vintages v ON v.id = ce.vintage_id
  JOIN wines    w ON w.id = v.wine_id
 WHERE ce.user_id = 1042
 ORDER BY ce.purchased_on DESC
 LIMIT 20;

Limit  (cost=98241.55..98241.60 rows=20 width=64)
       (actual time=1284.902..1284.911 rows=20 loops=1)
  ->  Sort  (cost=98241.55..98241.66 rows=44 width=64)
            (actual time=1284.900..1284.905 rows=20 loops=1)
        Sort Key: ce.purchased_on DESC
        Sort Method: top-N heapsort  Memory: 28kB
        ->  Nested Loop  (cost=0.86..98240.37 rows=44 width=64)
                         (actual time=42.118..1284.771 rows=42 loops=1)
              ->  Nested Loop  (cost=0.43..98002.11 rows=44 width=26)
                    ->  Seq Scan on cellar_entries ce
                          (cost=0.00..97561.00 rows=44 width=26)
                          (actual time=41.902..1281.004 rows=42 loops=1)
                          Filter: (user_id = 1042)
                          Rows Removed by Filter: 2099958
                    ->  Index Scan using vintages_pkey on vintages v ...
              ->  Index Scan using wines_pkey on wines w ...
Planning Time: 0.412 ms
Execution Time: 1284.998 ms

Read it inside out — the innermost nodes run first, and each node's cost includes its children. The story here is one line: Rows Removed by Filter: 2099958. To find 42 rows, the executor examined 2.1 million. Then it sorted them, because nothing delivered rows in purchased_on order.

Add the index the leftmost-prefix rule predicts, matching the sort direction:

CREATE INDEX cellar_entries_user_purchased_idx
  ON cellar_entries (user_id, purchased_on DESC);

Limit  (cost=1.29..92.44 rows=20 width=64)
       (actual time=0.052..0.318 rows=20 loops=1)
  ->  Nested Loop  (cost=1.29..201.88 rows=44 width=64)
                   (actual time=0.051..0.309 rows=20 loops=1)
        ->  Index Scan using cellar_entries_user_purchased_idx on cellar_entries ce
              (cost=0.43..38.20 rows=44 width=26)
              (actual time=0.031..0.061 rows=20 loops=1)
              Index Cond: (user_id = 1042)
        ->  Index Scan using vintages_pkey on vintages v ...
Planning Time: 0.388 ms
Execution Time: 0.402 ms

Two things changed, and the second is the one people miss. The sequential scan became an index scan — expected. And the Sort node disappeared entirely, because the index already delivers rows in purchased_on DESC order, so LIMIT 20 stops after twenty rows instead of after sorting forty-two. On a user with 3,000 entries, that difference is the whole page.

Every index is a second data structure that must be updated on every insert, on every delete, and on every update touching its columns. Nine indexes on a hot table means an insert does one heap write and nine index writes, each with its own page splits and WAL. This is the cost side of a ledger that most teams only ever look at one side of.

The discipline follows: every index is justified by a named query and a plan that demonstrably improved. Not a hunch, not a column that "gets filtered on sometimes" — a query you can name, whose EXPLAIN you captured before and after. Indexes that cannot pass that test get dropped, and Postgres will tell you which ones are candidates via pg_stat_user_indexes (idx_scan = 0 after a full business cycle is a strong signal, though check for a rarely-used report first).

Note the shape of the second one: the running schema's bigint keys were not a style preference. They were a locality decision made at design time, and this is where it pays. The correct framing when someone proposes UUIDs is not "UUIDs are bad" — it is "which requirement are we meeting: unguessability in URLs, client-side generation, or cross-system merging?" Each of those has an answer, and two of the three are satisfied by UUIDv7 or by a separate public identifier column alongside a bigint primary key.

The mental model that causes trouble is "a join looks up related data." The accurate model is: a join produces every pair of rows satisfying the condition. If a wine has three tasting notes, joining wines to notes yields three rows for that wine — not one row with three notes attached. Row counts change, and that is not an implementation detail; it is the operation.

INNER JOIN keeps only matched pairs. LEFT JOIN keeps every left row, filling NULLs where no match exists. FULL JOIN preserves both sides, which in practice you will use mostly for reconciliation work. And the anti-join — rows on one side with no counterpart — is a distinct operation worth naming, because the two ways of writing it are not equivalent.

The most common silent bug in this territory is the outer join annihilated by its own WHERE clause. Suppose you want every wine with a count of its high-rated notes, including wines never tasted:

-- WRONG: the WHERE turns this back into an inner join
SELECT w.name, count(tn.id) AS high_notes
  FROM wines w
  LEFT JOIN tasting_notes tn ON tn.vintage_id IN (
         SELECT v.id FROM vintages v WHERE v.wine_id = w.id)
 WHERE tn.rating >= 90
 GROUP BY w.name;

Never-tasted wines get NULL for tn.rating, and NULL >= 90 is unknown, so WHERE drops them — the three-valued logic from m1 §2, arriving as a reporting bug. The rule: a predicate on the optional side belongs in the ON clause, which filters what matches, not in WHERE, which filters what survives.

-- RIGHT: the rating filter is part of the match condition
SELECT w.name, count(tn.id) AS high_notes
  FROM wines w
  JOIN vintages v      ON v.wine_id = w.id
  LEFT JOIN tasting_notes tn
         ON tn.vintage_id = v.id AND tn.rating >= 90
 GROUP BY w.id, w.name;

For anti-joins, prefer NOT EXISTS. The tempting NOT IN (SELECT ...) form has a trap: if the subquery returns even one NULL, the whole predicate evaluates to unknown for every row and the query returns nothing. Not fewer rows — zero rows, silently, the day someone makes a column nullable.

-- users who have never recorded a tasting note
SELECT u.id, u.display_name
  FROM users u
 WHERE NOT EXISTS (
       SELECT 1 FROM tasting_notes tn WHERE tn.user_id = u.id);

Before writing an aggregate, answer one question: what does one row of the result mean? One row per user? Per user per month? Per wine? That is the grain, and GROUP BY is how you declare it. Getting explicit about the grain is what prevents the single most expensive class of SQL bug, which is not a wrong query — it is a query that returns a number, the number looks reasonable, and it is inflated by a factor nobody can see.

Here is the arithmetic. Elena's cellar entry 88213 records 6 bottles of Montrose 2016 at 21,500 cents each — a purchase of 129,000 cents. She has recorded three tasting notes on that vintage. Now someone writes a spend report that also filters by rating:

-- WRONG: joining 1:N before aggregating
SELECT u.display_name, sum(ce.purchase_price_cents * ce.quantity) AS spend_cents
  FROM users u
  JOIN cellar_entries ce ON ce.user_id = u.id
  JOIN tasting_notes  tn ON tn.vintage_id = ce.vintage_id AND tn.user_id = u.id
 WHERE tn.rating >= 90
 GROUP BY u.id, u.display_name;

The join to tasting_notes multiplies entry 88213 into three rows, each carrying its own copy of the 129,000. The sum reports 387,000 for a purchase that cost 129,000. This is fan-out, and its signature is diagnostic: the inflation is exactly proportional to the number of matched child rows, so the best-reviewed wines are overstated the most — which makes the report look directionally sensible and therefore trustworthy.

Two correct fixes, and choosing between them is about intent. If the child table is only a filter — "entries whose vintage has a high-rated note" — use EXISTS, which tests without joining and therefore cannot fan out. If you need values from the child side, aggregate it to the target grain first in a CTE, then join one-to-one. What you must not do is reach for SELECT DISTINCT: it suppresses the visible duplication while leaving the grain wrong, and it silently collapses rows that are legitimately identical — two purchases of the same wine at the same price on the same day become one, and now the report undercounts instead.

A GROUP BY aggregate destroys rows: nine tasting notes become one average, and the individual notes are gone. A window function computes the same kind of value while keeping every row — each row gets its aggregate alongside its own data. That single difference resolves three query shapes that are painful without it.

The syntax has three parts inside OVER (...). PARTITION BY defines the group (like GROUP BY, but without collapsing). ORDER BY defines the sequence within the partition, which is what makes ranking and running totals possible. The frame clause defines which rows within the partition the function sees — and the default when you write ORDER BY is "everything from the partition start through the current row," which is exactly what a running total needs.

Top-N per group. The highest-rated vintage in each region. With GROUP BY region, max(rating) you get the rating but lose which vintage earned it; the classic repair — joining back on (region, max_rating) — duplicates rows on ties and is three times the code. ROW_NUMBER is the canonical form:

WITH ranked AS (
  SELECT w.region, w.producer, w.name, v.year, tn.rating,
         ROW_NUMBER() OVER (PARTITION BY w.region ORDER BY tn.rating DESC, v.year) AS rn
    FROM tasting_notes tn
    JOIN vintages v ON v.id = tn.vintage_id
    JOIN wines    w ON w.id = v.wine_id
)
SELECT region, producer, name, year, rating
  FROM ranked WHERE rn = 1
 ORDER BY region;

   region      |    producer      |      name       | year | rating
---------------+------------------+-----------------+------+--------
 Rioja          | Bodegas Muga     | Reserva         | 2018 |     93
 Mosel          | Fürst Löwenstein | Riesling Kabi.  | 2019 |     95
 Saint-Estèphe  | Château Montrose | Château Montrose| 2016 |     96
 Willamette     | Bergström        | Cumberland Res. | 2021 |     92

Note the tiebreak inside the ORDER BY. Without v.year, ties produce a nondeterministic winner that changes between runs — the kind of thing that turns into a bug report titled "the dashboard keeps changing." Use RANK() instead if you want all tied rows returned, and DENSE_RANK() if you do not want gaps after ties.

Comparing to a neighbor. How each of Elena's ratings for a wine moved relative to her previous one:

SELECT tn.tasted_on, w.name, v.year, tn.rating,
       LAG(tn.rating) OVER (PARTITION BY tn.vintage_id ORDER BY tn.tasted_on) AS prev_rating,
       tn.rating - LAG(tn.rating) OVER (PARTITION BY tn.vintage_id ORDER BY tn.tasted_on) AS delta
  FROM tasting_notes tn
  JOIN vintages v ON v.id = tn.vintage_id
  JOIN wines    w ON w.id = v.wine_id
 WHERE tn.user_id = 1042
 ORDER BY w.name, tn.tasted_on;

Running total. Elena's cumulative cellar spend by month:

SELECT month, monthly_cents,
       sum(monthly_cents) OVER (ORDER BY month) AS cumulative_cents
  FROM (SELECT date_trunc('month', purchased_on)::date AS month,
               sum(purchase_price_cents * quantity)    AS monthly_cents
          FROM cellar_entries WHERE user_id = 1042
         GROUP BY 1) m
 ORDER BY month;

   month    | monthly_cents | cumulative_cents
------------+---------------+------------------
 2024-01-01 |         48200 |            48200
 2024-02-01 |         12000 |            60200
 2024-03-01 |        129000 |           189200
 2024-04-01 |         31500 |           220700

This one shows the two tools composing: GROUP BY sets the grain to one row per month, and the window then runs across those rows without collapsing them further.

A three-step question should read as three steps. WITH lets you name each stage, which turns nested subqueries into a pipeline you can review one piece at a time — and, importantly, run one piece at a time when it produces a number nobody believes.

Here is the whole module in one query: monthly spend per user for 2024, restricted to users with at least one high-rated note, ranked by total spend.

WITH engaged_users AS (
  -- stage 1: filter, using EXISTS so no fan-out is possible
  SELECT u.id, u.display_name
    FROM users u
   WHERE EXISTS (SELECT 1 FROM tasting_notes tn
                  WHERE tn.user_id = u.id AND tn.rating >= 90)
),
monthly AS (
  -- stage 2: set the grain — one row per user per month
  SELECT ce.user_id,
         date_trunc('month', ce.purchased_on)::date AS month,
         sum(ce.purchase_price_cents * ce.quantity)  AS spend_cents
    FROM cellar_entries ce
   WHERE ce.purchased_on >= DATE '2024-01-01'
     AND ce.purchased_on <  DATE '2025-01-01'
   GROUP BY ce.user_id, 2
),
totals AS (
  -- stage 3: rank users by annual spend, keeping the monthly rows
  SELECT m.*,
         sum(m.spend_cents) OVER (PARTITION BY m.user_id)               AS year_cents,
         DENSE_RANK() OVER (ORDER BY sum(m.spend_cents)
                            OVER (PARTITION BY m.user_id) DESC)         AS spend_rank
    FROM monthly m
)
SELECT eu.display_name, t.month, t.spend_cents, t.year_cents, t.spend_rank
  FROM totals t
  JOIN engaged_users eu ON eu.id = t.user_id
 ORDER BY t.spend_rank, t.month;

 display_name  |   month    | spend_cents | year_cents | spend_rank
---------------+------------+-------------+------------+------------
 Elena Vasquez | 2024-01-01 |       48200 |     220700 |          3
 Elena Vasquez | 2024-02-01 |       12000 |     220700 |          3
 Elena Vasquez | 2024-03-01 |      129000 |     220700 |          3
 Elena Vasquez | 2024-04-01 |       31500 |     220700 |          3

Each stage has a declared grain: one row per user, then one row per user-month, then the same rows enriched. When the numbers come out wrong, you can select from any stage alone and find the step where the grain changed — which is a debugging property, not an aesthetic one.

Everything so far has been about queries that are wrong. This one is about queries that are right, individually, and catastrophic collectively.

An ORM's lazy-loading default makes related data look like attribute access. You fetch Elena's cellar entries — one query — and then the template renders entry.vintage.wine.name for each. Each of those attribute accesses issues its own query. For 200 entries, that is 1 + 200 + 200 queries, each individually fast: 0.4 ms of database time, 0.6 ms of round trip, no slow-query log entry anywhere. The page takes 1.2 seconds and nothing looks broken.

The symptom in production is unmistakable once you know it: latency scales linearly with list size, so it is fine in staging with 8 rows and terrible for the collector with 3,000; and the logs show hundreds of identical queries differing only in a bind parameter.

Consuming a bottle from Elena's cellar is three operations: read quantity for entry 88213, decide whether it is greater than zero, write quantity - 1. The invariant — never record consumption of a bottle you do not have — is a property of that whole sequence, not of any single statement in it. Between the read and the write, the world can change.

A transaction makes a sequence of statements atomic: everything commits or nothing does. BEGIN opens it, COMMIT makes every change visible together, ROLLBACK discards them as if nothing happened. That much handles failure — a crash halfway through a two-table update cannot leave one table changed. But atomicity alone does not handle concurrency, which is the harder half and the reason isolation levels exist.

Restate the thesis in its concurrent form: constraints protect single rows against bad values; transactions protect multi-step stories against interruption; isolation levels decide how much of other transactions' work your transaction can see while it runs. Every anomaly in the next section is a specific answer to that last question turning out to be the wrong one for your invariant.

Six anomalies matter in practice. Each gets a one-line definition and a schedule.

Dirty read — reading data another transaction has written but not committed. Postgres never permits this at any isolation level, so it is here for vocabulary completeness and for reading other engines' documentation.

Non-repeatable read — reading the same row twice in one transaction and getting different values, because someone committed in between.

Phantom — re-running the same range query and finding rows that were not there before.

Lost update — the one that costs money:

T1 (phone)                       T2 (tablet)
1. BEGIN
2. SELECT quantity               
     FROM cellar_entries
    WHERE id = 88213;   -- 2
3.                               BEGIN
4.                               SELECT quantity
                                   FROM cellar_entries
                                  WHERE id = 88213;   -- 2
5. UPDATE cellar_entries
     SET quantity = 1           -- computed in the app: 2 - 1
    WHERE id = 88213;
6. COMMIT
7.                               UPDATE cellar_entries
                                   SET quantity = 1   -- also 2 - 1
                                  WHERE id = 88213;
8.                               COMMIT

Two bottles consumed. quantity = 1. One bottle unaccounted for.

Both transactions are individually correct. Both read a true value. The CHECK (quantity >= 0) constraint does not fire, because 1 is a perfectly legal quantity. Nothing errors, and the cellar is now wrong by one bottle — discovered eventually by a user who counts.

Read skew — reading two related values from different moments and computing a total that was never true at any instant. This is the anomaly that corrupts reports rather than rows.

Write skew — two transactions each read an overlapping set, each make a decision that is correct given what they read, and each write to different rows, so nothing conflicts and the combined result violates an invariant neither transaction broke alone. Concretely: a rule that a shared cellar must retain at least one owner; two administrators concurrently demote the two remaining owners; each sees the other still in place; both commit; the cellar has zero owners. This one matters most because, as the next section shows, Postgres's REPEATABLE READ still permits it.

The ANSI standard defines four isolation levels by the anomalies they must prevent. That is the key to reading them correctly: the levels are upper bounds on permitted anomalies, not descriptions of implementations. An engine is free to be stricter, and Postgres is — which means "we run at READ COMMITTED" describes a different set of possible failures on Postgres than on MySQL or Oracle. Portability claims about isolation must be re-verified per engine, every time.

Postgres offers three distinct levels (requesting READ UNCOMMITTED gets you READ COMMITTED):

READ COMMITTED (the default) — each statement sees a snapshot taken at the moment that statement began. You never read uncommitted data, but two statements in one transaction can see different worlds. This is where read skew, non-repeatable reads, and phantoms live.

REPEATABLE READ — one snapshot for the entire transaction, taken at its first statement. This is snapshot isolation, and here is the part that matters: it is stronger than the ANSI name implies. ANSI REPEATABLE READ permits phantoms; Postgres's implementation does not, because a snapshot simply does not contain rows that were not there when it was taken. What it does still permit is write skew, since two transactions writing to different rows never conflict. And if your transaction tries to update a row that changed after your snapshot, you get a serialization failure (error 40001) rather than a silent overwrite.

SERIALIZABLE — Serializable Snapshot Isolation. Postgres tracks read/write dependencies between concurrent transactions and aborts one if the set could not have arisen from any serial execution. This is genuine serializability, including write skew. The price is real and unavoidable: transactions can fail with 40001 at commit, and you must retry them. A codebase using SERIALIZABLE without a retry loop is not using SERIALIZABLE; it is using intermittent errors.

Four ways to make consume-a-bottle safe, in the order you should reach for them.

1. The atomic statement. Do the read, the decision, and the write in one statement, so no interleaving exists:

UPDATE cellar_entries
   SET quantity = quantity - 1
 WHERE id = 88213 AND quantity > 0
RETURNING quantity;

Postgres locks the row for the duration of the update; a concurrent writer blocks, then re-evaluates quantity > 0 against the committed value. If the row is gone to zero, zero rows are returned and the application knows the operation did not happen — the return value is the answer, with no separate check. The CHECK (quantity >= 0) constraint remains as defense in depth: if anyone ever writes a computed value instead of this form, the constraint catches it. This is the default choice, and it should be uncomfortable to depart from it.

2. SELECT ... FOR UPDATE. When the decision genuinely requires application logic between read and write — checking a plan limit, computing a price from several rows — lock the rows pessimistically:

BEGIN;
SELECT quantity, purchase_price_cents
  FROM cellar_entries
 WHERE id = 88213
   FOR UPDATE;              -- other transactions block here
-- application logic runs
UPDATE cellar_entries SET quantity = 1 WHERE id = 88213;
COMMIT;

This serializes access to the hot row, which is the cost: contention scales badly if that row is popular. Use FOR UPDATE SKIP LOCKED when you are pulling work from a queue and want the next unlocked row rather than to wait.

3. SERIALIZABLE with a retry loop. When the invariant spans rows in a way that no single statement or row lock captures — the write-skew shape — let Postgres detect the conflict and retry on 40001:

for attempt in range(3):
    try:
        with conn.transaction(isolation_level="SERIALIZABLE"):
            owners = count_owners(cellar_id)
            if owners <= 1:
                raise LastOwnerError()
            demote(member_id)
        break
    except SerializationFailure:      # SQLSTATE 40001
        sleep(0.05 * 2 ** attempt + random.uniform(0, 0.05))
else:
    raise TooManyRetries()

Two requirements are not optional. The retry must be bounded, and the transaction body must be safe to run again — which is to say idempotent, or free of side effects outside the database. A retry loop that re-sends an email each attempt has traded a data bug for a customer-facing one.

4. An application-level mutex. Named here to reject it. A lock in your process coordinates the threads in that process, and your second application instance knows nothing about it. This is the check-then-act race wearing a hat. (A distributed lock in Redis is a different proposal with different failure modes — but if you find yourself designing one to protect a single row in a database that already offers row locks, stop.)

Types are the cheapest invariants available, and the defaults people reach for are frequently wrong in ways that surface much later.

Money is never floating point. float8 cannot represent 0.1 exactly, so summing 40,000 prices for a portfolio report accumulates error, and finance opens a ticket about a discrepancy of a few cents that nobody can reproduce in a unit test. Use integer cents, as the running schema does with purchase_price_cents, or numeric(12,2) where fractional units are real. Integer cents also removes an entire category of rounding argument at the API boundary, since there is no fractional value to round.

Time is timestamptz, always. The name is misleading: timestamptz does not store a time zone. It stores an absolute moment (UTC internally) and renders it in the session's zone, which is what you want. timestamp without time zone stores a wall-clock reading with no indication of where the clock was — it stores a lie, and the lie compounds when your first non-US user files a bug about a purchase dated tomorrow. The exception is a genuine calendar date with no instant attached: purchased_on is a date, because the domain fact is "the day I bought it," not a moment.

Exactness where it matters. numeric(3,1) for abv and numeric(4,3) for a recommendation score are deliberate: both are compared and displayed, and a score of 0.847 must not become 0.8470000000000001 in a JSON response.

Text. Prefer text over varchar(n) unless the length limit is a real domain invariant — in Postgres they perform identically, and an arbitrary varchar(50) is a migration waiting to happen the first time a producer name runs long. citext for email gives case-insensitive comparison and uniqueness in one move, which is why the running schema uses it: UNIQUE on a plain text email column happily admits both Elena@marchpane.example and elena@marchpane.example, and now the account recovery flow has two candidates.

Arrays are the honest middle ground for the m3 grapes problem. A text[] of grape varieties is a small ordered set with no attributes of its own and no foreign keys needed, and Postgres can index it with GIN so containment queries (grapes @> ARRAY['Merlot']) are fast and exact — no substring false positives. The moment grapes need attributes (a color, a canonical name, a per-grape article), it becomes a table. The array is not a way to avoid the junction table; it is what you use when the junction table would have exactly two columns forever.

jsonb is an excellent tool with a narrow brief. It stores a parsed, binary JSON document; it supports containment and path queries; it can be indexed with GIN. Two situations genuinely call for it.

Shapes you do not control. The recommendation engine's raw scoring output — feature weights, model version, per-signal contributions — is stored for audit and debugging. Its structure changes when the model changes, you do not query into it in normal operation, and the reason you keep it is to answer "why did we recommend this" six months later. That is a document, and forcing it into columns would mean a migration every time the model team ships. The running schema keeps the decision in typed columns (score numeric(4,3) NOT NULL CHECK (score BETWEEN 0 AND 1), reason text NOT NULL) and could keep the provenance blob in jsonb alongside — the invariants stay enforced, the opaque payload stays opaque.

Genuinely sparse attributes with no invariants. Imported metadata from a partner catalog where every partner sends different fields and none of them drive behavior.

Everything else is a column. The test is four questions, and one yes is enough:

A membership role, a bottle size, an order status: a small set of allowed values. Three tools, and the choice turns on two questions — who needs to extend the set, and do the values carry attributes of their own?

In practice: bottle size is an ENUM ('375ml', '750ml', '1.5L', '3L') — the set is fixed by the physical world, engineering owns it, and sorting in declaration order is a small free win. Membership role from the m2 worksheet is an ENUM for the same reason. If roles later needed permission bitmaps or display names, that would be the signal to promote to a lookup table — the same promotion rule as m2 §4, applied to a value set instead of an attribute.

A migration is a schema change applied to data that already exists while traffic continues. Two failure modes matter: the change locks a table long enough to stall the application, and the change is not safely reversible if the deploy goes wrong.

The AI-review angle returns here with teeth. Ask an assistant to add a required column and you will get a single, clean, correct-looking statement — one that takes an ACCESS EXCLUSIVE lock on a 2.1-million-row table and rewrites it while every query waits behind it. The statement is not wrong; it is unreviewed for lock behavior, and lock behavior is the reviewer's job. Know these:

The pattern that makes all of this safe is expand/contract, and its property is that every step is independently deployable and independently reversible.

Expand — add the new shape alongside the old, nullable and unconstrained. Nothing reads it; nothing breaks. Dual-write — deploy code that writes both shapes, so new rows are correct in both. Backfill — copy old to new in batches, with a progress query so you know where you are. Switch reads — deploy code reading the new shape; the old is still maintained, so a rollback is a code deploy rather than a data recovery. Contract — after a soak period, add the constraints, stop dual-writing, drop the old columns.

Two closing rules. Migrations are forward-only — a "down" migration that drops a column you have been writing to for a week does not restore the data, so the real rollback plan is the previous deploy plus the old shape still being present, which is exactly what expand/contract preserves. And rehearse against a copy at production scale; the timing difference between 10,000 rows and 2.1 million is the difference between a migration and an incident.

The relational bet is: model the domain, ask questions later. You capture entities, relationships, and invariants without reference to any particular query, and the planner figures out how to answer whatever you ask. When a new question arrives — "average rating by region over the last 90 days" — you write it, and it works, possibly slowly until you add an index. The schema is query-agnostic by construction.

The single-table bet is: enumerate the access patterns first, then shape storage to serve them. In DynamoDB's single-table design, you list every query the application will make, design partition and sort keys so each is a direct key lookup or a narrow range scan, and store heterogeneous item types together so that one request retrieves a user and their cellar entries in a single round trip at predictable cost. Queries are decided at design time and served at fixed latency regardless of table size.

Neither is the default. They are bets about how well you can predict the questions — and the same product can justify either answer at different stages of its life, which is why this is a decision with a review date rather than a permanent identity.

Each pitch below is written as its advocate would write it, because a comparison you win against a straw man tells you nothing about your own system.

The case for DynamoDB single-table. Reads are single-digit milliseconds at any table size, because a key lookup is a hash and a disk read — there is no planner, no statistics, no plan regression at 3am when a row estimate shifts. There are no connection pools to size and no vacuum to tune; scaling is a configuration value, not a project. The operational surface is genuinely someone else's pager. And the two objections relational advocates reach for first are both out of date: TransactWriteItems provides real ACID transactions across up to 100 items, and strongly consistent reads are available per-request at double the read cost. Condition expressions enforce per-item invariants at write time — a conditional write that fails is the same mechanism as a constraint violation. Cost is predictable per request rather than per provisioned instance, which for spiky workloads is materially cheaper.

What it charges. A new access pattern that your keys do not serve requires either a new global secondary index or reshaping the data — a migration of a different and often larger kind than adding an index in Postgres. Ad hoc joins and aggregates do not exist; "average rating by region for the last 90 days" is a scan or an export to an analytics system, not a query. Invariants that span items — the multi-row rules from m1 §5, and any uniqueness constraint on a non-key attribute — largely move into application code, which means re-litigating this entire guide's first module by hand, in every service. And the transaction limits are real: 100 items, no interactive transactions, no "read, think, write" across a network call.

The case for Postgres. New questions cost a query, not a migration — which for a product still discovering what it is worth is the dominant consideration. Every invariant in m1 is enforced by one component on every write path. Transactions span arbitrary rows and tables with real isolation guarantees. One system serves both product and analytics at moderate scale, so there is no pipeline between the operational store and the ability to answer a question. And the ecosystem is deep: every ORM, every BI tool, every engineer you hire.

What it charges. You own the planner's moods — plan regressions, stale statistics, the occasional query that was fast for a year and is not. You own connection management, and a serverless architecture will need a pooler in front. You own vacuum, bloat, and the operational cost of a primary you cannot horizontally scale by configuration. Past a very large single node, the scaling story is real work: read replicas, then partitioning, then sharding, in ascending order of pain.

The load-bearing question: how well are the access patterns known in advance, and how stable are they? If you can write them all down today and believe the list in a year, single-table is a strong choice and its constraints cost you little. If the list is a guess — a product still finding its shape, an analytics surface that grows with the business — then the relational bet is the one that pays, because the cost of a wrong prediction is paid in migrations rather than in queries.

Four secondary axes, in the order that usually matters:

Measured scale, not aspired scale. 2.1 million rows is not a scaling problem. Ten million is not a scaling problem. A single well-indexed Postgres node handles workloads far larger than most teams predicting they will outgrow it. Bring a number: current row counts, current writes per second, current p99. If you cannot produce them, that is the finding.

Invariant density. How much of m1's toolkit does this domain actually need? Financial records, permissions, inventory, anything with regulatory exposure — high density, and moving those invariants into application code is a large, permanent tax. An event log or a session store has almost none, and the relational advantage largely evaporates.

Analytics needs. If the product's questions are its value — dashboards, cohort analysis, ad hoc investigation — ad hoc queryability is the feature, not a convenience.

Team fluency. Single-table design done badly is worse than either option done well, and it is genuinely harder to learn: the modeling process is unfamiliar, and mistakes surface as "we cannot answer that" months later. This is a legitimate input, not an excuse.

And the hybrid, named without hedging: Postgres as system of record with a specific proven hot path offloaded to DynamoDB (or a cache) is a common, honest end state. The rule is to draw the boundary along the access-pattern-certainty line — offload the read-heavy, pattern-stable, invariant-light workload, and keep the invariant-dense writes where the constraints and transactions are.

The guide's field reference, arranged the way you will meet these — symptom first, because that is what you are handed.

Twenty-one entries, and the useful property of the list is that the left column is what someone actually brings you — a ticket, a graph, a complaint — while the middle column is the thing you had to know in advance to recognize it.

One question ran this entire guide: what invariant is at stake, and which layer enforces it? It is worth seeing it applied to the three artifacts you will actually be handed.

Reviewing a proposed schema — generated or human. What illegal states can this represent? For each business rule the product believes, which layer enforces it, and is "nobody" on the list? Is every natural key declared UNIQUE alongside its surrogate? Is every relationship's cardinality declared and its key on the correct side? Does any plural stored in a single column have a defended reason not to be a table? For the three questions the product will obviously ask next, can this schema answer them without a migration? Four minutes, and it catches more than a careful read of the application code will.

Reviewing a slow query with its plan — start with estimated versus actual rows, because a plan built on a bad estimate is a rational answer to a false premise and no index will fix it. Then ask what grain the result claims and whether any join could have changed it. Then check whether the proposed index is justified by this named query and whether its column order matches the predicate and the sort. Then ask what it costs on the write side and whether it makes an existing index redundant.

Reviewing a migration — what lock does each statement take and for how long on production row counts? Is each step independently deployable and reversible, and where is the point of no return? What happens to rows that violate the new constraint, and have you counted them? Is the backfill batched, and does it have a progress query? And the one people skip: what is the soak period, and what signal would tell you to stop?

None of this requires being a database expert. It requires holding one question steadily and refusing to accept "the application handles it" as an answer to it — because the application handles it on the code paths that exist today, and the schema handles it on all of them, forever. That asymmetry is the whole guide.