A data model is a set of claims about what is always true. Normalization decides where each fact lives; constraints decide whether the claim can be violated; isolation level decides whether two concurrent transactions can violate it together while each one looks correct on its own.

That last part is where most interviews get interesting, and it is the part people skip.

Normalization, and when to stop

The forms that matter

1NF — one value per cell. No tags column holding "urgent,billing,eu".

2NF — no non-key column depends on only part of a composite key. If the key is (order_id, product_id) and you store product_name, that depends on product_id alone. It belongs in products.

3NF — no non-key column depends on another non-key column. Storing zip_code and city together, where city is determined by zip_code, is the classic violation.

BCNF is a stricter 3NF for tables with overlapping candidate keys. Know the name, know it is rarely the deciding factor in practice, and move on.

The one-line version worth being able to say: every non-key column depends on the key, the whole key, and nothing but the key.

Why it is the default

Normalization removes update anomalies. If a customer's email lives in one place, changing it is one write and it cannot end up inconsistent. If it is copied into every order, it is N writes and the first failure leaves you with two versions of the truth.

When to denormalize on purpose

Three cases where duplication is correct, not lazy:

Historical snapshots. An invoice line must keep the price as it was charged. That is not a denormalized copy of products.price — it is a different fact. The current price and the charged price are genuinely two pieces of information, and joining to get the second one is a bug.

Read-path cost you have actually measured. A comment_count on posts avoids counting a million rows on every page load. You have now taken on the job of keeping it correct (triggers, or a scheduled recompute, or both). Take it on knowingly.

Cross-service boundaries. If orders and customers live in different services, the order service storing the customer name at order time is not denormalization — it is a copy made deliberately because the other service might be down.

Keys

Natural versus surrogate

A natural key is data that already identifies the row: email, ISBN, country code. A surrogate key is a meaningless identifier you generate.

Default to a surrogate. Natural keys change — people change email addresses, companies change tax IDs, countries change codes — and a changing primary key cascades through every foreign key that references it. Surrogate keys are immutable by construction.

Keep the natural key as a UNIQUE column. You get identity stability and the uniqueness guarantee.

bigint versus UUID

The index locality row is the one that costs money. A v4 UUID inserts at a random point in the B-tree, so every insert dirties a different page, the cache hit rate collapses, and pages split messily. Sequential keys append to the rightmost page, which stays hot.

UUID v7 puts a timestamp in the high bits, so inserts are roughly ordered again. It is the right default when you need client-generated ids — which you do if the client must know the id before the round trip, or if you merge data from multiple databases.

bigserial leaking business volume is a real concern for public-facing ids (/orders/1042 tells a competitor your order count). The fix is not necessarily UUIDs — a separate public id column works and keeps the internal key compact.

Composite keys

For join tables, PRIMARY KEY (order_id, product_id) is usually right: it is the identity, and it gives you the uniqueness constraint for free. Adding a surrogate id to a pure join table buys you nothing and lets you insert the same pair twice unless you add the unique constraint anyway.

Column order in a composite key matters, because it determines the index order — see the leftmost-prefix rule in the query note.

Constraints are the model

This is the section most worth having strong opinions about.

A constraint in the schema is enforced for every writer: your application, the second service someone adds next year, the migration script, the engineer fixing data in psql at 2am. A check in application code is enforced for exactly the code path that contains it.

CREATE TABLE prices (
  id                bigserial PRIMARY KEY,
  option_id         bigint NOT NULL REFERENCES plan_options(id),
  amount_cents      bigint NOT NULL CHECK (amount_cents >= 0),
  currency          char(3) NOT NULL,
  provider_price_id text NOT NULL UNIQUE,
  active            boolean NOT NULL DEFAULT true,
  created_at        timestamptz NOT NULL DEFAULT now()
);

Foreign keys and delete behaviour

ON DELETE is a modelling decision, not a detail:

• RESTRICT / NO ACTION — refuse the delete. The default, and the right choice when the child is a real record.
• CASCADE — delete the children. Correct for genuinely owned data (an order and its line items). Dangerous everywhere else, because one delete can walk a long way.
• SET NULL — keep the child, forget the parent. Requires a nullable column, and you are saying an orphan is a valid state.

Partial unique indexes

The most useful constraint most people do not reach for. "Only one active price per option":

CREATE UNIQUE INDEX one_active_price
  ON prices (option_id)
  WHERE active;

Rows where active is false are invisible to this index, so you can keep the full history and still guarantee exactly one live row. No trigger, no application lock, no race.

EXCLUDE constraints

For "no two of these may overlap", which UNIQUE cannot express:

CREATE EXTENSION IF NOT EXISTS btree_gist;
 
ALTER TABLE bookings ADD CONSTRAINT no_double_booking
  EXCLUDE USING gist (
    room_id WITH =,
    during  WITH &&
  );

Two bookings for the same room whose time ranges intersect are now impossible. Reach for this any time the rule is about ranges — schedules, price validity windows, employment periods.

The counter-argument, taken seriously

Constraints are not free.

• The error is a database error. 23505 with an index name has to be translated at the boundary into something a client can act on, which couples your error handling to an index name.
• Adding one to a live table needs CREATE UNIQUE INDEX CONCURRENTLY, which cannot run inside a transaction and leaves an invalid index behind if it fails.
• The rule becomes invisible in the codebase. It lives in a migration file nobody reads.
• Relaxing it later is a production migration rather than a one-line code change.

How to say it: "Constraints bill you at migration time. Missing constraints bill you at repair time."

Transactions and isolation

What each level prevents

The anomalies, in the order they are usually taught:

• Dirty read — you see another transaction's uncommitted write. PostgreSQL never allows this at any level.
• Non-repeatable read — you read a row twice in one transaction and get different values, because someone committed in between.
• Phantom read — you run the same WHERE twice and get a different set of rows.
• Lost update — two transactions read a value, both compute a new one from it, and the second overwrites the first.
• Write skew — two transactions read overlapping data, each writes something different, and together they break an invariant neither one broke alone.

READ COMMITTED is the PostgreSQL default and permits everything except dirty reads. Each statement sees a fresh snapshot, which surprises people: two SELECTs in one transaction can legitimately disagree.

REPEATABLE READ gives the whole transaction one snapshot. In PostgreSQL it also prevents phantoms — stricter than the SQL standard requires — but not write skew.

SERIALIZABLE guarantees the result matches some serial order. It prevents write skew. It costs you serialization failures (40001) that the application must catch and retry, and that retry loop is the reason teams avoid it. If you propose SERIALIZABLE, propose the retry loop in the same sentence.

-- T1 (READ COMMITTED)                   -- T2
BEGIN;
SELECT balance FROM accounts
  WHERE id = 1;  -- 100
                                         UPDATE accounts SET balance = 50
                                           WHERE id = 1;
                                         COMMIT;
SELECT balance FROM accounts
  WHERE id = 1;  -- 50

-- T1 (READ COMMITTED)                   -- T2
BEGIN;
SELECT count(*) FROM orders
  WHERE status = 'pending';  -- 3
                                         INSERT INTO orders (status)
                                           VALUES ('pending');
                                         COMMIT;
SELECT * FROM orders
  WHERE status = 'pending';  -- 4 rows

-- lost: two sessions both read 5, both write 6
SELECT value FROM counters WHERE id = 1;
UPDATE counters SET value = 6 WHERE id = 1;
 
-- safe at every level: the row lock makes the second session re-evaluate
UPDATE counters SET value = value + 1 WHERE id = 1;

BEGIN ISOLATION LEVEL SERIALIZABLE;
  SELECT count(*) FROM prices WHERE option_id = 7 AND active;
  -- ...
COMMIT;

await prisma.$transaction(
  async (tx) => {
    const active = await tx.price.count({ where: { optionId: 7, active: true } })
    if (active !== 1) throw new Error("unexpected state")
    await tx.price.updateMany({
      where: { optionId: 7, active: true },
      data: { active: false },
    })
    await tx.price.create({ data: { optionId: 7, amountCents: 4000, active: true } })
  },
  { isolationLevel: Prisma.TransactionIsolationLevel.Serializable },
)

Write skew, concretely

Two admins change the price of the same plan option at the same time:

-- T1                                    -- T2
BEGIN;                                   BEGIN;
SELECT count(*) FROM prices
  WHERE option_id = 7 AND active;
-- 1
                                         SELECT count(*) FROM prices
                                           WHERE option_id = 7 AND active;
                                         -- 1
UPDATE prices SET active = false
  WHERE option_id = 7 AND active;
INSERT INTO prices (option_id, amount_cents, active)
  VALUES (7, 4000, true);
                                         UPDATE prices SET active = false
                                           WHERE option_id = 7 AND active;
                                         INSERT INTO prices (option_id, amount_cents, active)
                                           VALUES (7, 4500, true);
COMMIT;                                  COMMIT;

Both succeed. No deadlock, no error, no lock conflict — they touched different rows. Option 7 now has two active prices, and the next SELECT ... WHERE active returns whichever the planner reaches first.

Three fixes, in increasing order of how much I would trust them:

1. SERIALIZABLE — correct, and now one of the two transactions fails with 40001 and must be retried. Applies to every transaction in the database, not just this one.
2. Lock the parent row — SELECT ... FROM plan_options WHERE id = 7 FOR UPDATE before the read. Serialises writers through a row you both must take. Correct, and it depends on every writer remembering to do it.
3. The partial unique index — the second INSERT raises 23505 and the transaction fails. It cannot be forgotten, it applies to writers that do not exist yet, and it turns an invisible data corruption into a visible error.

Locking

SELECT ... FOR UPDATE takes a row lock until the transaction ends. FOR NO KEY UPDATE is weaker and lets foreign key checks through — the right choice when you are not changing the key.

Deadlocks happen when two transactions take the same locks in different orders. PostgreSQL detects the cycle and kills one with 40P01. Two mitigations: acquire locks in a consistent order (e.g. always ascending by id), and keep transactions short.

Advisory locks (pg_advisory_xact_lock(key)) lock an arbitrary number rather than a row. Useful for serialising a job that has no natural row to lock — a nightly recompute, a leader election, an import that must not run twice.

Modelling time

Most "we lost data" incidents are a modelling failure about time.

Insert-only history. Do not UPDATE a price — insert a new row and deactivate the old one. You keep the audit trail for free, and anything that referenced the old row still resolves.

Valid time versus transaction time. Valid time is when the fact was true in the world. Transaction time is when the database learned it. They differ constantly: a discount effective from 1 January, entered on 5 January. If you only store one, you cannot answer "what did we believe on the 3rd?" — which is exactly the question an audit asks.

Effective dating with a range and an exclusion constraint:

CREATE TABLE price_periods (
  id        bigserial PRIMARY KEY,
  option_id bigint NOT NULL REFERENCES plan_options(id),
  amount_cents bigint NOT NULL,
  valid     daterange NOT NULL,
  EXCLUDE USING gist (option_id WITH =, valid WITH &&)
);

Soft deletes. deleted_at timestamptz is convenient and has two costs people forget: every query must remember WHERE deleted_at IS NULL (one forgotten filter is a data leak), and unique constraints stop working, because a deleted row still occupies the value. The partial index fixes the second:

CREATE UNIQUE INDEX users_active_email
  ON users (email) WHERE deleted_at IS NULL;

Always store timestamptz, never timestamp. timestamp has no time zone and silently means something different depending on who wrote it.

Money

Never floating point. 0.1 + 0.2 is not 0.3, and a financial report that is off by a cent is a support ticket with your name on it.

Two acceptable choices: numeric(12,2) — exact decimal arithmetic, slower, no rounding surprises; or bigint storing minor units (cents), which is fast and exact but forces every caller to know the scale.

Whichever you pick, store the currency alongside the amount. An amount_cents column with no currency column is a bug waiting for your first international customer. And do not assume two decimal places: JPY has zero, KWD has three.

Enums, and how to store a fixed set

Three options:

• Native enum type — compact and validated, but adding a value requires ALTER TYPE and removing one is genuinely painful.
• CHECK constraint on text — readable, easy to change with a migration, no join.
• Lookup table with a foreign key — heavier, and the only option if the set needs extra attributes (a display label, a sort order, an is-active flag) or must be editable by users at runtime.

Decide by who owns the list. Developer-owned and stable: CHECK. Product-owned or carrying attributes: lookup table.

Antipatterns worth naming

Comma-separated values in a column. Breaks 1NF, cannot be indexed usefully, cannot have a foreign key. Use a join table, or a real array type if the values are not entities.

Entity-Attribute-Value. A generic (entity_id, attribute_name, value) table. You have rebuilt a schemaless store inside a relational database and given up types, constraints and sane queries. Getting five attributes for one entity is a five-way self-join. If the attributes really are open-ended, use jsonb — it is the same trade-off, honestly labelled, and it indexes.

Polymorphic foreign keys. (commentable_type, commentable_id) pointing at either posts or videos. No foreign key is possible, so referential integrity is gone. The alternatives: one join table per parent type (post_comments, video_comments), or a shared parent table that both types reference.

Nullable columns used as a state machine. Three nullable timestamps whose combinations encode status means most combinations are invalid and nothing stops you writing them. Add a status column with a CHECK, or a CHECK constraint relating the columns.

JSONB, used deliberately

Legitimate when the shape is genuinely unknown or per-tenant (custom form fields, third-party webhook payloads you store verbatim, sparse attributes across thousands of product types).

ALTER TABLE products ADD COLUMN attributes jsonb NOT NULL DEFAULT '{}'::jsonb;
CREATE INDEX products_attributes ON products USING gin (attributes);

What you give up: column-level NOT NULL and CHECK, foreign keys into the document, accurate planner statistics (estimates inside JSONB are much weaker), and the ability to rename a field with a migration.

The rule: if you find yourself writing WHERE attributes->>'status' = 'active' on a hot path, status was a column all along.

Multi-tenancy

Three models:

Shared schema with tenant_id on every table. Cheapest to operate, one migration for everyone. Every query must filter by tenant, and every composite index should lead with tenant_id. One forgotten filter is a cross-tenant data leak, which is why row-level security is worth the setup:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id')::bigint);

Now the database enforces isolation even if a query forgets.

Schema per tenant. Stronger isolation, migrations multiply by tenant count, and the catalogue struggles in the thousands.

Database per tenant. Strongest isolation and easiest per-tenant restore. Most expensive per tenant; usually reserved for enterprise contracts that require it.

The outbox pattern

"Write a row and publish an event" is two systems and no shared transaction. If you write then publish, a crash in between loses the event. If you publish then write, a crash loses the row.

Write both to the same database, in the same transaction:

BEGIN;
INSERT INTO orders (...) VALUES (...);
INSERT INTO outbox (aggregate_id, event_type, payload)
  VALUES (:order_id, 'order.created', :payload);
COMMIT;

A separate process reads outbox and publishes. Delivery becomes at-least-once — the publisher can crash after sending and before marking — so consumers deduplicate on event id, exactly as in the webhook section.

Practice

Recall checklist

• State 3NF in one sentence
• Three cases where denormalization is correct
• Why an invoice line's price is not a denormalized copy
• Why surrogate keys are the default, and where the natural key goes
• Why UUID v4 hurts insert performance and what v7 changes
• The six constraint types and what each guarantees
• Write the partial unique index for "one active row per parent" from memory
• What EXCLUDE USING gist expresses that UNIQUE cannot
• Four real costs of putting rules in the schema, and the one-line rebuttal
• Name five anomalies and which isolation levels permit each
• Walk through a write-skew interleaving out loud
• Three fixes for write skew, and the cost of each
• What SERIALIZABLE obliges the application to implement
• Two costs of soft deletes, and the index that fixes one
• Why money is never a float, and why currency is not optional
• Name three antipatterns and their replacements
• What you give up by choosing jsonb
• Why composite indexes in a multi-tenant schema lead with tenant_id
• Why the outbox pattern exists and what it makes delivery semantics