Database Data Modeling

Relational data modeling is the work of translating business meaning into entities, relationships, constraints and access paths, so that the database itself refuses states the business considers impossible. A good model makes the common queries cheap, the invariants enforceable under concurrency, and the next change survivable on a table that cannot be taken offline.

In a relational database the model is expressed as tables (one per entity or structural need), columns with types, primary keys that identify rows, foreign keys that link them, and constraints that reject invalid rows. The design is usually sketched first as an entity-relationship (ER) diagram, which shows entities, their attributes and how many of one relate to how many of another; the diagram is then translated into tables, with each entity becoming a table and each relationship becoming a foreign key or a separate table. The ER diagram is the starting point, not the model itself: the keys, constraints and indexes decide what the database actually enforces.

Start with a booking invariant. A hotel booking service must never sell the same room-night twice. A diagram with rooms, guests and bookings does not say that. The model also has to answer what a bookable unit is (a room? a room type with a count?), how a stay is represented as dates, which statuses reserve inventory and which release it, and what happens when two requests arrive in the same millisecond. If those answers live only in application code, they hold exactly as long as every code path, every script and every future teammate remembers them.

The mental model this lesson uses: a schema is a set of claims about the business that the database checks on every write. Types and NOT NULL claim that a value exists and has a shape. A foreign key claims that a referenced thing exists. A unique constraint claims that a thing is one-of-a-kind within a scope. Each claim you push into the database is one fewer rule that concurrent code can break, and each claim you leave in application code needs its own concurrency story. The rest of the article works through that idea in the order a design usually unfolds: entities, keys, normal forms, queries, awkward data types, change, and finally the double-booking race that ties it together.

SQL in this lesson is PostgreSQL syntax unless a comment says otherwise. Other engines differ in details such as how unique indexes treat NULL, which constraints exist, and how DDL locks behave, so treat the examples as illustrations to verify on your target engine and version. The storage and transaction machinery behind them is covered in Database Foundations.

The sequence below is a working order, not a waterfall. Requirements produce the invariants, entities give those invariants things to attach to, keys and constraints turn them into enforceable rules, the query list decides which structures make the model fast, and the migration lifecycle is how the model keeps up with the business afterwards. A new requirement or a production incident sends you back to the first box.

Two habits make this loop cheap. First, write invariants as sentences before drawing tables: “a room is sold for a night at most once”, “an order line records the price the customer accepted”, “an email identifies one customer within a tenant”. Each sentence should end up as a constraint, a transaction boundary, or an explicit entry on the list of rules the application must enforce. Second, list the operations early, with rough frequencies. A model that is correct on paper can still need a full scan and a huge sort for its busiest query, and a model tuned for one dashboard can make ordinary transactional updates impossible. The section on designing backward from queries returns to this.

An entity is something the business can point at and track over time. A customer is not the same thing as an order; an order line is not a comma-separated string in an order record. For each entity, write down how it is identified, how many of it relate to how many of another (cardinality), which relationships are optional, who owns it, and how its life ends. Ownership matters operationally: an order line has no meaning without its order, so the line’s key can include the order’s, while a product outlives any order that mentions it.

The figure is the entity-relationship (ER) diagram for the booking service, and it maps directly to the tables: each box is a table, each line is a foreign key relationship, and the 1 : N labels show the cardinality (one guest has many bookings, for example). The booking model has four tables. guests and rooms are independent entities. bookings records the agreement between a guest and a room for a stay. room_nightis not a business entity people talk about; it is a structural table that turns “no overlapping stays” into a uniqueness rule on (room_id, night), which a database can enforce cheaply. Tables like this are a legitimate modeling tool: when a rule is hard to state as a constraint over the natural rows, materialize the thing being protected as rows of its own.

Relationships have the same wrinkle. One customer places many orders, so the order stores customer_id. Orders and products are many-to-many, so a junction table (order_items) holds one row per line and carries attributes of the relationship, such as quantity and price. Be careful with the relationships that look one-to-one. “An order has one payment” is true until a card is declined and retried, a payment is split across two methods, or a partial refund arrives; modeling payments as many-per-order with a status costs almost nothing and avoids a migration later. The same goes for “one shipment per order” once backorders exist.

Deletion is a business decision

Decide what “delete” means for each entity, because the schema must support it. Physical removal is simplest and the foreign keys keep you honest: by default (NO ACTION) the database refuses to delete a customer who still has orders. ON DELETE CASCADE fits rows that are owned parts of a parent, such as draft cart lines, and is dangerous for financial records, where silently removing children is the wrong failure. Tombstoning (a deleted_at column) keeps history, but every query must filter it and every uniqueness rule must become a partial unique index over live rows, otherwise a deleted user blocks the email forever. Anonymization keeps the order row and scrubs the personal fields, which is often what a privacy erasure request needs while accounting still requires the transaction; confirm the legal requirement for your data rather than assuming. Archival moves cold rows elsewhere and needs a story for references that point at them.

Facts that must not change retroactively

Model temporal facts explicitly when history matters. An order’s purchased price must not silently change when the catalog price does, so the order line stores a snapshot of the price (and often the product name and tax rate) at purchase time. The catalog row answers “what does it cost today?”; the order line answers “what did this customer agree to?”. Those are different facts that happen to start with the same number, and joining the order back to the catalog collapses them.

Choose a snapshot that fits the question. If a report needs the product’s name as the customer saw it, copy the name too. If the business needs the full price history of the catalog itself, add a versioned price table with valid_from and valid_to and have the order line reference the version; that is more machinery than a snapshot column and only worth it when price history is queried in its own right. Money movements have a related rule: record them as immutable ledger entries and derive balances, with corrections as new compensating rows, rather than overwriting a balance column. The history then answers questions that an updated-in-place number cannot.

Finally, naming is part of the model. Name tables after business concepts, use one convention, and make foreign keys obvious (customer_id). A schema full of col1 and tbl_xhides the very invariants this lesson is about.

A primary key identifies one row. Natural keys carry business meaning but can change, which makes every referencing row change with them; an email address is the classic example. Surrogate keys (an identity column, or a UUID) are stable and small, and they are the usual choice for referenced tables. Surrogate does not mean “no business key”: keep the natural identifier as a separate unique constraint, because the surrogate will happily let you insert the same real-world thing twice. Identity values are also not gap-free. A rolled-back insert still consumes a value, so never use them as invoice numbers or anything an auditor expects to be consecutive.

Unique constraints enforce business uniqueness under concurrency, because the index check and the insert are one atomic step inside the engine. Foreign keys express referential integrity. NOT NULL and CHECK constraints encode invariants local to a row. Each constraint should describe real semantics, not the first thing that was true in the sample data. A unique email is not obviously global, nor case-sensitive: the example below indexes lower(email) so that Ana@Example.com and ana@example.com collide, which is a policy decision you make deliberately. Identifiers from other systems are usually unique only inside a scope.

-- PostgreSQL 10+ (identity columns). Runnable as written.
CREATE TABLE customers (
  id    BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL
);
-- Business uniqueness is case-insensitive here, so the index says so.
CREATE UNIQUE INDEX customers_email_key ON customers (lower(email));

CREATE TABLE products (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name        TEXT   NOT NULL,
  price_cents BIGINT NOT NULL CHECK (price_cents >= 0)
);

CREATE TABLE orders (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id),
  status      TEXT   NOT NULL CHECK (status IN ('pending','paid','cancelled')),
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  order_id         BIGINT  NOT NULL REFERENCES orders(id),
  line_no          INTEGER NOT NULL,
  product_id       BIGINT  NOT NULL REFERENCES products(id),
  quantity         INTEGER NOT NULL CHECK (quantity > 0),
  unit_price_cents BIGINT  NOT NULL CHECK (unit_price_cents >= 0),
  PRIMARY KEY (order_id, line_no)
);

-- Multi-tenant: an external identifier is unique inside a tenant, not globally.
CREATE TABLE accounts (
  id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id   BIGINT NOT NULL,
  external_id TEXT   NOT NULL,
  UNIQUE (tenant_id, external_id)
);

This schema snapshots price per order line. It still needs rules the DDL cannot express: allowed status transitions (next section), and an explicit approach to currency, taxes, rounding and refunds. Those belong in the model too, as columns and tables, not as assumptions in a service.

When a constraint fires, the database returns a SQLSTATE code and, usually, the constraint name. Map them deliberately instead of letting them surface as generic 500s: a duplicate key is typically a 409 Conflict, a foreign-key violation on a client-supplied id is a 422 or 404, and a check violation means a validation layer missed something.

-- What the constraints above report, as SQLSTATE codes your application can map to answers:
INSERT INTO customers (email) VALUES ('Ana@Example.com');
INSERT INTO customers (email) VALUES ('ana@example.com');
--   ERROR 23505 unique_violation       duplicate key ... "customers_email_key"
INSERT INTO orders (customer_id, status) VALUES (1, 'refunded');
--   ERROR 23514 check_violation        ... "orders_status_check"
INSERT INTO orders (customer_id, status) VALUES (99999, 'paid');
--   ERROR 23503 foreign_key_violation  ... "orders_customer_id_fkey"

Two operational notes. PostgreSQL creates an index for a primary key and a unique constraint but not for the referencing side of a foreign key, so orders.customer_id needs its own index if you ever look orders up by customer or delete customers; without it each parent delete scans the child table. And if a rule truly needs to be checked at the end of a transaction (two rows that reference each other), constraints can be declared DEFERRABLE; use that sparingly, since it moves the failure from the statement that caused it to COMMIT. Index design itself is the subject of Database Indexing.

Status fields and allowed transitions

The CHECK on orders.status lists the legal values, but a status column is also a state machine, and no value-list check can say that cancelled must never become paid. Write the arrows down, then enforce them where the change happens, with a guarded update whose WHERE clause names the state you expect.

-- Allowed arrow: pending -> paid. The WHERE clause is the guard.
UPDATE orders
   SET status = 'paid'
 WHERE id = 1
   AND status = 'pending'
RETURNING id, status;
-- One row back: the transition happened. Zero rows: the order was not pending any more
-- (already paid, cancelled, or the id is wrong). The caller reads the row to decide which.
-- Running it twice is harmless: the second run returns zero rows.

Under PostgreSQL’s default READ COMMITTED level, two concurrent updates against the same row serialize on the row lock, and the second re-evaluates its WHERE clause against the committed result of the first, which is why this pattern is safe without a separate read. (At REPEATABLE READ the second update fails with 40001 instead and must be retried.) If you need the database to reject illegal transitions even from ad-hoc SQL, a trigger or a transition table can do it, at the cost of more moving parts. Also avoid overloading one status column with unrelated concepts. “Paid”, “shipped” and “refund requested” are three independent facts; one enum that tries to enumerate every combination grows without bound, while a separate column or table per concept stays simple.

Database normalization is the discipline of storing each fact once. First normal form asks for atomic values and well-defined rows: no repeating groups, no “items” column holding a list. Functional dependencies reveal redundancy: if product_id determines product_name, then a table keyed on (order_id, product_id) that also stores the name repeats a fact that depends on only part of the key. Third normal form goes further and removes facts that depend on a non-key column, such as a category name determined by category_id. The payoff is that an update touches one row and cannot disagree with itself.

-- One wide table mixes three facts: what was ordered, what a product is, what a category is.
--   order_lines(order_id, product_id, product_name, category_id, category_name, qty)
--   key: (order_id, product_id)
--   product_id -> product_name, category_id      (depends on part of the key)
--   category_id -> category_name                (depends on a non-key column)
-- Each fact moves to the table whose key determines it:
CREATE TABLE categories (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
CREATE TABLE products (
  id          INTEGER PRIMARY KEY,
  name        TEXT    NOT NULL,
  category_id INTEGER NOT NULL REFERENCES categories(id)
);
CREATE TABLE order_lines (
  order_id   INTEGER NOT NULL,
  product_id INTEGER NOT NULL REFERENCES products(id),
  qty        INTEGER NOT NULL CHECK (qty > 0),
  PRIMARY KEY (order_id, product_id)
);

The failure this prevents is the update anomaly. Store the shipping address on every order row, and a customer move becomes a bulk rewrite. In a transactional engine a single UPDATE over all those rows is atomic, but real systems split the work: batches to bound lock time, a service call per record, a retry after a timeout. Any of those can stop halfway, leaving some rows with the new address and some with the old, and nothing in the data says which is correct.

Normalized schemas are not automatically fast or correct, though. A report that joins a dozen tables for every page view may pay for it in latency, and some facts are legitimately copied (the price snapshot above is a deliberate denormalization). The rule is to denormalize when a measured access pattern justifies it, and to write down four things: which table is the source of truth, what code path updates the copy, how drift is detected and repaired, and how stale the copy is allowed to be. If you cannot fill in all four, you have created a second authority, not a read optimization.

A materialized order summary is the standard example: a derived read model with one row per order (item count, total, customer) so a list page does not aggregate order lines on the fly. It is derived from the source tables, not an independent copy of the truth, and the design has to say how it stays right.

-- A derived read model. Authority stays in orders / order_items.
CREATE TABLE order_summary (
  order_id     BIGINT PRIMARY KEY,
  customer_id  BIGINT NOT NULL,
  item_count   INTEGER NOT NULL,
  total_cents  BIGINT  NOT NULL,
  refreshed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Update path (run by the projector for one changed order). Idempotent: replays converge.
INSERT INTO order_summary (order_id, customer_id, item_count, total_cents)
SELECT o.id, o.customer_id, sum(i.quantity), sum(i.quantity * i.unit_price_cents)
  FROM orders o
  JOIN order_items i ON i.order_id = o.id
 WHERE o.id = $1
 GROUP BY o.id, o.customer_id
ON CONFLICT (order_id) DO UPDATE
   SET item_count   = EXCLUDED.item_count,
       total_cents  = EXCLUDED.total_cents,
       refreshed_at = now();

-- Reconciliation path: find summaries that disagree with the source (bounded by an id range).
SELECT s.order_id
  FROM order_summary s
 WHERE s.order_id BETWEEN $1 AND $2
   AND s.total_cents IS DISTINCT FROM
       (SELECT sum(i.quantity * i.unit_price_cents) FROM order_items i WHERE i.order_id = s.order_id);

The update path can be a trigger in the same transaction (immediately consistent, but it lengthens every write and couples modules), a transactional outbox consumed by a projector (asynchronous, so the summary lags by the relay delay), or a periodic refresh of a materialized view. PostgreSQL’s REFRESH MATERIALIZED VIEW CONCURRENTLY avoids blocking readers but requires a unique index and recomputes the whole view, which suits small or slowly changing aggregates better than a hot per-row summary. The asynchronous options are explained in Transactional Outbox, and the separation of write and read models in CQRS. Copies in other stores, such as a search index or a cache, follow the same four-question test and are covered in Specialized Data Systems.

The opposite mistake is as common. Splitting every attribute into its own table produces queries with many joins, each one a place where the planner can pick a poor order and where a missing index hurts. There is no join count at which a schema becomes wrong; measure the query that matters before reaching for a copy. Likewise, copying product details into every order line to “avoid joins” silently drifts from the catalog unless you decide, as with the price, that the order line is meant to be a historical record.

List the operations and how often each runs: get order by ID, list recent orders for a customer, find unfulfilled orders, calculate daily revenue. Every operation implies a predicate, a sort order, an expected cardinality and a latency target, and each of those suggests a structure. A point lookup needs a key. A “newest N for a customer” query needs an index that matches both the filter and the order. A work queue over a small, changing subset benefits from a partial index. An aggregate over millions of rows is not an index problem at all; it belongs in a summary table or an analytics store.

-- PostgreSQL. Indexes derived from the operation list, not from the entity diagram.
-- get order by ID                 -> the primary key on orders(id) already serves it.

-- list recent orders for a customer
CREATE INDEX orders_customer_recent_idx ON orders (customer_id, created_at DESC);
SELECT id, status, created_at
  FROM orders
 WHERE customer_id = $1
 ORDER BY created_at DESC
 LIMIT 20;

-- find unfulfilled orders (assumes a shipped_at column; 'unfulfilled' is not one of the three statuses)
ALTER TABLE orders ADD COLUMN shipped_at TIMESTAMPTZ;
CREATE INDEX orders_unfulfilled_idx ON orders (created_at)
 WHERE status = 'paid' AND shipped_at IS NULL;

-- foreign keys are not indexed automatically in PostgreSQL; the PK of order_items already
-- leads with order_id, but orders.customer_id is only covered by the composite index above.

The composite index (customer_id, created_at DESC) is worth understanding concretely. Without it, answering “the newest 20 orders for customer 42” means finding all of that customer’s rows, sorting them by time, and keeping 20. With it, the engine descends the tree to customer 42 and reads the first 20 entries, already in the right order, then stops. The figure compares the work for three access paths under stated assumptions.

Treat that model as intuition, not a forecast. A plan estimate is not a promise: the planner chooses using statistics, may prefer a scan on a small table or a predicate that matches most rows, and real timings depend on cache state and data distribution. Compare EXPLAIN with measured execution on production-shaped data using your engine’s tooling, as covered in the indexing lesson. Balance the forces: an index speeds a read and taxes every write; a denormalized copy speeds a read and adds an update path; analytics queries on the transactional database compete with the checkout flow. Designing only for one dashboard can make ordinary transactional updates impossible, and designing purely from entity diagrams can leave the hottest query with no usable path.

Money. Store money in exact decimals or integer minor units, with an explicit currency and a stated rounding policy, never binary floating point. The reason is arithmetic, not style: 0.1 + 0.2 in double precision does not equal 0.3, and the error compounds across line items and tax calculations. “Cents” is shorthand, because the number of minor units per major unit depends on the currency: JPY has none, USD has two, KWD has three. Decide where rounding happens (per line or per order), which mode applies (PostgreSQL’s round() on numeric rounds halves away from zero, which is not the same as every language’s default), and keep tax, discounts and refunds as explicit rows or columns rather than recomputing them later from current rates.

-- PostgreSQL
SELECT 0.1::float8 + 0.2::float8 = 0.3::float8  AS float_equal,    -- false
       0.1::numeric + 0.2::numeric = 0.3::numeric AS numeric_equal; -- true

-- Money: integer minor units plus a currency, or numeric with a fixed scale.
ALTER TABLE orders ADD COLUMN currency CHAR(3) NOT NULL DEFAULT 'USD';
-- unit_price_cents BIGINT already holds minor units. JPY has 0 decimal places, USD 2, KWD 3,
-- so "cents" is shorthand: the currency decides how many minor units make a major unit.

Time. Use timezone-aware instants for events: the moment a payment was captured is one point on the timeline regardless of where anyone is standing. In PostgreSQL, timestamptz stores an instant and the session’s TimeZone setting only affects how input is interpreted and output is displayed; the zone is not stored with the value. Do not use that type for things that are not instants. A hotel night is a calendar date in the hotel’s time zone, a birthday is a date, and a business window such as “store hours” is a local time range plus a zone name that must be resolved per day because offsets change with daylight saving time. Store the zone name when the local meaning matters.

-- timestamptz stores an instant. The session TimeZone only affects how input is read and output is shown.
SET TIME ZONE 'UTC';
SELECT '2026-03-08 12:00:00+05:30'::timestamptz;    -- 2026-03-08 06:30:00+00
SET TIME ZONE 'America/New_York';
SELECT '2026-03-08 12:00:00+05:30'::timestamptz;    -- same instant, shown as 2026-03-08 01:30:00-05

-- A hotel night is a calendar date in the hotel's own zone, so it is a DATE, not a timestamp.
-- check_in and check_out are dates; a stay is the half-open range [check_in, check_out).

Ranges are where date and time modeling most often goes wrong. Represent a stay as a half-open interval, check-in inclusive and check-out exclusive, and the number of nights claimed is simply check_out − check_in. Two stays that touch at a boundary, one ending the day another begins, do not overlap, and no special case is needed. A closed interval would make that common back-to-back booking look like a conflict, or force you to subtract a day in half your queries.

Nulls. Model missing, unknown and not-applicable deliberately. A nullable shipped_at is a fine encoding of “not shipped yet”. A nullable discount_percent is ambiguous: no discount, an unknown discount, or one that does not apply? SQL’s three-valued logic makes NULL behave in ways that surprise: NULL = NULL is unknown, so a filter drops it; x NOT IN (subquery) returns nothing if the subquery yields any NULL; and unique constraints treat nulls in an engine-specific way. PostgreSQL by default allows many NULLs in a unique column (and since version 15 accepts NULLS NOT DISTINCT to change that), while some other engines allow only one. Prefer IS DISTINCT FROM for null-safe comparison and NOT EXISTS over NOT IN for anti-joins.

-- PostgreSQL 15+ for NULLS NOT DISTINCT; the default behaviour works on every supported version.
SELECT NULL = NULL                       AS eq,          -- NULL (unknown), not true
       NULL IS NOT DISTINCT FROM NULL    AS same_or_both_null,  -- true
       1 IS DISTINCT FROM NULL           AS differs;     -- true

SELECT 1 AS r WHERE 1 NOT IN (2, NULL);  -- returns no rows: the comparison with NULL is unknown

CREATE TABLE n1 (a int UNIQUE);                      -- default: NULLs are never equal
INSERT INTO n1 VALUES (NULL), (NULL);                -- succeeds, two rows
CREATE TABLE n2 (a int UNIQUE NULLS NOT DISTINCT);   -- treat NULLs as equal
INSERT INTO n2 VALUES (NULL), (NULL);                -- ERROR 23505

On a large production table, change the schema in steps that every deployed version of the application can tolerate. The pattern is expand, migrate, contract. Expand by adding the new structure as nullable and unused. Deploy writers that populate both the old and new shapes. Backfill existing rows in bounded batches. Switch readers to the new shape, behind a flag if you can. Enforce the new constraint once the data satisfies it. Only then, when no running code uses the old structure, contract by dropping it. Renaming a column in one step is the tempting shortcut that fails: during a rolling deploy, old instances still use the old name and fail until they are replaced.

The undo row is the reason for the discipline. Up to the last phase, reverting is a configuration flip or a redeploy, because the old column was never touched. Dropping it is the one irreversible step, so do it last, after a full release cycle and with a backup you have verified you can restore from.

-- PostgreSQL. Example: split customers.display_name into a required full_name.
-- Always cap how long a DDL statement may wait for its lock.
SET lock_timeout = '3s';

-- 1. EXPAND: nullable column, no rewrite, brief lock.
ALTER TABLE customers ADD COLUMN full_name TEXT;

-- 2. DUAL WRITE is application code: every writer sets display_name AND full_name.

-- 3. BACKFILL (see the loop below), then prove it:
SELECT count(*) FROM customers WHERE full_name IS NULL;

-- 5. ENFORCE without a long exclusive scan:
ALTER TABLE customers
  ADD CONSTRAINT customers_full_name_present CHECK (full_name IS NOT NULL) NOT VALID;  -- new rows are checked at once
ALTER TABLE customers VALIDATE CONSTRAINT customers_full_name_present;                 -- scans, but allows reads and writes
ALTER TABLE customers ALTER COLUMN full_name SET NOT NULL;   -- PostgreSQL 12+ trusts the validated CHECK and skips the scan
ALTER TABLE customers DROP CONSTRAINT customers_full_name_present;

-- 6. CONTRACT, only after no deployed code reads display_name:
ALTER TABLE customers DROP COLUMN display_name;

-- Indexes on a busy table: build without blocking writes. Not allowed inside a transaction block.
-- If it fails it leaves an INVALID index that you must drop and rebuild.
CREATE INDEX CONCURRENTLY customers_full_name_idx ON customers (full_name);

Verify the actual lock behavior for your database and version, because the safe patterns differ. In PostgreSQL, adding a nullable column is a catalog change, and since version 11 so is adding a column with a constant default, while a volatile default rewrites the table. Even a metadata-only ALTER TABLE needs a brief ACCESS EXCLUSIVE lock, and if a long transaction already holds a conflicting lock, your statement waits in the queue and every query behind it waits too. That is why the script sets lock_timeout: failing fast and retrying is better than stalling the application. Adding a foreign key can follow the same NOT VALID then VALIDATE split. An online index build with CREATE INDEX CONCURRENTLY avoids blocking writes, still consumes substantial I/O, takes longer than a normal build, cannot run inside a transaction block, and can fail and leave an INVALID index that has to be dropped and rebuilt.

// Node.js with the "pg" package. Backfills in id order, one short transaction per batch.
import pg from 'pg';
const pool = new pg.Pool();
const sleep = (ms) => new Promise((r) => setTimeout(r, ms));

const BATCH_SQL =
  'WITH batch AS (SELECT id FROM customers WHERE id > $1 ORDER BY id LIMIT $2), ' +
  'upd AS (UPDATE customers c SET full_name = coalesce(c.display_name, \'unknown\') ' +
  '        FROM batch WHERE c.id = batch.id AND c.full_name IS NULL RETURNING c.id) ' +
  'SELECT (SELECT max(id) FROM batch) AS last_id, (SELECT count(*) FROM upd) AS updated';

export async function backfillFullName({ batchSize = 1000, pauseMs = 200 } = {}) {
  let lastId = 0;
  let total = 0;
  for (;;) {
    const { rows } = await pool.query(BATCH_SQL, [lastId, batchSize]);
    if (rows[0].last_id === null) return total;   // no rows beyond lastId: done
    lastId = Number(rows[0].last_id);             // key-ordered progress, safe to resume after a crash
    total += Number(rows[0].updated);
    await sleep(pauseMs);                         // leave room for foreground traffic and replicas
  }
}

Batch size and pacing are the tuning knobs. Every batch holds row locks for its duration, writes write-ahead log, and may increase replica lag; a pause between batches leaves room for foreground traffic. The calculator uses a per-batch time you must measure on production-like data, then shows how duration, lock exposure and duty cycle trade off.

A backfill is also a long-running job, so make it observable and safe to stop: log progress by key, count remaining rows, watch replica lag and lock waits, and expose a way to pause. Do not assume the database migration solves compatibility for everyone who reads your data. For event and API schemas, use versioned contracts and compatibility checks: add optional fields, never change the meaning of an existing one, and let consumers ignore what they do not understand. Consumer compatibility across services is a separate problem from a table migration, and the outbox and event sourcing lessons show where those contracts appear.

The naive implementation reads “available” and then inserts a booking. Two concurrent requests for overlapping nights both run the read before either inserts, both see availability, and both succeed. Each request looks correct in isolation; the defect exists only in the interleaving, which is why it passes tests and appears under launch-day load.

-- Looks safe, is not. Two requests can both run the SELECT before either runs the INSERT.
BEGIN;
SELECT count(*) FROM bookings
 WHERE room_id = 1 AND status IN ('held','confirmed')
   AND check_in < '2026-06-14' AND check_out > '2026-06-12';   -- 0 for both requests
INSERT INTO bookings (room_id, guest_id, check_in, check_out, status)
VALUES (1, 11, '2026-06-12', '2026-06-14', 'confirmed');
COMMIT;

Wrapping those statements in a transaction does not fix it. Under PostgreSQL’s default READ COMMITTED level, each statement sees the rows committed when it starts, so the check can pass in both transactions. Under REPEATABLE READ, which in PostgreSQL is snapshot isolation, both transactions read from snapshots taken before the other inserted, and inserting different rows is allowed, so the overlap still slips through. Isolation levels and their anomalies, including this write-skew pattern, are the topic of Transactions and Concurrency. What prevents the failure is an enforceable concurrency boundary, and there are four common ones. Use the figure to compare the unprotected check-then-insert with the first, second, and fourth boundaries; the third, a conditional update, is covered in its own section below.

1. A unique inventory claim per night

Create one row per room per night in room_night with primary key (room_id, night), inserted in the same transaction as the booking. The uniqueness check happens inside the index, atomically with the insert. A second request that needs a night already claimed by an uncommitted transaction waits for it to finish; if that transaction commits, the waiter gets a unique violation, and if it rolls back, the waiter proceeds. This works at any isolation level and needs no retry loop beyond reporting the conflict.

-- PostgreSQL. Booking model with a per-night inventory claim. Runnable as written.
CREATE TABLE rooms (
  id       BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  hotel_id BIGINT NOT NULL,
  number   TEXT   NOT NULL,
  UNIQUE (hotel_id, number)
);

CREATE TABLE bookings (
  id         BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id    BIGINT NOT NULL REFERENCES rooms(id),
  guest_id   BIGINT NOT NULL,
  check_in   DATE   NOT NULL,
  check_out  DATE   NOT NULL,
  status     TEXT   NOT NULL CHECK (status IN ('held','confirmed','cancelled')),
  CHECK (check_out > check_in)
);

-- One row per room per night that is sold. The primary key IS the double-booking rule.
CREATE TABLE room_night (
  room_id    BIGINT NOT NULL REFERENCES rooms(id),
  night      DATE   NOT NULL,
  booking_id BIGINT NOT NULL REFERENCES bookings(id),
  PRIMARY KEY (room_id, night)
);

// Node.js with the "pg" package. One transaction: the booking row and every night it claims.
import pg from 'pg';
const pool = new pg.Pool();

export async function bookRoom({ roomId, guestId, checkIn, checkOut }) {   // dates as 'YYYY-MM-DD'
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const { rows } = await client.query(
      'INSERT INTO bookings (room_id, guest_id, check_in, check_out, status) ' +
      "VALUES ($1, $2, $3, $4, 'confirmed') RETURNING id",
      [roomId, guestId, checkIn, checkOut],
    );
    const bookingId = rows[0].id;
    await client.query(
      'INSERT INTO room_night (room_id, night, booking_id) ' +
      "SELECT $1::bigint, d::date, $2::bigint FROM generate_series($3::date, $4::date - 1, interval '1 day') AS d",
      [roomId, bookingId, checkIn, checkOut],
    );
    await client.query('COMMIT');
    return { ok: true, bookingId };
  } catch (err) {
    await client.query('ROLLBACK').catch(() => {});
    if (err.code === '23505' && err.constraint === 'room_night_pkey') {
      return { ok: false, reason: 'ROOM_UNAVAILABLE' };   // map to HTTP 409
    }
    throw err;                                            // anything else is a real failure
  } finally {
    client.release();
  }
}

Cancelling or expiring a hold must delete its room_night rows in the same transaction that changes the booking status, otherwise a cancelled booking keeps blocking the room. The same applies to a hold with an expires_at: a cleanup job releases the claims. One more subtlety is retries. If a client times out and resends the same request, the claim insert will report a conflict with the guest’s own earlier booking. Give each request an idempotency key stored on the booking with a unique constraint, so a replay returns the original booking instead of a false “unavailable”; see Idempotency.

2. An exclusion constraint on date ranges

When the thing being protected is naturally a range, an exclusion constraint states the rule directly: no two active stays for the same room may have overlapping periods. It needs a GiST index and, for the equality part, the btree_gist extension. It removes the helper table and the loop that fills it, and the WHERE clause lets cancelled stays stop blocking. The trade-offs are that it is PostgreSQL-specific, and that GiST index maintenance costs more than a B-tree primary key.

-- PostgreSQL. One row per stay, overlap rejected by the database. Needs the btree_gist extension
-- (CREATE EXTENSION needs sufficient privileges; managed services usually allow it).
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE stay (
  id      BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  room_id BIGINT    NOT NULL,
  period  DATERANGE NOT NULL,                        -- '[2026-06-10,2026-06-13)' = nights 10, 11, 12
  status  TEXT      NOT NULL CHECK (status IN ('held','confirmed','cancelled')),
  EXCLUDE USING gist (room_id WITH =, period WITH &&)
    WHERE (status IN ('held','confirmed'))           -- cancelled stays stop blocking the room
);

INSERT INTO stay (room_id, period, status) VALUES (1, '[2026-06-10,2026-06-13)', 'confirmed');
INSERT INTO stay (room_id, period, status) VALUES (1, '[2026-06-13,2026-06-15)', 'confirmed'); -- ok: back to back
INSERT INTO stay (room_id, period, status) VALUES (1, '[2026-06-12,2026-06-14)', 'held');
--   ERROR 23P01 exclusion_violation: conflicting key value violates exclusion constraint "stay_room_id_period_excl"

3. A conditional update on available capacity

Hotels usually sell a room type, not a specific room, so availability is a count. Keep a row per hotel, room type and night with capacity and booked, and reserve with a single conditional update. The booked < capacity predicate is evaluated atomically with the increment, so two requests for the last room serialize on that row and exactly one wins. The check constraint is a backstop against a code path that forgets the guard.

-- PostgreSQL. The hotel sells a room TYPE; any king room will do. Inventory is a counter per night.
CREATE TABLE room_type_night (
  hotel_id  BIGINT  NOT NULL,
  room_type TEXT    NOT NULL,
  night     DATE    NOT NULL,
  capacity  INTEGER NOT NULL CHECK (capacity >= 0),
  booked    INTEGER NOT NULL DEFAULT 0 CHECK (booked >= 0),
  PRIMARY KEY (hotel_id, room_type, night),
  CHECK (booked <= capacity)                          -- backstop if a code path forgets the guard
);

-- Reserve nights Jun 10, 11, 12 (half-open [Jun 10, Jun 13)) in one statement.
UPDATE room_type_night
   SET booked = booked + 1
 WHERE hotel_id = 1 AND room_type = 'king'
   AND night >= '2026-06-10' AND night < '2026-06-13'
   AND booked < capacity
RETURNING night;
-- Application: the stay is 3 nights, so exactly 3 rows must come back.
-- Fewer rows means at least one night was full: ROLLBACK, so the nights that did increment are undone.

A multi-night stay updates several rows, so compare the number of rows returned with the number of nights and roll back if any night was full; otherwise you will have partially reserved a stay. Two requests that touch overlapping nights in different orders can deadlock, in which case PostgreSQL aborts one with 40P01. Retry it, or lock the rows in a consistent order (for example, by selecting them with ORDER BY night FOR UPDATE first).

4. Serializable isolation with retries

At SERIALIZABLE, PostgreSQL tracks what each transaction read and wrote and aborts one of any set of transactions whose outcome could not have come from running them one at a time. The original check-then-insert code becomes safe, with the price that the database may abort a transaction with SQLSTATE 40001, sometimes before COMMIT and sometimes at it, and sometimes for conflicts that were not real. The application must therefore be ready to rerun the entire transaction, including re-reading, on that error. Keep serializable transactions short, retry a bounded number of times with jitter, and make sure the work has no external side effects that cannot be repeated. This choice requires no schema support, so it also protects rules that are hard to encode as constraints.

// Node.js with the "pg" package. Serializable transaction runner with bounded, jittered retries.
import pg from 'pg';
const pool = new pg.Pool();
const sleep = (ms) => new Promise((r) => setTimeout(r, ms));
const RETRYABLE = new Set(['40001', '40P01']);   // serialization_failure, deadlock_detected

export async function withSerializable(work, maxAttempts = 5) {
  for (let attempt = 1; ; attempt++) {
    const client = await pool.connect();
    try {
      await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE');
      const result = await work(client);        // reads and writes; the whole unit is replayed on retry
      await client.query('COMMIT');             // COMMIT itself can raise 40001
      return result;
    } catch (err) {
      await client.query('ROLLBACK').catch(() => {});
      if (!RETRYABLE.has(err.code) || attempt >= maxAttempts) throw err;
    } finally {
      client.release();
    }
    await sleep(Math.random() * 25 * 2 ** attempt);   // jitter so colliding requests do not collide again
  }
}

Which one to choose follows from what you are protecting. Pick the unique claim when the rule is “this identifiable unit can be sold once for this slot”, because it is portable and its failure mode is a plain conflict. Pick the exclusion constraint when the rule is naturally an interval overlap and you are on PostgreSQL. Pick the conditional counter for pooled inventory. Reach for serializable transactions when the invariant spans several tables or cannot be reduced to one of the above and the retry cost is acceptable. In every case, the database, not the request handler, is the final arbiter.

A schema is not finished when it deploys. The signals that tell you whether the model is working are mostly cheap to collect:

• Constraint violations by constraint name. A steady rate of room_night_pkey conflicts is real demand for the same rooms; a sudden spike in foreign-key or check violations points at a bad deploy or a client bug.
• Serialization and deadlock retries, if you use serializable transactions or multi-row counters, along with their final-failure rate after the retry budget.
• Top queries against the operation list. Track latency and rows touched per operation (for PostgreSQL, pg_stat_statements is the usual source) and re-check the plan when data volume or distribution changes.
• Drift in derived read models, the count of rows the reconciliation job repairs and the age of the oldest unprocessed outbox row, which together bound how stale a summary can be.
• Migration health: lock-wait timeouts hit by DDL, backfill progress and remaining rows, replica lag during the job, and invalid indexes left behind by failed concurrent builds.

The relational model fits best when constraints, joins and multi-row atomicity dominate, which describes most transactional business data. When one entity is always read and written as a nested document, or the dominant workload is full-text search, similarity search or large scans, a different store may serve that access pattern better, but keep the transactional source of truth and project into the specialized system rather than splitting the invariants across two stores. When data outgrows one machine, the model has to decide what to partition by and which constraints survive a shard boundary; that is the subject of Scaling Databases and Distributed Data. A uniqueness constraint, for instance, is easy on one primary and requires coordination or a partition-aware key across shards.