Database Design

Database Design

Design-time decisions for relational schemas: how to model data, where to denormalize, which index to reach for, and how to evolve a live schema without downtime. Examples target PostgreSQL 18 (the current stable major branch as of Jun 2026; PG 19 is in beta, ~Sep 2026 — verify at postgresql.org/support/versioning). The migration/locking semantics below hold for PG 12+.

> For the operational deep dives — EXPLAIN ANALYZE internals, partitioning automation, pgvector, PgBouncer tuning, replication, backup/PITR runbooks, config tuning — see the sibling skill postgres-mastery. This skill covers the modeling and migration design that comes before those.

Schema Design Patterns

Normalization Quick Reference

| Form | Rule | When to break | |------|------|---------------| | 1NF | Atomic values, no repeating groups | JSONB arrays for tags/metadata | | 2NF | No partial dependencies | Denormalized read models | | 3NF | No transitive dependencies | Caching computed fields | | BCNF | Every determinant is a candidate key | Rarely broken |

Denormalization Patterns

When to denormalize: read-heavy paths where the normalized query is provably hot (verified in pg_stat_statements), the derived value is read far more than written, and you can guarantee it stays consistent. Default to not denormalizing — a counter cache is permanent operational debt.

Counter cache done correctly. A naive +1/-1 trigger that only fires on INSERT/DELETE drifts: it misses rows that are re-parented (UPDATE of the FK), can go negative under concurrent deletes, and starts wrong if the column was added to a non-empty table. Handle all three.

-- 1. Add the column, then BACKFILL the true value (never trust DEFAULT 0 on existing rows)
ALTER TABLE posts ADD COLUMN comments_count INT NOT NULL DEFAULT 0;
UPDATE posts p
SET comments_count = sub.c
FROM (SELECT post_id, count(*) AS c FROM comments GROUP BY post_id) sub
WHERE p.id = sub.post_id;

-- 2. Trigger covering INSERT, DELETE, *and* re-parenting UPDATEs, with a non-negative floor
CREATE FUNCTION sync_comments_count() RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE posts SET comments_count = comments_count + 1 WHERE id = NEW.post_id;
  ELSIF TG_OP = 'DELETE' THEN
    -- GREATEST guards against drift sending the count below zero
    UPDATE posts SET comments_count = GREATEST(comments_count - 1, 0) WHERE id = OLD.post_id;
  ELSIF TG_OP = 'UPDATE' AND NEW.post_id IS DISTINCT FROM OLD.post_id THEN
    UPDATE posts SET comments_count = GREATEST(comments_count - 1, 0) WHERE id = OLD.post_id;
    UPDATE posts SET comments_count = comments_count + 1            WHERE id = NEW.post_id;
  END IF;
  RETURN NULL;
END; $$ LANGUAGE plpgsql;

-- AFTER trigger so the count reflects committed rows; name the UPDATE columns to skip no-op updates
CREATE TRIGGER trg_comments_count
  AFTER INSERT OR DELETE OR UPDATE OF post_id ON comments
  FOR EACH ROW EXECUTE FUNCTION sync_comments_count();

Caveats to design for:

• Concurrency / hotspotting. Every comment on a viral post serializes on the same posts row (row lock for the duration of the transaction). For very hot parents, prefer an append-only comment_events ledger summed on read, or batch-aggregate periodically, instead of a per-row trigger.
• Reconciliation. Triggers drift over time (replication edge cases, manual DELETEs, logical-replication skips). Run a scheduled job that recomputes the truth and alerts on mismatch:

``sql SELECT p.id FROM posts p WHERE p.comments_count <> (SELECT count(*) FROM comments c WHERE c.post_id = p.id); ``

• Alternative: materialized view. For dashboard-style aggregates that tolerate staleness, a MATERIALIZED VIEW with REFRESH MATERIALIZED VIEW CONCURRENTLY (requires a unique index) avoids trigger maintenance entirely.

Indexing Strategies

| Type | Use case | Example | |------|----------|---------| | B-tree | Equality, range, sorting (default) | CREATE INDEX idx_users_email ON users(email) | | GIN | JSONB, arrays, full-text search | CREATE INDEX idx_data ON items USING GIN(metadata) | | GiST | Geometric, range types, proximity | PostGIS spatial queries | | BRIN | Large sequential/time-series tables | CREATE INDEX idx_ts ON events USING BRIN(created_at) | | Composite | Multi-column queries | CREATE INDEX idx_org_status ON tickets(org_id, status) | | Partial | Subset of rows | CREATE INDEX idx_active ON users(email) WHERE active = true |

Composite index rule: Left-to-right prefix matching. Index on (a, b, c) serves queries on (a), (a, b), (a, b, c) — not (b, c). Put the most selective equality column first, then the column you range/sort on last (WHERE a = ? AND b = ? ORDER BY c → (a, b, c)).

Designing the index, not just adding one

Selectivity decides everything. An index only helps when it eliminates most rows. As a rule of thumb the planner will skip a plain B-tree if a predicate returns more than ~5–10% of the table — the index + heap fetches cost more than a scan. Check it before guessing:

SELECT attname, n_distinct, correlation
FROM pg_stats WHERE tablename = 'orders';   -- high |n_distinct| = selective = good index candidate

Covering indexes (INCLUDE). Append non-key columns so a query is served entirely from the index (Index-Only Scan, no heap lookup). Use for hot read paths:

CREATE INDEX idx_orders_user_covering ON orders (user_id, created_at DESC) INCLUDE (total, status);
-- SELECT total, status FROM orders WHERE user_id = $1 ORDER BY created_at DESC LIMIT 20;  → Index Only Scan

Expression indexes. Index the expression you actually filter on, or the planner can't use the index:

CREATE INDEX idx_users_lower_email ON users (lower(email));     -- WHERE lower(email) = lower($1)
CREATE INDEX idx_events_day ON events (date_trunc('day', created_at));

Operator classes tune an index for a specific operator. The big wins:

• text_pattern_ops — lets a B-tree serve LIKE 'prefix%': CREATE INDEX ON users (email text_pattern_ops);
• jsonb_path_ops — smaller, faster GIN index when you only use the @> containment operator: CREATE INDEX ON events USING gin (metadata jsonb_path_ops);

Find missing and unused indexes (design feedback loop — make this part of every schema review):

-- Enable once: shared_preload_libraries = 'pg_stat_statements' in postgresql.conf
-- Hottest queries by total time — these are your indexing targets
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 20;

-- Unused indexes (idx_scan = 0) — dead weight on every write; drop them
SELECT relname AS table, indexrelname AS index, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0 AND NOT indisunique
ORDER BY pg_relation_size(indexrelid) DESC;

Every index is a write-time tax: each INSERT/UPDATE must maintain it, and it consumes cache/WAL. Index for the queries you actually run, then prune. For GIN/GiST/BRIN/pgvector internals and EXPLAIN ANALYZE plan-reading, see postgres-mastery.

Query Optimization

-- Always start here
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;

Key indicators in query plans:

• Seq Scan on a large table where the predicate is selective → likely a missing/unusable index. But a Seq Scan is often the correct plan — for low-selectivity predicates (returning a large fraction of rows), small fully-cached tables, or when a Parallel Seq Scan beats random index I/O. Don't add an index just because you see "Seq Scan"; only when actual rows ≪ table size and the scan is hot.
• Nested Loop with high outer-row counts and a high-cost inner side → consider a Hash Join (usually a stats/ANALYZE problem, or a missing index on the inner join key).
• Rows Removed by Filter ≫ actual rows → the index (or scan) isn't selective enough; the work to discard rows dominates.
• Estimated rows wildly off from actual (e.g. estimates 10, gets 100k) → stale stats; run ANALYZE, or raise default_statistics_target / add extended statistics (CREATE STATISTICS) for correlated columns.
• High Buffers: shared read (vs shared hit) → data not cached; check shared_buffers and working-set size.

N+1 Detection and Fixes

The N+1 pattern (1 query for the list + N queries for each row's relation) is the #1 ORM performance bug. Detect it by logging SQL and watching for a repeated parameterized query: in Prisma 7 set new PrismaClient({ log: ['query'] }); in Drizzle pass a logger: true to the client. Each fires once per row.

// Prisma 7.x (the production-recommended line as of Jun 2026; "Prisma Next", the all-TypeScript
// rewrite, is still pre-release — verify the current line at prisma.io/docs)
// BAD: N+1 — one query per user
const users = await prisma.user.findMany();
for (const u of users) {
  const posts = await prisma.post.findMany({ where: { authorId: u.id } }); // N queries
}

// GOOD: eager load the relation in one round trip
const users = await prisma.user.findMany({ include: { posts: true } });

// CAVEAT: Prisma `include` does NOT emit a SQL JOIN by default — it issues a second batched
// `WHERE authorId IN (...)` query (the default "query" join strategy). That's fine and avoids
// row fan-out. If you specifically want a single JOIN, opt in per-query:
const users2 = await prisma.user.findMany({
  relationLoadStrategy: 'join',          // emit one LATERAL JOIN instead of two queries
  include: { posts: true },
});
// Also: `select` only the columns you need — `include` pulls every column of the relation.

// Drizzle (drizzle-orm 0.45.x stable as of Jun 2026; a 1.0 beta is in flight, so pin your
// version and check the relational-query API in the v1 migration notes). Two idioms — pick by shape:

// (a) Relational query API — batches like Prisma's default, returns a nested object graph:
const usersWithPosts = await db.query.users.findMany({
  with: { posts: { columns: { id: true, title: true } } },  // select only needed columns
});

// (b) Explicit JOIN — one round trip, but FLAT rows with the parent repeated per child.
// You must de-duplicate/group in app code, and a LEFT JOIN multiplies parent rows by child count:
const rows = await db
  .select({ userId: users.id, postId: posts.id })
  .from(users)
  .leftJoin(posts, eq(users.id, posts.authorId));

Rule of thumb: a JOIN (idiom b) is one round trip but fans out parent columns across N child rows (more bytes on the wire, app-side grouping). A batched IN (...) load (Prisma default, Drizzle with) is two cheap queries with no fan-out. For a single hot endpoint, hand-write the SQL and shape it exactly; see the JOIN/pagination case studies in postgres-mastery.

Migration Workflow

Zero-Downtime Checklist (expand-migrate-contract)

Every online schema change follows the same shape: expand (add the new thing, backward-compatible), migrate (backfill + dual-write while old and new code coexist), contract (drop the old thing only after every deploy uses the new). Concretely, to add a required column:

1. Add the column as nullable, no default — instant (metadata only). Briefly takes ACCESS EXCLUSIVE, so it still queues behind long-running queries; on a busy table guard it with a short lock timeout (below).
2. Deploy app code that writes the new column (dual-write) before backfilling, so new rows are already populated.
3. Backfill existing rows in batches (see the production template below) — never one giant UPDATE.
4. Add the NOT NULL constraint safely via a validated CHECK (see recipe below) — do not run a bare SET NOT NULL on a large table.
5. Deploy app code that reads the new column.
6. Contract: drop the old column / trigger after a confirmation period.

Always set a lock timeout so a migration can't park behind a slow query and block the table:

SET lock_timeout = '3s';        -- fail fast instead of blocking all writes
SET statement_timeout = '0';    -- but allow the statement itself to run (e.g. CREATE INDEX)

# Migration file naming: YYYYMMDDHHMMSS_description.{up,down}.sql
20260101120000_add_users_role.up.sql
20260101120000_add_users_role.down.sql   # every migration ships a tested rollback

Adding a NOT NULL constraint safely

A bare ALTER TABLE ... ALTER COLUMN ... SET NOT NULL rewrites/scans the whole table under ACCESS EXCLUSIVE. Instead add a CHECK (... IS NOT NULL) NOT VALID, validate it without a write lock, then promote it:

-- Step 1: add the check WITHOUT scanning existing rows — instant
ALTER TABLE users ADD CONSTRAINT users_role_not_null CHECK (role IS NOT NULL) NOT VALID;

-- Step 2: validate existing rows — takes only SHARE UPDATE EXCLUSIVE (writes keep flowing)
ALTER TABLE users VALIDATE CONSTRAINT users_role_not_null;

-- Step 3 (PG 12+): SET NOT NULL is now nearly instant — the planner reuses the validated
-- CHECK as proof and skips the full-table scan. Then drop the redundant CHECK.
ALTER TABLE users ALTER COLUMN role SET NOT NULL;
ALTER TABLE users DROP CONSTRAINT users_role_not_null;

(On PG 11 and earlier, step 3's SET NOT NULL still scans; keep the CHECK constraint as the enforcement mechanism instead of promoting it.)

Lock levels for common DDL (PostgreSQL 12+; verified against PG 18)

| Operation | Lock taken | Blocks reads? | Blocks writes? | Notes | |-----------|-----------|---------------|----------------|-------| | ADD COLUMN (nullable, no default) | ACCESS EXCLUSIVE (brief) | momentarily | momentarily | Metadata-only; fast but still queues behind long txns | | ADD COLUMN ... DEFAULT | ACCESS EXCLUSIVE (brief) | momentarily | momentarily | PG 11+: constant default stored as metadata, no table rewrite | | ADD COLUMN ... DEFAULT (e.g. now(), a sequence) | ACCESS EXCLUSIVE (long) | yes | yes | Rewrites every row — avoid online; backfill in batches instead | | ALTER COLUMN ... SET DEFAULT | ACCESS EXCLUSIVE (brief) | momentarily | momentarily | Affects future rows only | | ALTER COLUMN ... TYPE | ACCESS EXCLUSIVE (long) | yes | yes | Full rewrite + index rebuild; use add-new-column + backfill instead | | SET NOT NULL (bare) | ACCESS EXCLUSIVE (scan) | yes | yes | Use the validated-CHECK recipe above | | ADD FOREIGN KEY | SHARE ROW EXCLUSIVE on both tables | no | yes | Add NOT VALID, then VALIDATE CONSTRAINT (SHARE UPDATE EXCLUSIVE) to avoid the write lock during the scan | | ADD CHECK ... NOT VALID | ACCESS EXCLUSIVE (brief) | momentarily | momentarily | No row scan until you VALIDATE | | VALIDATE CONSTRAINT | SHARE UPDATE EXCLUSIVE | no | no | Safe online | | CREATE INDEX (plain) | SHARE | no | yes | Blocks writes for the whole build | | CREATE INDEX CONCURRENTLY | SHARE UPDATE EXCLUSIVE | no | no | See caveats below |

> Lock-level details evolve across major versions; for the authoritative matrix verify against the "Explicit Locking" page of the PostgreSQL docs for your version (postgresql.org/docs/current/explicit-locking.html).

CREATE INDEX CONCURRENTLY — use it in production, but mind the sharp edges

-- Preferred online index build
CREATE INDEX CONCURRENTLY idx_orders_email ON orders (email);

• It cannot run inside a transaction block — so it can't go in a migration that the tool wraps in BEGIN/COMMIT. Run it outside a transaction (Prisma Migrate, Rails, etc. need an explicit "no transaction" annotation; raw scripts must not wrap it).
• On failure (including a lock timeout or a conflicting transaction) it leaves an INVALID index behind that still incurs write cost but isn't used. Detect and clean up, then retry:

```sql SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid; -- find leftovers DROP IN

…

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: san-npm
• Source: san-npm/skills-ws
• License: MIT
• Homepage: https://skills-ws.vercel.app

Install and usage instructions live in the source repository linked above.