systemsadvanced 25m read

Designing for Scale · Real-Time Analytics

How to count billions of events in real-time without crushing your database, using stream processing, time-window aggregations, and Lambda architecture.

The problem this post solves

When a product launches, analytics is just a SELECT COUNT(*) on the production database. It works perfectly until the data grows, at which point the analytical queries start competing with the transactional queries for disk I/O, slowing down the entire application.

The standard fix is to move the data to a data warehouse (like Snowflake or Redshift) using a nightly batch job. This solves the performance problem but introduces a latency problem: the dashboard is always 24 hours out of date.

This post covers the architecture required to deliver real-time analytics—dashboards that update within seconds of an event occurring—at a scale where inserting every single event into a database is mathematically impossible.

First principles

From first principles

Start with the question

Why can't we insert every view/click into a database and query it?

1
At high scale, events arrive at tens of thousands per second.
forced by · Every user action (click, scroll, view) generates an event.
2
Inserting ten thousand rows a second into a relational database causes severe write amplification and lock contention.
forced by · B-Trees must constantly rebalance and update indexes.
3
Even if you could write them, querying them requires scanning millions of rows per dashboard load.
forced by · To show 'Views in the last 5 minutes', the DB must find and sum all the individual view rows.
4
Scanning millions of rows per second for multiple concurrent dashboard users will saturate any disk array.
forced by · I/O bandwidth is finite.

⇒ Therefore

Real-time analytics requires aggregating the data *before* it hits the database. You cannot store the raw events and hope to query them fast enough.

Architecture: The Lambda Architecture

The modern standard for handling both real-time and historical data is the Lambda Architecture, which splits the flow of data into two paths: the Speed Layer and the Batch Layer.

The Ingestion Layer (Kafka)

All events (clicks, views, purchases) are fired as fire-and-forget messages to a distributed event log (Kafka). Kafka is an append-only, sequential log. It can absorb millions of writes per second because it does almost no processing; it just writes bytes sequentially to disk.

The Batch Layer (The Source of Truth)

Kafka holds the data for a few days, but it also dumps all raw events into cheap, long-term storage (like Amazon S3). This is the Data Lake.

Every night, a massive batch job (using Spark) reads all the raw events from yesterday, cleans them, aggregates them, and loads them into a Data Warehouse. Advantage: It is perfectly accurate. It can handle late-arriving data. It can recalculate everything from scratch if a bug is found in the aggregation logic. Disadvantage: It is slow.

The Speed Layer (The Real-Time View)

While the Batch Layer is slowly processing yesterday's data, the Speed Layer is processing today's data as it happens.

A stream processing engine (like Apache Flink) reads the events from Kafka the millisecond they arrive. It does not store them. It keeps a running tally in memory.

Mental modelStream Processing Aggregation

A bouncer with a clicker at the door of a club. The bouncer doesn't write down the name of every person who enters. They just click the counter. Every 5 minutes, they write down the final number and reset the clicker.

Memory usage is bounded. Counting 1 person or 1 million people takes the same amount of RAM (one integer).
The output is tiny. Instead of writing 10,000 'view' rows to the database, it writes one row: `(minute: 12:05, views: 10,000)`.

🔔 Fires when you see

Use whenever write volume exceeds database capacity but the business only cares about the aggregate sum.

The output of the stream processor is written to a fast, specialized analytical database (like Apache Druid, ClickHouse, or Pinot). These databases are optimized for rapid dashboard queries over pre-aggregated data.

The Serving Layer

When the user opens their dashboard to see "Views over the last 30 days":

The API queries the Data Warehouse for the first 29 days (highly accurate, deeply historical).
The API queries the Fast OLAP database for the current day (real-time, slightly less accurate).
The API merges the two results and returns them to the user.

Handling Time

In stream processing, time is the hardest problem. There are two kinds of time:

Event Time: When the event actually happened on the user's phone.
Processing Time: When the event arrived at your servers.

If a user goes into a subway tunnel, their phone might record 5 clicks (Event Time: 12:01). They emerge from the tunnel at 12:05, and the phone uploads the 5 clicks (Processing Time: 12:05).

If your stream processor aggregates based on Processing Time, the dashboard will show a spike of activity at 12:05, which is a lie. The analytics will be wrong.

The tradeoff

How to handle late-arriving data (The Watermark)

Strict Windowing

+ you gain Simple. If the 12:01 window closes at 12:02, any data arriving after that is dropped.

− you pay Data loss for users with bad connections.

pick when Only for low-stakes metrics where directional accuracy is enough.

Watermarks and Allowed Lateness

+ you gain Accuracy. The system keeps the 12:01 window open in memory until it receives a 'watermark' (a signal that no more 12:01 data is expected), or for a specific allowed lateness (e.g., 10 minutes).

− you pay Requires holding window state in memory longer, consuming more RAM.

pick when Mandatory for accurate event-time processing.

Failure modes

Querying Kafka directly. Kafka is a log, not a database. You cannot query it for "Total views for video X". Attempting to build an API that scans Kafka will fail immediately. Kafka is a pipe; you must pour the pipe into a database to query it.

Dual writes. A common mistake is having the application server try to write to the primary database AND to Kafka simultaneously. If the database write succeeds but the Kafka write fails, the systems are out of sync. The application should write only to the database, and use Change Data Capture (CDC) to stream the changes to Kafka, or write only to Kafka and use a consumer to update the database.

Assuming the Speed Layer is perfectly accurate. The Speed Layer is a best-effort approximation. Messages can be duplicated during network retries, or dropped if they arrive too late. The Batch Layer is the source of truth; it overwrites the Speed Layer's approximations every night. (This is changing with modern streaming systems that guarantee exactly-once processing, but the principle holds for legacy setups).

What to carry forward

Key points

You can now

Explain why relational databases fail at massive event-ingestion scales.
Design a stream processing pipeline that reduces write volume by orders of magnitude.
Solve the late-arriving data problem using Event Time and watermarks.
Combine a Batch Layer and a Speed Layer to serve a comprehensive dashboard.