Scalable Data Schema Design

Design database schemas that grow with your application—from prototype to millions of users.

When to Use

Use this skill when:

• Starting a new project and designing initial data models

• Existing schema is causing performance issues

• Planning to scale from thousands to millions of records

• Migrating between database systems

• Data model needs to evolve without breaking changes

Core Principles

1. Start Simple, Plan for Complex

Prototype Phase (< 10K records):

• Optimize for development speed

• Denormalize for convenience

• Use simple indexes

• Single database instance

Growth Phase (10K - 1M records):

• Normalize to reduce redundancy

• Add strategic indexes

• Introduce read replicas

• Monitor query performance

Scale Phase (> 1M records):

• Partition large tables

• Shard by logical boundaries

• Add caching layers

• Consider polyglot persistence

2. Query Patterns Drive Schema

Anti-pattern: Design schema, then write queries Best practice: Identify queries, then design schema

Workflow:

1. List top 10 most frequent queries

2. List top 5 most expensive queries

3. Design indexes to support both

4. Benchmark with realistic data volumes

5. Plan for Evolution

Schema versioning strategy:

• Additive changes only (new columns, new tables)

• Deprecate old fields instead of deleting

• Use database migration tools (Flyway, Liquibase, Alembic)

• Version your schema in git

Design Patterns

Pattern 1: Event Sourcing for Audit Trails

Use when: You need complete history of changes

-- Anti-pattern: Update in place UPDATE users SET email = 'new@example.com' WHERE id = 123; -- Lost: Previous email, who changed it, when

-- Better: Event log CREATE TABLE user_events ( id BIGSERIAL PRIMARY KEY, user_id BIGINT NOT NULL, event_type VARCHAR(50) NOT NULL, -- 'email_changed', 'profile_updated' event_data JSONB NOT NULL, -- {old: 'old@...', new: 'new@...'} created_at TIMESTAMPTZ DEFAULT NOW(), created_by BIGINT -- Who made the change );

CREATE INDEX idx_user_events_user_id ON user_events(user_id); CREATE INDEX idx_user_events_created_at ON user_events(created_at DESC);

-- Current state is derived from events CREATE VIEW current_users AS SELECT DISTINCT ON (user_id) user_id, event_data->>'email' as email, created_at as last_updated FROM user_events WHERE event_type = 'email_changed' ORDER BY user_id, created_at DESC;

Trade-offs:

• ✓ Complete audit trail

• ✓ Time travel queries

• ✗ More storage

• ✗ Queries more complex

Pattern 2: Soft Deletes for Recovery

Use when: Data is valuable or deletion is rare

-- Basic soft delete CREATE TABLE products ( id BIGSERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, price DECIMAL(10,2), deleted_at TIMESTAMPTZ, -- NULL = active, NOT NULL = deleted deleted_by BIGINT -- Who deleted it );

-- Index for active records only (partial index) CREATE INDEX idx_products_active ON products(id) WHERE deleted_at IS NULL;

-- Queries SELECT * FROM products WHERE deleted_at IS NULL; -- Active only SELECT * FROM products WHERE deleted_at IS NOT NULL; -- Deleted only

Trade-offs:

• ✓ Easy recovery

• ✓ Audit compliance

• ✗ Every query needs WHERE deleted_at IS NULL

• ✗ Unique constraints more complex

Pattern 3: Polymorphic Associations

Use when: Multiple entity types share a relationship

-- Anti-pattern: Nullable foreign keys CREATE TABLE comments ( id BIGSERIAL PRIMARY KEY, text TEXT NOT NULL, post_id BIGINT, -- Comment on post photo_id BIGINT, -- OR comment on photo video_id BIGINT, -- OR comment on video -- Only one should be set, but DB can't enforce this created_at TIMESTAMPTZ DEFAULT NOW() );

-- Better: Polymorphic with type CREATE TABLE comments ( id BIGSERIAL PRIMARY KEY, text TEXT NOT NULL, commentable_type VARCHAR(50) NOT NULL, -- 'Post', 'Photo', 'Video' commentable_id BIGINT NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW(),

-- Ensure only valid types CONSTRAINT valid_commentable_type CHECK (commentable_type IN ('Post', 'Photo', 'Video')) );

CREATE INDEX idx_comments_commentable ON comments(commentable_type, commentable_id);

-- Query SELECT * FROM comments WHERE commentable_type = 'Post' AND commentable_id = 123;

Trade-offs:

• ✓ Flexible, extensible

• ✗ Can't use foreign key constraints

• ✗ Joins more complex

When to avoid: Use separate tables if types have very different fields.

Pattern 4: Hierarchical Data (Adjacency List vs Nested Sets)

Adjacency List (simple, common):

CREATE TABLE categories ( id BIGSERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, parent_id BIGINT REFERENCES categories(id), created_at TIMESTAMPTZ DEFAULT NOW() );

CREATE INDEX idx_categories_parent ON categories(parent_id);

-- Get immediate children (fast) SELECT * FROM categories WHERE parent_id = 5;

-- Get all descendants (requires recursive CTE - slower) WITH RECURSIVE subcategories AS ( SELECT * FROM categories WHERE id = 5 UNION ALL SELECT c.* FROM categories c JOIN subcategories s ON c.parent_id = s.id ) SELECT * FROM subcategories;

Nested Sets (fast reads, slow writes):

CREATE TABLE categories ( id BIGSERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, lft INT NOT NULL, -- Left boundary rgt INT NOT NULL, -- Right boundary created_at TIMESTAMPTZ DEFAULT NOW() );

CREATE INDEX idx_categories_lft_rgt ON categories(lft, rgt);

-- Get all descendants (fast!) SELECT * FROM categories WHERE lft > 10 AND rgt < 20;

-- Get path to root (fast!) SELECT * FROM categories c1 JOIN categories c2 ON c1.lft BETWEEN c2.lft AND c2.rgt WHERE c1.id = 15 ORDER BY c2.lft;

Choose:

• Adjacency List: Frequent updates, shallow hierarchies

• Nested Sets: Mostly reads, deep hierarchies

• Materialized Path: Mix of both (store path as string: /1/5/12/ )

Pattern 5: Time-Series Data (Partitioning)

Use when: Data grows continuously over time

-- Basic time-series table CREATE TABLE metrics ( id BIGSERIAL, metric_name VARCHAR(100) NOT NULL, value DOUBLE PRECISION NOT NULL, recorded_at TIMESTAMPTZ NOT NULL, tags JSONB, PRIMARY KEY (id, recorded_at) -- Include partition key ) PARTITION BY RANGE (recorded_at);

-- Create partitions (monthly) CREATE TABLE metrics_2026_01 PARTITION OF metrics FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE metrics_2026_02 PARTITION OF metrics FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

-- Index on each partition (automatically created on partition key) CREATE INDEX idx_metrics_2026_01_name ON metrics_2026_01(metric_name); CREATE INDEX idx_metrics_2026_02_name ON metrics_2026_02(metric_name);

-- Queries automatically use correct partition SELECT AVG(value) FROM metrics WHERE recorded_at BETWEEN '2026-01-15' AND '2026-01-20' AND metric_name = 'cpu_usage';

Benefits:

• ✓ Query only relevant partitions (partition pruning)

• ✓ Drop old partitions easily (instant delete)

• ✓ Parallel queries across partitions

• ✓ Manage storage per partition

Indexing Strategy

When to Index

Index when:

• Column used in WHERE clause frequently

• Column used in JOIN conditions

• Column used in ORDER BY

• Foreign keys (even if DB doesn't auto-create)

Don't index when:

• Table is small (< 1000 rows)

• Column has low cardinality (few distinct values)

• Column is updated frequently

• Index would be larger than table

Index Types

B-Tree (default, most common):

CREATE INDEX idx_users_email ON users(email); -- Use for: =, <, >, <=, >=, BETWEEN, IN, LIKE 'prefix%'

Hash (exact matches only):

CREATE INDEX idx_users_email ON users USING HASH(email); -- Use for: = only (faster than B-Tree for equality)

GIN (Generalized Inverted Index - for arrays/JSONB):

CREATE INDEX idx_products_tags ON products USING GIN(tags); -- Use for: JSONB @>, arrays && or @> operators

Partial Index (index subset of rows):

CREATE INDEX idx_active_users ON users(email) WHERE deleted_at IS NULL; -- Smaller, faster for common queries

Composite Index (multiple columns):

CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC); -- Order matters! Works for: -- WHERE user_id = X -- WHERE user_id = X ORDER BY created_at DESC -- Doesn't help: -- WHERE created_at = Y (user_id not in query)

Index Maintenance

-- Find unused indexes (PostgreSQL) SELECT schemaname, tablename, indexname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0 AND indexname NOT LIKE '%_pkey';

-- Find duplicate indexes SELECT tablename, array_agg(indexname) FROM pg_indexes GROUP BY tablename, indexdef HAVING COUNT(*) > 1;

-- Rebuild bloated indexes REINDEX INDEX CONCURRENTLY idx_users_email;

Schema Evolution

Additive-Only Migrations

Safe (zero downtime):

-- Add new column with default ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Add new table CREATE TABLE user_preferences (...);

-- Add new index concurrently (PostgreSQL) CREATE INDEX CONCURRENTLY idx_users_created ON users(created_at);

Risky (requires downtime or careful planning):

-- Drop column (breaks old code) ALTER TABLE users DROP COLUMN old_field;

-- Rename column (breaks old code) ALTER TABLE users RENAME COLUMN old_name TO new_name;

-- Change column type (may lock table) ALTER TABLE users ALTER COLUMN age TYPE BIGINT;

Safe Migration Pattern

3-Step Deploy:

-- Step 1: Add new column (deploy, code ignores it) ALTER TABLE users ADD COLUMN email_new VARCHAR(255);

-- Step 2: Backfill + dual write (deploy, code writes both) UPDATE users SET email_new = email WHERE email_new IS NULL; -- App code now writes to both email and email_new

-- Step 3: Switch over (deploy, code uses only email_new) ALTER TABLE users DROP COLUMN email; ALTER TABLE users RENAME COLUMN email_new TO email;

Versioned Schema

-- Track migrations CREATE TABLE schema_migrations ( version VARCHAR(50) PRIMARY KEY, applied_at TIMESTAMPTZ DEFAULT NOW() );

-- Example migration file: 20260121_add_user_phone.sql BEGIN;

ALTER TABLE users ADD COLUMN phone VARCHAR(20);

INSERT INTO schema_migrations (version) VALUES ('20260121_add_user_phone');

COMMIT;

Database Selection Guide

SQL (Relational)

PostgreSQL:

• ✓ Best for: Complex queries, ACID transactions, JSONB

• ✓ Advanced features: Full-text search, geospatial (PostGIS)

• Use when: You need strong consistency and rich query capabilities

MySQL/MariaDB:

• ✓ Best for: Read-heavy workloads, replication

• ✓ Widely supported, mature ecosystem

• Use when: You need simple replication or hosting constraints

SQLite:

• ✓ Best for: Embedded databases, single-user apps

• Use when: No separate DB server needed

NoSQL

MongoDB (Document):

• ✓ Best for: Flexible schema, rapid iteration

• ✓ Schema-less (actually schema-on-read)

• Use when: Schema changes frequently, hierarchical data

Redis (Key-Value):

• ✓ Best for: Caching, sessions, pub/sub

• Use when: Ultra-fast reads, temporary data

DynamoDB (Key-Value):

• ✓ Best for: Serverless, predictable performance

• Use when: AWS ecosystem, pay-per-request pricing

Cassandra (Wide-Column):

• ✓ Best for: Time-series, write-heavy workloads

• Use when: Multi-datacenter, massive scale

Decision Matrix

Use Case Database Why

User accounts, transactions PostgreSQL ACID, relations

Product catalog (e-commerce) PostgreSQL Complex queries, inventory

Session storage Redis Fast, ephemeral

Activity feed Cassandra Write-heavy, time-series

Analytics events ClickHouse Columnar, OLAP

Document storage MongoDB Flexible schema

Geospatial queries PostgreSQL + PostGIS Best geo support

Performance Optimization

Query Analysis

-- Analyze query plan (PostgreSQL) EXPLAIN ANALYZE SELECT u.name, COUNT(o.id) as order_count FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE u.created_at > '2026-01-01' GROUP BY u.id, u.name;

-- Look for: -- "Seq Scan" on large tables → Add index -- "Hash Join" instead of "Nested Loop" → May need different index -- High "actual time" → Slow query

Common Fixes

Problem: Slow JOIN on large tables

-- Before SELECT * FROM orders o JOIN users u ON o.user_id = u.id WHERE o.status = 'pending';

-- Fix: Index foreign key CREATE INDEX idx_orders_user_id ON orders(user_id); CREATE INDEX idx_orders_status ON orders(status);

Problem: Slow COUNT(*) on large table

-- Before (scans entire table) SELECT COUNT(*) FROM orders;

-- Fix: Use estimate for large tables SELECT reltuples::BIGINT AS estimate FROM pg_class WHERE relname = 'orders';

-- Or: Maintain counter CREATE TABLE table_stats ( table_name VARCHAR(50) PRIMARY KEY, row_count BIGINT, updated_at TIMESTAMPTZ );

Problem: N+1 queries

-- Before (1 query + N queries for each user) users = query("SELECT * FROM users LIMIT 10") for user in users: orders = query("SELECT * FROM orders WHERE user_id = ?", user.id)

-- Fix: Eager load with JOIN or IN SELECT u., o. FROM users u LEFT JOIN orders o ON u.id = o.user_id LIMIT 10;

-- Or use IN for large sets user_ids = [1, 2, 3, ...] SELECT * FROM orders WHERE user_id IN (?, ?, ?)

Anti-Patterns to Avoid

Anti-Pattern Why It's Bad Better Approach

ENUM in column type Hard to change Lookup table with foreign key

Storing arrays as strings Can't query efficiently Array column or join table

GUID/UUID as primary key Fragmented indexes, 16 bytes BIGSERIAL (8 bytes, sequential)

Premature optimization Complexity without benefit Start simple, optimize when needed

Missing foreign keys Data integrity issues Always use FK constraints

No created_at/updated_at Can't audit or debug Add to all tables

Over-normalization Too many JOINs Denormalize for read patterns

Under-normalization Data duplication, update anomalies Normalize until 3NF, then denormalize strategically

Output Format

When helping with schema design:

Schema Analysis

Current Pain Points

• [Specific issue 1]
• [Specific issue 2]

Recommended Schema

[SQL DDL for new tables/changes]

Indexes

[Recommended indexes with rationale]

Migration Strategy

[Step-by-step migration plan]

Trade-offs

• ✓ Advantages
• ✗ Disadvantages

Expected Performance Impact

• Reads: [faster/slower, by how much]
• Writes: [faster/slower, by how much]
• Storage: [increase/decrease]

Integration

Works with:

• database-schema - Initial schema design

• api-design - Align schema with API needs

• systems-decompose - Feature-driven schema design

• data-infrastructure-at-scale - Infrastructure for scaled schemas