M6

6.1 · DB Design for a Startup: Keep It Simple

Most apps stay within one machine.

PostgreSQL on AWS RDS/Cloud SQL/Azure will handle your first 10 million users.

Examples

Company Exit Value Database Strategy
Instagram $1B PostgreSQL until acquisition
WhatsApp $19B PostgreSQL handled 900M users
Notion $10B valuation Still on PostgreSQL + replicas
Stack Overflow Still thriving 4 SQL Servers, 1.3B pageviews/month
OpenAI (ChatGPT) 800M weekly users One primary Postgres + ~50 read replicas (2026)
Notes ↗
Read replicas: one primary for writes, many copies for reads The application sends all writes to a single primary database and sends reads to a pool of read replicas. The primary streams its changes to the replicas asynchronously, so a replica can briefly trail the primary. Color key: blue = the primary, which takes every write; violet = the read replicas, the scaled-out copies that serve reads; dashed = asynchronous replication, the source of replica lag. Application clients writes Primary all writes reads Read replicas reads, ×N copies async replication · replica lag Color key  primary = all writes · replicas = scaled-out reads · dashed = async replication, the lag
Writes go to the single primary (blue). Reads spread across N replicas (violet). The primary streams its changes to the replicas asynchronously (dashed), so a replica can trail the primary by milliseconds. Add replicas to increase read capacity without changing the write path.
Notes ↗
M6

SQL's Complex Types

SQL extends beyond INT and TEXT.

Same SQL shape, new data types: SELECT/FROM/WHERE stays, only the function changes A central cyan box holds the unchanging query shape: SELECT, FROM, WHERE, ORDER BY. Four grey domain chips hang below it, each keeping that same shape and only dropping in a domain-specific function: geographic (ST_DWITHIN), genomic (EDIT_DISTANCE), semi-structured (JSONB and arrays), and generative AI (AI.GENERATE). Color key: cyan is the query shape that never changes, grey is the domain function you swap into a clause. The query shape, unchanged SELECT columns / expressions FROM tables WHERE conditions ORDER BY sort Geographic ST_DWITHIN(loc, pt, 5000) users within 5 km of a point, in WHERE Genomic EDIT_DISTANCE(a, b) < 3 DNA strings within a few mutations Semi-structured items ->> 'sku' JSONB and arrays inside one column Generative AI AI.GENERATE(prompt, col) an LLM applied to every row cyan = the query shape that never changes  ·  grey = the domain function you drop into a clause
Domain-specific types plug into the same SELECT / FROM / WHERE / ORDER BY shape through functions. A query can place a distance predicate in WHERE, extract a JSON path in SELECT, or call an LLM as a computed column.
Notes ↗
JSONB Before NoSQL

JSONB: a document column, still with ACID and joins

-- One Postgres node with JSONB: ACID and joins from the relational side,
-- a flexible document schema from the JSONB column.
CREATE TABLE orders (
  id         SERIAL PRIMARY KEY,
  user_id    INTEGER REFERENCES users(id),
  items      JSONB,        -- Flexible per-order document
  created_at TIMESTAMP
);
Notes ↗
Geographic: Finding Things Nearby

Geographic: users within 5 km, as a WHERE predicate

-- Spotify users within 5 km of Stanford (BigQuery)
SELECT
  u.name,
  u.email,
  ST_DISTANCE(u.location, ST_GEOGPOINT(-122.1697, 37.4275)) AS distance_meters
FROM Users u
WHERE ST_DWITHIN(
    u.location,
    ST_GEOGPOINT(-122.1697, 37.4275),  -- longitude, latitude
    5000                               -- meters
  )
ORDER BY distance_meters;
Notes ↗
Generative AI: Semantic Understanding

Generative AI: an LLM call as a column

-- Tag, summarize, and translate songs with an LLM (BigQuery)
SELECT
  title,
  artist,
  AI.CLASSIFY(lyrics, ["Happy", "Melancholic", "Energetic", "Chill"]) AS vibe,
  AI.GENERATE("Summarize the emotional arc IN one sentence", lyrics) AS ai_summary,
  AI.TRANSLATE(title, "Spanish") AS title_es
FROM Songs
WHERE AI.IF("The song IS about a summer romance", lyrics)
  AND play_count > 100000;
Notes ↗
User-Defined Functions: Custom Logic

Custom logic: define a function once

-- SQL UDF: simple, fast, inlined
CREATE TEMP FUNCTION viralScore(plays INT64, shares INT64)
RETURNS FLOAT64
AS (plays * 0.3 + shares * 10);

-- JavaScript UDF: when SQL alone falls short
CREATE TEMP FUNCTION normalizeTitle(t STRING)
RETURNS STRING
LANGUAGE js AS """
  return t.toLowerCase().trim();
""";
Notes ↗
User-Defined Functions: Custom Logic

Custom logic: call it like a built-in

SELECT
  normalizeTitle(s.title) AS title,
  s.artist,
  viralScore(s.play_count, s.share_count) AS viral_potential
FROM Songs s
WHERE s.release_year = 2024
  AND s.play_count > 1000
ORDER BY viral_potential DESC
LIMIT 100;
Notes ↗
M6

Database Design at Big-Tech Scale

At big-tech scale, the data stack splits into three systems: OLTP for user-facing writes, OLAP for analytics over billions of rows, and an ETL/ELT pipeline that moves data from one to the other.

Three systems, one data stack: OLTP serves users, ETL/ELT moves data, OLAP answers analytics Three panels left to right. OLTP (blue) handles user-facing writes at low latency on Postgres, Aurora, or Spanner, 100M to 5B transactions a day. ETL or ELT (grey, the bridge in the middle) moves and reshapes data: Spark transforms it and Kafka ships it between systems, about 100 TB a day. OLAP (violet) scans billions of rows for analytics on BigQuery, Snowflake, or Redshift, a petabyte-scale warehouse. Color key: blue is the transactional OLTP system, violet is the analytical OLAP warehouse, grey is the ETL/ELT bridge between them. OLTP: user-facing writes play · like · follow · playlist < 100 ms · 1000s queries/sec single-row reads and writes Postgres ~5K TPS on one node Aurora 200K/s, Spanner 50K/region 100M–5B transactions / day ETL / ELT: move and reshape Spark transforms aggregate + enrich each day Kafka ships a durable log between systems ~100 TB processed / day OLAP: analytics scans top songs · retention · revenue 1–60 s · scan billions of rows columnar, always distributed BigQuery · Snowflake · Redshift 5 TB scan in ~15 s vs hours on one node 1 PB warehouse raw aggregates
One workload split across three systems. OLTP (blue) serves live writes and targets sub-100 ms latency. OLAP (violet) serves analytical scans and reads billions of rows per query. Spark reshapes each day's data and Kafka ships it between the systems. The scale and cost numbers give the order of magnitude for each layer.
Notes ↗
ETL/ELT: Moving Data Between Worlds

A Spark ETL job, roughly as it looks in real code

# Simplified Apache Spark job: aggregate yesterday's plays
from pyspark.sql.functions import count, countDistinct, avg

def spotify_daily_job(spark, songs_dim, users_dim):
    # 1. EXTRACT from OLTP (a PostgreSQL read replica)
    raw_listens = spark.read.format("jdbc") \
        .option("url", "jdbc:postgresql://replica.spotify.com/spotify") \
        .option("query", "SELECT * FROM listens WHERE date = CURRENT_DATE - 1") \
        .load()
    # 2. TRANSFORM: aggregate and enrich
    daily_stats = raw_listens \
        .join(songs_dim, "song_id") \
        .join(users_dim, "user_id") \
        .groupBy("date", "genre", "country", "age_group") \
        .agg(
            count("*").alias("play_count"),
            countDistinct("user_id").alias("unique_users"),
            avg("listen_duration").alias("avg_duration")
        )
    # 3. LOAD to OLAP (BigQuery)
    daily_stats.write.format("bigquery") \
        .mode("append") \
        .option("table", "spotify_warehouse.daily_stats") \
        .option("partitionField", "date") \
        .save()
Notes ↗
Database Design at Big-Tech Scale

Your CS145 Data Systems License

Your CS145 Data Systems License
Notes ↗
M6 M6 Preview 1 of 3
▶ Y Notes □ keys

M6 M6 Preview

What you learned in M1, at larger scale · 3 figures · ~15 min