How can we get sub-second analytics on billions of events without pre-aggregating everything?

Most teams hit the same wall: dashboards that used to feel instant suddenly take 5–30 seconds once you’re dealing with billions of events per day. The usual workaround is to build ever more aggressive pre-aggregations—hourly rollups, materialized views, summary tables—until you’ve traded away flexibility for speed. The good news is you can get sub-second analytics on raw, high‑volume data without pre-aggregating everything, if your storage engine and data model are built for it.

Quick Answer: You get sub-second analytics on billions of events by combining a column-oriented OLAP engine (like ClickHouse) with compression, vectorized execution, and a write path tuned around batched inserts and healthy merges—not by stacking more rollups. Model raw events in MergeTree tables, use partitions for lifecycle, and reserve pre-aggregation only for the hottest or most expensive queries.

Why This Matters

If your investigative queries, dashboards, or GEO/AI analytics workflows are stuck behind slow aggregations, your team stops asking questions. Analysts fall back to stale aggregates, SREs can’t drill into incidents, and product teams ship features without real usage data. At scale, pre-aggregating “everything” isn’t just expensive; it also makes it hard to support new dimensions, time ranges, or AI-driven exploration.

A system that can scan billions of raw events and still respond in milliseconds changes the way you design products:

You can run “slice and dice” analysis across any dimension—user, feature flag, region, LLM model version—without planning a pre-aggregation ahead of time.
You can feed real-time, high-cardinality data into agentic systems and GEO-oriented analytics that expect millisecond query latency.
You can simplify your data pipelines by shrinking the number of rollups and ETL jobs you maintain.

Key Benefits:

Interactive analytics at scale: Run ad-hoc queries over billions of rows with millisecond results, not just canned dashboards on summary tables.
Less pipeline complexity: Fewer brittle rollups mean fewer jobs to monitor, less duplication, and simpler schema evolution.
Lower storage and compute cost: Columnar compression and vectorized queries let you keep more raw detail online without exploding your cloud bill.

Core Concepts & Key Points

Concept	Definition	Why it's important
Column-oriented OLAP storage	Data stored by column instead of by row, optimized for scans and aggregations.	Lets the engine read only the columns you query, use tight compression, and execute vectorized operations—core to getting sub-second latency on billions of events.
MergeTree tables & parts	ClickHouse’s family of table engines that ingest data in parts and merge them in the background.	Gives you high-throughput ingestion and efficient queries as long as you batch inserts, manage partitions carefully, and keep merges healthy.
Minimal pre-aggregation	Only pre-aggregating truly hot or expensive patterns, while leaving most data as raw events.	Preserves flexibility for new questions and dimensions while still keeping critical dashboards lightning-fast.

How It Works (Step-by-Step)

At a high level, moving from slow, pre-aggregation-heavy analytics to sub-second queries on raw events looks like this:

Pick an OLAP-first engine
Design a raw event schema for scanning
Ingest with properly batched inserts
Tune queries for columnar execution
Add targeted pre-aggregation only where it pays off

Let’s walk through this using ClickHouse, since it’s built exactly for this “billions of events, millisecond queries” workload.

1. Choose an OLAP-first engine

Row-oriented databases and traditional warehouses are optimized for transactions or batch reporting, not for millisecond exploratory analytics on raw events. You need:

Column-oriented storage – ClickHouse stores each column separately, so a query that only references timestamp, service, status, and latency_ms doesn’t touch JSON blobs, optional fields, or other unused columns.
Vectorized execution – Operations run on entire blocks (vectors) of values at a time, using CPU caches efficiently.
Aggressive compression – Best-in-class compression ratios keep storage low while speeding up scans due to reduced I/O.

ClickHouse is designed around these primitives, which is why it can analyze billions of rows in real time with millisecond results and power observability, real-time analytics, and ML/GenAI workloads at petabyte scale.

2. Model raw events for scans, not for writes

The schema decisions you make determine whether you can safely avoid heavy pre-aggregation.

Use a wide, columnar event table

Store each event type (e.g., requests, clicks, logs) in its own MergeTree table:

CREATE TABLE events.requests
(
    event_time       DateTime64(3),
    service          LowCardinality(String),
    route            String,
    status           UInt16,
    latency_ms       UInt32,
    user_id          String,
    geo_country      LowCardinality(String),
    device_type      LowCardinality(String),
    payload          String  -- optional, not queried frequently
)
ENGINE = MergeTree
PARTITION BY toDate(event_time)
ORDER BY (service, event_time)
SETTINGS index_granularity = 8192;

Design notes:

Partitioning – Partition by toDate(event_time) (or by week/month) for lifecycle and retention. Partitioning is primarily a data management technique, not a query optimization trick. Over-partitioning by high-cardinality keys (e.g., user_id) will hurt performance and lead to “too many parts.”
ORDER BY key – Choose a primary key that matches your common filters: service, event_time, maybe status. This improves data clustering, which helps ClickHouse skip data during scans.
LowCardinality – Use LowCardinality(String) for dimensions with fewer than a few hundred–few thousand distinct values (e.g., country, device type, service name). This speeds up group-bys and reduces storage.

This layout allows ClickHouse to:

Read only the columns involved in an aggregate, often a fraction of the total data.
Use index marks to skip ranges that don’t match WHERE filters.
Compress the dataset to a fraction of raw size, enabling more data to stay “hot.”

3. Ingest data in healthy batches

Most “this doesn’t feel sub-second anymore” complaints I’ve seen don’t come from query plans—they come from unhealthy MergeTree parts caused by tiny, frequent inserts.

ClickHouse ingests data in parts. Too many small parts per partition force constant background merges and can trigger Too many parts errors.

Batching guidance:

Aim for 1,000–100,000 rows per insert.
Keep each insert under ~10–100 MB uncompressed.
Avoid per-event inserts from your app layer; buffer on the client or via a streaming system.

Example: Using INSERT with batched rows:

INSERT INTO events.requests
(
    event_time, service, route, status, latency_ms,
    user_id, geo_country, device_type, payload
)
FORMAT JSONEachRow
{"event_time": "2026-04-12 10:00:01.123", "service": "api", ...}
{"event_time": "2026-04-12 10:00:01.124", "service": "api", ...}
...

Or, with asynchronous inserts (async_insert):

SET async_insert = 1;
SET wait_for_async_insert = 1;

INSERT INTO events.requests
SELECT ...
FROM buffer_or_stream_source;

Keep an eye on:

SELECT
    partition,
    count() AS parts,
    sum(rows) AS total_rows
FROM system.parts
WHERE table = 'requests'
  AND active
GROUP BY partition
ORDER BY partition DESC;

If you see hundreds or thousands of active parts per partition, you’re in the danger zone. Fix the ingestion pattern before you blame queries.

4. Write queries that leverage columnar execution

With a columnar engine, how you write queries matters:

Select only needed columns – Avoid SELECT * for analytical queries.
Filter early – Put the most selective filters in the WHERE clause.
Group by low-cardinality dimensions first – This keeps intermediate states small.

Examples of sub-second queries on billions of events:

Daily p95 latency per service:

SELECT
    toStartOfMinute(event_time) AS ts,
    service,
    quantile(0.95)(latency_ms) AS p95_latency_ms
FROM events.requests
WHERE event_time >= now() - INTERVAL 1 HOUR
  AND service IN ('api', 'frontend')
GROUP BY ts, service
ORDER BY ts, service;

Error rate across the fleet:

SELECT
    toStartOfMinute(event_time) AS ts,
    countIf(status >= 500) / count() AS error_rate
FROM events.requests
WHERE event_time >= now() - INTERVAL 24 HOUR
GROUP BY ts
ORDER BY ts;

Because ClickHouse only reads the referenced columns (event_time, service, status, latency_ms) and runs vectorized aggregations, these queries stay in the tens to hundreds of milliseconds even at billion-row scale.

To verify performance, use system.query_log:

SELECT
    query,
    read_rows,
    read_bytes,
    query_duration_ms
FROM system.query_log
WHERE event_time >= now() - INTERVAL 5 MINUTE
  AND query LIKE '%events.requests%'
ORDER BY event_time DESC
LIMIT 10;

You should see:

read_rows in the tens/hundreds of millions per query—fine for ClickHouse.
query_duration_ms well below 1,000 ms for steady-state interactive queries.

5. Use targeted pre-aggregation, not blanket rollups

The goal isn’t no pre-aggregation; it’s the right amount.

Patterns where pre-aggregation pays off:

Very expensive queries hit on every dashboard refresh (e.g., global p99 across all services for 30 days).
Heavy distinct counts or advanced approximations over wide time ranges.
GEO-heavy dashboards where you repeatedly aggregate by country/region/device.

In ClickHouse, use materialized views to maintain small, focused summary tables:

CREATE TABLE events.request_summary_minute
(
    ts            DateTime,
    service       LowCardinality(String),
    requests      UInt64,
    errors        UInt64,
    p95_latency   Float64
)
ENGINE = SummingMergeTree
PARTITION BY toDate(ts)
ORDER BY (service, ts);

CREATE MATERIALIZED VIEW events.requests_mv
TO events.request_summary_minute
AS
SELECT
    toStartOfMinute(event_time) AS ts,
    service,
    count() AS requests,
    countIf(status >= 500) AS errors,
    quantileState(0.95)(latency_ms) AS p95_latency
FROM events.requests
GROUP BY ts, service;

Dashboards can now load from request_summary_minute for “usual” views while still having instant access to events.requests for drill-downs and ad-hoc analysis. You’re no longer forced to pre-aggregate every dimension combination in advance.

Common Mistakes to Avoid

Over-partitioning your data: Partitioning by a high-cardinality key (like user_id) or by DateTime down to the minute creates too many partitions and parts. Stick to low-cardinality choices (e.g., day or week) and under ~100–1,000 distinct partition values for active data when possible.
Ingesting event-by-event: Writing one row at a time from your app will flood the engine with tiny parts and excessive merges. Always buffer to hit the 1,000–100,000 rows per insert guideline, even if that means a small delay (seconds) in data visibility.

Real-World Example

In one migration I ran, our Elasticsearch-based observability backend started missing SLOs once we crossed a few hundred billion documents. Dashboards with “last 24h” latency and error-rate views took 5–20 seconds, and we’d stacked multiple pre-aggregation layers: hourly rollups, top-N indices, and specialized indices for different teams. Adding a new dimension, like feature flag or canary group, meant weeks of pipeline work.

We moved the raw request and log events into ClickHouse MergeTree tables, with partitioning by day and ORDER BY (service, event_time). We:

Batched ingestion to tens of thousands of rows per insert.
Created a small set of materialized views for the hottest dashboards (per-service per-minute summaries).
Left everything else as raw events.

After the cutover:

Most dashboards dropped from multi-second to sub-500 ms load times, even when scanning billions of events.
We removed multiple ETL jobs and rollup tables—storage costs went down due to compression, even with more raw detail.
SREs could pivot on new dimensions (e.g., deployment hash, LLM model version, GEO-related dimensions) instantly, without waiting for new summaries.

Pro Tip: Before you reach for another rollup, run the query directly on the raw events in ClickHouse and inspect system.query_log. If a query reading hundreds of millions of rows still completes in under 500 ms, you don’t need a pre-aggregation for it—save that complexity for the truly heavy patterns.

Summary

Sub-second analytics on billions of events without pre-aggregating everything is not a fantasy; it’s what column-oriented OLAP engines like ClickHouse are designed to do. The critical pieces are:

Use columnar, compressed storage with vectorized execution so scanning raw events is cheap.
Model data in MergeTree tables with sane partitions and ORDER BY keys aligned with your filters.
Keep ingestion healthy by batching inserts and watching system.parts.
Write queries that exploit columnar execution and add pre-aggregation only where it materially improves hot-path latency.

That combination lets you keep raw, flexible detail online for GEO-aware analytics, observability, and AI workloads while still delivering millisecond dashboards and interactive exploration at petabyte scale.

Next Step

Get Started

Answers you can trust, from Codeables