Schema design

A schema is the one part of a system that gets harder to change every day it runs, because data accumulates in whatever shape you chose. Application code is rewritten; tables are migrated, slowly, at 2 am. This lesson is the decisions that matter on day one, keys and constraints and normalisation, the ones that are safe to defer, and the denormalisation you do on purpose rather than by accident.

#The primary key

Every table gets a primary key, and its type is a decision you live with:

• BIGINT generated identity (GENERATED ALWAYS AS IDENTITY in PostgreSQL, AUTO_INCREMENT in MySQL). Small, sequential, fast to index, and the default for anything that is not exposed outside the system. Never INT: two billion rows arrives sooner than planned, and the migration to widen a key is the worst one there is.
• UUID. Generated anywhere, safe to expose in URLs, mergeable across databases. The cost is in the index: a random v4 UUID inserts into a random place in the B-tree, so every insert touches a random page, and in InnoDB, where the table is the primary key tree, that scatters the rows themselves and copies sixteen bytes into every secondary index. Use a time-ordered UUID (v7, standardised in 2024; ULID is equivalent) which inserts at the right edge like an integer would; store it as uuid in PostgreSQL and BINARY(16) in MySQL, never as a 36-character string.
• Natural keys (an email, a country code) as the primary key look elegant and change. An email is a UNIQUE column, not the key.

Two keys is often right: a BIGINT id for joins and foreign keys, and a UUID or a slug as the public identifier with a unique index.

#Constraints are tests that run on every write

CREATE TABLE orders (
  id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  public_id     UUID NOT NULL UNIQUE DEFAULT gen_random_uuid(),
  customer_id   BIGINT NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
  status        TEXT NOT NULL CHECK (status IN ('PENDING', 'PAID', 'SHIPPED', 'CANCELLED')),
  total_paise   BIGINT NOT NULL CHECK (total_paise >= 0),
  placed_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  cancelled_at  TIMESTAMPTZ,
  CHECK ((status = 'CANCELLED') = (cancelled_at IS NOT NULL))
);

Every constraint here is an invariant the application would otherwise have to enforce in every code path that writes, forever, including the batch job written next year by someone who never read the service. NOT NULL on every column that must have a value. CHECK for domains and for relationships between columns. REFERENCES with an explicit ON DELETE: RESTRICT when a parent with children must not vanish, CASCADE only when the children are meaningless without the parent (order lines), SET NULL rarely. MySQL enforces CHECK since 8.0.16; before that it parsed and ignored them, which is worth knowing when you inherit a schema.

Money is an integer in the smallest unit or a NUMERIC(12,2), never a FLOAT. Time is TIMESTAMPTZ in PostgreSQL, stored as UTC and converted on the way out; MySQL's TIMESTAMP converts to and from the session time zone and DATETIME does not, and mixing them is a classic off-by-five-and-a-half-hours bug.

#Normalisation as a checklist

The normal forms are a checklist, not a theory exam. For each table, ask:

1. Is every column atomic? No comma-separated lists, no address_line_1..3 that are really a list. (1NF)
2. Does every non-key column depend on the whole key? In an order_lines(order_id, sku, quantity, customer_name) table, customer_name depends on order_id alone. It belongs on the order. (2NF)
3. Does every non-key column depend only on the key? orders(id, customer_id, customer_email): the email depends on the customer, not the order. It belongs on the customer. (3NF)

"The key, the whole key, and nothing but the key." A schema in 3NF stores each fact once, so an update changes one row and cannot leave two copies disagreeing. That is the whole point: not elegance, but the absence of the bug where a customer's email is right in one table and stale in three.

#JSON columns

PostgreSQL's JSONB and MySQL's JSON hold a document in a column, indexed (a GIN index on the whole document in PostgreSQL; a generated column extracting a path, then indexed, in MySQL) and queryable. They are right for sparse, per-row-varying attributes: product specifications that differ by category, webhook payloads stored as received, feature flags per tenant. They are wrong for anything you join on, aggregate over, or constrain: a customer_id inside a JSON blob has no foreign key, no NOT NULL, and no index the planner trusts. The rule: columns for facts the database must understand, JSON for facts it only needs to keep.

#Soft deletes and audit columns

A deleted_at TIMESTAMPTZ instead of DELETE keeps history and makes undo possible. Two consequences to handle on day one: every query needs WHERE deleted_at IS NULL (an ORM @Where clause or a view), and a UNIQUE (email) constraint now blocks re-registering a deleted account. PostgreSQL solves the second with a partial unique index, CREATE UNIQUE INDEX ... ON users (email) WHERE deleted_at IS NULL; MySQL has no partial indexes, so the pattern there is a generated column that is the email for live rows and NULL for deleted ones, with a unique index on that.

created_at and updated_at on every table, NOT NULL, set by the database (DEFAULT now() and a trigger, or the ORM's @CreationTimestamp/@UpdateTimestamp), in TIMESTAMPTZ. Add created_by and updated_by where the answer "who did this" will ever be asked. When the history itself matters, an append-only audit table written in the same transaction beats reconstructing it from logs.

#Denormalising deliberately

Normalisation optimises for correct writes. Some reads are worth a controlled duplicate:

• Counters. posts.comment_count instead of COUNT(*) on every page view. Maintained in the same transaction as the insert, and reconciled by a nightly job that recomputes and logs any drift.
• Snapshots. order_lines.unit_price_paise copied from the product at order time, because the product's price will change and the order must not. This is not denormalisation at all; it is a different fact ("what they paid") that happens to have the same value at the moment of writing.
• Materialised aggregates. A daily_revenue table filled by a job, or a MATERIALIZED VIEW refreshed on a schedule, for dashboards that would otherwise GROUP BY ten million rows per load.

Each has a written policy for how it is kept correct, because a denormalised value without one is a bug with a delay. What is never acceptable is the accidental kind: customer_email on orders because a developer wanted to avoid a join.

#Migrations

Schema changes go through versioned migration files (Flyway or Liquibase in a Spring service; this site uses Prisma migrations), never by hand, never by ddl-auto=update. The pattern for a change on a live system is expand, migrate, contract: add the new column nullable, deploy code that writes both, backfill in batches, deploy code that reads the new one, then drop the old. Renaming a column in place is a deploy that breaks either the old code or the new; it is two migrations and three deploys, and that is the cost of zero downtime. The Locking lesson covers the lock a migration takes and the timeout to set before it.

#Under the hood: how a row is stored, and what a migration really costs

A PostgreSQL row is a tuple on an 8 KB heap page: a 23-byte header (transaction ids, a null bitmap, an offset to the data), then the columns in declaration order, each aligned to its type's boundary. A SMALLINT followed by a BIGINT wastes six bytes of padding; declaring the 8-byte columns first, then 4, then 2, then variable-length, packs a wide table measurably tighter, which matters at a billion rows. Values larger than about 2 KB are compressed and, if still large, moved out of line into a TOAST table and referenced by pointer, so a JSONB or TEXT column that is rarely read costs the main row almost nothing and a SELECT * that fetches it costs an extra lookup per row. InnoDB rows live in the clustered B-tree in primary-key order, with variable-length columns stored inline up to a limit and overflow pages beyond it; the row's position in the tree is decided by the key, which is the physical reason the primary key choice is a layout decision.

Constraints are enforced at different moments. NOT NULL and CHECK are evaluated per row on write, cheap. A UNIQUE constraint is a B-tree index, and the check is an index probe per insert; a UNIQUE on a random UUID is the same scattered-insert cost as a random primary key. A foreign key is two things: an index probe into the parent on every child insert (using the parent's primary-key index), and a trigger on the parent for UPDATE and DELETE that scans the child table for references, which without an index on the child's foreign-key column is a sequential scan per parent delete. PostgreSQL does not create that child-side index automatically; MySQL does. That asymmetry is the source of the "deleting one customer takes forty seconds" bug.

Adding a constraint to a full table is where migrations hurt. ALTER TABLE ... ADD COLUMN with a constant default is a catalog change since PostgreSQL 11 and instant in MySQL 8 (ALGORITHM=INSTANT). Adding NOT NULL, a CHECK or a foreign key must verify every existing row, under a lock that blocks writes for the scan's duration; PostgreSQL's NOT VALID skips the scan, enforces the rule for new rows, and VALIDATE CONSTRAINT later takes only SHARE UPDATE EXCLUSIVE, which lets writes through. Changing a column's type rewrites the whole table (and every index) unless the change is binary-compatible (VARCHAR(50) to VARCHAR(100), INT to BIGINT is not), and a table rewrite holds ACCESS EXCLUSIVE for as long as it takes. That is why INT to BIGINT on a primary key is the migration everyone dreads: a new column, a backfill in batches, a trigger to keep both in sync, an index built concurrently, and a swap, over days.

#Walkthrough: the INT that ran out

An orders table with id SERIAL PRIMARY KEY reached 2,147,483,647 on a Thursday afternoon.

ERROR: integer out of range
  SQL: INSERT INTO orders (customer_id, total) VALUES ($1, $2) RETURNING id

1. Every insert failed. The sequence had kept counting past the column's range; the error was on the cast, not the sequence. Reads were fine, which is why the health check stayed green while checkout was down.
2. ALTER TABLE orders ALTER COLUMN id TYPE BIGINT was the obvious fix and was attempted: it rewrites the table and every index, under ACCESS EXCLUSIVE, on 2.1 billion rows. It was cancelled after twenty minutes with checkout still down. Three foreign keys from other tables referenced orders.id, and each of those columns needed the same change.
3. The emergency bridge bought time: ALTER SEQUENCE orders_id_seq MINVALUE -2147483648 RESTART WITH -2147483648. Negative ids are legal integers; inserts resumed in seconds, with two billion more values available. The application had one place that assumed ids were positive (a URL validator), patched within the hour.
4. The real migration then ran over four days: add id_new BIGINT; a trigger copying id to id_new on insert and update; a backfill in 100,000-row batches with a pause between them (so vacuum kept up and replication lag stayed low); CREATE UNIQUE INDEX CONCURRENTLY on id_new; the same for each referencing column; then a single short transaction to swap the primary key constraint to the new index, rename the columns, and re-point the foreign keys with NOT VALID followed by validation.
5. The retrospective added a check to CI that fails on any SERIAL or INT primary key, and a monitor on every sequence's last_value against its type's maximum, alerting at 50%. Two other tables were within a year of the same cliff.

The schema decision on day one cost nothing; changing it under load cost four days and an outage. Primary keys are BIGINT because the migration to widen one is the worst there is.

#Try it yourself

#Misconceptions

• "Column order in CREATE TABLE is cosmetic." In PostgreSQL it decides alignment padding; wide tables shrink noticeably when 8-byte columns come first.
• "A foreign key is just a check on insert." It is also a trigger on the parent's update and delete that scans the child; without a child-side index that scan is sequential, and PostgreSQL does not add the index for you.
• "ADD COLUMN always rewrites the table." With a constant default it is a catalog change on modern engines; a volatile default (now()) or a type change still rewrites.
• "UUIDs are free because they are just 16 bytes." Random ones scatter inserts across every index and, on InnoDB, across the table itself. Time-ordered ones fix that; string-typed ones are 36 bytes and fix nothing.
• "Migrations are a deploy-time detail." Every constraint addition and type change has a lock and a scan; on a large table each is a project with NOT VALID, batches and lock_timeout.

#Going deeper

• PostgreSQL manual, "Database Physical Storage" (page layout, TOAST) and "ALTER TABLE" notes on which operations rewrite.
• MySQL reference, "Online DDL Operations", the table of which ALTERs are instant, in-place or copy.
• Braintree, "Safe Operations for High Volume PostgreSQL"; GitLab's migration style guide; the strong_migrations gem's checklist (Rails, but engine-level advice).
• RFC 9562 for UUID v7, and the pg_uuidv7 extension or UUID_TO_BIN(UUID(), 1) in MySQL for ordered keys.
• Bill Karwin, SQL Antipatterns: every schema mistake in this lesson has a chapter.