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

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();