Module 04c // Database Internals

Schemas, Indexes, Normalization & Query Plans

> The four levers that decide whether SQL is fast or painful — how your tables are shaped, how rows are stored, how redundancy is controlled, and how the planner executes your query.

1. Schema Design

A schema is the blueprint of your database — tables, columns, types, constraints, and the relationships that tie them together. Good schemas encode business rules as constraints so bad data cannot exist, even if application code has a bug.

Pick the right data types

  • BIGINT for surrogate keys (don't outgrow INT at 2.1B rows).
  • NUMERIC(precision, scale) for money — never FLOAT.
  • TIMESTAMPTZ, never TIMESTAMP. Always store UTC.
  • TEXT over VARCHAR(n) in Postgres — same performance, no arbitrary limit.
  • JSONB for semi-structured data; JSON only when you need to preserve key order.
  • UUID for distributed IDs; otherwise BIGSERIAL/IDENTITY is smaller and faster.

Constraints are documentation that the database enforces

CREATE TABLE customers (
  id          BIGSERIAL PRIMARY KEY,
  email       CITEXT      NOT NULL UNIQUE,
  full_name   TEXT        NOT NULL CHECK (length(full_name) BETWEEN 1 AND 200),
  country     CHAR(2)     NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id           BIGSERIAL PRIMARY KEY,
  customer_id  BIGINT      NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
  status       TEXT        NOT NULL CHECK (status IN ('pending','paid','shipped','cancelled')),
  total_cents  BIGINT      NOT NULL CHECK (total_cents >= 0),
  placed_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

Surrogate vs natural keys

A surrogate key (id BIGSERIAL) is stable and small. A natural key (email, ISBN) has business meaning but can change. Best practice: use a surrogate as the primary key and put a UNIQUEconstraint on the natural key so both invariants hold.

Naming

  • Tables in snake_case, plural (orders, order_items).
  • Foreign keys named <table>_id (customer_id).
  • Boolean columns are predicates (is_active, has_shipped), never active_flag.
  • Timestamps end in _at (created_at), durations in _seconds.

2. Normalization (and When to Break It)

Normalization is the process of removing redundancy so each fact is stored in exactly one place. Less redundancy → fewer update anomalies → fewer bugs.

The normal forms, in plain English

  • 1NF — every column holds a single atomic value. No comma-separated lists, no repeating groups.
  • 2NF — no partial dependency on a composite key. Every non-key column depends on the whole key.
  • 3NF — no transitive dependency. Non-key columns depend on the key, the whole key, and nothing but the key.
  • BCNF — every determinant is a candidate key. A stricter form of 3NF that resolves rare edge cases.

Worked example: from messy to 3NF

The starting table violates 1NF, 2NF, and 3NF at once:

-- BAD: a single row tries to hold an order, its lines, and customer info
CREATE TABLE orders_bad (
  order_id        INT,
  customer_email  TEXT,
  customer_city   TEXT,         -- depends on customer_email, not order_id (3NF violation)
  product_codes   TEXT,         -- 'A12, B07, C99' (1NF violation)
  line_qtys       TEXT          -- '2, 1, 3'       (1NF violation)
);
-- GOOD: split by the dependencies
CREATE TABLE customers (
  id     BIGSERIAL PRIMARY KEY,
  email  CITEXT NOT NULL UNIQUE,
  city   TEXT   NOT NULL
);

CREATE TABLE orders (
  id           BIGSERIAL PRIMARY KEY,
  customer_id  BIGINT NOT NULL REFERENCES customers(id),
  placed_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  order_id    BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id  BIGINT NOT NULL REFERENCES products(id),
  qty         INT    NOT NULL CHECK (qty > 0),
  PRIMARY KEY (order_id, product_id)
);

When to denormalize on purpose

  • Read-heavy analytics — star schemas duplicate dimension attributes into fact rows to avoid joins.
  • Hot aggregates — store order_total_cents on orders instead of summing order_items every read; keep it correct with a trigger or rebuild job.
  • Audit snapshots — copy the price at the time of sale into order_items.unit_price_cents so changing the product price doesn't rewrite history.

Denormalize when profiling proves the join cost matters, never preemptively. Every duplicated column is a future bug waiting for an inconsistent update.

3. Indexes

An index is a secondary data structure that lets the database find rows without scanning the table. It speeds up reads and slows down writes — every insert/update/delete has to maintain every relevant index. Pick them deliberately.

Index types you'll actually use

  • B-tree (default) — equality and range. Works for =, <, >, BETWEEN, ORDER BY, prefix LIKE 'abc%'.
  • Hash — equality only. Rarely worth it over B-tree.
  • GIN — multi-valued data: JSONB, arrays, full-text search (tsvector), trigrams.
  • GiST / SP-GiST — geometric data, ranges, nearest-neighbor.
  • BRIN — huge tables where rows are physically clustered (time-series). Tiny on disk, weaker selectivity.
-- Equality + range lookups on a hot column
CREATE INDEX idx_orders_placed_at ON orders (placed_at);

-- Composite: left-to-right rule. Useful for filters on (customer_id) OR (customer_id, status).
CREATE INDEX idx_orders_customer_status ON orders (customer_id, status);

-- Partial: only index the rows you actually query
CREATE INDEX idx_orders_pending ON orders (placed_at)
  WHERE status = 'pending';

-- Covering: include extra columns so the index alone answers the query (index-only scan)
CREATE INDEX idx_orders_lookup ON orders (customer_id) INCLUDE (status, total_cents);

-- Expression: index the value you actually filter on
CREATE INDEX idx_customers_lower_email ON customers (lower(email));

-- JSONB containment with GIN
CREATE INDEX idx_events_payload ON events USING GIN (payload jsonb_path_ops);

-- Time-series table — BRIN is tiny and effective if rows are append-only
CREATE INDEX idx_logs_ts_brin ON logs USING BRIN (ts);

The left-prefix rule for composite indexes

An index on (a, b, c) can be used for filters on (a),(a, b), or (a, b, c) — but not for a filter on(b) alone. Order columns by selectivity and the queries you actually run.

Index-only scans

When every column the query needs lives in the index (key columns orINCLUDEd columns), Postgres skips the heap entirely. This is the fastest read path. Use EXPLAIN ANALYZE and look forIndex Only Scan.

When indexes hurt

  • Write-heavy tables — every index is a write tax.
  • Low-cardinality columns (a boolean) — the planner will prefer a sequential scan anyway.
  • Functions on the column kill the index: WHERE lower(email) = ? needs an expression index on lower(email).
  • Leading wildcards: LIKE '%foo' can't use a B-tree — use a trigram (pg_trgm) GIN index.
  • Unused indexes — check pg_stat_user_indexes for idx_scan = 0 and drop them.

Build indexes without blocking writes

-- CONCURRENTLY avoids an exclusive lock; takes longer but production stays up
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);

-- Inspect index health
SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC
LIMIT 20;

4. Reading Query Plans

The query planner turns your SQL into a tree of physical operators.EXPLAIN shows the plan; EXPLAIN ANALYZE actually runs the query and shows real timings and row counts.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
SELECT c.full_name, sum(o.total_cents) AS spent
FROM customers c
JOIN orders   o ON o.customer_id = c.id
WHERE o.placed_at >= now() - interval '30 days'
GROUP BY c.full_name
ORDER BY spent DESC
LIMIT 20;
Limit  (cost=18421.10..18421.15 rows=20 width=40) (actual time=92.4..92.5 rows=20 loops=1)
  ->  Sort  (cost=18421.10..18534.22 rows=45248 width=40) (actual time=92.4..92.4 rows=20 loops=1)
        Sort Key: (sum(o.total_cents)) DESC
        Sort Method: top-N heapsort  Memory: 27kB
        ->  HashAggregate  (cost=16800.10..17252.58 rows=45248 width=40) (actual time=85.1..89.7 rows=44219 loops=1)
              Group Key: c.full_name
              ->  Hash Join  (cost=2104.00..15890.10 rows=181980 width=16) (actual time=12.3..68.9 rows=182144 loops=1)
                    Hash Cond: (o.customer_id = c.id)
                    ->  Index Scan using idx_orders_placed_at on orders o  (cost=0.43..12300.00 rows=181980 width=16)
                          Index Cond: (placed_at >= (now() - '30 days'::interval))
                    ->  Hash  (cost=1450.00..1450.00 rows=52286 width=32)
                          ->  Seq Scan on customers c  (cost=0.00..1450.00 rows=52286 width=32)
Planning Time: 0.42 ms
Execution Time: 92.7 ms

How to read it

  • Read inside-out, bottom-up. The innermost/lowest node runs first.
  • cost — planner's estimate in arbitrary units. Compare relative cost, not absolute.
  • rows — planner's estimate. actual ... rows — what really happened. A 10× gap means stale statistics or a bad estimate; that's the #1 cause of slow plans.
  • loops — actual time is per loop; multiply by loops for total.
  • BUFFERS — shared hit = cache, read = disk. High read on a hot query means the working set doesn't fit in RAM.

Scan and join operators, decoded

  • Seq Scan — read every row. Fine for small tables or when you need most rows; bad on a 50M-row table filtering 100.
  • Index Scan — walk the index, then fetch the heap row. Good when selectivity is high.
  • Index Only Scan — the index covers all needed columns; never touches the heap.
  • Bitmap Heap Scan — build a bitmap of matching pages, then read them in order. Good when an index returns thousands of rows.
  • Nested Loop — for each outer row, probe the inner. Great when outer is tiny.
  • Hash Join — build a hash table on the smaller side, probe with the larger. Great for big equi-joins.
  • Merge Join — both sides are sorted on the join key; zip them together. Great when sort order already exists (e.g. index order).

Statistics: the planner's source of truth

Postgres samples each table into pg_statistic via ANALYZE. Stale statistics cause bad estimates → bad join orders → slow queries. After bulk loads, always:

ANALYZE orders;

-- For skewed columns, raise the sample resolution
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

-- Multi-column correlation (e.g. city implies country)
CREATE STATISTICS stats_city_country (dependencies) ON city, country FROM addresses;
ANALYZE addresses;

A debugging checklist

  1. Run EXPLAIN (ANALYZE, BUFFERS) on the slow query.
  2. Find the node where estimated vs actual rows diverge most — fix statistics first.
  3. Look for Seq Scan on a large table with a selective filter — likely a missing index.
  4. Look for Nested Loop with a huge outer side — usually a bad estimate forced a poor join choice.
  5. Check Sort with external merge Disk — bump work_mem for that session.
  6. Re-check after every change. Plans are not stable across data volume.

Takeaways

  • Model the domain first — types, keys, foreign keys, and CHECK constraints catch bugs the application never can.
  • Normalize to 3NF by default; denormalize only with a measured reason.
  • Indexes are bets. Add them to fit your read patterns, drop them when idx_scan = 0.
  • Composite indexes follow the left-prefix rule. Covering indexes enable index-only scans.
  • EXPLAIN ANALYZE is the source of truth. Estimated vs actual rows is the first place to look.
  • Keep statistics fresh — every plan decision hangs off them.