OLAP Databases

Columnar analytics stores for large scans and dashboard queries

S
System Design Sandbox··8 min read
Learn when to use OLAP databases such as ClickHouse, Druid, Pinot, and BigQuery. Covers OLTP vs OLAP, columnar storage, query patterns, and pre-aggregated rollups.

Content of this blog

#Introduction

An OLTP database answers, "What is the current state of this user?"

An OLAP database answers, "What happened across millions or billions of rows?"

That distinction matters in system design. If an advertiser dashboard asks for clicks grouped by campaign, ad, country, and minute across 90 days, a row-oriented transactional database is the wrong default. You want a storage engine built for scans, filters, and aggregations.

#OLTP vs OLAP

OLTP systems handle transactions:

  • create an order
  • update a payment status
  • fetch one account balance
  • enforce constraints

They optimize small reads and writes with strong consistency.

OLAP systems handle analytics:

  • count clicks by campaign per minute
  • compute p95 latency by service
  • group revenue by merchant and day
  • scan months of events with filters

They optimize large scans and aggregations.

Postgres can do analytics at moderate scale. At high scale, systems like ClickHouse, Druid, Pinot, and BigQuery are built for this workload.

#Columnar Storage

Row stores keep a row together:

timestamp, ad_id, campaign_id, country, browser, cost
timestamp, ad_id, campaign_id, country, browser, cost

Columnar stores keep columns together:

timestamp column
ad_id column
campaign_id column
country column
browser column
cost column

If a query only needs timestamp, campaign_id, and cost, the database can skip reading unrelated columns. Similar values compress well, so scans are faster and cheaper.

That is why columnar OLAP databases are strong for dashboards that group and aggregate large event tables.

#Query Patterns

OLAP queries usually look like this:

SELECT
  toStartOfMinute(timestamp) AS minute,
  ad_id,
  count(*) AS clicks
FROM ad_clicks
WHERE campaign_id = 'cmp_123'
  AND timestamp >= '2026-04-01'
  AND timestamp < '2026-04-20'
GROUP BY minute, ad_id
ORDER BY minute;

The database should support:

  • time-range pruning
  • column pruning
  • fast group-by aggregation
  • approximate distinct counts when exact counts are too expensive
  • materialized views for repeated dashboard queries

The API should expose time range, filters, dimensions, and granularity.

#Pre-Aggregation and Rollups

Raw events are useful for audits and deep investigations, but dashboards often need the same aggregates repeatedly.

For an ad platform, keep both:

raw_clicks
clicks_by_minute
clicks_by_campaign_day

Pre-aggregated tables reduce query latency and protect the database from repeated expensive scans. They also make retention cheaper: keep raw events for a shorter period and aggregates for longer.

#Common Interview Mistakes

Using the transactional database for all analytics. It may work early, then collapse under scan-heavy dashboards.

Forgetting dimensions. Analytics are not just counts. Users filter by campaign, ad, region, device, and time.

Only storing pre-aggregates. Raw events are still needed for audits, corrections, and new aggregate definitions.

Ignoring freshness. Real-time dashboards need a pipeline from stream processor to OLAP store with visible lag.

#Summary: What to Remember

  • OLTP is for transactions; OLAP is for analytics.
  • Columnar storage makes scan-heavy aggregation fast.
  • Time range, filters, dimensions, and granularity shape the API.
  • Pre-aggregation keeps dashboards fast.
  • Keep raw events when billing, auditability, or reprocessing matter.
Practice Now