---
name: db-schema-design
description: "Designs database schemas and tunes SQL indexing and query performance. Use when writing migrations or CREATE TABLE statements, choosing column types or primary keys, diagnosing slow queries or EXPLAIN plans, designing composite indexes, fixing N+1 problems, or reviewing any Postgres/MySQL schema."
license: MIT
metadata:
  author: lammanhhoang
  version: "1.0.0"
---

# Database Schema Design & Query Performance

Numbered rules are citable: report violations as "violates rule #12". Postgres is the default dialect; MySQL differences are called out inline. For full rationale, exceptions, the complete PostgreSQL-wiki "Don't Do This" list, and catalog audit queries, read references/RULES.md (load when auditing an existing schema, when a rule is challenged, or when you need the SQL to find violations). For migration execution, copy assets/migration-checklist.md into the PR.

## When to use

- Writing or reviewing `CREATE TABLE` / `ALTER TABLE` / migration files.
- Choosing column types, primary keys, or constraints.
- Diagnosing slow queries, reading EXPLAIN output, designing indexes.
- Fixing N+1 query patterns.

When NOT to use: NoSQL document modeling (MongoDB/DynamoDB — different tradeoffs), analytical star schemas in warehouses (denormalization is the norm there), pure application-level ORM API design with no SQL consequences.

## Rules — Types

1. MUST use `text`, not `varchar(n)` and never `char(n)`. If a length limit is a real business rule, add `CHECK (length(col) <= 200)` — a CHECK can be changed without rewriting the table; a `varchar(n)` change historically required one. `char(n)` blank-pads and breaks comparisons. MySQL: `TEXT` columns can't have defaults and are stored off-page, so use `VARCHAR(n)` with a deliberately generous n; still never `CHAR(n)` except true fixed-width codes (`CHAR(2)` country).
2. MUST use `timestamptz` for every point-in-time value. Never `timestamp` (without time zone) — it stores a wall-clock reading with no zone, and "we always write UTC" breaks the first time a client session has a different `TimeZone`. MySQL: use `DATETIME` storing UTC (beware `TIMESTAMP`'s 2038 range limit), convert at the edges.
3. MUST use `numeric(p,s)` for currency amounts. Never the `money` type (output is locale-dependent, fractional precision is fixed by lc_monetary). Never `float`/`double precision` for currency — binary floats can't represent 0.1.
4. MUST NOT use `timetz`, `CURRENT_TIME`, or `timestamp(0)`/`timestamptz(0)`. Precision suffixes round (00:00:00.6 becomes 00:00:01); use `date_trunc('second', ...)` when truncation is intended.
5. MUST name all identifiers lowercase `snake_case` and never quote them. One quoted `"userId"` at creation forces quoting in every query forever.
6. MUST use half-open ranges — `col >= '2026-07-01' AND col < '2026-08-01'` — never `BETWEEN` for timestamps. `BETWEEN` is closed on both ends: it double-counts rows landing exactly on the boundary and silently includes `2026-08-01 00:00:00`.
7. MUST use `NOT EXISTS (SELECT ...)`, never `NOT IN (SELECT ...)`. One NULL in the subquery makes `NOT IN` return zero rows, and Postgres cannot plan `NOT IN` as an anti-join (forces a per-row subplan on large sets).
8. MUST use `GENERATED ALWAYS AS IDENTITY`, not `serial`. `serial` creates a loosely-attached sequence with separate permissions and allows accidental manual inserts into the id column; identity is SQL-standard and owned by the column.

## Rules — Keys

9. MUST give every table a surrogate primary key; default is `id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY`. Plain `int` overflows at 2.1 billion — always `bigint`.
10. SHOULD use UUIDv7 (time-ordered, RFC 9562) when you need distributed/client-side generation or opaque external identifiers: `uuidv7()` in Postgres 18+, the `pg_uuidv7` extension or app-side generation before that. MUST NOT use random UUIDv4 as a clustered PK (MySQL/InnoDB, SQL Server) or on any high-insert-rate table: random inserts hit random index pages — page splits, dead cache, write amplification. If you're stuck with v4, keep it as a secondary unique column and use a bigint PK.
11. MUST NOT use natural keys (email, username, SSN, SKU, phone) as the primary key — they change, and the change cascades through every FK. Enforce them with `UNIQUE` constraints on their own column instead.
12. REVIEW GATE — every foreign key column MUST have an index. Postgres does NOT auto-index the referencing side of an FK (only the referenced side, via its PK/unique constraint). Unindexed FKs cause sequential scans on every parent `DELETE`/`UPDATE` and on every join. MySQL/InnoDB auto-creates one, so this gate is Postgres-specific. Audit query: references/RULES.md section 9.

## Rules — Indexing

13. MUST order composite index columns: equality predicates first, then the single range predicate, then ORDER BY columns. Only columns up to and including the first range condition can be used for the index seek — everything after a range column is only a filter. `WHERE tenant_id = $1 AND status = $2 AND created_at > $3` wants `(tenant_id, status, created_at)`, not `(created_at, tenant_id, status)`.
14. A B-tree index CANNOT serve `LIKE '%term'` or `LIKE '%term%'` (leading wildcard). `LIKE 'term%'` is fine. For contains-search, use a `pg_trgm` GIN index (`CREATE INDEX ... USING gin (col gin_trgm_ops)`) or full-text search. MySQL: FULLTEXT index.
15. A function or expression on an indexed column disables the index: `WHERE lower(email) = $1` skips the index on `email`. Either create the expression index — `CREATE INDEX ON users (lower(email))` and use the byte-identical expression in queries — or move the function to the other side: `WHERE created_at >= now() - interval '1 day'`, not `WHERE age(created_at) < interval '1 day'`. Same trap: `WHERE date(created_at) = '2026-07-26'` kills the index on `created_at` — write the half-open range (#6) instead. MySQL/Oracle: implicit casts count too — comparing a string column to a numeric literal silently casts the column and kills the index (Postgres raises a type error instead of silently casting).
16. SHOULD design hot queries for index-only scans: `CREATE INDEX ON orders (customer_id) INCLUDE (total, status)` (Postgres 11+) lets the query answer from the index without heap visits. Verify with EXPLAIN: "Index Only Scan" with low "Heap Fetches" (high Heap Fetches means the visibility map is stale — VACUUM).
17. Indexes can satisfy ORDER BY and eliminate Sort nodes — `ORDER BY created_at DESC LIMIT 20` with an index on `(user_id, created_at DESC)` reads 20 rows and stops. Direction and NULLS FIRST/LAST must match the index or a full sort happens anyway.
18. Every index MUST be justified by a named query. Each additional index slows every INSERT and (non-HOT) UPDATE and consumes cache. Drop unused indexes: `pg_stat_user_indexes` where `idx_scan = 0` (and not enforcing a constraint). Duplicate-prefix indexes — `(a)` alongside `(a, b)` — the single-column one is almost always redundant.
19. SHOULD use partial indexes for skewed predicates: `CREATE INDEX ON jobs (run_at) WHERE state = 'pending'` stays tiny on a table where 99% of rows are terminal-state.

## Rules — Normalization

20. MUST design to 3NF by default: every non-key column depends on the key, the whole key, and nothing but the key. Repeating column groups (`phone1`, `phone2`), CSV-in-a-column, and cross-row copy-paste of the same fact are 1NF/3NF violations, full stop.
21. MAY denormalize only with a measured read-path justification: the specific query, its current latency, and why an index or materialized view can't fix it. Document at the column: `COMMENT ON COLUMN orders.customer_name IS 'denormalized from customers; sync via trigger; justified by <link>'`. An unexplained duplicate column in review is a defect.

## Rules — Migrations (expand-and-contract)

22. MUST NOT rename or retype a live column/table in place — deploys are not atomic with DDL, so old code breaks the instant the rename lands. Sequence: **expand** (add new nullable column) → **backfill** in batches → dual-write or switch reads behind a flag → **enforce** (NOT NULL / constraints) → **contract** (drop old column, releases later).
23. MUST use `CREATE INDEX CONCURRENTLY` on live Postgres tables (plain CREATE INDEX blocks writes). It cannot run inside a transaction — most migration frameworks need `disable_ddl_transaction!` or equivalent. If it fails it leaves an INVALID index: drop it, fix, retry. MySQL 8.0: `ALTER TABLE ... ADD INDEX ..., ALGORITHM=INPLACE, LOCK=NONE`; `ADD COLUMN` is `ALGORITHM=INSTANT` since 8.0.12.
24. MUST NOT run `ALTER TABLE ... SET NOT NULL` cold on a big table (full-table scan under ACCESS EXCLUSIVE). Postgres: `ADD CONSTRAINT c CHECK (col IS NOT NULL) NOT VALID` (instant) → `VALIDATE CONSTRAINT c` (weak lock, scans online) → `SET NOT NULL` (instant on PG 12+, proven by the check) → drop the check. Same NOT VALID → VALIDATE pattern for adding foreign keys.
25. MUST backfill in batches keyed by PK range (1,000–10,000 rows per statement, short sleep between), never one giant `UPDATE table SET ...` — that takes long row locks, bloats the table, and can wedge replication.
26. MUST set `lock_timeout` (e.g. `SET lock_timeout = '5s'`) before DDL on live tables. Even an "instant" ALTER queues behind long-running queries while blocking everything behind it.
27. MUST follow: write plan → run against a production-copy/staging → verified backup exists → apply → verify (row counts, constraint state, application errors). Use assets/migration-checklist.md as the PR checklist.

## Rules — Queries & N+1

28. Detect N+1 by the log shape: one parent query followed by the same child statement repeated once per parent row, differing only in the bound id. Enable statement logging (or your ORM's query counter) in dev and fail tests that exceed a query budget.
29. Fix N+1 by shape:

| Situation | Fix |
|---|---|
| Child is 1:1 or N:1 (order → customer) | JOIN in the parent query |
| Child is 1:N (customer → orders), JOIN would duplicate parents | One batch query: `WHERE parent_id = ANY($1)`, group in app code |
| Resolver/loader architecture (GraphQL, per-field access) | Dataloader: coalesce ids per tick into one `= ANY($1)` query, cache per-request |
| ORM | Eager load: Rails `includes`, Django `select_related`/`prefetch_related`, SQLAlchemy `selectinload`/`joinedload` |

30. SHOULD select only needed columns. `SELECT *` blocks index-only scans (rule #16), drags TOASTed/large columns over the wire, and breaks when columns are dropped mid expand-contract.

## Rules — Reading EXPLAIN

31. MUST use `EXPLAIN (ANALYZE, BUFFERS)` (MySQL 8.0.18+: `EXPLAIN ANALYZE`) for real diagnosis — plain EXPLAIN shows estimates only. Wrap in `BEGIN; ... ROLLBACK;` when ANALYZE-ing writes.
32. Plan-symptom table:

| Symptom | Diagnosis | Fix |
|---|---|---|
| Seq Scan on big table, "Rows Removed by Filter" huge | Missing or unusable index | Add index matching the predicate; check rules #13–#15 |
| Nested Loop whose inner side is scanned thousands of times | Missing index on the join key | Index the join column (often rule #12) |
| `rows=` estimate off from `actual rows` by 10x+ | Stale planner statistics | `ANALYZE <table>`; for skewed columns raise the stats target |
| Index Only Scan with high "Heap Fetches" | Stale visibility map | `VACUUM <table>` |
| Sort node feeding a LIMIT | Index could provide the order | Composite index matching ORDER BY (rule #17) |
| High `shared read` in BUFFERS | Working set doesn't fit cache | Smaller index / covering index / more selective predicate |

33. When estimates are wildly wrong, fix statistics BEFORE adding indexes — the planner may already have the right index and be choosing badly on bad numbers.

## Gotchas

- Postgres `ADD COLUMN ... DEFAULT <constant>` is instant (PG 11+); a **volatile** default (`now()` is fine — it's stable per-statement; `random()`/`uuidv7()` is not) rewrites the whole table.
- MySQL `utf8` is the broken 3-byte alias — always `utf8mb4`.
- `count(col)` skips NULLs; `count(*)` is what you almost always mean and is not slower.
- Dropping a column in Postgres is instant but doesn't reclaim space until rewrite/VACUUM FULL — fine, just don't panic at table size.
- An index on `(a, b)` serves `WHERE a = ?` but NOT `WHERE b = ?` alone.

## What NOT to do

- No `varchar(255)` cargo-culting (#1), no `timestamp` without tz (#2), no `money`/float currency (#3), no `serial` (#8).
- No UUIDv4 clustered PKs (#10), no natural-key PKs (#11), no unindexed FKs (#12).
- No in-place renames on live tables (#22), no unbatched backfills (#25), no `CREATE INDEX` without CONCURRENTLY on live Postgres tables (#23).
- No index without a query that justifies it (#18).