OLAP Database Systems: OLAP vs OLTP, Columnar Storage and ClickHouse Explained

What is an OLAP database, and why does an analytical query that takes 40 minutes on a transactional database finish in under a second on a columnar OLAP engine? This deck answers that for managers, architects and engineers, in that order of depth.

What the deck covers:

OLAP vs OLTP: two opposite workloads on the same data, and why running analytics on the production database slows checkout for every customer.

Row stores vs column stores: how columnar storage, column pruning, dictionary and run-length encoding, LZ4/ZSTD compression and vectorized (SIMD) execution let an OLAP query read roughly 170x less data than a row store (worked, illustrative arithmetic on a 1 TB, 100-column table).

ClickHouse table design: MergeTree engine, ORDER BY sort keys, PARTITION BY, granules and the sparse primary index, and how the sort key decides what a query can skip.

Data modeling for analytics: normalized 3NF vs dimensional star schema (Kimball), fact and dimension tables.

Five eras of OLAP: MOLAP cubes (Essbase), ROLAP and MPP warehouses (Teradata, Netezza, Greenplum), column stores (C-Store, MonetDB, Vertica), cloud data warehouses (Redshift, BigQuery, Snowflake) and real-time columnar engines (ClickHouse, Apache Druid, Apache Pinot).

Choosing an OLAP engine by data freshness and query concurrency: enterprise BI, customer-facing analytics, data science on a lakehouse (Databricks, Trino, Apache Iceberg) and operational monitoring.

A streaming real-time analytics reference architecture: CDC with Debezium from PostgreSQL and MySQL, Apache Kafka, a sharded and replicated ClickHouse cluster with ClickHouse Keeper.

The business case: dashboard p95 latency, reporting-driven incidents, cost per terabyte scanned and transaction-to-dashboard lag.

Presented by Shiv Iyer, Founder and CEO of ChistaDATA Inc. (24x7 ClickHouse consulting, support and managed services on 100% open-source ClickHouse) and MinervaDB Inc. Figures marked illustrative are worked examples, not benchmarks; test on your own workload.

https://chistadata.com

Transcript

1. C H I S T A D A T A | P OINT O F VIE W OLAP database systems How analytical databases differ from transactional systems, and what that means for your data architecture November 2026 Each bar: one attribute stored contiguously © 2026 ChistaDATA Inc. All rights reserved. ChistaDATA is a trademark of ChistaDATA Inc. This document is the intellectual property of ChistaDATA Inc. and may not be reproduced or distributed without written permission. chistadata.com
2. A M O N D AY M O R N I N G , 9 : 0 0 A . M . "Show me revenue by region for each of the last 36 months." The CFO asks one question. Where it runs decides whether the answer arrives before the meeting starts. Production order database 40+ minutes Row store, OLTP Columnar OLAP engine ClickHouse, same data Under 1 second Meanwhile, checkout slows for every customer while the OLTP query scans the order table. This deck explains why the gap exists, and how to close it. Illustrative scenario: 36 months of order history, roughly 2 billion rows. Timings vary with hardware, schema and indexing.
3. RELEVANT TO MANAGERS · ARCHITECTS · ENGINEERS CHISTADATA | OLAP DATABASE SYSTEMS: A POINT OF VIEW In plain terms: OLTP is the shop till, OLAP is the year-end review OLTP OLAP The shop till The year-end review Online transaction processing Online analytical processing • Serves one customer at a time • Reads a whole year of receipts at once • Records each sale in a blink • Looks at a few details: date, store, amount • Must never slow down the queue • Answers "how did we do?", not "what did Anna buy?" IN DATABASE TERMS IN DATABASE TERMS Short transactions on one row, in milliseconds Big scans over a few columns of millions of rows The lesson: ask the till to do the year-end review and the queue stops. That is what happens when reports run on the production database. © 2026 ChistaDATA Inc. All rights reserved. ChistaDATA is a trademark of ChistaDATA Inc. No part of this document may be reproduced without written permission. chistadata.com 3
4. CHISTADATA | OLAP DATABASE SYSTEMS: A POINT OF VIEW Executive summary OLAP systems are engineered for the opposite workload to OLTP. Treating analytics as "a bigger transactional database" is the most common and most expensive architecture mistake. 01 Opposite workloads OLTP serves thousands of short transactions that touch a few rows each. OLAP serves fewer, larger queries that scan millions to billions of rows across a handful of columns. 02 Separate systems Running both on one engine forces trade-offs in indexing, locking and memory, so organizations typically replicate operational data into a dedicated analytical store through CDC or ETL. 03 Columnar storage Storing each column contiguously lets the engine read only the attributes a query needs, and values of one type compress far better than mixed rows. 04 Vectorized execution Processing data in batches across every core keeps CPUs busy with useful work instead of waiting on disk or interpreting one row at a time. 05 Engine choice Data freshness and query concurrency, not data volume alone, decide between batch warehouses, lakehouses and real-time columnar engines such as ClickHouse. © 2026 ChistaDATA Inc. All rights reserved. ChistaDATA is a trademark of ChistaDATA Inc. No part of this document may be reproduced without written permission. chistadata.com 4
5. CHISTADATA | OLAP DATABASE SYSTEMS: A POINT OF VIEW One topic, three questions: how to read this deck MANAGERS ARCHITECTS ENGINEERS Business owners, product and data leaders Platform, data and solution architects Database, data and backend engineers "What does it cost, what does it risk, and when does it pay back?" "Where should analytical data live, how fresh must it be, and how will it scale?" "Why is a column store faster, and how do I design tables for it?" SLIDES TO WATCH SLIDES TO WATCH SLIDES TO WATCH 2 11 16 18 20 21 8 9 14 18 19 9 11 12 13 14 THE METRIC TO TRACK THE METRIC TO TRACK THE METRIC TO TRACK Dashboard wait time and production incidents caused by reporting Lag from a committed transaction to the dashboard Rows read compared with rows returned per query Look for the marker in the top-right corner of each slide. It names the roles the slide speaks to most. © 2026 ChistaDATA Inc. All rights reserved. ChistaDATA is a trademark of ChistaDATA Inc. No part of this document may be reproduced without written permission. chistadata.com 5
6. RELEVANT TO MANAGERS · ARCHITECTS · ENGINEERS CHISTADATA | OLAP DATABASE SYSTEMS: A POINT OF VIEW Eight terms you will hear today, in one line each Row store Column store Keeps each record together, like one paper form per order. Keeps each field together, like one drawer for every date. Compression Vectorized execution Packs repeated values tightly, so less data is read from disk. Works on thousands of values per step, not one at a time. Star schema Sort key One central sales table with small lookup tables around it. The order data is stored in, so queries can skip blocks. CDC Data freshness Change data capture: copies each change out of the live database. How old the newest data on a dashboard is. Each term returns later in the deck. Come back to this page whenever a word is unfamiliar. © 2026 ChistaDATA Inc. All rights reserved. ChistaDATA is a trademark of ChistaDATA Inc. No part of this document may be reproduced without written permission. chistadata.com 6
7. CHISTADATA | OLAP DATABASE SYSTEMS: A POINT OF VIEW RELEVANT TO MANAGERS · ARCHITECTS · ENGINEERS OLTP and OLAP ask opposite questions of the same data OLTP Online transaction processing OLAP Online analytical processing "What is the status of order 48213?" "What was revenue by region in each of the last 36 months?" Order Date Customer Region Product Amount Order Date Customer Region Product Reads or updates one complete row, located through an index Reads three columns across every row in the range; ignores the rest 1 to 100 All 100+ Milliseconds 10⁶ to 10⁹ 3 of 100+ Seconds Rows per query Columns read Latency target Rows per query Columns read Latency target Amount Illustrative. Ranges describe typical workloads, not hard limits. © 2026 ChistaDATA Inc. All rights reserved. ChistaDATA is a trademark of ChistaDATA Inc. No part of this document may be reproduced without written permission. chistadata.com 7
8. CHISTADATA | OLAP DATABASE SYSTEMS: A POINT OF VIEW RELEVANT TO ARCHITECTS Analytics moved out of the transactional database because the two workloads compete for the same resources 1 SO URCES Transactional databases PostgreSQL, MySQL, Oracle 2 INGE ST ION Change data capture Row-level changes, seconds behind ERP and CRM Orders, customers, finance Batch ETL / ELT Scheduled bulk loads SaaS applications 3 ANALYTICAL STO RE 4 CONS UMPTION Columnar OLAP engine BI dashboards Wide fact tables Ad-hoc SQL Pre-computed aggregates Analysts and data science Finance, sales, operations Hot data on SSD, history on object storage Billing, marketing, support Events and logs Clickstream, IoT, telemetry Customer-facing analytics Embedded in products Streaming Machine learning Kafka topics, continuous inserts Feature generation, training sets Why analytics leaves the transactional database Snapshots and locks Long-running reports hold old row versions open, which delays cleanup and slows concurrent writes. Index overhead Every index added for a report adds cost to each insert and update the application performs. Memory pressure Large scans push the hot working set out of the buffer cache, so customer-facing queries slow down. © 2026 ChistaDATA Inc. All rights reserved. ChistaDATA is a trademark of ChistaDATA Inc. No part of this document may be reproduced without written permission. chistadata.com 8
9. CHISTADATA | OLAP DATABASE SYSTEMS: A POINT OF VIEW RELEVANT TO ARCHITECTS · ENGINEERS Row stores keep each record together; column stores keep each attribute together LO GICAL TABL E RO W-O RIENTED S TORAG E OLTP engines Order Date Region Amount O1 D1 R1 A1 O2 D2 R2 A2 O3 D3 R3 A3 O4 D4 R4 A4 Users see rows and columns. The storage engine decides how those values are laid out on disk, and that layout sets the cost of every query. O1 D1 R1 A1 O2 D2 R2 A2 O3 D3 R3 A3 O4 D4 R4 A4 Page 1 Page 2 Strong at Writing or reading one complete record in a single I/O Weak at Summing Amount means reading every byte of every row CO L UMN-O RIENT ED S TORAG E OLAP engines O1 O2 O3 O4 D1 order.bin D2 D3 D4 date.bin Strong at Aggregating a few attributes: Amount is read as one contiguous file R1 R2 R3 region.bin R4 A1 A2 A3 A4 amount.bin Weak at Single-row inserts touch every column file, so engines batch writes Simplified. Production engines add page headers, null bitmaps, sparse indexes and per-column compression. © 2026 ChistaDATA Inc. All rights reserved. ChistaDATA is a trademark of ChistaDATA Inc. No part of this document may be reproduced without written permission. chistadata.com 9
10. CHISTADATA | OLAP DATABASE SYSTEMS: A POINT OF VIEW Before the numbers: make your guess QUICK POLL · SHOW OF HANDS A dashboard query needs 3 of 100 columns from a 1 TB orders table. Compared with a row store, how much less data does a compressed column store read from disk? A B C D About 2x less About 10x less About 50x less About 170x less Columns help a little One order of magnitude Most of the table is skipped Almost nothing is read Hold your answer. The next slide shows the arithmetic. © 2026 ChistaDATA Inc. All rights reserved. ChistaDATA is a trademark of ChistaDATA Inc. No part of this document may be reproduced without written permission. chistadata.com 10
11. CHISTADATA | OLAP DATABASE SYSTEMS: A POINT OF VIEW RELEVANT TO MANAGERS · ENGINEERS A column store with compression reads about 170x less data than a row store for the same query ~170x Data read for one aggregate query, GB 1 TB table, 100 columns, query references 3 columns Row store 1,000 GB less data read than a row store for the same query Column pruning Column store 30 GB Only Region, Date and Amount are read; the other 97 columns are never touched. Compression Column store, compressed 6 GB Values of one type and similar range compress well; 5x is assumed here. Data skipping Sort keys and partitions let engines skip whole blocks, reducing reads further. Illustrative calculation: uncompressed 1 TB table with equal-width columns; compression ratio of 5x assumed. Actual ratios depend on data types and cardinality. © 2026 ChistaDATA Inc. All rights reserved. ChistaDATA is a trademark of ChistaDATA Inc. No part of this document may be reproduced without written permission. chistadata.com 11
12. CHISTADATA | OLAP DATABASE SYSTEMS: A POINT OF VIEW RELEVANT TO ENGINEERS Compression and vectorized execution cut the cost of every scan CO MP RE SS ION one column, thr ee st ep s Raw values Region column, 8 rows Dictionary encoding 0 = EU, 1 = US, 2 = UK Run-length encoding Value and repeat count EXE CUTIO N Row at a time EU EU EU EU US US UK UK v1 0 0 0 0 1 1 2 LZ4 or ZSTD on the result v2 v3 v4 v5 v6 v7 v8 SUM(amount) called once per row 2 8 calls. Interpretation and branching overhead repeat for every value. 0×4 1×2 2×2 Vectorized v1 Block codec row at a time vs vect orize d v2 v3 v4 v5 v6 v7 v8 compressed bytes Each step works because a column holds one data type with many repeats. SUM(amount) called once per batch 1 call per batch of thousands of values. SIMD instructions add several values per CPU instruction. © 2026 ChistaDATA Inc. All rights reserved. ChistaDATA is a trademark of ChistaDATA Inc. No part of this document may be reproduced without written permission. chistadata.com 12
13. CHISTADATA | OLAP DATABASE SYSTEMS: A POINT OF VIEW RELEVANT TO ENGINEERS In ClickHouse, the sort key decides what a query can skip TABL E DES IGN IN CLICKHOUS E S QL WHAT THE Q UERY READS One partition, split into granules of 8,192 rows, sorted by region then date CREATE TABLE sales ( order_date Date, region LowCardinality(String), product_id UInt32, amount Decimal(12, 2) ) ENGINE = MergeTree PARTITION BY toYYYYMM(order_date) ORDER BY (region, order_date); SELECT toStartOfMonth(order_date), sum(amount) FROM sales WHERE region = 'EU' AND order_date >= '2024-0101' GROUP BY 1; APAC APAC APAC EU EU EU SA SA NA NA NA NA 3 of 12 granules read · 9 skipped using the sparse primary index 1 ORDER BY is the main index. 2 PARTITION BY prunes first. 3 Types are part of the design. Filters on its leading columns let ClickHouse skip whole granules; filters on other columns cannot. Monthly partitions outside the date range are never opened. LowCardinality stores region as a dictionary; narrow numeric types compress better. Illustrative layout. Source: ClickHouse documentation, MergeTree table engine (default index_granularity of 8,192 rows). © 2026 ChistaDATA Inc. All rights reserved. ChistaDATA is a trademark of ChistaDATA Inc. No part of this document may be reproduced without written permission. chistadata.com 13
14. CHISTADATA | OLAP DATABASE SYSTEMS: A POINT OF VIEW RELEVANT TO ARCHITECTS · ENGINEERS Dimensional models trade write efficiency for simpler, faster analytical queries NO RMAL IZE D (3 NF) OLTP design Orders Order line Customer DI MENSIO NAL ( STAR SCHEMA) OLAP design Product Date Category day, month, quarter Supplier Promotion Customer campaign, discount segment, region Sales fact keys + amount, quantity Address Store City Channel Product web, retail category, brand Country Store city, country Revenue by region and category joins 8 tables Revenue by region and category joins 3 tables Each fact is stored once, so updates are cheap and consistent. Analytical queries pay for that with long join chains. Dimensions are denormalized so each query is one fact table plus a few small lookups. Columnar engines often flatten further into one wide table. Source: dimensional modeling as described by R. Kimball, The Data Warehouse Toolkit (1996; 3rd ed. 2013). © 2026 ChistaDATA Inc. All rights reserved. ChistaDATA is a trademark of ChistaDATA Inc. No part of this document may be reproduced without written permission. chistadata.com 14
15. PA R T T W O The physics are settled. The choice of engine is not. 01 How did we get here? Five eras of OLAP 02 How do the systems differ? Columnar storage, compression and vectorized execution are now standard. What separates engines is how fresh the data is and how many people query it at once. Side-by-side comparison 03 Which engine fits? Freshness and concurrency © 2026 ChistaDATA Inc. All rights reserved.
16. CHISTADATA | OLAP DATABASE SYSTEMS: A POINT OF VIEW RELEVANT TO MANAGERS · ARCHITECTS OLAP architecture has moved from pre-built cubes to real-time columnar engines 1990s 2000s Mid 2000s 2010s 2016 onward MOL AP RO LAP CO L UMNAR CLO UD RE AL-TIME Multidimensional cubes Relational warehouses and MPP Column stores Cloud data warehouses Real-time columnar engines Aggregates are pre-computed into proprietary cube files. Fast for known questions; new questions need a cube rebuild. Star schemas on relational databases, later spread across shared-nothing appliances. Research engines show column layout and vectorized execution outperform row stores on analytics. Storage and compute separate; capacity is rented by the query or the hour instead of bought. Open-source engines ingest streams and answer subsecond queries at high concurrency. Examples Examples Examples Examples Examples Codd coins "OLAP" (1993); Essbase Teradata, Netezza, Greenplum C-Store, MonetDB/X100, Vertica BigQuery, Redshift, Snowflake ClickHouse, Apache Druid, Apache Pinot Direction of travel: from answering pre-defined questions overnight to answering any question on fresh data in under a second. Dates are approximate and indicate when each approach reached commercial adoption. Product names are trademarks of their respective owners. © 2026 ChistaDATA Inc. All rights reserved. ChistaDATA is a trademark of ChistaDATA Inc. No part of this document may be reproduced without written permission. chistadata.com 16
17. CHISTADATA | OLAP DATABASE SYSTEMS: A POINT OF VIEW RELEVANT TO MANAGERS · ARCHITECTS · ENGINEERS OLTP and OLAP systems differ on every major design axis Dimension OLTP OLAP Purpose Run the business: record each transaction Understand the business: analyze history Typical query Look up or update one record by key Scan, filter and aggregate large ranges Rows per query Tens Millions to billions Storage layout Row-oriented pages Compressed column files Schema Normalized (3NF) Denormalized star or wide tables Indexing B-tree primary and secondary indexes Sort keys, sparse and skip indexes Write pattern Many small concurrent writes Large batched or streamed inserts Consistency Row-level locks or MVCC, strict ACID Atomic per batch or partition Data volume Gigabytes to low terabytes Terabytes to petabytes Example engines PostgreSQL, MySQL, Oracle, SQL Server ClickHouse, Snowflake, BigQuery, Redshift Typical characteristics; modern engines blur some lines (for example, PostgreSQL extensions add columnar storage, and ClickHouse supports lightweight updates). © 2026 ChistaDATA Inc. All rights reserved. ChistaDATA is a trademark of ChistaDATA Inc. No part of this document may be reproduced without written permission. chistadata.com 17
18. CHISTADATA | OLAP DATABASE SYSTEMS: A POINT OF VIEW RELEVANT TO MANAGERS · ARCHITECTS Tens CO NCURRENCY Thousands The right analytical engine depends on data freshness and query concurrency Enterprise BI and reporting Customer-facing analytics Hundreds of analysts; data refreshed overnight or hourly Thousands of external users; data seconds old; sub-second response Fit: Cloud data warehouse Fit: Real-time columnar engine Snowflake, BigQuery, Redshift ClickHouse, Apache Pinot FOUR QUESTIONS THAT DECIDE THE ENGINE 1 How fresh must the data be? Nightly loads suit a warehouse; seconds-old data needs streaming ingestion. 2 How many queries run at once? Customer-facing use means thousands of concurrent queries, not dozens. Data science and exploration Operational monitoring Few users; large ad-hoc scans over historical and raw data Engineers and alerting on live events, logs and metrics Fit: Lakehouse Fit: Real-time columnar engine Databricks, Trino on Apache Iceberg ClickHouse, Apache Druid 3 What latency is acceptable? Set a p95 target per use case before comparing engines. 4 How is cost incurred? Per-query billing rewards occasional use; steady high load favors provisioned clusters. Hours (batch) DATA FRE SHNES S Seconds (streaming) Engine examples are indicative, not endorsements; many organizations run two or more of these patterns side by side. © 2026 ChistaDATA Inc. All rights reserved. ChistaDATA is a trademark of ChistaDATA Inc. No part of this document may be reproduced without written permission. chistadata.com 18
19. CHISTADATA | OLAP DATABASE SYSTEMS: A POINT OF VIEW RELEVANT TO ARCHITECTS · ENGINEERS A streaming reference architecture keeps production untouched and analytics seconds behind it 1 SOURCES 2 CAPTURE 3 STREAM Orders, billing Customer, catalog 5 CONSUME ClickHouse cluster PostgreSQL MySQL 4 ANALYTICAL STORE Shard 1 CDC Apache Kafka Debezium reads the WAL and binlog, so no queries hit production Durable, replayable change log; decouples sources from the store BI dashboards Shard 2 Replica A Replica A Product APIs Replica B Replica B ClickHouse Keeper coordinates replication SaaS and events Clickstream, CRM Analysts, executives Customer-facing Notebooks Data science THREE DESIGN DECISIONS ARCHITECTS OWN Freshness Availability Scale Set by how often the store pulls from Kafka. Smaller batches mean fresher data and more merge work. Two replicas per shard survive the loss of a node; Keeper keeps replicas consistent. Add shards as data grows. A Distributed table sends each query to every shard in parallel. Illustrative reference design; component choices vary by workload. Debezium, Apache Kafka and ClickHouse are trademarks of their respective owners. © 2026 ChistaDATA Inc. All rights reserved. ChistaDATA is a trademark of ChistaDATA Inc. No part of this document may be reproduced without written permission. chistadata.com 19
20. CHISTADATA | OLAP DATABASE SYSTEMS: A POINT OF VIEW RELEVANT TO MANAGERS The business case rests on four outcomes managers can measure Each outcome has a baseline you can measure today. The business case is the gap between that baseline and the target. 01 02 03 04 FASTER DECISIONS PROTECTED REVENUE LOWER RUN COST FRESHER DATA Dashboard wait time Reporting-driven incidents Cost per terabyte scanned Transaction-todashboard lag p95 latency of the 20 mostused reports Production slowdowns traced to analytical queries, per quarter Monthly analytics infrastructure spend divided by data scanned Minutes between a committed order and its appearance in reports Owner Head of analytics Owner VP engineering Owner Finance, platform lead Owner Data product owner Next step: capture all four baselines during the assessment phase (slide 19), before any platform decision. Metrics are a recommended framework; targets are set per organization during assessment. © 2026 ChistaDATA Inc. All rights reserved. ChistaDATA is a trademark of ChistaDATA Inc. No part of this document may be reproduced without written permission. chistadata.com 20
21. CHISTADATA | OLAP DATABASE SYSTEMS: A POINT OF VIEW RELEVANT TO MANAGERS How ChistaDATA delivers OLAP programs 01 Assess 02 Design 03 Migrate 04 Operate 2 TO 4 WEEKS 3 TO 6 WEEKS 6 TO 12 WEEKS ONGOING • Profile query and ingestion workloads • Measure latency, concurrency and cost baselines • Identify use cases that need a dedicated OLAP store • Select engine and deployment model • Design sort keys, partitions and materialized views • Plan CDC or streaming ingestion • Build pipelines and backfill history • Validate results against source systems • Cut over dashboards and applications • 24x7 monitoring and incident response • Capacity planning and version upgrades • Quarterly performance and cost reviews Deliverable Deliverable Deliverable Deliverable Workload assessment and business case Target architecture and schema blueprint Production OLAP platform Managed service with agreed SLAs 2 Share query logs and data 3 Agree targets for p95 latency, Next steps for your team 1 Name two or three use cases, each with a business owner volumes for a workload review freshness and cost Durations are typical ranges and depend on data volume, source systems and scope. Contact: chistadata.com © 2026 ChistaDATA Inc. All rights reserved. ChistaDATA is a trademark of ChistaDATA Inc. No part of this document may be reproduced without written permission. chistadata.com 21
22. CHISTADATA | OLAP DATABASE SYSTEMS: A POINT OF VIEW Speaker and contact SPE AKER SI CONTACT CHIS TADATA Shiv Iyer Founder and Chief Executive Officer ChistaDATA Inc. GLOBAL SALES (844) 395-5717 24×7 EMAIL info@chistadata.com WEB chistadata.com Book an introductory call on the contact page LINKEDIN linkedin.com/in/thewebscaledba EMAIL ceo@chistadata.com HEADQUARTERS 440 N Barranca Ave #9718 Covina, CA 91723, United States ChistaDATA Inc. delivers ClickHouse consulting, 24×7 support and managed services from the San Francisco Bay Area and 11 global offices. Contact details as published at chistadata.com, November 2026. © 2026 ChistaDATA Inc. All rights reserved. ChistaDATA is a trademark of ChistaDATA Inc. No part of this document may be reproduced without written permission. chistadata.com 22