Schema design is the decision with the longest half-life in any system. Indexes can be added, queries can be rewritten, hardware can be replaced — but a wrong key choice or a wrong normalization decision propagates into every query, every migration, and every downstream consumer for years. This post covers the formal part (normal forms, keys, constraints), the pragmatic part (when denormalization is correct, how to model hierarchies and temporal data), the PostgreSQL-specific part (JSONB and when it is the right answer), and the operational part (how to change a schema without locking production out of its own database).

Series map: 00 Foundations & Setup → 01 SQL Fundamentals & Data Types → 02 Joins, Aggregation & Window Functions → 03 Indexes & the Query Planner → 04 Query Optimization & Statistics → 05 Transactions, MVCC, Isolation & Locking → 06 Schema Design, Normalization & JSONB (this post) → 07 Operations, Backup, Replication & Security.

Learning Outcome

Decompose a table through 1NF, 2NF, 3NF and BCNF, and state what each step eliminates.
Make an informed, reversible decision to denormalize, and name the cost you accepted.
Choose between natural keys, bigint identity, UUIDv4 and UUIDv7 with index locality as the deciding factor.
Model one-to-many, many-to-many, inheritance, and hierarchies with the standard patterns and their trade-offs.
Represent temporal data with range types and forbid overlaps with an EXCLUDE constraint.
Apply a clear rule for when a value belongs in JSONB and when it belongs in a real column, and index JSONB correctly.
Run schema migrations with expand/contract so no deployment needs a maintenance window.

Chapter 1 — Normalization, worked

Terminology

Functional dependency — A → B means every value of A determines exactly one value of B.
Candidate key — a minimal set of columns that functionally determines every other column.
Primary key — the candidate key you chose to designate.
Partial dependency — a non-key column depending on only part of a composite key.
Transitive dependency — a non-key column depending on another non-key column.
Update anomaly — one logical fact stored in many rows, so changing it requires changing many rows and can leave them inconsistent.

Start with the classic denormalized order sheet, one row per line item:

1
2
3
4
5
6
7
8
9
10
11
12
13
14 CREATE TABLE orders_raw (
    order_id     int,
    order_date   date,
    customer     text,          -- "Ada Lovelace"
    cust_email   text,
    cust_city    text,
    cust_country text,
    products     text,          -- 'SKU-1, SKU-7, SKU-9'   <- multiple values in one cell
    qty          text,          -- '2, 1, 3'
    unit_price   numeric,
    supplier     text,
    supplier_tel text
);

1NF — atomic values, no repeating groups

products and qty hold lists. Split them into rows; every cell now holds a single value.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 CREATE TABLE orders_1nf (
    order_id     int,
    line_no      int,
    order_date   date,
    customer     text,
    cust_email   text,
    cust_city    text,
    cust_country text,
    sku          text,
    qty          int,
    unit_price   numeric(12,2),
    supplier     text,
    supplier_tel text,
    PRIMARY KEY (order_id, line_no)
);

1NF eliminates: parsing strings in WHERE clauses, and the impossibility of indexing or constraining an individual product.

2NF — no partial dependencies on a composite key

The key is (order_id, line_no). But order_date, customer, cust_email, cust_city, cust_country depend on order_id alone. Split.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20 CREATE TABLE orders_2nf (
    order_id     int PRIMARY KEY,
    order_date   date NOT NULL,
    customer     text NOT NULL,
    cust_email   text NOT NULL,
    cust_city    text,
    cust_country text
);
 
CREATE TABLE order_lines_2nf (
    order_id   int NOT NULL REFERENCES orders_2nf,
    line_no    int NOT NULL,
    sku        text NOT NULL,
    qty        int  NOT NULL CHECK (qty > 0),
    unit_price numeric(12,2) NOT NULL,
    supplier      text,
    supplier_tel  text,
    PRIMARY KEY (order_id, line_no)
);

2NF eliminates: repeating the customer's email once per line item.

3NF — no transitive dependencies

In orders_2nf, cust_email → customer, cust_city, cust_country: those depend on the customer, not on the order. In order_lines_2nf, supplier → supplier_tel. Both are non-key columns determining other non-key columns.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35 CREATE TABLE customers (
    customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email       citext NOT NULL UNIQUE,
    name        text   NOT NULL,
    city        text,
    country     char(2) NOT NULL REFERENCES countries(code)
);
 
CREATE TABLE suppliers (
    supplier_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        text NOT NULL UNIQUE,
    phone       text
);
 
CREATE TABLE products (
    sku          text PRIMARY KEY,
    name         text NOT NULL,
    supplier_id  bigint NOT NULL REFERENCES suppliers
);
 
CREATE TABLE orders (
    order_id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id  bigint NOT NULL REFERENCES customers,
    ordered_at   timestamptz NOT NULL DEFAULT now()
);
 
CREATE TABLE order_lines (
    order_id     bigint NOT NULL REFERENCES orders ON DELETE CASCADE,
    line_no      int NOT NULL,
    sku          text NOT NULL REFERENCES products,
    qty          int NOT NULL CHECK (qty > 0),
    unit_price   numeric(12,2) NOT NULL CHECK (unit_price >= 0),
    PRIMARY KEY (order_id, line_no)
);

3NF eliminates: the update anomaly where changing a supplier's phone number requires touching every order line they ever appeared on.

The post's order sheet normalized step by step: orders_raw with list-valued products and qty, 1NF orders_1nf keyed on (order_id, line_no), 2NF orders_2nf and order_lines_2nf, and 3NF customers, orders, suppliers, products and order_lines, with the dependency removed at each step and a note on why unit_price stays on order_lines.

Note unit_price stays on order_lines even though products have prices. That is not a normalization failure — the price at the time of sale is a genuinely different fact from the current catalogue price. Ask "does this value depend on the key, or on the moment?" before deleting a column that looks redundant.

BCNF — every determinant is a candidate key

3NF still permits an anomaly when a table has overlapping candidate keys. Classic example: a course scheduling table where each course is taught by one instructor, and each instructor teaches one course.

1
2
3
4
5
6
7
8
9
10
11 -- 3NF but not BCNF
CREATE TABLE enrolment (
    student_id int,
    course     text,
    instructor text,
    PRIMARY KEY (student_id, course)
);
-- Dependencies: (student_id, course) -> instructor   AND   instructor -> course
-- `instructor` determines `course` but is not a candidate key. Anomaly: you cannot
-- record that an instructor teaches a course until some student enrols.

BCNF decomposition:

1
2
3
4 CREATE TABLE teaches   (instructor text PRIMARY KEY, course text NOT NULL);
CREATE TABLE enrolment (student_id int, instructor text REFERENCES teaches,
                        PRIMARY KEY (student_id, instructor));

In practice 3NF is the working target and BCNF violations are rare; they show up mostly where two natural keys overlap.

Chapter 2 — The honest case for denormalization

Normalization optimises for write correctness. It costs joins on read. Denormalize when you can state, in one sentence, which read you are buying and which write cost you are paying.

Defensible denormalizations:

Pattern	Buys	Costs	Keep correct with
Cached counter (`post.comment_count`)	Removing a `count(*)` from every page render	Drift if the write path is missed	Trigger, or a scheduled reconciliation job
Copied immutable value (`order_lines.unit_price`)	Historical accuracy, not just speed	Nothing — this is not really denormalization	Nothing needed
Denormalized read table / summary table	A dashboard query that would otherwise join six tables	Staleness, extra storage, refresh cost	Materialized view or incremental refresh (post 04)
Duplicated `tenant_id` on child tables	Row-level security and partition pruning without joins	An extra column to keep consistent	Foreign key on the composite `(tenant_id, parent_id)`

Indefensible ones: duplicating a mutable attribute with no trigger and no reconciliation, or "we might need to join later so let's flatten now". The test is whether you can describe how the copy stays correct. If you cannot, you have not denormalized — you have introduced a bug with a schedule.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17 -- A cached counter kept honest by a trigger
ALTER TABLE posts ADD COLUMN comment_count int NOT NULL DEFAULT 0;
 
CREATE FUNCTION bump_comment_count() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE posts SET comment_count = comment_count + 1 WHERE id = NEW.post_id;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE posts SET comment_count = comment_count - 1 WHERE id = OLD.post_id;
    END IF;
    RETURN NULL;
END $$;
 
CREATE TRIGGER comments_count_trg
AFTER INSERT OR DELETE ON comments
FOR EACH ROW EXECUTE FUNCTION bump_comment_count();

Watch out: this trigger serialises all comment inserts for a single hot post onto one row lock. For a viral post that is a contention bottleneck. The alternative is an append-only delta table summed periodically — a trade of freshness for throughput.

Chapter 3 — Keys: natural, surrogate, and index locality

Terminology

Natural key — a key drawn from the data itself: ISBN, ISO country code, email address.
Surrogate key — a meaningless generated identifier: a sequence or a UUID.
Index locality — how close together in the btree consecutive inserts land. High locality means new entries pack into the same few rightmost pages.

Natural keys are excellent when the value is genuinely immutable, short, and externally defined — char(2) ISO country codes, currency codes, ISBNs. They are dangerous when the "obviously stable" value turns out to be mutable: email addresses change, national ID formats get reissued, SKUs get renumbered in an acquisition. A natural key that changes forces a cascading update across every referencing table.

Default to a surrogate primary key and a UNIQUE constraint on the natural key. You get stable references and enforced uniqueness.

bigint identity vs UUIDv4 vs UUIDv7

1
2
3
4
5
6
7
8
9
10 -- Sequence-backed, SQL-standard form (prefer over the older `serial`)
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
 
-- Random UUID (v4)
id uuid PRIMARY KEY DEFAULT gen_random_uuid()
 
-- Time-ordered UUID (v7). PostgreSQL 18 ships uuidv7();
-- on earlier versions use the pg_uuidv7 extension or generate in the application.
id uuid PRIMARY KEY DEFAULT uuidv7()

	`bigint` identity	UUIDv4	UUIDv7
Size	8 bytes	16 bytes	16 bytes
Insert locality in a btree	Perfect — always the rightmost page	Terrible — every insert lands on a random page	Good — time-prefixed, so near-sequential
Generatable client-side	No (needs a round trip or a sequence cache)	Yes	Yes
Leaks information	Yes — row counts and creation order are guessable	No	Creation timestamp only
Merging data from multiple sources	Collides	Safe	Safe

Index locality is the real issue, not the extra 8 bytes. With random UUIDv4 primary keys, every insert dirties a different btree leaf page. Once the index exceeds the shared buffer cache, each insert becomes a random read plus a random write, write amplification rises through full-page writes in the WAL, and the index fragments. This is the reason UUIDv4 primary keys develop a reputation for "getting slow at scale" — and the reason UUIDv7, which puts a millisecond timestamp in the high bits, fixes most of it while keeping client-side generation.

Practical guidance:

Single database, ids never leave the system → bigint GENERATED ALWAYS AS IDENTITY.
Ids exposed in URLs, generated on the client, or merged across services → UUIDv7.
UUIDv4 → only when unpredictability of ordering is itself a requirement.
Never store a UUID as text. The uuid type is 16 bytes; the text form is 36 plus overhead and compares slower.

Chapter 4 — Modelling patterns

One-to-many

A foreign key on the many side. Decide the delete behaviour explicitly — the default NO ACTION is a good default precisely because it forces the conversation.

1
2
3
4
5
6 CREATE TABLE invoice_lines (
    invoice_id bigint NOT NULL REFERENCES invoices ON DELETE CASCADE,
    ...
);
CREATE INDEX ON invoice_lines (invoice_id);   -- FKs are NOT indexed automatically

Common pitfall: PostgreSQL indexes the referenced side (it must be unique) but never the referencing side. A missing index on the child's foreign key column makes every parent delete or update do a sequential scan of the child table, and makes the child hold locks longer.

Many-to-many with a composite primary key

1
2
3
4
5
6
7
8
9 CREATE TABLE post_tags (
    post_id bigint NOT NULL REFERENCES posts ON DELETE CASCADE,
    tag_id  int    NOT NULL REFERENCES tags  ON DELETE CASCADE,
    added_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (post_id, tag_id)
);
-- The PK covers (post_id, tag_id). Add the mirror index for the other direction:
CREATE INDEX ON post_tags (tag_id, post_id);

The composite primary key does double duty: it enforces "a tag can only be applied once" and serves as the access path from post to tags. The second index serves tag to posts. Add a surrogate key to a join table only when something else must reference the relationship itself.

Inheritance: single table vs class table

For a hierarchy of related entities — say vehicle with car and truck subtypes:

Approach	Shape	Good	Bad
Single table	One table, a `type` discriminator, all subtype columns nullable	One query returns everything; no joins	Cannot enforce `NOT NULL` per subtype without `CHECK` gymnastics; wide sparse rows
Class table	`vehicle` + `car` + `truck`, each subtype table referencing the parent	Proper constraints per subtype; narrow tables	Every read joins; enforcing "exactly one subtype" needs work
Concrete table	`car` and `truck` fully independent, no parent	Simplest per-type queries	No way to reference "a vehicle" generically; duplicated columns

Single table with CHECK constraints is the usual pragmatic answer for two or three subtypes with mostly shared columns:

1
2
3
4
5
6
7
8
9
10 CREATE TABLE vehicles (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    kind         text NOT NULL CHECK (kind IN ('car','truck')),
    plate        text NOT NULL UNIQUE,
    seats        int,          -- cars only
    payload_kg   int,          -- trucks only
    CONSTRAINT car_fields   CHECK (kind <> 'car'   OR (seats IS NOT NULL AND payload_kg IS NULL)),
    CONSTRAINT truck_fields CHECK (kind <> 'truck' OR (payload_kg IS NOT NULL AND seats IS NULL))
);

PostgreSQL's own INHERITS table inheritance exists but is largely a legacy feature — it does not propagate unique or foreign key constraints to children. Use the patterns above instead.

Hierarchies: adjacency list, closure table, ltree

Pattern	Storage	Read a subtree	Move a subtree	Notes
Adjacency list (`parent_id`)	1 column	Recursive CTE	Update one row	Simple, correct, the default choice
Closure table	One row per ancestor/descendant pair	Single indexed join	Delete + reinsert the subtree's pairs	Fast reads, expensive writes, exact
`ltree`	One `ltree` path column	`path <@ 'root.a'` with a GiST index	Rewrite paths of all descendants	Elegant for shallow, rarely-moved trees

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18 -- Adjacency list + recursive CTE (works everywhere, no extension needed)
CREATE TABLE categories (
    id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    parent_id bigint REFERENCES categories,
    name      text NOT NULL
);
CREATE INDEX ON categories (parent_id);
 
WITH RECURSIVE subtree AS (
    SELECT id, parent_id, name, 1 AS depth, ARRAY[id] AS path
    FROM categories WHERE id = 7
  UNION ALL
    SELECT c.id, c.parent_id, c.name, s.depth + 1, s.path || c.id
    FROM categories c JOIN subtree s ON c.parent_id = s.id
    WHERE s.depth < 20                     -- cycle/runaway guard
)
SELECT * FROM subtree ORDER BY path;

1
2
3
4
5
6
7
8 -- ltree: materialised path
CREATE EXTENSION IF NOT EXISTS ltree;
ALTER TABLE categories ADD COLUMN path ltree;
CREATE INDEX categories_path_gist ON categories USING gist (path);
 
SELECT * FROM categories WHERE path <@ 'root.electronics';   -- whole subtree
SELECT * FROM categories WHERE path ~ 'root.*{1}';           -- direct children of root

Start with the adjacency list. Move to a closure table or ltree only when subtree reads are measurably the bottleneck.

Soft deletes and what they cost

1
2 ALTER TABLE users ADD COLUMN deleted_at timestamptz;

The costs are real and cumulative:

Every query must remember the filter. One forgotten WHERE deleted_at IS NULL is a data leak.
Unique constraints stop working. UNIQUE (email) now blocks re-registering an address whose owner "deleted" their account.
Foreign keys stop meaning what they say. A child can reference a soft-deleted parent.
The table never shrinks, and indexes carry dead weight forever.

Mitigations, in order of preference:

1
2
3
4
5
6
7
8
9 -- 1. Partial unique index: uniqueness applies only among live rows
CREATE UNIQUE INDEX users_email_live ON users (email) WHERE deleted_at IS NULL;
 
-- 2. A view that carries the filter, so application code cannot forget it
CREATE VIEW active_users AS SELECT * FROM users WHERE deleted_at IS NULL;
 
-- 3. Best where it fits: move the row instead of flagging it
CREATE TABLE users_archive (LIKE users INCLUDING ALL, deleted_at timestamptz NOT NULL);

Option 3 keeps the live table clean, the constraints honest, and the history intact. Prefer it unless the row must remain referenceable.

Chapter 5 — Temporal data and exclusion constraints

Two different time concepts are often conflated:

Transaction time — when the database learned the fact (created_at, updated_at).
Valid time — when the fact is true in the real world (valid_from, valid_to).

A price that takes effect next Monday has a valid time in the future and a transaction time of now. Model them separately when both matter.

Range types make valid-time intervals a first-class value and let the database forbid overlaps outright:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23 CREATE EXTENSION IF NOT EXISTS btree_gist;   -- needed to mix = and && in one constraint
 
CREATE TABLE room_bookings (
    id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id  int NOT NULL REFERENCES rooms,
    guest_id bigint NOT NULL REFERENCES guests,
    during   tstzrange NOT NULL,
 
    -- No two bookings for the same room may overlap in time. Enforced by the database,
    -- not by application code, and therefore correct under concurrency.
    EXCLUDE USING gist (room_id WITH =, during WITH &&),
 
    CONSTRAINT nonempty CHECK (NOT isempty(during))
);
 
INSERT INTO room_bookings (room_id, guest_id, during)
VALUES (12, 900, tstzrange('2026-07-01 15:00+09', '2026-07-04 10:00+09', '[)'));
 
-- Overlapping insert:
INSERT INTO room_bookings (room_id, guest_id, during)
VALUES (12, 901, tstzrange('2026-07-03 15:00+09', '2026-07-06 10:00+09', '[)'));
-- ERROR:  conflicting key value violates exclusion constraint "room_bookings_room_id_during_excl"

An EXCLUDE constraint is the generalisation of UNIQUE: instead of "no two rows are equal on these columns", it says "no two rows satisfy these operators pairwise". UNIQUE (a) is exactly EXCLUDE (a WITH =).

Useful range operators: && overlaps, @> contains, <@ contained by, -|- adjacent, + union, * intersection. Always use the half-open '[)' bound convention for time — it makes back-to-back intervals adjacent rather than overlapping, and it composes correctly.

1
2
3 SELECT * FROM room_bookings WHERE during @> now();                    -- current bookings
SELECT * FROM room_bookings WHERE during && tstzrange(now(), now() + interval '7 days');

For a full history table, daterange/tstzrange plus an exclusion constraint on (entity_id WITH =, valid WITH &&) gives a non-overlapping, gap-tolerant slowly-changing-dimension table that the database itself keeps consistent.

Chapter 6 — JSON vs JSONB

Terminology

json — stores the exact input text. Preserves whitespace, key order, and duplicate keys. Re-parsed on every access.
jsonb — stores a decomposed binary representation. Loses whitespace, key order, and duplicates (last wins). Parsed once on input; indexable.
jsonpath — the SQL/JSON path language, available through jsonb_path_query and the @? / @@ operators.

Use jsonb. json is appropriate only when you must reproduce the input byte-for-byte, which is an audit-log requirement, not a data-modelling one.

Operators

Operator	Returns	Example
`->`	`jsonb`	`data -> 'address'`
`->>`	`text`	`data ->> 'name'`
`#>`	`jsonb` at a path	`data #> '{address,city}'`
`#>>`	`text` at a path	`data #>> '{address,city}'`
`@>`	boolean, containment	`data @> '{"status":"active"}'`
`?`	boolean, key exists	`data ? 'email'`
`?	`/`?&`	any / all keys exist
`@?`	boolean, jsonpath matches	`data @? '$.items[*] ? (@.qty > 5)'`
`@@`	boolean, jsonpath predicate	`data @@ '$.total > 100'`
`jsonb_path_query`	set of `jsonb`	`SELECT jsonb_path_query(data, '$.items[*].sku')`

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15 CREATE TABLE events (
    id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    kind    text NOT NULL,
    at      timestamptz NOT NULL DEFAULT now(),
    payload jsonb NOT NULL
);
 
SELECT payload ->> 'campaign' AS campaign, count(*)
FROM events
WHERE payload @> '{"channel":"email"}'
GROUP BY 1;
 
SELECT jsonb_path_query(payload, '$.items[*] ? (@.qty > 5).sku') AS bulk_sku
FROM events WHERE kind = 'order';

Indexing JSONB

1
2
3
4
5
6
7
8
9
10
11
12
13 -- Default GIN (jsonb_ops): supports @>, ?, ?|, ?&, @?, @@. Larger index.
CREATE INDEX events_payload_gin ON events USING gin (payload);
 
-- jsonb_path_ops: supports ONLY @> (and jsonpath equality). Smaller and faster.
CREATE INDEX events_payload_gin_path ON events USING gin (payload jsonb_path_ops);
 
-- Expression btree: for one hot scalar key, this beats GIN outright.
CREATE INDEX events_campaign ON events ((payload ->> 'campaign'));
 
-- Partial + expression: narrowest and cheapest of all
CREATE INDEX events_order_total ON events (((payload ->> 'total')::numeric))
    WHERE kind = 'order';

Choosing: if you query a known key with equality or range, use an expression btree index. If you query arbitrary keys with containment, use GIN — jsonb_path_ops when @> is all you need, default jsonb_ops when you also need key-existence operators.

Common pitfall: payload ->> 'total' > '100' compares text, so '99' > '100' is true. Cast explicitly — (payload ->> 'total')::numeric > 100 — and make sure your index uses the identical expression, cast included, or it will not be used.

The rule for JSONB vs a real column

Make it a real column when any of these is true:

You filter, sort, join, or group by it regularly.
It must be NOT NULL, unique, checked, or a foreign key.
Every row has it.
Its type matters (dates, money, booleans).

Make it JSONB when all of these are true:

The shape is genuinely open-ended or supplied by an external system you do not control.
It is read as a whole blob far more often than it is queried by an inner field.
The set of keys varies by row and enumerating them would produce a wide sparse table.

Typical correct shapes: webhook payloads as received, third-party API responses, per-tenant custom fields, feature flags, sparse product attributes that differ by category, and event payloads whose schema evolves faster than your migration cadence.

The failure mode is "the whole row is one JSONB column". You lose constraints, statistics quality, and type safety, and you gain nothing PostgreSQL was not already better at. If you find yourself writing WHERE data->>'user_id' = ... on every query, that key wanted to be a column. The hybrid — a normalized core with a JSONB sidecar for genuinely variable attributes — is the design that ages well.

For the document-database perspective on the same modelling questions (embed vs reference, and how those map back to relational tables), see the MongoDB course posts mongo_07_schema_design and mongo_23_sql_to_mongo_migration. The mapping is close: MongoDB's "embed" corresponds to JSONB or a composite type here, and its "reference" corresponds to a foreign key — with the difference that PostgreSQL will actually enforce the reference.

Chapter 7 — Enum vs lookup table, revisited

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17 -- Option A: native enum type
CREATE TYPE order_status AS ENUM ('pending','paid','shipped','cancelled');
ALTER TABLE orders ADD COLUMN status order_status NOT NULL DEFAULT 'pending';
 
-- Option B: lookup table + foreign key
CREATE TABLE order_statuses (
    code       text PRIMARY KEY,
    label      text NOT NULL,
    is_terminal boolean NOT NULL DEFAULT false,
    sort_order int NOT NULL
);
ALTER TABLE orders ADD COLUMN status text NOT NULL REFERENCES order_statuses;
 
-- Option C: text + CHECK
ALTER TABLE orders ADD COLUMN status text NOT NULL
    CHECK (status IN ('pending','paid','shipped','cancelled'));

	Enum type	Lookup table	Text + CHECK
Storage	4 bytes	Text or a small FK	Text
Add a value	`ALTER TYPE ... ADD VALUE` — cannot run inside a transaction block in older versions, and cannot be used in the same transaction that adds it	`INSERT` — ordinary DML	`ALTER TABLE ... DROP CONSTRAINT` + add — takes a lock and validates
Remove or rename a value	Rename since PostgreSQL 10; removal requires recreating the type	`DELETE` (FK protects in-use values)	Constraint swap
Attach metadata (label, ordering, flags)	No	Yes	No
Ordering	Declaration order, built in	`sort_order` column	Alphabetical only

Rule of thumb: an enum for a closed set that genuinely never changes ('left' / 'right', weekday names) and where the 4-byte storage matters. A lookup table for anything a product manager might want to add to, or that needs a human-readable label, an ordering, or extra attributes — which in practice is most status columns.

Chapter 8 — Migration discipline

Every schema change happens against a running system. The rule is that a migration must never require the application and the database to change at the same instant.

Expand / contract

Split every breaking change into phases that are individually backwards-compatible:

Expand — add the new structure. Nullable column, new table, new index built CONCURRENTLY. Old code keeps working.
Backfill — populate the new structure in batches, committing as you go so no single transaction holds a snapshot for hours.
Dual-write — deploy application code that writes both old and new. Reads still come from old.
Migrate reads — deploy code that reads from new. Verify.
Contract — stop writing the old structure, then drop it, once you are certain no rollback will need it.

Renaming a column becomes: add new_name → backfill → dual-write → switch reads → drop old_name. Five deployments instead of one, and zero downtime at every step.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 -- Backfill in bounded batches, never one giant UPDATE
DO $$
DECLARE rows_done int;
BEGIN
  LOOP
    UPDATE users SET email_normalised = lower(email)
    WHERE id IN (
        SELECT id FROM users WHERE email_normalised IS NULL
        ORDER BY id LIMIT 5000 FOR UPDATE SKIP LOCKED
    );
    GET DIAGNOSTICS rows_done = ROW_COUNT;
    EXIT WHEN rows_done = 0;
    COMMIT;                 -- requires a procedure or a client-side loop
  END LOOP;
END $$;

Adding a NOT NULL column safely

Adding a plain column with a default has been non-rewriting since PostgreSQL 11 — the default is recorded in the catalogue and materialised lazily. That makes this fast even on a huge table:

1
2 ALTER TABLE orders ADD COLUMN currency char(3) NOT NULL DEFAULT 'JPY';   -- PG 11+: no rewrite

A volatile default still rewrites the whole table:

1
2 ALTER TABLE orders ADD COLUMN token uuid NOT NULL DEFAULT gen_random_uuid();  -- FULL REWRITE

For that case, and for adding NOT NULL to an existing column, use the constraint-first route:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15 -- 1. Add nullable, no default. Instant.
ALTER TABLE orders ADD COLUMN token uuid;
 
-- 2. Backfill in batches (see above), and have the application populate new rows.
 
-- 3. Add a NOT VALID check: takes a brief lock, validates nothing yet.
ALTER TABLE orders ADD CONSTRAINT orders_token_nn CHECK (token IS NOT NULL) NOT VALID;
 
-- 4. Validate: takes only SHARE UPDATE EXCLUSIVE, so reads and writes continue.
ALTER TABLE orders VALIDATE CONSTRAINT orders_token_nn;
 
-- 5. PostgreSQL 12+ can now prove NOT NULL from the validated CHECK without a full scan.
ALTER TABLE orders ALTER COLUMN token SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_token_nn;

The same NOT VALID → VALIDATE CONSTRAINT two-step works for foreign keys, and it is the only sane way to add one to a large table.

Why a careless ALTER TABLE is dangerous

Most ALTER TABLE forms take an ACCESS EXCLUSIVE lock — the mode that conflicts with everything, including plain SELECT. Two things make that far worse than it sounds:

The lock request must wait for existing conflicting locks to be released. One long-running SELECT or an open idle in transaction session (post 05) delays it indefinitely.
While it waits, every subsequent query queues behind it. PostgreSQL's lock queue is ordered. A migration blocked for 30 seconds behind one slow report takes the whole table offline for those 30 seconds — for everyone.

The discipline:

1
2
3
4 SET lock_timeout = '3s';          -- fail fast; retry in a loop rather than queueing
SET statement_timeout = '30s';
ALTER TABLE orders ADD COLUMN promo_code text;

Wrap DDL in a retry loop with lock_timeout, and check pg_stat_activity for long transactions before you start. Also remember:

CREATE INDEX CONCURRENTLY and DROP INDEX CONCURRENTLY avoid the exclusive lock (at the cost of two table passes and the possibility of leaving an INVALID index behind if they fail — check pg_index.indisvalid and re-run).
CREATE INDEX CONCURRENTLY cannot run inside a transaction block, which means most migration frameworks need an explicit "no transaction" flag for that step.
Changing a column's type generally rewrites the table. Widening varchar(50) to varchar(100) or to text does not; narrowing does.
Adding a column is cheap; dropping one is also cheap (the column is only marked dropped), but the space is not reclaimed until the rows are rewritten.

Practice Exercises

Exercise 1 — Normalize the order sheet

Take orders_raw from Chapter 1 and produce the full 3NF schema yourself, with all foreign keys, CHECK constraints, and NOT NULLs. Then write the single query that reconstructs the original flat view.

Exercise 2 — Find the BCNF violation

Given a table (student, course, instructor) where each instructor teaches exactly one course, list the functional dependencies, show why it is in 3NF but not BCNF, and give the decomposition.

Solution

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16 -- FDs: (student, course) -> instructor ; instructor -> course
-- Candidate keys: (student, course) and (student, instructor)
-- `instructor` is a determinant but not a candidate key -> BCNF violation.
 
CREATE TABLE teaches (
    instructor text PRIMARY KEY,
    course     text NOT NULL
);
CREATE TABLE enrolment (
    student_id int  NOT NULL REFERENCES students,
    instructor text NOT NULL REFERENCES teaches,
    PRIMARY KEY (student_id, instructor)
);
-- Reconstruct: SELECT e.student_id, t.course, t.instructor
--              FROM enrolment e JOIN teaches t USING (instructor);

Exercise 3 — Measure UUID index locality

Create two tables of ten million rows, one keyed by bigint identity and one by gen_random_uuid(). Compare insert throughput, final pg_relation_size of the primary key index, and WAL generated (pg_stat_wal or pg_current_wal_lsn() deltas). Report what you measured on your own hardware rather than assuming a figure.

Exercise 4 — Forbid overlapping bookings

Build the room_bookings table with an EXCLUDE constraint. Prove it holds under concurrency: two sessions each insert an overlapping booking for the same room inside open transactions, and show that the second blocks and then fails on commit.

Solution

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18 CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE room_bookings (
    id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id int NOT NULL,
    during  tstzrange NOT NULL,
    EXCLUDE USING gist (room_id WITH =, during WITH &&)
);
 
-- Session A                                  -- Session B
BEGIN;                                        BEGIN;
INSERT INTO room_bookings (room_id, during)
VALUES (1, tstzrange('2026-07-01','2026-07-05','[)'));
                                              INSERT INTO room_bookings (room_id, during)
                                              VALUES (1, tstzrange('2026-07-03','2026-07-08','[)'));
                                              -- blocks, waiting on A
COMMIT;                                       -- ERROR: conflicting key value violates
                                              --        exclusion constraint

Exercise 5 — JSONB or column?

Given an events table receiving webhooks from three providers, decide for each of these fields whether it should be a real column or stay inside payload: provider, received_at, signature, customer_email, raw_headers, amount_cents. Justify each in one sentence, then write the DDL and the indexes.

Solution

1
2
3
4
5
6
7
8
9
10
11
12
13
14 -- Columns: queried, sorted, constrained, present on every row.
-- Stays in JSONB: provider-specific and read as a blob.
CREATE TABLE webhook_events (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    provider     text        NOT NULL REFERENCES providers,   -- filtered constantly
    received_at  timestamptz NOT NULL DEFAULT now(),          -- sorted/ranged constantly
    signature    text        NOT NULL,                        -- verified on every row
    customer_email citext,                                    -- joined to customers
    amount_cents bigint      CHECK (amount_cents >= 0),       -- aggregated, typed
    payload      jsonb       NOT NULL                         -- raw_headers + the rest
);
CREATE INDEX ON webhook_events (provider, received_at DESC);
CREATE INDEX ON webhook_events USING gin (payload jsonb_path_ops);

Exercise 6 — Zero-downtime rename

Write the five-step expand/contract plan to rename users.username to users.handle, including the exact SQL for each step and what the application deploys in between.

Exercise 7 — Hierarchy showdown

Load a 100 000-node category tree. Implement it three ways (adjacency list, closure table, ltree) and measure: full-subtree read, ancestor path lookup, and moving a mid-level subtree. Record which pattern wins each operation.

Cheat-sheet

What	How
Surrogate key, modern form	`id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY`
Time-ordered UUID	`uuid DEFAULT uuidv7()` (PostgreSQL 18+; `pg_uuidv7` earlier)
Natural key alongside a surrogate	surrogate PK + `UNIQUE (natural_col)`
Index a foreign key	`CREATE INDEX ON child (parent_id);` — never automatic
Many-to-many	join table with `PRIMARY KEY (a_id, b_id)` + mirror index
Case-insensitive unique	`citext` column, or `UNIQUE (lower(col))`
Uniqueness among live rows only	`CREATE UNIQUE INDEX ... WHERE deleted_at IS NULL`
Subtree read	`WITH RECURSIVE` on `parent_id`, with a depth guard
Materialised path	`ltree` + `USING gist (path)`, query with `<@`
No overlapping intervals	`EXCLUDE USING gist (key WITH =, range WITH &&)` (needs `btree_gist`)
Half-open interval	`tstzrange(a, b, '[)')`
JSON containment	`payload @> '{"k":"v"}'::jsonb`
JSON scalar as text	`payload ->> 'k'` (cast before comparing numerically)
JSON path query	`jsonb_path_query(payload, '$.items[*] ? (@.qty > 5)')`
Smallest JSONB index	`USING gin (payload jsonb_path_ops)` — supports `@>` only
Hot JSON key	expression btree: `CREATE INDEX ON t ((payload ->> 'k'));`
Closed value set with metadata	lookup table + FK, not an enum
Add column with static default	`ADD COLUMN c type NOT NULL DEFAULT 'x'` — no rewrite since PG 11
Add NOT NULL safely	`CHECK ... NOT VALID` → `VALIDATE CONSTRAINT` → `SET NOT NULL`
Add FK safely	`ADD CONSTRAINT ... REFERENCES ... NOT VALID` → `VALIDATE CONSTRAINT`
Build an index online	`CREATE INDEX CONCURRENTLY` (not inside a transaction block)
Protect production from DDL	`SET lock_timeout = '3s';` before every `ALTER TABLE`

What's next

Post 07 closes the base series with operations: what the WAL really is, backup strategies from pg_dump to point-in-time recovery, streaming and logical replication with their failover hazards, why connection pooling is near-mandatory in front of PostgreSQL's process-per-connection model, roles and row-level security, and the handful of metrics worth waking someone up for.

References

Data Definition
Constraints
JSON Types
JSON Functions and Operators
Range Types, including exclusion constraints
ltree
ALTER TABLE
UUID functions
RFC 9562 — UUID versions 6, 7, and 8
Date, C. J., Database Design and Relational Theory — normal forms treated rigorously.
MongoDB course, posts 07 (mongo_07_schema_design) and 23 (mongo_23_sql_to_mongo_migration) — the document-model counterparts.

Note: this post is in progress. Planned additions: multi-tenancy schema strategies (shared table vs schema-per-tenant vs database-per-tenant), a composite-type and array-column section, and a measured comparison of GIN jsonb_ops versus jsonb_path_ops index size on a real payload set.