Database Schema Design

Schema mistakes echo for years. A denormalized table chosen for convenience becomes a bottleneck. A missing index slows queries by 100x. Your schema is your foundation. Build it carefully.

Normalization vs Denormalization

Start normalized. Only denormalize after measuring and confirming performance problems:

-- Normalized (start here)
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  user_id INT NOT NULL REFERENCES users(id),
  title VARCHAR(256) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Only denormalize if measurement shows a problem
-- For example: SELECT users.*, COUNT(posts.id) FROM users...
-- becomes slow. Then add post_count to users table.
-- But maintain it with a trigger to prevent inconsistency.

Indexing Strategy

Index what you query, not everything. Every index has a cost: slower writes, more storage. Index deliberately:

-- Primary key index (automatic)
CREATE TABLE users (
  id SERIAL PRIMARY KEY
);

-- Unique constraint index
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Foreign key index (frequent joins)
CREATE INDEX idx_posts_user_id ON posts(user_id);

-- Composite index for common queries
-- "SELECT * FROM orders WHERE user_id = ? AND created_at > ?"
CREATE INDEX idx_orders_user_date 
  ON orders(user_id, created_at DESC);

-- Measure before and after
EXPLAIN ANALYZE SELECT * FROM posts WHERE user_id = 1;

Finding Slow Queries

Enable query logging. Find problems before production:

-- PostgreSQL: Enable slow query log
ALTER SYSTEM SET log_min_duration_statement = 1000; -- 1 second

-- Then restart
SELECT pg_reload_conf();

-- Query the log
SELECT * FROM pg_stat_statements 
ORDER BY total_time DESC 
LIMIT 10;

Common Query Patterns

Optimize for your access patterns. Different patterns need different structures:

-- Pattern 1: Frequent point lookups
-- Solution: Primary key + unique constraints
SELECT * FROM users WHERE id = 1;

-- Pattern 2: Range queries
-- Solution: B-tree index on range column
SELECT * FROM posts WHERE created_at > NOW() - INTERVAL '7 days';

-- Pattern 3: Text search
-- Solution: GIN index for full-text search
CREATE INDEX idx_posts_search ON posts USING GIN(
  to_tsvector('english', title || ' ' || content)
);

-- Pattern 4: Aggregations
-- Solution: Materialized views or denormalization
CREATE MATERIALIZED VIEW user_stats AS
SELECT user_id, COUNT(*) as post_count, MAX(created_at) as last_post
FROM posts
GROUP BY user_id;

Real-World Incident

A system I worked on had a slow reporting feature. Every query took 2+ seconds. Investigation revealed the schema had timestamps stored incorrectly, queries couldn't use indexes, and all 10 million rows were scanned. The fix:

• Fix timestamp type: Use TIMESTAMP, not TEXT
• Add index: Index the timestamp column used in WHERE clause
• Query rewrite: Use date ranges instead of string matching

Result: Query dropped from 2 seconds to 50ms. Index a single column. 2000% improvement. This is why schema design matters.

Why This Topic Matters in Production

Architecture decisions become expensive only after the system succeeds. That is why unclear boundaries, implicit contracts, and mixed responsibilities feel acceptable early and painful later.

Most architecture failures are not caused by one bad decision. They are caused by many unowned assumptions that slowly become coupling: implicit contracts, hidden side effects, and unclear module boundaries. Teams feel productive until change frequency increases, then every release carries disproportionate risk.

In production, architecture quality is observed through behavior under stress: whether incidents are diagnosable, whether rollbacks are safe, and whether one subsystem failure is contained or amplified. Good architecture is less about abstract diagrams and more about preserving predictable change as systems and teams grow.

Core Concepts

Boundary quality matters more than component count. A smaller number of explicit boundaries beats many loosely defined layers.

Contract-first thinking prevents drift: schema, invariants, and error semantics should be defined before implementation details.

Ownership is an architecture primitive. Unowned modules become long-term reliability risks.

High-churn logic should be isolated from critical execution paths to limit blast radius.

• Define explicit module ownership so each boundary has one clear maintainer.
• Model contracts as first-class artifacts: request schema, response schema, and failure semantics.
• Keep high-churn code isolated from foundational platform paths.
• Prefer deterministic behavior over clever abstraction in critical request paths.

Real-World Mistakes

Optimizing for local code elegance while ignoring cross-service coupling.

Treating architecture docs as static artifacts instead of living decision records.

Allowing transport concerns to leak into core domain services.

Skipping backward-compatibility planning for internal interfaces.

• Embedding domain rules in adapters and transport handlers.
• Using shared utility files as hidden dependency hubs.
• Relying on convention-only contracts without automated validation.
• Skipping architecture review for seemingly small service changes.

Recommended Patterns

Use architectural decision records with explicit context, alternatives, and rollback conditions.

Run boundary reviews for high-impact changes before implementation begins.

Enforce schema validation and invariant checks at every system edge.

Instrument boundary latency and error classes to detect structural degradation early.

• Use service interfaces for domain operations and keep route handlers thin.
• Keep architecture decision records for high-impact design trade-offs.
• Enforce schema validation at ingress and invariant checks in domain services.
• Instrument boundaries with request IDs to make call flow traceable.

Implementation Checklist

• Define ownership for every critical module and service boundary.
• Version and validate contracts at ingress and integration points.
• Measure p95/p99 latency and error rates by architectural boundary.
• Document rollback strategies for high-risk structural changes.

Architecture Notes

Boundary-first architecture scales better than framework-first architecture because it keeps design intent stable while implementation details evolve.

Teams should review architecture through incident history: repeated failure patterns usually reveal structural coupling rather than isolated bugs.

A practical litmus test: if rollback decisions require cross-team emergency synchronization, your boundaries are too entangled.

Applied Example

Boundary-Safe Service Contract

type CreateOrderInput = {
  customerId: string;
  items: Array<{ sku: string; quantity: number }>;
};

type CreateOrderResult =
  | { ok: true; orderId: string }
  | { ok: false; code: "VALIDATION" | "FORBIDDEN" | "DEPENDENCY"; message: string };

export async function createOrder(input: CreateOrderInput): Promise<CreateOrderResult> {
  // transport validation should happen before this boundary
  if (!input.customerId || input.items.length === 0) {
    return { ok: false, code: "VALIDATION", message: "Invalid order payload" };
  }

  // domain + dependency orchestration here
  return { ok: true, orderId: crypto.randomUUID() };
}

Trade-offs

Explicit layering increases initial implementation cost but reduces long-term debugging cost.

Strict ownership can slow ad hoc changes while improving accountability and operational quality.

Contract rigor adds ceremony but dramatically lowers integration failure rates.

• Layered design increases initial wiring cost but lowers long-term regression risk.
• Strict boundaries can slow prototyping but materially improve maintainability.
• Explicit contracts require discipline yet reduce integration breakage between teams.

Production Perspective

Reliability improves when failure modes are classified and routed to explicit recovery paths.

Security posture improves when policy checks are centralized rather than scattered.

Performance tuning gets easier when latency can be attributed to a specific boundary.

Maintainability compounds when architecture encodes intent and ownership clearly.

• Reliability improves when dependency failures are classified rather than treated as a generic 500.
• Security posture improves when auth and policy are separated from business rules.
• Performance work becomes predictable when latency budgets are applied per boundary.
• Maintainability compounds when architecture encodes ownership and review expectations.

Final Takeaway

Strong architecture is not about complexity. It is about reducing ambiguity under pressure so systems remain understandable, debuggable, and safe to change.

Architecture should optimize for safe change, not only for initial delivery speed.

If your system is easy to reason about during incidents, your architecture is working.