Data Modeling in Databases for System Design Interviews and Production Systems

A Complete Guide for Senior Engineers

---

Preface

---

Part I: Foundations of Data Modeling

Chapter 1: The Two Worlds of Data Modeling

Logical Modeling: The World of Business Meaning

Physical Modeling: The World of Performance

• A normalized table in PostgreSQL for a system that does complex queries and transactional updates
• A denormalized document in MongoDB for a system that reads entire customer profiles frequently
• A wide table in Cassandra for a system that needs massive write throughput across regions
• A compressed columnar format in ClickHouse for analytics that aggregates millions of customers

The Bridge Between Worlds

Chapter 2: OLTP vs OLAP - The Great Divide

OLTP: The World of Transactions

OLAP: The World of Analysis

The Disaster of Mixing Workloads

Modern Convergence: HTAP Systems

Chapter 3: Row-Store vs Column-Store

Row Storage: How Traditional Databases Work

Row 1: [1, "Alice", "alice@example.com", 30, "2024-01-15"]
Row 2: [2, "Bob", "bob@example.com", 25, "2024-01-16"]
Row 3: [3, "Carol", "carol@example.com", 35, "2024-01-17"]

The Problem: Analytical Queries

1. Read every row from disk
2. Extract only the age column from each row
3. Compute the average

Column Storage: The Analytical Solution

Column "id": [1, 2, 3, ...]
Column "name": ["Alice", "Bob", "Carol", ...]
Column "email": ["alice@example.com", "bob@example.com", ...]
Column "age": [30, 25, 35, ...]
Column "created_at": ["2024-01-15", "2024-01-16", "2024-01-17", ...]

The Trade-off

• Point queries (get one row)
• Small range queries (get a few hundred rows)
• Mixed read/write workloads
• Updates to individual rows

• Full table scans
• Aggregations across many rows
• Queries that access few columns
• Compression of large datasets

• Point queries (must reconstruct row from multiple column files)
• Updates (must modify multiple column files)
• High-frequency inserts (batch loading is preferred)

A War Story: The Column Store Mistake

• Inserting individual transactions as they occurred
• Updating transaction status frequently
• Querying individual transactions by ID
• Maintaining strict consistency

---

Part II: Normalization Deep Dive

Chapter 4: The Philosophy of Normalization

What Normalization Actually Prevents

orders(order_id, customer_id, customer_name, customer_address, product, quantity)

First Normal Form: The Foundation

products(product_id, name, colors)

Where colors might contain: "red, blue, green"

• How do you find all blue products? You need string parsing: WHERE colors LIKE '%blue%'. This is slow and error-prone (it would match "lightblue" too).
• How do you add a color? String manipulation.
• How do you ensure no duplicates? Complex validation.
• How do you constrain to valid colors? Essentially impossible.

products(product_id, name)
product_colors(product_id, color)

Second Normal Form: Full Dependency on Keys

order_items(order_id, product_id, quantity, product_name, product_price)
Primary key: (order_id, product_id)

• Product names are duplicated across every order containing that product
• Updating a product name requires updating every order_items row
• You can't add a product until it appears in an order

products(product_id, product_name, product_price)
order_items(order_id, product_id, quantity)

Third Normal Form: No Transitive Dependencies

employees(employee_id, name, department_id, department_name, department_location)

• Department information is duplicated across employees
• Changing a department name requires updating all employees in that department
• You can't record a department with no employees

departments(department_id, department_name, department_location)
employees(employee_id, name, department_id)

BCNF: The Refinement

courses(student_id, course, instructor)

courses(course, instructor)
enrollments(student_id, course)

The Real Reason Normalization Matters: Consistency

Chapter 5: The Banking System Case Study

The Domain

• Accounts (checking, savings, loan)
• Account holders (customers, who may have multiple accounts)
• Transactions (deposits, withdrawals, transfers)
• Running balances
• Statements

The Naive Design (Don't Do This)

accounts(
  account_id,
  customer_name,
  customer_address,
  customer_phone,
  account_type,
  balance,
  last_transaction_date,
  last_transaction_amount,
  monthly_interest_rate,
  total_fees_ytd
)

The Normalized Design

customers(
  customer_id PRIMARY KEY,
  name,
  address,
  phone,
  date_of_birth,
  tax_id,
  created_at
)

accounts(
  account_id PRIMARY KEY,
  customer_id REFERENCES customers,
  account_type,
  currency,
  opened_at,
  closed_at,
  status
)

transactions(
  transaction_id PRIMARY KEY,
  account_id REFERENCES accounts,
  transaction_type,
  amount,
  running_balance,
  description,
  created_at,
  posted_at
)

The Ledger Principle

1. Debit checking account by $100
2. Credit savings account by $100

sql
SELECT SUM(
  CASE
    WHEN transaction_type = 'credit' THEN amount
    WHEN transaction_type = 'debit' THEN -amount
  END
) AS balance
FROM transactions
WHERE account_id = ?

The Running Balance Optimization

transaction_id | amount | running_balance
1              | 1000   | 1000
2              | -200   | 800
3              | 500    | 1300
4              | -100   | 1200

Why Not Shard?

1. Begin transaction on shard A
2. Begin transaction on shard B
3. Debit account on shard A
4. Credit account on shard B
5. Commit on shard A
6. Commit on shard B

Archiving Strategy

sql
CREATE TABLE transactions (
  -- columns
) PARTITION BY RANGE (posted_at);

CREATE TABLE transactions_2024_q1
  PARTITION OF transactions
  FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

Isolation Levels: A Brief Detour

1. Transaction A reads debit: -100
2. Transaction B inserts credit: +100
3. Transaction B commits
4. Transaction A continues, doesn't see the credit
5. Transaction A computes balance missing $100

---

Part III: Denormalization in Distributed Systems

Chapter 6: When Normalization Isn't Enough

The Read-Heavy Reality

The Join Problem at Scale

1. Fetch the post
2. Join to get author information
3. Join to get like count
4. Join to get comment count
5. Join to get the first few comments
6. For each comment, join to get commenter information

The Denormalization Solution

json
{
  "post_id": "123",
  "content": "Hello world",
  "author": {
    "user_id": "456",
    "name": "Alice",
    "avatar_url": "https://..."
  },
  "stats": {
    "like_count": 1523,
    "comment_count": 87,
    "share_count": 23
  },
  "preview_comments": [
    {
      "comment_id": "789",
      "user": {"user_id": "111", "name": "Bob"},
      "content": "Great post!",
      "created_at": "2024-01-15T10:30:00Z"
    }
  ],
  "created_at": "2024-01-15T10:00:00Z"
}

The Consistency Challenge

A Real-World Example: Facebook's Approach

• Reading your feed is fast: one or a few reads from a precomputed cache
• Posting is complex: your post must propagate to thousands of friends' feeds
• Consistency is eventual: your post might appear in friends' feeds at different times
• Storage is enormous: every user has their own feed copy

Chapter 7: Patterns of Denormalization

Precomputed Aggregates

sql
SELECT customer_id, COUNT(*)
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY customer_id;

sql
customer_monthly_stats(
  customer_id,
  year_month,
  order_count,
  total_revenue,
  updated_at
)

sql
INSERT INTO customer_monthly_stats (customer_id, year_month, order_count, total_revenue)
VALUES (?, ?, 1, ?)
ON CONFLICT (customer_id, year_month)
DO UPDATE SET
  order_count = customer_monthly_stats.order_count + 1,
  total_revenue = customer_monthly_stats.total_revenue + EXCLUDED.total_revenue,
  updated_at = NOW();

Materialized Views

sql
CREATE MATERIALIZED VIEW customer_order_summary AS
SELECT
  c.customer_id,
  c.name,
  COUNT(o.order_id) as order_count,
  SUM(o.total) as lifetime_value
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

sql
REFRESH MATERIALIZED VIEW customer_order_summary;

• Refresh can be expensive for large datasets
• Data staleness between refreshes
• Not all databases support incremental refresh

Event-Driven Projections

• Events: OrderPlaced, OrderShipped, OrderDelivered, OrderCanceled
• Projection: current_orders table showing only in-progress orders

Cache-Based Denormalization

1. Database stores normalized data
2. Application denormalizes at read time
3. Result is cached
4. Subsequent reads hit cache

python
def get_post_with_details(post_id):
    # Try cache first
    cached = redis.get(f"post:{post_id}")
    if cached:
        return json.loads(cached)

    # Build denormalized view from normalized data
    post = db.query("SELECT * FROM posts WHERE id = ?", post_id)
    author = db.query("SELECT * FROM users WHERE id = ?", post.author_id)
    stats = db.query("SELECT * FROM post_stats WHERE post_id = ?", post_id)

    result = {
        "post": post,
        "author": author,
        "stats": stats
    }

    # Cache with TTL
    redis.setex(f"post:{post_id}", 3600, json.dumps(result))

    return result

• Keeps the database normalized for write consistency
• Provides denormalized reads for performance
• Bounds staleness through cache TTL
• Allows easy cache invalidation when data changes

---

Part IV: Database Case Studies

Chapter 8: Social Media Platform

Post Schema: The Foundation

sql
posts(
  post_id BIGINT PRIMARY KEY,
  author_id BIGINT REFERENCES users,
  content TEXT,
  created_at TIMESTAMP
)

json
{
  "_id": ObjectId("..."),
  "author_id": "12345",
  "author_snapshot": {
    "name": "Alice",
    "avatar_url": "https://..."
  },
  "content": "Hello world!",
  "created_at": ISODate("2024-01-15T10:00:00Z"),
  "stats": {
    "like_count": 0,
    "comment_count": 0,
    "share_count": 0
  }
}

The Likes Challenge

sql
likes(
  like_id BIGINT PRIMARY KEY,
  post_id BIGINT REFERENCES posts,
  user_id BIGINT REFERENCES users,
  created_at TIMESTAMP,
  UNIQUE(post_id, user_id)
)

sql
UPDATE posts SET like_count = like_count + 1 WHERE post_id = ?

1. Like events go to a Kafka topic
2. A consumer aggregates counts per time window (say, every second)
3. Aggregated counts are applied to posts

Comments: The Hierarchy Problem

sql
comments(
  comment_id BIGINT PRIMARY KEY,
  post_id BIGINT REFERENCES posts,
  parent_id BIGINT REFERENCES comments,  -- NULL for top-level
  author_id BIGINT REFERENCES users,
  content TEXT,
  created_at TIMESTAMP
)

sql
SELECT * FROM comments WHERE parent_id = ?

sql
comments(
  comment_id BIGINT PRIMARY KEY,
  post_id BIGINT REFERENCES posts,
  path TEXT,  -- e.g., "1/5/12/45" meaning root=1, grandparent=5, parent=12, self=45
  depth INT,
  author_id BIGINT REFERENCES users,
  content TEXT,
  created_at TIMESTAMP
)

sql
SELECT * FROM comments
WHERE path LIKE '1/5/%'
ORDER BY path

sql
comments(
  comment_id BIGINT PRIMARY KEY,
  post_id BIGINT REFERENCES posts,
  author_id BIGINT REFERENCES users,
  content TEXT,
  created_at TIMESTAMP
)

comment_tree(
  ancestor_id BIGINT REFERENCES comments,
  descendant_id BIGINT REFERENCES comments,
  depth INT,
  PRIMARY KEY(ancestor_id, descendant_id)
)

• Insert (45, 45, 0) — self-reference
• Insert (12, 45, 1) — immediate parent
• Insert (5, 45, 2) — grandparent
• Insert (1, 45, 3) — root

sql
SELECT c.* FROM comments c
JOIN comment_tree ct ON c.comment_id = ct.descendant_id
WHERE ct.ancestor_id = 1
ORDER BY ct.depth

1. Limit comment depth to 2-3 levels. Twitter, Facebook, and Reddit all do this. Deep threading is rare, and limiting depth simplifies everything.
2. Use adjacency list with caching. With limited depth, recursive queries are manageable. Cache rendered comment threads.
3. Denormalize reply counts and preview replies into the parent comment for fast rendering.

Feed Generation: The Hardest Problem

sql
SELECT p.* FROM posts p
JOIN follows f ON p.author_id = f.followed_id
WHERE f.follower_id = ?
ORDER BY p.created_at DESC
LIMIT 50

sql
feed_items(
  user_id BIGINT,      -- whose feed
  post_id BIGINT,
  created_at TIMESTAMP,
  PRIMARY KEY(user_id, created_at DESC, post_id)
)

sql
SELECT * FROM feed_items WHERE user_id = ? ORDER BY created_at DESC LIMIT 50

• Regular users: Fan-out-on-write. Their posts are inserted into followers' feeds immediately.
• Celebrity users: Fan-out-on-read. Their posts are stored separately and merged into feeds at read time.

Handling Viral Posts

• Read hot spots on the post data
• Write hot spots from likes and comments
• Network traffic spikes to replicas
• Cache pressure

Chapter 9: Analytics Platform (OLAP)

The Star Schema

• Fact tables: Large tables recording events or measurements (page views, purchases, clicks)
• Dimension tables: Smaller tables describing attributes of the facts (users, products, dates, locations)

                    [dim_date]
                        |
[dim_user] ---- [fact_page_views] ---- [dim_page]
                        |
                    [dim_device]

Why This Structure?

sql
SELECT
  dp.page_name,
  COUNT(*) as view_count
FROM fact_page_views f
JOIN dim_user du ON f.user_key = du.user_key
JOIN dim_device dd ON f.device_key = dd.device_key
JOIN dim_date ddate ON f.date_key = ddate.date_key
JOIN dim_page dp ON f.page_key = dp.page_key
WHERE du.country = 'US'
  AND dd.device_type = 'mobile'
  AND ddate.year = 2024
  AND ddate.month = 1
GROUP BY dp.page_name
ORDER BY view_count DESC

Dimension Design

sql
dim_date(
  date_key INT PRIMARY KEY,
  full_date DATE,
  year INT,
  quarter INT,
  month INT,
  month_name VARCHAR(20),
  week INT,
  day_of_week INT,
  day_name VARCHAR(20),
  is_weekend BOOLEAN,
  is_holiday BOOLEAN
)

Slowly Changing Dimensions

sql
UPDATE dim_user SET tier = 'premium' WHERE user_id = 123

sql
dim_user(
  user_key INT PRIMARY KEY,  -- surrogate key
  user_id INT,               -- natural key (not unique)
  name VARCHAR(100),
  tier VARCHAR(20),
  valid_from DATE,
  valid_to DATE,             -- NULL for current
  is_current BOOLEAN
)

1. Update old row:
   valid_to = today, is_current = false
2. Insert new row:
   valid_from = today, valid_to = NULL, is_current = true

sql
dim_user(
  user_key INT PRIMARY KEY,
  user_id INT,
  name VARCHAR(100),
  current_tier VARCHAR(20),
  previous_tier VARCHAR(20)
)

Fact Table Design

sql
fact_page_views(
  view_id BIGINT,
  user_key INT REFERENCES dim_user,
  page_key INT REFERENCES dim_page,
  device_key INT REFERENCES dim_device,
  date_key INT REFERENCES dim_date,
  time_key INT REFERENCES dim_time,
  session_id VARCHAR(50),
  referrer VARCHAR(500),
  load_time_ms INT,
  is_bounce BOOLEAN
)

Partitioning for Analytics

sql
CREATE TABLE fact_page_views (
  -- columns
) PARTITION BY RANGE (date_key);

CREATE TABLE fact_page_views_2024_01
  PARTITION OF fact_page_views
  FOR VALUES FROM (20240101) TO (20240201);

Pre-Aggregated Rollups

sql
daily_active_users(
  date_key INT,
  platform VARCHAR(20),
  country VARCHAR(50),
  active_users INT,
  new_users INT,
  returning_users INT,
  PRIMARY KEY(date_key, platform, country)
)

sql
INSERT INTO daily_active_users
SELECT
  date_key,
  platform,
  country,
  COUNT(DISTINCT user_id) as active_users,
  ...
FROM fact_page_views
WHERE date_key = <yesterday>
GROUP BY date_key, platform, country

A Real Analytics Architecture

1. Raw event storage: All events, minimally processed. Often in object storage (S3) in compressed formats (Parquet). This is the source of truth.
2. Data warehouse: Cleaned, modeled data in a star schema. Tools like BigQuery, Snowflake, or Redshift. This is what analysts query.
3. Pre-aggregated cubes: Common aggregations precomputed. Either in the warehouse as rollup tables, or in specialized OLAP cubes.
4. Caching layer: Query results cached for dashboards. Fresh enough for business purposes (hourly, daily).

Chapter 10: Logging & Observability System

The Write Challenge

• Millions of log lines per second
• From thousands of servers
• With varying schemas (application logs, system logs, access logs)
• Requiring sub-second ingestion latency

Time-Series Data Model

logs(
  timestamp TIMESTAMP,
  source VARCHAR(100),
  level VARCHAR(20),
  message TEXT,
  metadata JSONB
)

logs_2024_01_15_00  -- Hour 0 of Jan 15, 2024
logs_2024_01_15_01  -- Hour 1 of Jan 15, 2024
...

Write Path Optimization

Query Patterns and Indexing

• Time range (almost always)
• Source/service
• Log level (errors, warnings)
• Keywords in message

sql
-- Composite index for common query pattern
CREATE INDEX idx_logs_time_source_level
ON logs (timestamp, source, level);

The Elasticsearch Model

json
{
  "@timestamp": "2024-01-15T10:30:00Z",
  "source": "api-server-01",
  "level": "ERROR",
  "message": "Connection timeout to database",
  "metadata": {
    "request_id": "abc-123",
    "user_id": "456",
    "duration_ms": 5000
  }
}

message: "timeout" AND source: "api-server-*" AND @timestamp: [now-1h TO now]

Hot-Warm-Cold Architecture

• New logs write to hot nodes
• After 24 hours, indexes move to warm nodes
• After 7 days, indexes move to cold nodes
• After 30 days, indexes are deleted or archived

TTL-Based Deletion

sql
-- Partition-based deletion
DROP TABLE logs_2023_12_15;  -- Delete logs older than 30 days

Schema-on-Read vs Schema-on-Write

• Flexible: different services can log different fields
• Agile: no schema migrations needed
• Resilient: malformed data doesn't break ingestion

• Query-time parsing overhead
• Harder to optimize queries
• Data quality issues discovered at query time

Chapter 11: E-Commerce Platform

Product Catalog: The Flexibility Challenge

sql
products(product_id, name, category_id, base_price)
product_attributes(product_id, attribute_name, attribute_value)

sql
-- Find laptops with 16GB RAM
SELECT p.* FROM products p
JOIN product_attributes pa ON p.product_id = pa.product_id
WHERE pa.attribute_name = 'ram' AND pa.attribute_value = '16GB'

sql
products(product_id, name, category_id, base_price)
laptops(product_id, ram, storage, screen_size, processor)
shirts(product_id, size, color, material)
books(product_id, author, isbn, page_count)

sql
products(
  product_id,
  name,
  category_id,
  base_price,
  attributes JSONB
)

sql
CREATE INDEX idx_products_ram ON products ((attributes->>'ram'));

Inventory: The Consistency Challenge

1. Customer A reads inventory: 5 units
2. Customer B reads inventory: 5 units
3. Customer A reserves 3, writes inventory: 2 units
4. Customer B reserves 3, writes inventory: 2 units
5. Both think they succeeded, but we sold 6 units of a 5-unit inventory

sql
UPDATE inventory
SET quantity = quantity - 3
WHERE product_id = ? AND quantity >= 3
RETURNING quantity;

sql
inventory_shards(
  product_id,
  shard_id,
  quantity
)
-- For product 123 with 100 units:
-- (123, 0, 20), (123, 1, 20), (123, 2, 20), (123, 3, 20), (123, 4, 20)

Orders: The Immutable Record

sql
orders(
  order_id,
  customer_id,
  status,
  total_amount,
  created_at,
  updated_at
)

order_items(
  order_item_id,
  order_id,
  product_id,
  product_name,      -- snapshot at order time
  unit_price,        -- snapshot at order time
  quantity,
  line_total
)

Flash Sales: The Thundering Herd

1. Load the product page
2. Add to cart
3. Checkout

Cart: Redis vs Database

• Fast reads and writes
• Simple data model (hash of product_id → quantity)
• Automatic expiration (cart abandoned after 24 hours)
• Data loss risk if Redis restarts without persistence

• Durable
• Can store complex cart data (saved for later, gift options)
• Enables analytics on cart behavior
• Slower than Redis

• Active carts in Redis for speed
• Persist to database periodically (every few minutes)
• On Redis miss, restore from database

Search Index Synchronization

Chapter 12: IoT / Time-Series System

The Volume Challenge

• 100,000 × 12 readings/minute = 1.2 million readings/minute
• 1.2M × 60 × 24 = 1.7 billion readings/day

Time-Based Partitioning

sql
device_readings(
  device_id VARCHAR(50),
  timestamp TIMESTAMP,
  reading_type VARCHAR(20),
  value DOUBLE,
  metadata JSONB,
  PRIMARY KEY (device_id, timestamp, reading_type)
)
PARTITION BY RANGE (timestamp);

sql
SELECT * FROM device_readings
WHERE device_id = '123'
  AND timestamp BETWEEN '2024-01-15' AND '2024-01-16';

Rolling Window Retention

Last 24 hours: Full resolution (every 5 seconds)
Last 7 days: 1-minute aggregates
Last 90 days: 1-hour aggregates
Beyond 90 days: Daily aggregates only

Downsampling

sql
-- Hourly aggregates from minute data
INSERT INTO readings_hourly (device_id, hour, reading_type, avg_value, min_value, max_value, count)
SELECT
  device_id,
  DATE_TRUNC('hour', timestamp) as hour,
  reading_type,
  AVG(value),
  MIN(value),
  MAX(value),
  COUNT(*)
FROM readings_minute
WHERE timestamp >= ? AND timestamp < ?
GROUP BY device_id, DATE_TRUNC('hour', timestamp), reading_type;

Compression Strategies

• Delta encoding: Store differences between consecutive values instead of absolute values
• Run-length encoding: "72.1 repeated 50 times" instead of 50 separate values
• Gorilla compression: A technique from Facebook's time-series database, achieving 10x compression

Efficient Recent Queries

sql
-- Latest reading per device (common for dashboards)
SELECT DISTINCT ON (device_id) *
FROM device_readings
ORDER BY device_id, timestamp DESC;

-- Last N readings for a device
SELECT * FROM device_readings
WHERE device_id = '123'
ORDER BY timestamp DESC
LIMIT 100;

Chapter 13: Search System

The Inverted Index

• Doc 1: "The quick brown fox"
• Doc 2: "The lazy brown dog"
• Doc 3: "Quick foxes are rare"

"the"    → [Doc 1, Doc 2]
"quick"  → [Doc 1, Doc 3]
"brown"  → [Doc 1, Doc 2]
"fox"    → [Doc 1]
"lazy"   → [Doc 2]
"dog"    → [Doc 2]
"foxes"  → [Doc 3]
"are"    → [Doc 3]
"rare"   → [Doc 3]

Why Relational LIKE Doesn't Work

sql
SELECT * FROM documents WHERE content LIKE '%quick%';

Full-Text Search Features

Hybrid Architecture

• Relational database: Source of truth for structured data. Handles transactions, relationships, consistency.
• Search engine: Optimized index for search. Eventually consistent with the database.

1. Application writes to database
2. Change event triggers index update
3. Search engine indexes the document
4. Search queries hit the search engine
5. Results include IDs; application fetches full data from database

Relevance Scoring

Chapter 14: Ad-Tech / High Throughput Counters

Impression and Click Tracking

• 10 billion impressions/day
• 100 million clicks/day
• Must track by: ad, campaign, publisher, user segment, geography, device, time

sql
impressions(
  impression_id,
  ad_id,
  campaign_id,
  publisher_id,
  user_id,
  country,
  device_type,
  timestamp
)

Event Streaming Architecture

1. Event ingestion: Events flow to Kafka topics (impressions, clicks)
2. Stream processing: Flink/Spark processes streams, aggregates in windows
3. Pre-aggregated storage: Aggregated counts go to a fast store (Redis, Cassandra)
4. Raw event archive: Full events go to object storage for later analysis

High-Cardinality Counters

sql
SELECT COUNT(DISTINCT user_id) FROM impressions WHERE ad_id = ?

Real-Time Dashboards

• Stream processor aggregates by campaign, by minute
• Results stored in Redis:
  campaign:123:impressions:2024-01-15T10:30 = 45678
• Dashboard queries Redis, sums across time buckets
• Historical data rolls up to Clickhouse for long-term storage

Windowed Aggregation

---

Part V: Database Selection

Chapter 15: Choosing the Right Database

PostgreSQL: The Versatile Workhorse

• ACID compliance with sophisticated transaction support
• Rich SQL with CTEs, window functions, JSON operators
• JSONB for flexible schema alongside relational tables
• Full-text search (good enough for many use cases)
• Extensibility (PostGIS for geo, pg_vector for embeddings)
• Mature replication and high-availability options
• Large ecosystem of tools and expertise

• Horizontal scaling requires external tools (Citus) or careful sharding
• Write throughput limited by single-leader architecture
• Not optimized for analytical workloads at scale
• Connection model (process per connection) limits concurrency

• Your schema is relational or hybrid relational+JSON
• You need transactions across tables
• You need the flexibility of SQL
• You want one database to start with

• You need massive write throughput (>100K writes/sec)
• You need linear horizontal scale
• Your data is purely analytical at petabyte scale

MySQL: The Established Standard

• Extremely well-tested and stable
• Huge community and tooling ecosystem
• Good read scaling via replication
• InnoDB engine is solid for OLTP
• Vitess provides sharding solution (YouTube scale)

• Historically weaker SQL feature set than PostgreSQL (improving)
• JSON support less sophisticated than PostgreSQL's JSONB
• Replication can lag under heavy write load
• Default configuration often suboptimal

• You have MySQL expertise
• You're building on the LAMP stack
• You need a database that every hosting provider supports

• You need advanced SQL features
• You're starting fresh with no MySQL investment

MongoDB: The Document Database

• Flexible schema—great for varied document structures
• Horizontal scaling built-in (sharding)
• Good developer experience for object-oriented code
• Atlas (managed service) is polished
• Aggregation pipeline is powerful

• No multi-document transactions (until recent versions, and with limitations)
• Join-like operations ($lookup) are expensive
• Easy to create performance problems with poor data modeling
• Storage efficiency less than relational for highly relational data

• Your data is document-oriented (content, catalogs, configurations)
• Schema flexibility is genuinely needed
• You're denormalizing anyway

• Your data is highly relational
• You need cross-document transactions
• Your team doesn't understand document modeling

Cassandra: The Distributed Write Machine

• Linear horizontal scale for writes
• Multi-datacenter replication
• Tunable consistency
• No single point of failure
• Handles very large datasets

• Query model is extremely limited (no joins, limited filtering)
• Data modeling requires deep understanding
• Operational complexity is high
• Read latency higher than alternatives for some patterns

• You need massive write throughput (>1M writes/sec)
• You need multi-region active-active
• Your query patterns fit Cassandra's model

• You need flexible queries
• You need strong consistency
• You don't have operational expertise

DynamoDB: The Managed NoSQL

• Fully managed (no operations)
• Scales automatically
• Single-digit millisecond latency
• Integrates with AWS ecosystem
• Pay-per-request pricing option

• Query model is limited (like Cassandra)
• Costs can explode with inefficient access patterns
• Vendor lock-in (AWS only)
• Item size limit (400KB)

• You're on AWS and want managed infrastructure
• Your access patterns are well-defined
• You need extreme scale

• You need flexible queries
• Multi-cloud is a requirement
• You're cost-sensitive at high scale

Redis: The Speed Layer

• Sub-millisecond latency
• Rich data structures (sets, sorted sets, hyperloglog)
• Pub/sub capabilities
• Lua scripting for atomic operations
• Cluster mode for horizontal scale

• Memory-bound (expensive at large scale)
• Persistence options have trade-offs
• Not a primary database for most use cases
• Data loss possible under failures

• You need a cache layer
• You need session storage
• You need real-time leaderboards or counters
• You need a pub/sub broker

• You need durable primary storage
• Your dataset exceeds memory capacity
• You need complex queries

Elasticsearch: The Search Engine

• Inverted index for fast text search
• Powerful query DSL
• Real-time indexing
• Kibana for visualization
• Good for log aggregation

• Not a database (eventual consistency, no transactions)
• Resource-intensive
• Cluster management is complex
• Write-heavy workloads stress the system

• You need full-text search
• You're building log analytics
• You need complex text queries

• You're using it as a primary database
• You need transactions
• Your use case is simple search (PostgreSQL FTS might suffice)

ClickHouse: The Analytical Speed Demon

• Extremely fast analytical queries
• Efficient compression
• Real-time ingestion
• SQL interface
• Cost-effective for large datasets

• Not for OLTP (no transactions, limited updates)
• Operational complexity
• Replication model requires understanding
• JOINs can be problematic

• You need sub-second analytics on billions of rows
• You're building real-time dashboards
• You have high-volume event data

• You need transactions
• You need frequent updates
• You're not doing analytics

BigQuery / Snowflake: The Warehouse Giants

• Massive scale with zero infrastructure
• Separation of storage and compute
• Pay-per-query pricing
• SQL interface
• Integrations with BI tools

• Not for OLTP
• Latency too high for interactive use (seconds to minutes)
• Costs can surprise at high query volume
• Vendor lock-in

• You have petabytes of analytical data
• You want zero operational burden
• Query latency of seconds is acceptable

• You need sub-second queries
• You need real-time analytics
• You're cost-sensitive at high volume

NewSQL: CockroachDB, TiDB, Spanner

• SQL with horizontal scale
• ACID across distributed data
• Automatic sharding
• Strong consistency

• Higher latency than single-node PostgreSQL
• Operational complexity varies
• Younger ecosystems
• Performance characteristics differ from traditional RDBMS

• You've outgrown single-node PostgreSQL
• You need SQL with horizontal scale
• You need geo-distributed data with consistency

• Single-node PostgreSQL meets your needs
• You need the maturity of traditional databases
• Your team lacks distributed systems expertise

Chapter 16: Scaling Strategies

Vertical Scaling: The First Option

• Hundreds of CPU cores
• Terabytes of RAM
• Petabytes of storage

• Your data fits on one large machine
• Your read/write rate fits single-machine throughput
• You can afford the hardware

• Physical limits exceeded
• Cost becomes prohibitive
• Availability requirements exceed single-machine guarantees

Read Replicas: The First Distribution

Client → Load Balancer → Primary (writes)
                       → Replica 1 (reads)
                       → Replica 2 (reads)
                       → Replica 3 (reads)

• Read-your-writes: Route reads to primary for N seconds after writes
• Sticky sessions: Users always read from the same replica
• Wait for replication: Delay reads until replicas catch up

Sharding: The Nuclear Option

• Cross-shard queries: Queries spanning shards require scatter-gather, dramatically increasing complexity
• Cross-shard transactions: Essentially impossible without significant infrastructure
• Rebalancing: Adding shards requires moving data, which is operationally risky
• Operational burden: N shards means N times the monitoring, backups, and incident surface

Global Secondary Indexes

• Users sharded by user_id
• Global index from email → user_id, shard

Multi-Region Replication

• Latency: Users should read from nearby replicas
• Availability: Regional outages shouldn't cause global outages
• Compliance: Data sovereignty laws require certain data to stay in-region

---

Part VI: Indexing Masterclass

Chapter 17: Understanding Index Structures

B-Trees: The Relational Standard

• Efficient point lookups (find exact value)
• Efficient range scans (find values between X and Y)
• Efficient ordered retrieval (sorted results without extra sorting)
• Balanced read and write performance

• Lookup: O(log N)
• Insert: O(log N)
• Delete: O(log N)
• Range scan: O(log N + K) where K is results

LSM Trees: The Write-Optimized Alternative

• Very high write throughput (sequential writes)
• Efficient disk utilization
• Good for write-heavy workloads

• Read requires checking multiple SSTables (read amplification)
• Background compaction consumes CPU and I/O (write amplification)
• Compaction can cause latency spikes

Bloom Filters: Probabilistic Acceleration

if bloom_filter.might_contain(key):
    actually_read_sstable(key)
else:
    skip_sstable()  # Saved a disk read!

Index Types You Should Know

sql
CREATE INDEX idx ON orders(customer_id) INCLUDE (status, total);
-- Query can use index only:
SELECT status, total FROM orders WHERE customer_id = 123;

sql
CREATE INDEX idx ON orders(customer_id) WHERE status = 'pending';
-- Only pending orders are indexed, smaller index, faster queries

sql
CREATE INDEX idx ON users(LOWER(email));
-- Now case-insensitive email lookups are fast

Write Amplification: The Hidden Cost

• Insert/update performance degrades with index count
• Indexes aren't free—add only what queries need
• For write-heavy workloads, be aggressive about removing unused indexes

sql
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

---

Part VII: API and Database Design Together

Chapter 18: Designing APIs That Respect Database Realities

Resource Modeling

• customers
• orders
• order_items
• products

• /customers/{id}
• /customers/{id}/orders
• /orders/{id} (includes items as nested resource)
• /products/{id}

Pagination: Get This Right

GET /orders?page=10&per_page=20

sql
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 180;

• Performance: OFFSET 10000 requires reading 10000 rows to skip them
• Inconsistency: If new orders are inserted while paginating, you'll miss or duplicate items

GET /orders?cursor=eyJpZCI6MTIzNH0=&limit=20

sql
SELECT * FROM orders WHERE id < 1234 ORDER BY id DESC LIMIT 20;

• Consistent performance regardless of page depth
• Stable results even with concurrent inserts
• No skipped or duplicated items

Filtering Without Killing Performance

GET /products?category=electronics&price_min=100&price_max=500&in_stock=true

Sorting: The Database Must Support It

GET /products?sort=price_asc

sql
SELECT * FROM products ORDER BY price ASC;

• Index commonly sorted columns
• Limit allowed sort fields to indexed columns
• Default sort matches an existing index

Rate Limiting: Protect the Database

Idempotency: Safe Retries

POST /orders
Idempotency-Key: abc-123-unique-key

sql
CREATE TABLE idempotency_keys (
  key VARCHAR(100) PRIMARY KEY,
  result JSONB,
  created_at TIMESTAMP,
  expires_at TIMESTAMP
);

---

Part VIII: Distributed System Considerations

Chapter 19: CAP and Data Modeling

Consistency vs Availability: Real Choices

• CP: Refuse requests that can't be confirmed consistent. Some users get errors.
• AP: Accept requests that might create inconsistency. All users get responses.

• Design for single-source-of-truth
• Avoid denormalization that creates consistency challenges
• Accept that some operations will fail during partitions

• Design for eventual consistency
• Embrace denormalization for performance
• Build reconciliation mechanisms for conflicts

Eventual Consistency in Practice

• Writes propagate asynchronously
• Different clients may see different states
• Given enough time without new writes, all replicas converge

Idempotent Writes: The Distributed Must-Have

sql
UPDATE accounts SET balance = balance + 100 WHERE id = 123;

sql
INSERT INTO transactions (transaction_id, account_id, amount)
VALUES ('txn-abc-123', 123, 100)
ON CONFLICT (transaction_id) DO NOTHING;

Schema Evolution: The Long Game

• Add nullable column
• Add new table
• Add index

• Remove column (old code still references it)
• Rename column (old code uses old name)
• Change column type (old code expects old type)

1. Add new column (nullable)
2. Deploy code that writes to both old and new columns
3. Backfill new column from old column
4. Deploy code that reads from new column
5. Remove old column (after all old code is gone)

---

Part IX: Interview Preparation

Chapter 20: Database Modeling Interview Questions

Conceptual Questions

Scenario Questions

sql
riders(rider_id, name, email, phone, created_at)
drivers(driver_id, name, email, phone, vehicle_id, current_location, status, rating_avg)
vehicles(vehicle_id, driver_id, make, model, license_plate)
trips(trip_id, rider_id, driver_id, pickup_location, dropoff_location, status,
      started_at, completed_at, distance, fare)
payments(payment_id, trip_id, amount, method, status, processed_at)
ratings(rating_id, trip_id, rater_type, rating, comment, created_at)

• Ingestion: Kafka for buffering and distribution
• Real-time: Events streamed to Flink for real-time aggregates (metrics, counters)
• Short-term: ClickHouse for last 7 days of events, supporting ad-hoc analysis
• Long-term: S3 + Parquet for historical events, queryable via Athena/Presto

sql
CREATE TABLE events (
  user_id UInt64,
  event_type String,
  event_data String,  -- JSON
  timestamp DateTime
) ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(timestamp)
ORDER BY (user_id, timestamp);

• Missing indexes on filtered columns: Add appropriate indexes
• Full table scans: Maybe a sequential scan is actually optimal, or maybe statistics are stale (run ANALYZE)
• Index bloat: REINDEX to rebuild fragmented indexes
• Table bloat: VACUUM FULL to reclaim space (requires downtime) or pg_repack

sql
CREATE TABLE orders_2024_01 PARTITION OF orders FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

Tradeoff Questions

Red Flags Interviewers Watch For

• "I always use [X]": Dogmatic preference for one database regardless of requirements
• "We'll figure it out later": No thought for scale or evolution
• Premature optimization: Sharding a database that has 1000 rows
• Ignoring consistency: Not considering what happens during failures
• Not questioning requirements: Accepting "we need real-time" without asking "what does real-time mean for this use case?"

How to Explain Tradeoffs Clearly

1. State the options
2. Explain the tradeoffs of each
3. State your recommendation
4. Explain why given the context

---

Part X: Final Section

Executive Summary

Decision-Making Framework

1. What are the entities and relationships? Start with logical modeling. Don't think about databases yet.
2. What are the query patterns? List the queries the system must support. Estimate their frequency and latency requirements.
3. What are the consistency requirements? For each operation, what happens if data is stale or inconsistent?
4. What is the scale? Current and projected. Events per second. Data volume. User concurrency.
5. What are the operational constraints? Team expertise. Budget. Managed vs self-hosted.

• Relational + normalized for complex queries with consistency
• Relational + denormalized for read-heavy with some complexity
• Document DB for document-oriented data with known access patterns
• Wide-column for massive write scale with simple queries
• Columnar for analytical aggregations
• Time-series for IoT/event streams

Database Selection Matrix

Scaling Mental Model

Single PostgreSQL
      ↓ reads exceeding capacity
Add read replicas
      ↓ writes exceeding capacity
Vertical scaling (bigger machine)
      ↓ vertical limits reached
Table partitioning
      ↓ single-node limits reached
Application-level sharding
      ↓ operational burden too high
Managed distributed DB (CockroachDB, Spanner, etc.)

30-Day Mastery Roadmap

• Days 1-2: Normalization theory (1NF through BCNF) with banking schema exercise
• Days 3-4: Denormalization patterns with social media schema exercise
• Days 5-7: PostgreSQL deep dive—partitioning, indexing, EXPLAIN ANALYZE

• Days 8-9: Document databases (MongoDB data modeling)
• Days 10-11: Wide-column databases (Cassandra data modeling)
• Days 12-14: Analytical databases (ClickHouse, star/snowflake schemas)

• Days 15-16: Replication and consistency models
• Days 17-18: Sharding strategies and trade-offs
• Days 19-21: Distributed transactions and sagas

• Days 22-23: API design and pagination
• Days 24-25: Full system design exercises (e-commerce, social media)
• Days 26-28: Interview practice with peers
• Days 29-30: Review weak areas, consolidate notes

• Read one engineering blog post about data at scale
• Do one LeetCode database problem
• Design one schema from scratch

• "Designing Data-Intensive Applications" by Martin Kleppmann
• PostgreSQL documentation (seriously, it's excellent)
• Database vendor engineering blogs (Netflix, Uber, Pinterest, Airbnb)

---

Closing Thoughts

---