Linux, macOS, FreeBSD

Conventional wisdom says OLTP query times are measured in milliseconds and OLAP query times are measured in seconds or minutes. That assumption is out of date. On the 100 GB ClickBench `hits` dataset (99.9M rows, 105 columns), the fastest OLAP engines now answer the median analytical query in **148 ms** on a single `c6a.4xlarge` AWS instance — and answer 43 out of 43 queries in under 10 seconds.

Below are the OLAP databases ranked by their published results on [ClickBench](https://benchmark.clickhouse.com/) on `c6a.4xlarge`. ClickHouse maintains both ClickBench and this page; the benchmark is open-source, the methodology is published, and every number below is reproducible from the public scripts against the public data.

## How we ranked these databases

Every number on this page comes from the publicly available [ClickBench](https://benchmark.clickhouse.com/) results on the `c6a.4xlarge` AWS instance against the 100 GB `hits` dataset (99.9M rows, 105 columns): hot-cache query timings across 43 analytical queries, plus the load time and compressed footprint reported by each engine's run. The numbers, scripts, and dataset are all public; results are reproducible from the ClickBench repository.

Cost-per-query, concurrency, and operational complexity are ranked separately in [the best columnar databases](/resources/engineering/best-columnar-databases).

## ClickBench scorecard (c6a.4xlarge, hits 100 GB)

| Engine                  | Wins (of 43) | Median hot | Slowest hot | Failed | Load time | Compressed size |
|-------------------------|--------------|------------|-------------|--------|-----------|-----------------|
| **ClickHouse**          | 32           | 0.148 s    | 9.6 s       | 0      | 5 min     | 14.21 GiB       |
| **DuckDB**              | 10           | 0.348 s    | 6.5 s       | 0      | 2 min     | 19.05 GiB       |
| **Apache Pinot**        | 1 (tie)      | 3.02 s*    | 19.1 s      | 5      | 34 min    | not reported    |
| **Apache Druid**        | 1            | 1.77 s*    | 60.1 s      | 14     | 5.5 hr    | 42.09 GiB       |
| **Trino (Parquet)**     | 0            | 2.72 s     | 36.2 s      | 0      | x         | 13.73 GiB       |

* Median calculated over completed queries only. Failed queries are not counted in the median, but they are counted in the failure column.

The gap between the fastest engine (ClickHouse) and the second fastest (DuckDB) is roughly 2x on the median, but the gap to engines #3–5 is an order of magnitude. Failure count is a binary divider: engines with zero failures (ClickHouse, DuckDB, Trino) can be deployed against an arbitrary analytical workload; engines with 5+ failures (Pinot, Druid) require workload pre-validation.

Latency budget interacts with engine choice as a one-way constraint: an engine that hits a tighter budget can also serve a looser one, but the reverse isn't true. ClickHouse and DuckDB hit sub-second median on this workload, so they can also serve internal BI dashboards refreshed every few seconds; Trino hits a 2.7 s median, so it cannot be retrofitted to a sub-second user-facing dashboard without architectural change.

## 1. ClickHouse

ClickHouse is an open-source columnar database designed for sub-second analytical query latency at billion-row scale. On ClickBench `c6a.4xlarge`, ClickHouse:

- Wins or ties on 32 of 43 queries.
- Posts a median hot-cache latency of 148 ms and a tail of 9.6 s (Q28).
- Compresses the 100 GB dataset to 14.21 GiB, the smallest footprint of any engine in the table, and loads it in 5 minutes.
- Completes every query in the suite.

The architecture is `MergeTree`-family columnar storage with sparse primary indexes, vectorised query execution using SIMD instructions, and asynchronous background merges that keep append-mostly workloads write-efficient. Compute and storage can be separated in ClickHouse Cloud, which keeps data on S3 and scales compute independently. The full deep-dive on the design is in [why columnar databases are fast](/resources/engineering/why-columnar-databases-are-fast).

Typical fit is user-facing dashboards at sub-500ms latency, operational analytics, observability, and streaming ingest workloads where sub-second response on billions of rows is the requirement. Production deployments at Cloudflare, Uber, eBay, GitHub, and Sentry confirm the production envelope at petabyte scale. 

ClickHouse is not an OLTP database for transactional workloads, for which Postgres alongside ClickHouse via CDC is the usual pattern; see [Postgres alongside ClickHouse](/resources/engineering/is-postgres-an-olap-database).

<blockquote style="font-size:16px">"At Coinhall, managing vast amounts of blockchain data efficiently is crucial for our consumer-facing trading platform. Initially, we used BigQuery, but as our data grew, so did its costs and performance issues. After exploring several alternatives, we found ClickHouse to be the clear winner. ClickHouse significantly outperformed other databases we tested like Snowflake, Rockset, and SingleStore, and delivered at 40x cost savings."<p style="font-weight:bold"><a href="https://clickhouse.com/blog/trade-secrets-how-coinhall-uses-clickhouse-to-power-its-blockchain-data-platform">CTO Aaron Choo</a></p>
</blockquote>

## 2. DuckDB

DuckDB is an open-source in-process columnar database, the OLAP equivalent of SQLite. On ClickBench `c6a.4xlarge`, DuckDB:

- Wins 10 of 43 queries (Q5, Q12, Q14, Q16–Q18, Q28, Q32–Q34).
- Posts a median hot-cache latency of 348 ms, 2.35x ClickHouse's median, but still firmly sub-second.
- Tail latency is 6.5 s (Q28).
- Loads the dataset in 2 minutes, the fastest load of any columnar engine in the table.
- Completes every query in the suite.

DuckDB is an in-process query engine with strong performance when the dataset is small enough to fit in memory on a single machine.

## 3. Apache Pinot

Apache Pinot is an open-source real-time OLAP engine, optimised for low-latency user-facing workloads at high concurrency. On ClickBench `c6a.4xlarge`, Pinot:

- Ties ClickHouse on Q0 (0.001 s), the only query where Pinot reaches the leader's latency.
- Posts a median hot-cache latency of 3.02 s across the 38 queries it completes.
- Tail is 19.1 s on Q20 and 14.7 s on Q5, heavy aggregations where the inverted-index strategy doesn't help.
- Fails 5 queries (Q6, Q18, Q21, Q28, Q42); the engine cannot return a result.
- Loads the dataset in 34 minutes, but was unable to load all data, thus did not post a compressed size.

The architecture is segment-based columnar storage with a heavy index toolkit (inverted, sorted, range, JSON, geo, and text indexes) selectable per column. Pinot's design target is the high-QPS, small-range query: thousands of concurrent dashboard requests against a recent slice of data. Pinot is the engine behind LinkedIn's "Who Viewed Your Profile" feature.

Pinot is at its best when a limited set of well-known query shapes needs to serve extreme concurrency in user-facing surfaces. It is a complex multi-component cluster system, so small deployments and ad-hoc analytics workloads (where the index design hasn't been planned in advance, or where the queries need JOINs) are a poor fit.

## 4. Apache Druid

Apache Druid is an older, open-source, real-time analytics engine optimised for time-series and streaming ingest from Kafka. On ClickBench `c6a.4xlarge`, Druid:

- Wins Q24 (0.016 s), a filter pattern that hits its bitmap-index sweet spot.
- Posts a median hot-cache latency of 1.77 s across the 29 queries it completes.
- Tail is 60.1 s on Q35 and 59.3 s on Q14, patterns where ClickHouse runs in 0.25 s and 0.52 s respectively, a 100–230x gap.
- Fails 14 queries, by far the highest failure count in the table.
- Loads the dataset in 5.5 hours and stores it in 42.09 GiB, roughly 3x ClickHouse's compressed footprint.

The architecture is segment-based columnar storage with bitmap indexes on string dimensions, distributed across historical, middle-manager, and broker nodes. Ingestion is streaming-first: each Kafka message becomes queryable within seconds of arrival.

Druid is built for clusters and depends on pre-indexing, so it struggles with ad-hoc analytical queries that don't fit its segment design, and the 14 unsupported query patterns rule it out anywhere the workload isn't known in advance. ClickHouse and Pinot have the edge on high-concurrency low-latency dashboards.

## Federated query engines: Trino

Trino (and its predecessor Presto) is a distributed SQL query engine, not a database. It pushes queries against external storage (Iceberg, Hive, S3 Parquet, Postgres) without owning the data layout. On ClickBench `c6a.4xlarge` against partitioned Parquet on local disk, Trino:

- Completes every query in the suite.
- Posts a median hot-cache latency of 2.72 s and a tail of 36.2 s on Q23.
- Has a per-query floor of roughly 1.3 s; the lightest query in the suite (Q0) still takes 1.26 s, driven by query coordination overhead rather than scan work.
- "Loads" the dataset in 20 seconds because the Parquet files were already on disk; the engine doesn't own ingest.
- Reads 13.73 GiB of Parquet (file size, not the engine's storage footprint, since there isn't one).

Trino's value is federation rather than latency: one SQL surface across heterogeneous sources, no ingest step into a dedicated database, and decoupling of compute lifecycles from data lifecycles. The tradeoffs are operational complexity (catalogs, workers, coordinator, connector configs per source), no native caching or columnar storage layer, and query performance that varies sharply with the source format and partition layout. The 1.3 s coordination floor is structural; Trino cannot serve a sub-second user-facing dashboard regardless of dataset size.

Trino is at its best in lakehouse architectures over Iceberg, Delta, or Hudi where storage independence matters more than query latency, when one SQL surface across multiple databases is the requirement, or when the data already lives in Parquet on object storage and shouldn't move. It is a poor fit for interactive analytics on sub-second budgets, for workloads where consistent latency matters more than storage flexibility, and for teams without the operational capacity to run a multi-component distributed query layer.

## Cloud / serverless data warehouses

The cloud data warehouses post sub-second median latency on ClickBench, but ClickHouse Cloud leads the median by 3-5x and wins 38 of 43 queries, on a 2-node deployment against larger competing configurations (Snowflake 16×XL, Databricks 16×L, Redshift 4×ra3.4xlarge, BigQuery serverless). Load times are an order of magnitude apart in the other direction: Snowflake 42 min, Redshift 32 min, BigQuery 13 min vs. ClickHouse Cloud 18 s and Databricks 45 s.

### Cloud ClickBench scorecard

| Engine                                   | Wins (of 43) | Median hot | Slowest hot | Failed | Load time | Compressed size |
|------------------------------------------|--------------|------------|-------------|--------|-----------|-----------------|
| **ClickHouse Cloud** (2 × 356 GiB nodes) | 38           | 0.109 s    | 1.98 s      | 0      | 18 s      | 9.55 GiB        |
| **Amazon Redshift** (4 × ra3.4xlarge)    | 3            | 0.465 s    | 4.89 s      | 0      | 32 min    | 20.78 GiB       |
| **Snowflake** (16 × XL)                  | 2            | 0.366 s    | 1.27 s      | 0      | 42 min    | 11.46 GiB       |
| **BigQuery** (serverless)                | 0            | 0.544 s    | 1.85 s      | 0      | 13 min    | 8.16 GiB        |
| **Databricks** (16 × L)                  | 0            | 0.546 s    | 3.25 s      | 0      | 45 s      | 9.52 GiB        |

The four cloud warehouses were run on configurations larger than ClickHouse Cloud's 2-node footprint: Snowflake on 16×XL compute, Databricks on a 16×L SQL warehouse, Redshift on a 4-node ra3.4xlarge cluster, and BigQuery serverless (which self-scales). Even with that asymmetry, ClickHouse Cloud leads the median 3-5x and concedes only Q1, Q2, and Q7 to Redshift's light-filter cache profile (with Q6 tied), and Q8 and Q28 to Snowflake. The cloud configurations are not directly comparable to the `c6a.4xlarge` single-instance numbers earlier on this page; cross-reference the two tables with that caveat in mind.

The cloud warehouses reach sub-second median by running 4-16x more compute than ClickHouse Cloud's 2-node deployment, and warehouse cost scales with that size. Driving the warehouses harder will pull latency closer, but at compute spend that doesn't pencil out for an interactive workload. A separate [1B-to-100B row cost-performance comparison](https://clickhouse.com/blog/cloud-data-warehouses-cost-performance-comparison) shows the gap widening with scale: at 10B rows ClickHouse Cloud was 7-13x better cost-per-query than the next-best alternative; at 100B rows, 23-32x better than the largest Snowflake and Databricks warehouses, with BigQuery further behind.

![Fastest OLAP databases](https://cms-new.clickhouse.com/uploads/fastestolapdb_grid_b0dcaa156a.gif)

### ClickHouse Cloud

ClickHouse Cloud is the managed deployment of ClickHouse on AWS, GCP, and Azure, with compute and storage separated and elastic scaling per workload. On the cloud ClickBench leaderboard with 2 × 356 GiB nodes, ClickHouse Cloud wins 38 of 43 queries, posts a median of 109 ms, and tails at 1.98 s on Q28, the same Q28 that hits 9.6 s on the c6a.4xlarge single-node run, showing how added compute compresses the heavy-aggregation tail.

The 2-node deployment loads the dataset in 18 seconds and compresses it to 9.55 GiB, beating Snowflake, Redshift, and BigQuery on load time by 40-140x.

### Snowflake

Snowflake is a traditional cloud data warehouse with separated compute and storage, micro-partition columnar files, and elastic per-warehouse compute. On 16×XL compute (substantially larger than ClickHouse Cloud's 2-node deployment), Snowflake posts a 366 ms median (3.4x ClickHouse Cloud), a 1.27 s tail on Q33, and wins two queries: Q8 (0.366 s) where the larger compute footprint pulls ahead of every other cloud engine, and Q28 (0.883 s) where its plan beats ClickHouse Cloud's heaviest tail (1.98 s). Load time is 42 minutes; compressed size is 11.46 GiB.

The heavy-aggregation queries (Q5, Q16–Q18, Q32–Q34) still trail ClickHouse Cloud by 1.2-2x even at this larger footprint.

### Amazon Redshift

Amazon Redshift is AWS's columnar MPP warehouse. On a 4-node ra3.4xlarge cluster, Redshift posts a 465 ms median, a 4.89 s tail on Q23, zero failures, and a compressed footprint of 20.78 GiB, roughly 2x the other cloud engines. Load time is 32 minutes.

Redshift takes Q1, Q2, and Q7 outright (22-55 ms) and ties ClickHouse Cloud on Q6 (36 ms) — light filter queries where the result-cache profile shows. The heavy patterns are where the cluster strains: Q23 at 4.89 s, Q28 at 4.59 s, Q22 at 3.56 s, Q9 at 3.53 s.

### BigQuery

BigQuery is Google Cloud's serverless data warehouse, with a separated storage layer (Capacitor) and elastic compute (slots). On serverless, BigQuery posts a 544 ms median, a 1.85 s tail on Q13, zero failures, and the smallest compressed footprint of any engine in the entire suite at 8.16 GiB. Wins zero queries; its profile is consistent sub-second across the workload rather than peak speed on any individual query.

### Databricks

Databricks is the managed Spark and Delta Lake platform; ClickBench runs against its SQL Warehouse compute layer. On a 16×L SQL Warehouse (the second largest compute size tested), Databricks posts a 546 ms median query time (the slowest result of all tested), a 3.25 s tail on Q28, and zero failures. Load time is 45 seconds, the second-fastest cloud load after ClickHouse Cloud, and compressed size is 9.52 GiB.

## Choosing between them

The decision is bounded by latency requirement first, deployment shape second, and workload coverage third.

| Workload                                                              | Latency / concurrency requirement              | Recommended                   | Why                                                                                                                       |
|-----------------------------------------------------------------------|------------------------------------------------|-------------------------------|---------------------------------------------------------------------------------------------------------------------------|
| User-facing dashboard, billions of rows                  | sub-500 ms                                     | ClickHouse                    | Full workload coverage; sustains concurrency without per-query index planning; self-hosted or managed cloud                                             |
| The same small-range query repeated at thousands of QPS               | sub-second on one narrow, well-known shape     | Apache Pinot                  | Per-column inverted indexes are tuned to a single query shape; failure rate climbs as soon as the workload diversifies    |
| Notebook / single-machine analytics over local files                  | sub-second on 100 GB-ish                       | DuckDB, chDB                        | Impressive in-process speed on a laptop          |
| Lakehouse over Iceberg / Delta / Hudi                                 | seconds                                        | ClickHouse, Trino, Databricks             | Federated SQL over open table formats on object storage |
| Internal BI and batch reporting            | latency and concurrency aren't constraints     | Snowflake, Redshift, BigQuery | Mature SQL tooling and reporting integrations for scheduled jobs where query time and concurrent users don't drive choice |

For real-time OLAP at sub-second latency on broad analytical workloads, ClickHouse leads both leaderboards: 148 ms median with 32 wins self-hosted on the c6a.4xlarge leaderboard, and 109 ms median with 38 wins on 2 nodes via ClickHouse Cloud on the cloud leaderboard. Pinot earns a spot when the workload narrows to one repeating small-range query at extreme concurrency, where its per-column index toolkit fits — anywhere the query shapes diversify, the failure count on the leaderboard catches up. DuckDB posts genuinely impressive numbers in-process on a single machine. Trino and Databricks fit lakehouse architectures over open table formats, where storage independence matters more than query latency. Snowflake, Redshift, and BigQuery sit in the traditional cloud-DWH bucket for internal BI and batch reporting, where mature SQL tooling matters and neither latency nor concurrency is the binding constraint.

Cost-of-ownership and managed-vs-self-hosted tradeoffs are ranked separately in [the best columnar databases](/resources/engineering/best-columnar-databases). For the broader category overview, see [what is an OLAP database?](/resources/engineering/olap-database) and [what is real-time analytics?](/resources/engineering/what-is-real-time-analytics).

Interested in seeing how ClickHouse works on your data? Get started with ClickHouse Cloud in minutes and receive $300 in free credits.

Get started today

Frequently asked questions

The fastest OLAP databases in 2026 (ranked by ClickBench)

OLTP (online transaction processing) and OLAP (online analytical processing) are the two workload categories every database is built around. OLTP optimises for high-frequency single-row reads and writes with sub-50ms latency; OLAP optimises for aggregations across millions to billions of rows in sub-second time. Postgres, MySQL, and Oracle are OLTP; ClickHouse is OLAP, alongside batch warehouses like Snowflake and BigQuery.

![oltp_olap_uses](/uploads/eng_resource_oltp_olap_uses_f644f5ffb2.png)

## TL;DR

| Axis | OLTP | OLAP |
|---|---|---|
| Purpose | Operational reads and writes | Analytical reads |
| Storage layout | Row-oriented (heap tables, B-trees) | Column-oriented (columnar files, data skipping) |
| Query pattern | Point lookups, single-row mutations | Aggregations, filters, group-bys across wide ranges |
| Response time | Sub-50ms p99 on single-row lookups | Sub-second to seconds across terabyte scans |
| Data volume | Gigabytes to terabytes | Hundreds of gigabytes to petabytes |
| Concurrency | Thousands of concurrent transactions | Tens to thousands of concurrent reads |
| Schema | Normalised (3NF) | Denormalised, star/snowflake, or wide flat tables |
| Indexing | B-tree, hash, primary-key | Sparse primary keys, data skipping, materialised views |
| Consistency | ACID, serialisable transactions | ACID at write, eventual consistency acceptable on read |
| Write profile | High-frequency single-row mutations | Bulk inserts, append-mostly |
| Hardware bias | Memory + IOPS | CPU + sequential I/O bandwidth |
| Canonical examples | Postgres, MySQL, SQL Server, Oracle | ClickHouse, Snowflake, BigQuery, Redshift, Druid |

## What is OLTP?

OLTP is the database category that handles operational, transaction-shaped work like a checkout, a balance check, or a profile update. OLTP systems use row-oriented storage and B-tree indexes to make single-row reads and writes return in milliseconds, and they enforce ACID transactions so partial failures cannot leave inconsistent state.

Postgres, MySQL, SQL Server, Oracle, CockroachDB, and AWS Aurora are the canonical OLTP databases. Workloads in this category are write-heavy at the per-row level. A typical e-commerce platform sees thousands of order inserts, inventory decrements, and session updates per second, each touching a single row. OLTP databases handle this profile by keeping a hot working set in memory, fronting it with B-tree indexes, and serialising concurrent writes through multi-version concurrency control (MVCC). The Postgres documentation describes this transaction model in detail; the engine has been the reference OLTP design since the 1990s.

## What is OLAP?

OLAP is the database category that handles analytical, aggregation-shaped work like a revenue dashboard, an ad-hoc cohort query, or a billion-row time-series scan. OLAP systems use column-oriented storage, vectorised execution, and data skipping to scan millions to billions of rows and return aggregates in sub-second time.

ClickHouse, Snowflake, BigQuery, Amazon Redshift, Apache Druid, and DuckDB are the canonical OLAP databases. Workloads are read-heavy and touch a small subset of columns across a large subset of rows: a query that sums `order_value` and groups by `country` over a year of orders reads two columns out of fifty, scans hundreds of millions of rows, and returns a result of a few hundred rows. Columnar storage makes this efficient. Only the relevant columns are read from disk, compression ratios on column data run 5-20×, and SIMD-accelerated vectorised execution processes tens of millions of rows per second per core. See the [overview of columnar databases](https://clickhouse.com/resources/engineering/what-is-columnar-database) for the storage mechanics in depth.

<blockquote style="font-size:16px">"Our experience with Postgres identified a requirement for high-throughput ingest, coupled with a need for low-latency analytical queries originating from charts and statistics presented to the user. This naturally led us to believe we needed an OLAP/real-time analytical database."<p style="font-weight:bold"><a href="https://clickhouse.com/blog/langchain-why-we-choose-clickhouse-to-power-langchain">Ankush Gola, co-founder of LangChain</a></p>
</blockquote>

## How they differ on storage layout

The storage layout is the architectural choice every other axis follows from. OLTP databases store rows together on disk because operational queries access most of a row's columns at once (read a user, update a session, insert an order line). OLAP databases store columns together on disk because analytical queries access a few columns across many rows.

The numbers follow the layout. On a row-store, a query that aggregates `order_value` across 100 million orders must read the entire heap (every row's full payload), because the values are interleaved with all other columns. On a column-store, the same query reads only the `order_value` column file, compressed 10-20× by columnar encoding (delta, dictionary, ZSTD), and skipped at the granule level by sparse indexes when filters are present. ClickBench, the public OLAP leaderboard at [benchmark.clickhouse.com](https://benchmark.clickhouse.com/), shows columnar engines completing 42-query analytical workloads in 1-10 seconds on a single node; the same workload on Postgres takes minutes to hours.

The trade is symmetric. Inserting a single row into a column-store touches every column file and triggers eventual background merges. The column layout sustains bulk-insert rates of millions of rows per second but is inefficient for single-row mutations.

## How they differ on query patterns

OLTP queries are narrow and frequent: read one row by primary key, update one row, insert a small batch. OLAP queries are wide: scan a column, filter on a few predicates, group by one or two dimensions, aggregate the rest.

A canonical OLTP query that fetches a single customer's most recent five orders:

```sql
SELECT order_id, order_value, created_at
FROM orders
WHERE customer_id = 7842931
ORDER BY created_at DESC
LIMIT 5;
```

This returns in a few milliseconds on Postgres against a billion-row `orders` table when `customer_id` is indexed. The query touches one B-tree page chain and a handful of heap pages.

A canonical OLAP query that totals order value by country for the last quarter:

```sql
SELECT country, sum(order_value) AS revenue
FROM orders
WHERE created_at >= '2026-02-01'
GROUP BY country
ORDER BY revenue DESC;
```

This returns in under a second on ClickHouse against the same billion-row table, because the engine reads only the `country`, `order_value`, and `created_at` column files, prunes partitions, skips granules by sorting key, and aggregates in vectorised batches. On a row-store the query reads the full row payload for every order in the quarter, which is orders of magnitude more I/O.

## How they differ on consistency and concurrency

OLTP and OLAP take different positions on the CAP and consistency trade-offs because their workloads tolerate different things.

OLTP databases guarantee ACID transactions with serialisable or snapshot isolation. A bank transfer between two accounts must debit one and credit the other atomically, or roll back. There is no acceptable intermediate state. Postgres uses MVCC with snapshot isolation by default. OLTP systems handle thousands of concurrent transactions per second by keeping locks short and contention low through row-level locking.

OLAP databases might guarantee ACID at write (an insert either lands or doesn't) but accept eventual consistency on read paths. A dashboard query that runs at 12:00:01 can return data ingested at 12:00:00, with a brief lag through background merges or replication. ClickHouse, Druid, and Pinot use this model deliberately: it's how a column-store sustains millions of rows per second of ingest while keeping read latency low. Concurrency profiles differ too. Traditional OLAP engines (Snowflake, Databricks, BigQuery, Redshift) handle tens of concurrent analytical queries, while modern OLAP engines like ClickHouse can handle hundreds to thousands per node.

## HTAP and CDC: where the line blurs

Some databases are marketed as "unified" or HTAP (hybrid transactional/analytical processing) databases, single systems claiming to run both workloads. They work for narrow joint workloads where transactional and analytical queries share a small footprint, but the underlying architectural trade-offs haven't disappeared: row vs columnar storage, point vs scan I/O, lock-heavy vs lock-light concurrency. See the deep dive on [unifying OLTP and OLAP](https://clickhouse.com/resources/engineering/unifying-oltp-and-olap) for the full argument.

The standard production pattern in 2026 uses two dedicated databases connected by change data capture (CDC), not a unified one. Postgres or MySQL serve writes; ClickHouse, Snowflake, or BigQuery serve analytical reads; a CDC pipeline replicates between them in seconds.

## When to choose OLTP

Pick an OLTP database when the workload is mostly per-row writes and point reads. Specifically:

- Single-row reads or writes account for the majority of query volume.
- Tail latency must stay below 50ms (checkouts, payments, real-time leaderboards, session state).
- Concurrent transaction rate exceeds 1,000 per second.
- Transactional integrity matters, so multi-row updates must be atomic.
- Data volume sits in the gigabytes to low terabytes, a range Postgres, MySQL, and Oracle handle without partitioning gymnastics.

Postgres is the default choice for greenfield OLTP.

![when_to_use_olap_oltp](/uploads/eng_resource_when_to_use_olap_oltp_b8a77fea7e.png)

## When to choose OLAP

Pick an OLAP database when the workload is mostly aggregations and the data volume exceeds what a row-store can scan quickly. A workable heuristic:

- Reads scan large ranges, and perform aggregations and GROUP BYs.
- The write path is bulk inserts or streaming ingest, not single-row mutations at high concurrency.
- Total data volume exceeds a terabyte, or query latency on a row-store has crossed multi-second territory.
- Workloads include dashboards, ad-hoc exploration, time-series analytics, or log/metric search.

Traditional cloud data warehouses (Snowflake, BigQuery, Redshift, Databricks SQL) suit batch-oriented BI reporting with minutes-to-seconds latency tolerance. Real-time OLAP engines (ClickHouse, Druid, Pinot) suit sub-second user-facing analytics and streaming ingest. For a speed-ranked shortlist with ClickBench numbers per candidate, see the [fastest OLAP databases](/resources/engineering/fastest-olap-databases).

OLTP vs OLAP

OLAP operations are the canonical analytical actions performed on a multidimensional data model: drill-down, roll-up, slice, dice, and pivot. The vocabulary originated with OLAP cubes in the 1990s, after E.F. Codd's 1993 paper *Providing OLAP to User-Analysts*, and persists in columnar OLAP, where each operation maps directly to a SQL pattern over a fact table and its dimensions. For the broader category overview, see the [OLAP primer](/resources/engineering/what-is-olap).

## How OLAP operations map to SQL

Every OLAP operation has a direct SQL equivalent. The cube model treats data as a multidimensional structure where dimensions (time, geography, product) intersect with measures (revenue, units), but the underlying mechanics are filters and groupings. Drill-down and roll-up change the `GROUP BY`. Slice and dice change the `WHERE`. Pivot changes the result orientation. Columnar engines like ClickHouse, Snowflake, and BigQuery execute these operations against fact tables on demand, with no pre-aggregated cube required. The examples below use a small star schema with a `sales` fact table dimensioned by date, product, and region.

## Drill-down

Drill-down navigates from summary to detail by adding a finer-grained dimension to the grouping. An analyst viewing annual revenue per region drills down by adding month or day to the `GROUP BY` clause, exposing the same measure at a smaller grain. The operation does not change the underlying data, only the resolution at which it is read. Drill-down is the inverse of roll-up.

```sql
-- Starting view: revenue by region
SELECT region, SUM(revenue) AS revenue
FROM sales
GROUP BY region;

-- Drill down to month within region
SELECT region, toStartOfMonth(date) AS month, SUM(revenue) AS revenue
FROM sales
GROUP BY region, month
ORDER BY region, month;
```

## Roll-up

Roll-up aggregates from detail to summary by removing a dimension from the grouping, or by replacing a fine-grained column with a coarser one (day → month → year). Roll-up produces the totals that dashboards present at the top of the page (revenue across all regions, units across all products) without exposing transaction-level rows. The `ROLLUP` keyword, standardised in SQL:1999, performs hierarchical roll-up across multiple columns in a single query.

```sql
-- Hierarchical roll-up: subtotals at region, region+product, and grand total
SELECT region, product, SUM(revenue) AS revenue
FROM sales
GROUP BY ROLLUP(region, product);
```

## Slice

Slice filters on a single dimension to extract a two-dimensional view of the cube. From a sales cube spanning region × product × time, slicing on `region = 'EMEA'` returns only the EMEA plane. In SQL, slice is a `WHERE` clause with one predicate against one dimension. The result narrows the analytical surface without changing what is measured.

```sql
-- Slice: EMEA only, revenue by product over time
SELECT product, toStartOfMonth(date) AS month, SUM(revenue) AS revenue
FROM sales
WHERE region = 'EMEA'
GROUP BY product, month;
```

## Dice

Dice filters on multiple dimensions simultaneously to extract a sub-cube. Where slice picks a plane, dice picks a smaller box. A typical predicate set might combine `region IN ('EMEA','APAC')` with `date BETWEEN '2026-01-01' AND '2026-03-31'` and `product = 'Pro'`. Dice maps to a `WHERE` clause with multiple predicates across multiple dimensions. It is the most common operation in production, since analytical queries rarely scan the full cube.

```sql
-- Dice: EMEA + APAC, Q1 2026, Pro tier only
SELECT region, product, SUM(revenue) AS revenue
FROM sales
WHERE region IN ('EMEA', 'APAC')
  AND date BETWEEN '2026-01-01' AND '2026-03-31'
  AND product = 'Pro'
GROUP BY region, product;
```

## Pivot

Pivot re-orients the result so that values of one dimension become column headers rather than rows, turning a long table into a wide one. Microsoft SQL Server introduced a `PIVOT` keyword in 2005, and Oracle, Snowflake, BigQuery, and DuckDB followed; PostgreSQL exposes equivalent behaviour through the `crosstab` function in `tablefunc`. ClickHouse does not have a `PIVOT` keyword, instead it is expressed with conditional aggregates such as `sumIf`, documented in the [ClickHouse pivot knowledge base](https://clickhouse.com/docs/knowledgebase/pivot).

```sql
-- Pivot: revenue per product, with one column per region
SELECT
  product,
  sumIf(revenue, region = 'EMEA') AS emea,
  sumIf(revenue, region = 'APAC') AS apac,
  sumIf(revenue, region = 'AMER') AS amer
FROM sales
GROUP BY product;
```

## Drill-across

Drill-across combines measures from two separate fact tables that share at least one common dimension. A common case is joining a `sales` fact and a `returns` fact on `product` and `date` to compute net revenue. The operation appears constantly in production analytics, where the cube model fragments into multiple star schemas that Kimball-style modelling calls a fact constellation.

```sql
-- Drill-across: net revenue = sales − returns
SELECT
  s.product,
  s.revenue - r.refund_amount AS net_revenue
FROM (SELECT product, SUM(revenue) AS revenue FROM sales GROUP BY product) AS s
LEFT JOIN (SELECT product, SUM(refund_amount) AS refund_amount FROM returns GROUP BY product) AS r
  USING (product);
```

## MOLAP, ROLAP, and HOLAP

The five operations are vocabulary rather than architecture. The same drill-down or pivot runs differently depending on the OLAP implementation model the engine uses.

| Model | How data is stored | Example engines | Trade-off |
|---|---|---|---|
| MOLAP | Pre-aggregated multidimensional cubes | Essbase, Microsoft Analysis Services | Fast reads, slow rebuilds, cube explosion at high cardinality |
| ROLAP | Relational tables, aggregates computed on demand | ClickHouse, Snowflake, BigQuery, Druid | Flexible schema, scales with columnar engines, no pre-build cost |
| HOLAP | Hybrid (summaries pre-aggregated, detail relational) | SAP BW, older Microsoft AS modes | Compromise on both axes; complex to operate |

ROLAP became the standard model after columnar engines made on-demand aggregation fast enough to displace pre-built cubes. See [what is an OLAP cube](/resources/engineering/what-is-olap-cube) for the cube-to-columnar history, and [what is a columnar database](/resources/engineering/what-is-columnar-database) for the storage model that ROLAP runs on.

## FAQ

**** 

**** 

**** 

**** 


Resource hub

Engineering

The fastest OLAP databases in 2026 (ranked by ClickBench)

OLTP vs OLAP

What are OLAP operations?