Building Software
Sections

Contents Section 5, State

Data Modeling and Databases

Designing schemas, keys and constraints, choosing the right kind of store, and understanding how a database finds data quickly.

Section 5 of 9, subsection 1 of 4. Depth: do. Core 11 of 25. A 10-minute read.

Mistakes to catch in review

  1. Lists stored as comma-separated strings or opaque JSON blobs, making queries slow and integrity impossible to enforce.

  2. A price column that allows NULL, so a missing price drops out of the invoice SUM and the customer is under-billed.

  3. A filter on an unindexed column that runs instantly on test data and times out on production data.

  4. Offset pagination that gets slower with every page as the table grows.

What the Schema Promises

You ask an agent to build class registration: students sign up for classes, pay a fee, and join a waitlist when a class is full. It hands you three tables, an invoice query and an admin page, and all of it works on your laptop.

SQL
CREATE TABLE students (
  id     serial PRIMARY KEY,
  name   text,
  email  text
);

CREATE TABLE classes (
  id         serial PRIMARY KEY,
  title      text,
  starts_at  timestamptz,
  fee        numeric,
  waitlist   jsonb          -- e.g. '["ana@example.com", "raj@example.com"]'
);

CREATE TABLE registrations (
  id             serial PRIMARY KEY,
  class_id       integer,
  student_id     integer,
  student_name   text,      -- copied from students
  student_email  text,      -- copied from students
  registered_at  timestamptz DEFAULT now()
);

The test database has 12 classes and 300 registrations, and every test passes. A dataset that small hides two kinds of trouble: bad rows that nothing refused, and queries whose cost grows with the table. Both surface a year later, at 20 million registrations, with three other programs writing to the same tables.

A schema is two things at once. It is a promise about what the data can look like, which the database enforces on every write. It is also a physical layout that decides how much work every read costs.

A list in one column

Start with waitlist. It holds a JSON array of emails in the order people joined. PostgreSQL can index it, too: a GIN index on a jsonb column makes a containment query such as waitlist @> '["ana@example.com"]' fast; without one, that query reads every row.

One waitlist value holds three entries: a typo, ana@exmaple.com; a duplicate, Ana appearing twice; and a deleted student still waiting. A dashed foreign key arrow to students attaches to the whole value only, so it sees one value and not the entries inside. Nothing refuses them.
The foreign key cannot see inside the one waitlist value, so a typo, a duplicate and a deleted student all get in.

The trouble is what the database cannot check. Each email is supposed to belong to a real student, but a foreign key constrains a whole column value, and an array element is only part of one. A CHECK constraint can test the value's shape, but PostgreSQL supports checks only on the row being written. So a typo like ana@exmaple.com goes in, Ana can appear twice, and a deleted student stays on the waitlist.

The comma-separated version, 'ana@example.com,raj@example.com' in a text column, has the same gap and a speed problem too. The query an agent might try is WHERE waitlist LIKE '%ana@example.com%', which also matches diana@example.com. And a pattern that starts with % cannot use an ordinary B-tree index, which is sorted by the start of each value, so the database reads every row.

The question that exposes a list in a column: can the database guarantee that every item refers to something real and that no item appears twice? When it cannot, the list wants its own table, one row per item. In SQLite, with that fix after it:

SQLRuns on SQLite in your browser
CREATE TABLE classes (id integer PRIMARY KEY, title text, waitlist text);
INSERT INTO classes VALUES
  (17, 'Watercolor', 'raj@example.com,diana@example.com'),
  (18, 'Pottery',    'ana@example.com'),
  (19, 'Sketching',  'ana@example.com,ana@example.com');

-- Which classes is Ana waiting for?
SELECT id, title, waitlist FROM classes
WHERE waitlist LIKE '%ana@example.com%';

-- One row per item: each must name a real student, and the key allows one row per pair.
CREATE TABLE students (id integer PRIMARY KEY, email text NOT NULL UNIQUE);
CREATE TABLE registrations (
  student_id  integer NOT NULL REFERENCES students(id),
  class_id    integer NOT NULL REFERENCES classes(id),
  status      text NOT NULL,
  PRIMARY KEY (student_id, class_id)
);
INSERT INTO students VALUES (1, 'raj@example.com'), (2, 'diana@example.com'), (3, 'ana@example.com');
INSERT INTO registrations VALUES
  (1, 17, 'waitlisted'), (2, 17, 'waitlisted'), (3, 18, 'waitlisted'), (3, 19, 'waitlisted');

SELECT r.class_id FROM registrations r JOIN students s ON s.id = r.student_id
WHERE s.email = 'ana@example.com' AND r.status = 'waitlisted';

One fact in one place

The same information, with each fact stored once:

SQL
CREATE TABLE students (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  family_name  text NOT NULL,
  given_name   text NOT NULL,
  email        text NOT NULL UNIQUE
);

CREATE TABLE classes (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title      text NOT NULL,
  starts_at  timestamptz NOT NULL,
  fee        numeric(10,2) NOT NULL CHECK (fee >= 0),
  capacity   integer NOT NULL CHECK (capacity > 0)
);

CREATE TABLE registrations (
  student_id     bigint NOT NULL REFERENCES students(id),
  class_id       bigint NOT NULL REFERENCES classes(id),
  status         text NOT NULL CHECK (status IN ('enrolled', 'waitlisted', 'cancelled')),
  fee_charged    numeric(10,2) NOT NULL CHECK (fee_charged >= 0),
  registered_at  timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (student_id, class_id)
);

"Ana is waiting for class 17" is now one row in registrations, and the waitlist order is registered_at. The primary key on the pair (student_id, class_id) makes it impossible to register the same student for the same class twice. The UNIQUE on email means at most one student row has any given email.

The students table holds Ana's new email. Dashed arrows run from it to three rows inside the registrations table, which holds 40 copied rows, each still carrying the old email. The two places now disagree.
Ana's new email lands in one students row while 40 copied rows keep the old one, so the database contradicts itself.

The agent's student_name and student_email were copies. When Ana changes her email, her students row changes and her 40 old registrations keep the old address, so the database now disagrees with itself. That is an update anomaly, and normalization is the practice of splitting tables until each fact lives in one place so it cannot happen.

First normal form says each column holds one value, which waitlist broke. Second normal form says a non-key column must depend on the whole key: in a table keyed by (student_id, class_id), a student's email depends on student_id alone, so it belongs in students. Third normal form says a non-key column must not depend on another non-key column: if classes held instructor_id and instructor_email, the email would depend on the instructor and belongs in an instructors table.

fee_charged looks like a copy of classes.fee and is a separate fact. The fee may rise next term, and Ana's invoice must show what she paid.

Rules the database checks on every write

In the agent's version, class_id and student_id are only numbers. The database will store a registration for student 99999, who does not exist, and keep registrations after their student is deleted. Application code may check, but it has bugs, and migrations, admin scripts and other services write to the same tables.

A constraint is a rule the database checks on every insert and update, from every writer. Most are checked as each statement runs; unique, primary key and foreign key constraints can be declared DEFERRABLE, which moves the check to commit.

  • PRIMARY KEY says these columns identify the row, so together they must be unique and none may be null.
  • REFERENCES (a foreign key) says the value must exist in the other table. It also guards deletes: by default, deleting a student who still has registrations fails.
  • UNIQUE says no two rows share this value.
  • NOT NULL says the value must be present.
  • CHECK says the row must satisfy an expression, such as fee >= 0.

NULL is where they leak. A nullable foreign key column accepts NULL, which points at nothing. UNIQUE, by default, allows any number of NULLs, since one NULL never equals another. A CHECK passes when its expression comes out NULL, so CHECK (fee >= 0) alone lets a missing fee through.

Add a foreign key to every column that holds another table's id, and NOT NULL wherever a missing value would be wrong.

A missing fee

Class 3 was created before anyone set its fee, and Ana, student 42, is registered for it:

SQLRuns on SQLite in your browser
CREATE TABLE classes (
  id   integer PRIMARY KEY,
  fee  numeric CHECK (fee >= 0)   -- NULL >= 0 is NULL, and a CHECK rejects only false
);
CREATE TABLE registrations (
  student_id  integer NOT NULL,
  class_id    integer NOT NULL REFERENCES classes(id),
  PRIMARY KEY (student_id, class_id)
);
INSERT INTO classes VALUES (1, 120.00), (3, NULL), (7, 80.00);
INSERT INTO registrations VALUES (42, 1), (42, 3), (42, 7);

SELECT c.id, c.fee
FROM registrations r JOIN classes c ON c.id = r.class_id
WHERE r.student_id = 42;

SELECT count(*) AS classes, count(c.fee) AS priced, sum(c.fee) AS billed
FROM registrations r JOIN classes c ON c.id = r.class_id
WHERE r.student_id = 42;

sum skips NULLs, with no error and no warning. Ana is billed 200 for three classes, one never priced. If all three fees were NULL, sum would return NULL, which tends to become zero somewhere downstream.

The fixed schema declares fee numeric(10,2) NOT NULL CHECK (fee >= 0), which moves the failure to the moment someone creates a class without a fee, when a person can still fix it. Ask it of every column that feeds a total: what does this sum do when one value is missing?

Where to break the rules

Normalization optimizes for correct writes. The catalog page shows "14 of 20 seats taken" for every class, and you may choose to store enrolled_count integer NOT NULL DEFAULT 0 on classes so each page load skips counting registrations. That is a deliberate denormalization: a copy kept to make a read cheap, which can drift.

So the count changes in the same transaction as the registration, and it must stay correct when two students register at once. If the application reads 14, adds one and writes 15, two interleaved requests both write 15. An atomic update closes that gap:

SQL
BEGIN;
UPDATE classes
SET enrolled_count = enrolled_count + 1
WHERE id = 17 AND enrolled_count < capacity;
-- if 0 rows were updated, the class is full: roll back and waitlist instead
INSERT INTO registrations (student_id, class_id, status, fee_charged)
VALUES (42, 17, 'enrolled', 120.00);
COMMIT;

The UPDATE locks the class row, so a second registration waits, then re-checks enrolled_count < capacity against the new value. Cancellations decrement the same way, and a nightly recount catches any code path that forgot.

How a database finds a row

The agent's invoice query filters registrations on student_id, and no index covers that column. A foreign key would not add one: PostgreSQL requires an index on the key being referenced, and leaves the referencing column unindexed unless you create one.

Without an index, the only way to answer WHERE student_id = 42 is to read every row and test it, which is called a sequential scan. On 300 rows it takes microseconds. At 20 million registrations the table is on the order of a gigabyte, and every invoice reads all of it to find Ana's three rows.

An index is a second structure, kept sorted by some column, that points back to the rows. The usual kind is a B-tree. Picture the sorted values split into pages, with a page of signposts above them saying which page holds which range, and another page of signposts above that.

A tree of index pages. A top signpost page points to two signpost pages below it, which point to three pages of sorted values. A highlighted path runs from the top page down through the left signpost page to the middle page of sorted values, then down to the registrations table, marked one more page. An arrow from the middle sorted page to the right one is labelled range: walk forward. A note reads: a few pages per lookup.
A lookup reads one signpost page per level down to the sorted values, then one more page from the table; a range keeps walking along the bottom level.

PostgreSQL pages are 8 KB, so one page of signposts holds a few hundred entries, and three or four levels cover tens of millions of rows. Finding one value means reading a few index pages, and fetching each matching row usually means one more page from the table. A range is found the same way, then read by walking forward along the bottom level.

Indexes cost something on writes. Every insert adds an entry to every index on the table, so a table with eight indexes maintains eight index entries per insert on top of the row, and updates can add entries too. Index what your queries filter, join and sort by, and stop there.

Reading the plan

EXPLAIN shows the plan the database would choose, without running the query. EXPLAIN ANALYZE runs it and adds what actually happened: real row counts and timings. Here is one plan you might see for the invoice lookup on the agent's full-size table; the exact shape depends on your data and settings.

SQL
EXPLAIN (COSTS OFF)
SELECT class_id FROM registrations WHERE student_id = 42;
Output
Gather
  Workers Planned: 2
  ->  Parallel Seq Scan on registrations
        Filter: (student_id = 42)

Seq Scan with a Filter line means every row is read and tested. Gather and Parallel mean the read is split across worker processes, still covering the whole table. After CREATE INDEX registrations_student_id_idx ON registrations (student_id); a possible plan is:

Output
Index Scan using registrations_student_id_idx on registrations
  Index Cond: (student_id = 42)

Index Cond means the index narrowed the search before any table row was read, and for a filter matching a few rows of a large table the planner will usually pick this. It may instead pick a Bitmap Index Scan feeding a Bitmap Heap Scan, which also uses the index. If the filter matches a large share of the table, it may keep the sequential scan, because reading everything in order is then cheaper than jumping around.

That is why a plan from a 300-row test database tells you nothing: the table fits in a few pages, and a sequential scan is correctly chosen.

A function around a column hides it from a plain index: WHERE lower(email) = 'ana@example.com' cannot use an index on email, which is sorted by the raw values. Index the expression or compare the bare column. And when estimated and actual row counts differ widely, check whether the table's statistics are current, for example by running ANALYZE, before trusting the plan.

Choosing column order in a composite index

The waitlist page asks who is next for class 17:

SQL
SELECT student_id FROM registrations
WHERE class_id = 17 AND status = 'waitlisted'
ORDER BY registered_at
LIMIT 1;

A composite index sorts by its first column, then by the second within ties, the way a phone book sorts by family name and then given name. You can find every Nguyen quickly, and every Linh Nguyen quickly. Finding every Linh means reading most of the book. So the primary key index on (student_id, class_id) serves the invoice lookup and does little for this query.

An index on (class_id, status, registered_at) puts class 17's waitlisted rows next to each other, already in the order they joined. The database goes to the start of that block, reads one entry, and stops. The rule: columns tested with equality first, then the column you sort by or filter by range.

Reverse it to (registered_at, class_id, status) and class 17's rows are scattered through the index by date. The database walks it in date order, checking each entry until one matches, which can mean reading much of the index. PostgreSQL 18 can sometimes skip through a leading column with few distinct values, and a timestamp has millions.

Two index strips, each with a query on its left. The top strip, (class_id, status, registered_at), has one highlighted block, class 17 waitlisted, already in the order they joined, followed by other classes; the query arrow into the block reads read one, stop. The bottom strip, (registered_at, class_id, status), mixes other classes with scattered class 17 entries; the query arrow reads check each, and arrows step from entry to entry until the first class 17 entry. A note reads: equality first, then sort.
With equality columns first, class 17's waitlisted rows sit together in the order they joined, and the query reads one entry; with the timestamp first, the query checks entries in date order until one matches.

SQLite, which runs these playgrounds, prints shorter plans: SCAN walks a whole table or index, SEARCH means an index narrowed the rows, and USE TEMP B-TREE is a sort. Without statistics it assumes a table of about a million rows, so this one can stay empty.

SQLRuns on SQLite in your browser
CREATE TABLE registrations (
  student_id     integer NOT NULL,
  class_id       integer NOT NULL,
  status         text NOT NULL,
  registered_at  text NOT NULL,
  PRIMARY KEY (student_id, class_id)
);

-- 1. Only the primary key, which starts with student_id.
EXPLAIN QUERY PLAN
SELECT student_id FROM registrations
WHERE class_id = 17 AND status = 'waitlisted'
ORDER BY registered_at LIMIT 1;

-- 2. The timestamp first.
CREATE INDEX by_date ON registrations (registered_at, class_id, status);
EXPLAIN QUERY PLAN
SELECT student_id FROM registrations
WHERE class_id = 17 AND status = 'waitlisted'
ORDER BY registered_at LIMIT 1;

-- 3. Equality columns first, then the sort column.
DROP INDEX by_date;
CREATE INDEX by_class ON registrations (class_id, status, registered_at);
EXPLAIN QUERY PLAN
SELECT student_id FROM registrations
WHERE class_id = 17 AND status = 'waitlisted'
ORDER BY registered_at LIMIT 1;

CREATE INDEX ON registrations (class_id, status, registered_at) INCLUDE (student_id) also holds the column the query returns, which makes an Index Only Scan possible. PostgreSQL may still visit the table for rows it cannot yet confirm are visible to every transaction; EXPLAIN ANALYZE counts those as Heap Fetches, and they fall after the table is vacuumed.

Why page 2,000 is slow

The admin page lists students alphabetically, 50 at a time:

SQL
SELECT id, family_name, given_name FROM students
ORDER BY family_name, id
LIMIT 50 OFFSET 100000;

The database cannot find row 100,000 without counting to it. With an index on (family_name, id) supplying the order, it steps through 100,050 rows and throws away the first 100,000. Without that index it reads and sorts every row. Page 1 reads 50 entries and page 2,001 reads 100,050, and crawlers and export scripts do walk every page.

Keyset pagination remembers the last row of the previous page and asks for the rows after it:

SQL
SELECT id, family_name, given_name FROM students
WHERE (family_name, id) > ('Nguyen', 48213)
ORDER BY family_name, id
LIMIT 50;

With the index on (family_name, id), the database seeks straight there and reads 50 entries on any page. The id breaks ties between students who share a family name, so no row is skipped or repeated at a page boundary. Every page must use the same ORDER BY, and family_name must be NOT NULL, because a row comparison involving NULL is never true and those students would drop out of the listing. The cost is that users cannot jump straight to page 2,000, which an admin list rarely needs.

Picking the store

A relational database is the right default for most application data: it enforces these rules and answers questions you did not plan for. Other stores fit narrow, known access patterns.

A key-value store fits data read and written by a single key, like login sessions. A document store fits records always read and written whole. A search engine fits ranked full-text search over prose, like class descriptions mentioning watercolor; exact membership, like who is on a waitlist, goes through the relationship table. A time-series store fits measurements appended and read back by time range.

For each query, work out how many rows it touches as the table grows. An equality lookup through an index reads a few pages whether the table holds a thousand rows or a hundred million, while a sequential scan, a leading-wildcard LIKE or a deep OFFSET does work in proportion to the table. Indexed lookups still depend on cache and load, so confirm the prediction with realistic data volume and concurrent traffic.

Check your understanding

7 questions on this lesson. Pick an answer to see whether it holds and why.

  1. 1An agent stores each user's roles as a jsonb array, such as ["editor", "billing"], in a roles column on users. Which rule can PostgreSQL not enforce until the roles move to their own table, one row per user and role?
    Show the answer

    Answer B. A foreign key constrains a whole column value, and one array element is only part of it, so nothing stops edtior from going in. The jsonb type already rejects invalid JSON, a GIN index serves containment queries, and NOT NULL works on any column. Per-item integrity is what needs the separate table.

  2. 2An agent added a CHECK to the price column and says missing prices are now impossible. What does the last query return?
    SQL
    CREATE TABLE line_items (
      order_id  integer NOT NULL,
      price     numeric CHECK (price >= 0)
    );
    INSERT INTO line_items VALUES (1, 30), (1, NULL), (1, 20);
    
    SELECT sum(price) FROM line_items WHERE order_id = 1;
    Show the answer

    Answer C. NULL >= 0 evaluates to NULL, and a CHECK rejects a row only when its expression is false, so the NULL price is stored. sum then skips it and returns 50, and the order is under-billed with no error. sum returns NULL only when every value is NULL. Adding NOT NULL is what makes the missing price fail at insert.

  3. 3EXPLAIN ANALYZE on a merchant report query against a 4.8-million-row payments table shows this plan. What does it tell you?
    Output
    Seq Scan on payments
      Filter: ((merchant_id = 88) AND (status = 'failed'::text))
      Rows Removed by Filter: 4812331
    Show the answer

    Answer D. Seq Scan with a Filter line means the database read the whole table and tested each row; Rows Removed by Filter counts the rows it threw away. An index lookup would show an index scan node with an Index Cond. Stale statistics could explain an ignored index, but nothing in this plan shows that a usable index exists.

  4. 4A multi-tenant app runs this query on every page load. Which single index serves it best?
    SQL
    SELECT id, subject FROM tickets
    WHERE tenant_id = $1 AND created_at >= $2
    ORDER BY created_at
    LIMIT 20;
    Show the answer

    Answer A. Equality columns go first and the range or sort column after them. With tenant_id leading, one tenant's rows sit together in created_at order, so the database seeks to the start of the range and reads 20 entries. With created_at leading, every tenant's rows are interleaved by date, and the scan checks entries from all tenants until it finds 20 that match.

  5. 5The users table has an index on email, yet the plan for this login lookup shows a sequential scan on a large table. Why?
    SQL
    SELECT id FROM users WHERE lower(email) = lower($1);
    Show the answer

    Answer A. An index on email can find a given raw value, but lower(email) is a different expression the index never stored. Either index the expression with an index on lower(email), or store emails already lowercased and compare the bare column. Vacuuming affects visibility and heap fetches; it does not let an index answer a question it was not built for.

  6. 6posts has an index on (published_at, id) and published_at is NOT NULL. Page 1 is fast and this page is slow. Which change makes every page read about 25 index entries?
    SQL
    SELECT id, title FROM posts
    ORDER BY published_at, id
    LIMIT 25 OFFSET 100000;
    Show the answer

    Answer B. With OFFSET 100000 the database walks 100,025 index entries and discards the first 100,000; the existing index already supplies the order, so a second index or fresh statistics change nothing. The keyset condition lets it seek straight past the last row seen, and id breaks ties so no post is skipped or repeated at a page boundary.

  7. 7An agent's orders table copies several values from other tables. Which copy is a separate fact that belongs on the order row?
    Show the answer

    Answer C. The product's price will change, and the order must keep what the customer actually paid, so the price at checkout is its own fact. Account status, signup date and category name describe the customer or product as they are now and say nothing about this purchase; copies of them drift when the source row changes, which is the update anomaly normalization removes.

You understand it when you can

Drill

An agent designed an orders table with a tags column holding comma-separated strings, no foreign key to customers, and a dashboard query that filters on an unindexed created_at column. Find the three problems and predict which query times out first as the table grows.

Topics

Relational Modeling and Normalization
Tables, relationships and normal forms, and the update anomalies normalization prevents.
Keys and Constraints
Primary keys, foreign keys, unique and check constraints as rules the database enforces on every write.
Choosing a Data Store
Relational, document, key-value, search and time-series stores, matched to the access pattern.
Indexes
B-trees, composite and covering indexes, and the write cost every index adds.
Query Plans
Reading how the database executes a query, and spotting full scans, bad join orders and deep pagination.

Further reading

Start here

  • SQL Performance Explained. Markus Winand, 2012.

    Covers composite index column order, why a function on a column disables its index, and why OFFSET pagination slows down with every page, with the full text readable free as Use The Index, Luke.

Short reads

Watch

Books

  • SQL Antipatterns, Volume 1: Avoiding the Pitfalls of Database Programming. Bill Karwin, 2022, 2nd edition.

    Its Jaywalking chapter is exactly the comma-separated tags column, and other chapters cover missing foreign keys and entity-attribute-value tables, each with the query that breaks and the schema that fixes it.

  • Designing Data-Intensive Applications: The Big Ideas Behind Reliable, Scalable, and Maintainable Systems. Martin Kleppmann and Chris Riccomini, 2026, 2nd edition.

    Compares relational, document and graph data models against access patterns, then explains how B-trees and LSM-trees store and index data, which is the basis for choosing a data store.

Primary sources

  • PostgreSQL Documentation: Using EXPLAIN. Manual.

    The official guide to reading plan nodes, cost estimates and EXPLAIN ANALYZE timings, with worked examples of index scans, bitmap scans and joins.

  • PostgreSQL Documentation: Constraints. Manual.

    Defines check, not-null, unique, primary key and foreign key constraints and the ON DELETE actions, the rules the database enforces on every write.