Services

Resources

Company

OLAP (online analytical processing)

Online analytical processing (OLAP) is the query workload of analytics. There are few queries, but each one reads millions or billions of rows to count, sum or average a handful of columns. Its opposite is online transaction processing (OLTP), the workload of the app itself. An OLTP query touches a few rows by primary key and finishes in milliseconds. OLAP queries usually run against a data warehouse.

How it works

A typical OLAP query filters on a time range, groups by one or two dimensions and aggregates a measure:

SELECT date_trunc('week', started_at) AS week,
       city,
       count(*)  AS trips,
       avg(fare) AS avg_fare
FROM trips
WHERE started_at >= now() - INTERVAL '90 days'
GROUP BY week, city
ORDER BY week,

SELECT date_trunc('week', started_at) AS week,
       city,
       count(*)  AS trips,
       avg(fare) AS avg_fare
FROM trips
WHERE started_at >= now() - INTERVAL '90 days'
GROUP BY week, city
ORDER BY week,

SELECT date_trunc('week', started_at) AS week,
       city,
       count(*)  AS trips,
       avg(fare) AS avg_fare
FROM trips
WHERE started_at >= now() - INTERVAL '90 days'
GROUP BY week, city
ORDER BY week,

SELECT date_trunc('week', started_at) AS week,
       city,
       count(*)  AS trips,
       avg(fare) AS avg_fare
FROM trips
WHERE started_at >= now() - INTERVAL '90 days'
GROUP BY week, city
ORDER BY week,

SELECT date_trunc('week', started_at) AS week,
       city,
       count(*)  AS trips,
       avg(fare) AS avg_fare
FROM trips
WHERE started_at >= now() - INTERVAL '90 days'
GROUP BY week, city
ORDER BY week,

Early OLAP tools precomputed cubes: totals already rolled up by month, city and product, so "slice and dice" read stored sums. In One2N's in-transit analytics project the cubes lived in MySQL, and each interval the team deleted them and recomputed them.

Today most teams query raw events in a columnar engine such as ClickHouse, Apache Druid, StarRocks or BigQuery. These spread one query across every core and node, so the cube becomes optional. Materialized views remain for the few dashboards that refresh every minute.

Updates are where OLAP engines struggle. Data sits in large compressed, often immutable blocks, so changing one row means rewriting the block around it.

Say a ride app syncs 2 million new trips a day from PostgreSQL. Each trip changes status 4 times after it is created, and the sync applies each change as an UPDATE. That is 8 million row updates a day. The engine falls hours behind, and dashboards show trips stuck at "started".

The fix is to append each change as a new row with a version number. In ClickHouse that is a ReplacingMergeTree table on trip_id, read with argMax(status, version). Merges throw away old versions later. The engine now takes 10 million inserts a day and no updates.

Why it matters in production

Most OLAP trouble comes from running the wrong workload on the wrong engine. Analysts pointed at the app's PostgreSQL slow down checkouts. In One2N's fleet telemetry work, each query read a few columns across millions of data points. The team moved it to ClickHouse because the same work on an OLTP system was slower and cost more in storage.

Going the other way fails too. An OLAP engine is happy with tens of heavy concurrent queries, but an app sending it thousands of per-user lookups a second can swamp it. For data that must be queryable within seconds, stream processing feeds the engine straight from Kafka.

See also

On the One2N blog

References

  1. Chaudhuri and Dayal, An overview of data warehousing and OLAP technology (SIGMOD Record, 1997)

  2. Apache Druid docs: Introduction and design

OLAP diagram: PostgreSQL syncs 2M trips a day, each with 4 status changes, into ClickHouse, which stores data in large compressed blocks. In red, applying each change as an UPDATE means 8M updates a day, each rewriting a block, and the engine falls hours behind. In green, appending each change as a new row with a version number gives 10M inserts a day, read back with argMax(status, version).