Database Design at Big-Tech Scale

Concept. 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. You never run heavy analytics on the live OLTP database.

Intuition. A Spotify play is one fast write to Postgres (OLTP). "Top genres this quarter" scans 50 billion rows, which would choke that same Postgres for hours, so a nightly Spark job ships the data over Kafka to BigQuery (OLAP), where the scan finishes in seconds. Same data, three systems, three latency budgets.

Three panels: OLTP (blue) handles user-facing writes at under 100 ms on Postgres/Aurora/Spanner, 100M-5B txns/day; an ETL/ELT bridge (grey) moves ~100 TB/day with Spark transforming and Kafka shipping; OLAP (violet) scans billions of rows on BigQuery/Snowflake/Redshift, a 1 PB warehouse.

Figure 1. One workload split across three systems. OLTP (blue) takes the live writes and needs sub-100 ms latency; OLAP (violet) takes the analytical scans and needs to read billions of rows at once; the two have opposite access patterns, so they run on separate engines. The ETL/ELT bridge (grey) carries data from the first to the second: Spark reshapes each day's data and Kafka ships it between the systems. The scale and cost numbers are the order of magnitude each layer operates at, not exact quotes.

Why Not Just One Database?

You pushed startup Postgres to its limit earlier. Eventually a startup becomes an enterprise, and you stop running petabyte-scale analytics on the OLTP system that handles user clicks. The reason is not lock contention: Postgres uses MVCC, so a read-only analytical scan takes no row locks that block writers. The real problem is resource contention at three levels:

  1. A long scan saturates CPU and disk I/O, slowing every user-facing query on the same primary.

  2. It pollutes the cache, evicting the hot user-facing pages in memory to make room for cold analytical ones.

  3. It runs so long that routine cleanup stalls behind it, so the database slowly bloats. (Concretely: the scan holds one read snapshot open the whole time, which keeps VACUUM from reclaiming dead rows.)

So you load raw data into a cheap Data Lake (object storage) and move cleaned slices into an OLAP warehouse built for fast scans over billions of rows.

OLTP: User-Facing Speed

Every click, update, and "add to playlist" is OLTP. A Spotify play:

-- User plays "Anti-Hero" by Taylor Swift
BEGIN TRANSACTION;
INSERT INTO listens (user_id, song_id, timestamp)
VALUES (123, 789, NOW());
UPDATE users SET last_active = NOW() WHERE user_id = 123;
UPDATE songs SET play_count = play_count + 1 WHERE song_id = 789;
COMMIT;

Sub-100 ms latency, thousands of queries per second, single-row updates. One Postgres node handles about 5K transactions/sec for around $500/month; past that you reach for Aurora (200K/sec) or Spanner (50K/sec per region).

OLAP: Analytics at Scale

"What's trending? How are we doing?" is OLAP:

-- Which genres are growing fastest this quarter?
SELECT
    s.genre,
    DATE_TRUNC('week', l.listen_time) AS week,
    COUNT(DISTINCT l.user_id) AS unique_listeners,
    COUNT(*) AS total_plays
FROM listens l
JOIN songs s ON l.song_id = s.song_id
WHERE l.listen_time > '2024-01-01'
GROUP BY s.genre, week
ORDER BY week, unique_listeners DESC;

This scans 5 TB (50 billion rows) in about 15 seconds on BigQuery; on a single Postgres node it would take hours. The reason is parallelism:

1 machine scanning 1 TB:   200 s  (SSD at 5 GB/s)
100 machines in parallel:    2 s  (each scans 10 GB)

Same total work, 100 machines, 100x faster.

ETL/ELT: Moving Data Between Worlds

The pipeline that feeds analytics does three things: Extract from OLTP, Transform (join, aggregate, enrich), and Load into the warehouse. The only open question is the order of the last two, and that order is the whole difference between ETL and ELT.

Two rows of three boxes. ETL: Extract, then a slate Transform (Spark) box, then Load into the warehouse, transforming before loading. ELT: Extract, then Load raw into the lake, then the slate Transform (SQL + dbt) box, transforming in place. The slate Transform box moves from the middle to the end, showing the two steps swap.

Figure 2. ETL and ELT run the same three steps in a different order. ETL transforms before loading: a nightly Spark job cleans and aggregates the raw data, then writes only the finished tables into the warehouse. That made sense when warehouse storage was expensive, but one pipeline owns the schema, so every new question waits on it. ELT flips the last two steps: load the raw data into cheap storage first, then transform it inside the warehouse with SQL and dbt. Storage is now cheap and warehouse compute is elastic, so analysts reshape the raw data on demand. The slate box is the Transform step; watch it slide from the middle to the end. Kafka ships the data either way, and modern stacks lean ELT.

A Spark job for the transform step looks like this:

# Simplified Apache Spark job: aggregate yesterday's plays
def spotify_daily_job():
    # 1. EXTRACT FROM OLTP (PostgreSQL replicas)
    raw_listens = spark.read.jdbc(
        "postgresql://replica.spotify.com/listens",
        query="SELECT * FROM listens WHERE date = YESTERDAY"
    )
    # 2. TRANSFORM: aggregate AND enrich
    daily_stats = raw_listens \
        .JOIN(songs_dim, "song_id") \
        .JOIN(users_dim, "user_id") \
        .groupBy("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 \
        .mode("append") \
        .partitionBy("date") \
        .bigquery("spotify_warehouse.daily_stats")

This job reads from a read replica (not the primary), so the heavy extract never touches the database serving users. Kafka sits between the systems as the durable transport: the transform job publishes its results onto Kafka, and the warehouse, search indexes, and caches all subscribe, so nothing is lost in transit.

The Complete Picture

Users → API → PostgreSQL (OLTP: handle clicks)
                 ↓
              Kafka (carry events reliably)
                 ↓
              Spark (ETL: transform)
                 ↓
            BigQuery (OLAP: answer "what's trending?")
                 ↓
            Dashboards

Key Takeaways

  1. Split into three. OLTP for users, OLAP for analytics, ETL/ELT to connect them.

  2. Kafka ships the data. A durable log between systems means nothing is lost in transit.

  3. It is all SQL. PostgreSQL (OLTP), Spark SQL (ETL/ELT), BigQuery (OLAP).

  4. Use the right system per job. Decompose the problem instead of forcing one database to do everything.

Next

SQL vs NoSQL → You now split work across the right systems. But when is SQL itself the wrong system? The next two case studies are about when to leave SQL for NoSQL, and when not to.