beginnerintermediateadvanced

System Design · Section 95

Analytics Systems

Level: advanced
Read: 15 min
Concepts: 2

The database serving your application is tuned for the opposite of analytical work. Application queries touch a few rows by key and return in milliseconds; analytical queries scan millions of rows, read a few columns from each, and run for seconds or minutes. Row-oriented storage keeps a row's columns together, which is exactly right for the first and wasteful for the second, since reading two columns from fifty million rows reads all fifty columns of all fifty million. Indexes stop helping once a query covers most of a table, and the memory and disk bandwidth a large scan consumes are the same resources live traffic depends on — so a single report can evict the working set and slow ordinary queries after it has finished. That is resource competition between two workloads, not a defect, and it is why large historical analysis moves to a separate system. That system is a four-part pipeline. Collection emits immutable, timestamped events and buffers them through a queue, so the application never waits on the analytics path and an analytics outage costs lost events rather than failed requests. Pipelines run in two modes — streaming for sub-minute freshness at the cost of a continuously running system, batch for cost, simplicity and trivial re-runs — and most organisations need both for different questions. Storage splits between a lake of raw files with schema applied at read time and a warehouse of modelled, schema-on-write tables, both on columnar formats that store each column's values together, which is what makes reading two columns from a billion rows affordable and compresses far better than mixed-type rows. And pre-aggregation computes the repeated dashboard questions once into hourly and daily rollups, so query cost stops scaling with how many people open the dashboard. The rollups and the raw store are complements: a rollup answers its own question cheaply and every other question not at all, and only raw events can produce a new rollup — which is why discarding them is the one irreversible decision in the design.

System Design overview

System Design for Beginners CoursefreeCodeCamp.org

What is true here

Operational and analytical queries have opposite access patterns and compete for the same memory and disk bandwidth.
A large scan evicts the buffer cache, so its effect on ordinary queries outlasts the query itself.
Emit analytics events asynchronously; an inline write makes product latency depend on a system built for throughput.
Columnar storage reads only the columns a query needs and compresses far better, which is what makes large scans affordable.
Pre-aggregate the repeated questions but keep the raw events — a rollup answers only its own question, and only raw data can produce a new one.

What you will be able to do

Explain why a row-oriented, index-tuned store is structurally poor at large column-selective scans
Design an event schema and a collection path that cannot slow the application down
Choose between streaming and batch for a specific question, and justify running both
Decide what to pre-aggregate and what to keep raw, and explain which decisions are reversible

Why a separate system

Two workloads with opposite access patterns, competing for one set of resources.

Why an operational database struggles with analytical work

standardintermediate

The database serving your application is tuned for a workload that looks nothing like analytics. Application queries touch a few rows by key, return quickly, and run constantly; analytical queries scan millions of rows, read a handful of columns from each, and run for seconds or minutes. The mismatch is structural rather than a matter of tuning. A row-oriented store keeps all of a row's columns together, which is ideal when you want a whole row and wasteful when you want two columns from fifty million rows, because the engine reads every column to get the two it needs. Indexes help point lookups and stop helping once a query touches a large fraction of the table, at which point a full scan is the plan. And the resources those scans consume — memory, disk bandwidth, buffer cache — are the same resources every application query depends on, so a single large report can evict the working set that keeps ordinary queries fast, and the effect outlasts the report. None of this means the operational database is bad at analytics in principle; it means it is optimised for the opposite access pattern, and the two workloads compete for one set of resources. Small analytical queries over recent data are usually fine there. Large scans over history are what want a separate system, and the next concept is about how the data gets there.

Think of it as

A shop's till versus its accounts department. The till is built to serve one customer very quickly, over and over. The accounts department reads every transaction of the quarter and produces one number. Asking the cashier to also total the quarter, at the counter, while customers wait, is not a criticism of the cashier — it is asking one thing to do two jobs whose demands conflict.

sql

-- operational: one customer, by key, sub-millisecond
SELECT * FROM orders WHERE id = $1;

-- analytical: two columns, four years of rows
SELECT date_trunc('month', created_at) AS month,
       sum(total_cents)
  FROM orders
 WHERE created_at >= now() - interval '4 years'
 GROUP BY 1;
-- row storage reads every column of every row
-- to produce two; the index is no help once the
-- scan covers most of the table

What each workload asks the storage engine for

Operational (row-oriented, indexed)

+Seek to one row by key
+Read that row's columns together — exactly what row storage gives you
+Working set stays in the buffer cache
+Predictable, millisecond latency

Analytical on the same store

−Scan a large fraction of the table
−Read all columns to use two — most of the bytes are discarded
−Evicts the working set, so ordinary queries slow down afterwards
−Competes for the same memory and disk bandwidth as live traffic

Two workloads, one database

Two workloads, one database
Property	Operational query	Analytical query
Rows touched	A few, by key	Millions, by scan
Columns needed	Most of the row	A few, across many rows
Duration	Milliseconds	Seconds to minutes
Frequency	Constant	Occasional, often scheduled
Helped by an index?	Yes, decisively	Only while the query stays selective
Effect on the buffer cache	Keeps the hot set hot	Evicts the hot set

Remember: Operational and analytical queries have opposite access patterns — a few rows with all their columns, versus many rows with a few of their columns — and a row-oriented, index-tuned store is built for the first. Large scans read every column to use two, stop benefiting from indexes once they cover most of a table, and evict the buffer cache that keeps live traffic fast, with an effect that outlasts the query. That is resource competition between two workloads, not a defect, and it is why large historical analysis wants a separate system.

The pipeline

Collection, streaming and batch, lake and warehouse on columnar storage, and pre-aggregation.

Event collection, pipelines, columnar storage and pre-aggregation

coreadvanced

An analytics system is a pipeline from events to answers, and it has four parts. Collection is where events are emitted and buffered: an event is an immutable fact with a timestamp, an actor and properties, appended rather than updated, and it goes into a queue or log so the application never waits on the analytics path. Pipelines move and transform those events, in one of two modes — streaming, which processes each event as it arrives and gives sub-minute freshness at the cost of a continuously running system, and batch, which processes a window of events on a schedule and is cheaper, simpler and easier to re-run. Most organisations need both, for different questions. Storage is where events land, and the distinction worth knowing is that a warehouse stores structured, schema-defined tables optimised for querying, while a lake stores raw files in open formats and defers schema to read time; a warehouse answers faster, a lake keeps options open, and many systems keep raw events in a lake and modelled tables in a warehouse. The format underneath is columnar — Parquet, or a warehouse's native equivalent — which stores each column's values together so a query reading two columns from a billion rows reads only those two columns' bytes, and compresses far better because values within a column are similar. Pre-aggregation is the final piece: most dashboards ask the same few questions repeatedly, so computing daily or hourly rollups once and querying those is far cheaper than scanning raw events per view. Keep the raw events, because a rollup answers only the question it was built for and the raw data answers questions nobody has asked yet.

Think of it as

A newspaper archive. Every edition is kept exactly as printed and never edited — that is the raw event store, and its value is that any future question can be asked of it. On top of it sit indexes and summaries built for the questions people actually ask: circulation by month, articles by author. The summaries are what make the reading room fast, and the archive is what makes a new kind of question possible next year. Throwing away the archive because the summaries are quicker is the one irreversible mistake available.

json

{
  "event": "checkout_completed",
  "occurred_at": "2026-08-28T09:14:03.221Z",
  "actor_id": "u_92",
  "properties": {
    "order_id": "o_8814",
    "total_cents": 4200,
    "currency": "GBP",
    "items": 3
  }
}
// immutable, timestamped, appended -- never
// updated, so the store stays replayable

What we're doing: Cost the same dashboard question with and without pre-aggregation, then show what the rollup cannot answer.

preaggregation.txttext

Question: "revenue per day, last 90 days"
Raw events: 4.1 billion rows over 4 years

WITHOUT PRE-AGGREGATION
  every dashboard load scans 90 days of raw
  checkout events -- roughly 250 million rows.
  Columnar storage means only 2 columns are
  read, which is what makes this survivable at
  all, but it is still hundreds of gigabytes
  scanned per load, per viewer.

WITH PRE-AGGREGATION
  a nightly batch job writes:
    revenue_daily(date, currency, total_cents,
                  order_count)
  90 days = 90 rows x a few currencies.
  The dashboard reads ~300 rows.

Same answer. Six orders of magnitude less work,
and the cost no longer grows with how many
people open the dashboard.

WHAT THE ROLLUP CANNOT ANSWER
  "revenue per day for customers who signed up
   in the last 30 days"
  The rollup has no signup dimension. Adding it
  means a new rollup -- and that is possible
  ONLY because the raw events were kept.

  Had the pipeline written rollups and discarded
  raw events, this question would be
  unanswerable for all history, permanently.

11: Columnar storage is what keeps the un-aggregated version merely expensive rather than impossible. It reduces the bytes read by the ratio of selected columns to total columns, which is often twenty- or fiftyfold on a wide event table.
19: The decisive property is that cost stops scaling with viewership. An un-aggregated dashboard costs a full scan per load, so its bill grows with how useful it is — which is the wrong incentive to build into a reporting system.
27: This is the argument for keeping raw events even after the rollups exist. A rollup is a projection: it answers its own question cheaply and every other question not at all, and only the raw data can produce a new projection.

Why this works: Pre-aggregation and raw retention are complements rather than alternatives. The rollups make the known questions fast and cheap; the raw store makes the unknown questions possible. A system with only rollups is fast and permanently limited to what its authors thought of, and that limitation is irreversible for data already discarded.

Emitting analytics events synchronously from the request path

Wrong

python

def checkout(order):
    process_payment(order)
    warehouse.insert_event(...)   # a network call
    return 200                    # to the analytics
                                  # system, inline

Better

python

def checkout(order):
    process_payment(order)
    event_buffer.put_nowait({...})  # local buffer,
    return 200                      # flushed by a
                                    # background
                                    # writer

What you see: Checkout latency tracks the analytics system's latency, and a warehouse maintenance window or a slow ingest endpoint makes the product slow or fails requests outright — for a write nobody was waiting on.

Why: Analytics is the least important thing a request does and the easiest to make asynchronous, so putting it inline gives the product a hard dependency on a system built for throughput rather than latency. Buffering locally and flushing in the background means a full analytics outage costs some lost events, which is the correct severity for the data involved.

Events to answers, with the raw store kept

Application

emits events, never waits on analytics

Collection buffer

queue or log

Streaming pipeline

sub-minute freshness

Batch pipeline

scheduled, re-runnable

Raw event store (lake)

immutable, columnar files — answers future questions

Warehouse tables

modelled, schema-on-write

Pre-aggregated rollups

hourly and daily, for the repeated questions

Dashboards and reports

Streaming versus batch, for the same events

Streaming versus batch, for the same events
Property	Streaming	Batch
Freshness	Seconds to a minute	The schedule interval — typically hourly or daily
Operational cost	A system running continuously	A job that runs and exits
Re-running after a bug	Hard — replay from the log, carefully	Easy — re-run the window
Late-arriving events	Needs explicit windowing and watermarks	Usually captured by the next run
Best for	Alerting, live dashboards, fraud signals	Reporting, billing, modelled tables

Why columnar storage changes the arithmetic

Why columnar storage changes the arithmetic
Query	Row-oriented	Columnar
2 columns from 1 billion rows	Reads every column of every row	Reads 2 columns' bytes
Compression	Mixed types adjacent — poor ratios	Like values adjacent — high ratios
SELECT * on one row	One contiguous read	One read per column — worse
Single-row update	Natural	Awkward; columnar stores prefer appends

Remember: Four parts: collection (immutable timestamped events, buffered so the application never waits), pipelines (streaming for freshness, batch for cost and re-runnability — most systems need both), storage (a lake of raw columnar files plus modelled warehouse tables), and pre-aggregation (compute the repeated questions once so dashboard cost stops scaling with viewership). Columnar storage is what makes reading two columns from a billion rows affordable. And keep the raw events, because a rollup answers only its own question and only raw data can produce a new one.