OLAP Database Systems: Architecture, Engineering & Operations

ChistaDATA™ event ts latency EXECUTIVE TECHNICAL BRIEFING · OCTOBER 2026
tenant OLAP Database Systems: Architecture, Engineering & Operations country revenue Why columnar database systems fit analytics so well, how they are built, and what it takes to run them in production. Six columns. Read only the ones you need. SPEAKER AUDIENCE C L A S S I F I C AT I O N Shiv Iyer Founder and CEO, ChistaDATA Inc. CTOs, data platform leaders, architects and senior engineers ChistaDATA Inc. proprietary. Copyright and trademark protected. ChistaDATA™ OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 01 / 65

LEGAL NOTICE AND READING GUIDE Before we start Copyright, trademarks
and permitted use © 2026 ChistaDATA Inc. All rights reserved. This presentation, including its text, diagrams, exhibits, code samples, frameworks How to read this deck and models, is the intellectual property of ChistaDATA Inc. and is protected by copyright law. Every slide title states its conclusion. Read the titles alone and you get the argument. ChistaDATA and the ChistaDATA logo are trademarks of ChistaDATA Inc. MinervaDB is a trademark of MinervaDB Inc. Numbered exhibits carry a source line. Where a number is a teaching fsyncDATA is a trademark of its owner within the MinervaDB group. No part of this material may be reproduced, adapted, redistributed, published or used to deliver training or consulting to example rather than a measurement, the source says illustrative. others, in any form or by any means, without prior written permission from ChistaDATA Inc. Worked examples use ClickHouse 26.8 LTS. The concepts apply to columnar systems generally. ClickHouse is a registered trademark of ClickHouse, Inc. Snowflake, BigQuery, Amazon Redshift, Databricks, Apache Druid, Yellow notes are field humour. They are also usually true. Apache Pinot, Apache Iceberg, Apache Parquet, Apache Kafka, DuckDB and other product names belong to their respective owners and appear here for identification only. ChistaDATA Inc. is independent of these vendors and projects, and none of them endorses this material. FIELD HUMOUR No columns were harmed in the making of this deck. Several rows were ignored on purpose. ChistaDATA™ OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 02 / 65

YOUR SPEAKER Shiv Iyer has spent two decades making databases
fast Founder and CEO, ChistaDATA Inc. and MinervaDB Inc. Shiv Iyer founded and runs ChistaDATA Inc., which works only on ClickHouse, and MinervaDB Inc., a vendor-neutral database infrastructure company trusted by more than 900 enterprises. He has spent over two decades as a database architect and engineer. His work covers database internals, performance engineering, distributed and columnar systems, SQL and index optimisation, disk I/O, migration and security, across SQL, NoSQL, NewSQL and analytics platforms. EXHIBIT 1 Career path, simplified engineering MySQL AB He speaks at database conferences including Percona Live and MariaDB Fest and writes as thewebscaledba. web-scale ops PalominoDB Sun Microsystems MinervaDB, ChistaDATA Founder Percona VP, APAC fsyncDATA linkedin.com/in/thewebscaledba · chistadata.com · minervadb.com · fsyncdata.io Engineering work for PayPal, AOL, the BBC and National Geographic, among others. Source: Public profiles of Shiv Iyer FIELD HUMOUR Has been asked “why is the dashboard slow?” more times than any human should. Usually the answer was SELECT *. ChistaDATA™ OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 03 / 65

WHO WE ARE The work in this deck comes from
running these systems for clients EXHIBIT 2 Three companies, one engineering bench MinervaDB ChistaDATA fsyncDATA Vendor-neutral database infrastructure ClickHouse specialists India, inside global capability centres Consulting · 24×7 support · remote DBA Architecture, performance, upgrades Database and data platform operations Managed services Enterprise support and managed ClickHouse for GCC data estates SQL, NoSQL, NewSQL, columnar and Real-time analytics, observability, Home of the fsyncDATA Circle, a cloud data platforms product analytics at scale no-vendor club for GCC data leaders 900+ enterprises · minervadb.com Owner of this IP · chistadata.com fsyncdata.io Source: minervadb.com, chistadata.com, fsyncdata.io Principal-level Vendor-neutral 24×7 Engineers who have run these systems in production do the work We don’t resell licences, so the advice doesn’t depend on one Support and remote DBA coverage on the engines our clients actually run ChistaDATA™ OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 04 / 65

EXECUTIVE SUMMARY Columnar systems win on analytics because they read
less and do more per cycle 01 Analytics is a scanning problem 03 The engine matters as much as the layout 05 Most slow systems are modelled wrong, not under-sized Analytic queries touch few columns and very many rows. Row stores read whole rows to answer them, so most of the I/O is wasted. Vectorized execution, SIMD, sparse indexes and data skipping keep the CPU fed. Columnar storage with a row-at-a-time executor leaves most of the gain on the table. Sort keys, partitioning, insert batching and types cause more incidents than hardware does. These are design decisions you make once. 02 Columnar layout removes most of that waste 04 Architecture choices set the cost curve 06 Operations decide whether it stays fast Storing each column separately means reading only what the query names. Similar values next to each other also compress far better. Shared-nothing or separated storage, sharding keys, replication and pre-aggregation decide what you pay at ten times today’s volume. Parts, merges, replication lag, upgrades and cost need monitoring and rehearsal. Teams that rehearse recovery spend less time recovering. For leadership: a columnar platform is the right foundation for analytics and AI-ready data. Whether it saves money depends on modelling and operations discipline, and that’s where we spend most of this session. ChistaDATA™ OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 05 / 65

AGENDA Five sections take us from first principles to production
S1 · slides 7–16 S2 · slides 17–30 S3 · slides 31–42 Why OLAP is different Columnar fundamentals Architecture Two workloads, one misunderstanding, and how the industry got to columnar. Layout, compression, vectorized execution, indexes and data skipping. Write path, MPP, separated storage, distributed queries, replication, lakehouse. S4 · slides 43–54 S5 · slides 55–62 Close · slides 63–65 Engineering Operations What to do next Modelling, sort keys, partitions, ingestion, updates, query tuning, benchmarks. Observability, HA/DR, capacity, upgrades, FinOps, incidents, maturity. Takeaways, a 90-day adoption plan, and questions. FIELD NOTE ChistaDATA™ Questions are welcome at any point. If a question needs a whiteboard, we’ll park it for the end. OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 06 / 65

Why OLAP is a different animal Analytics asks a different
question of the database than a checkout page does. Once you see the shape of the work, the case for columnar storage mostly makes itself. S1 OLTP and OLAP, side by side What an OLAP engine has to deliver Anatomy of an analytic query Thirty years of columnar ideas Why row stores struggle with scans Today's OLAP landscape How dashboards get slow Quiz: which workload is this? SECTION 1 OF 5 · SLIDES 7–16 ChistaDATA™ S1 OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 07 / 65

S1 · TWO WORKLOADS OLTP touches a few rows; OLAP
touches every row EXHIBIT 3 Same table, two very different access patterns OLTP · the checkout page OLAP · the board report GROUP BY billions of rows in 12 rows out Thousands of requests a second. Each touches a few whole rows. A few big queries a minute. Each reads a few columns of every row. Latency budget: milliseconds. Writes are constant. Latency budget: seconds. Data arrives in batches or streams. Source: ChistaDATA analysis; schematic FIELD NOTE ChistaDATA™ S1 OLTP reads rows. OLAP reads columns. Most of this deck follows from that one sentence. OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 08 / 65

S1 · ANATOMY OF A QUERY An analytic query reads
few columns across billions of rows A TYPICAL DASHBOARD QUERY EXHIBIT 4 SELECT country, sum(revenue) AS rev FROM events WHERE event_time >= now() - INTERVAL 30 DAY GROUP BY country ORDER BY rev DESC LIMIT 10; 3 / 100 ~1.1 B columns referenced rows in the 30-day window Columns touched by the query events · 100 columns · 4.2 billion rows event_time country revenue FIELD HUMOUR Nobody has ever opened a BI tool and asked for SELECT * on 4 billion rows. Well, nobody who kept the job. Blue: the 3 columns the query needs. Grey: the 97 it doesn't. Source: ChistaDATA analysis; table shape illustrative ChistaDATA™ S1 OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 09 / 65

S1 · WHY ROW STORES STRUGGLE Row stores read every
column to use three of them EXHIBIT 5 Rows are stored together, so columns come along for the ride EXHIBIT 6 Row store: rows packed into pages I/O spent vs I/O useful Bytes read for the query on slide 9 8 KB page 1 100% Row store 8 KB page 2 3% Column store ~3% (before compression) 8 KB page 3 The row store reads about 33 bytes for every byte the query uses. Blue = needed fields. To get them, every page is read. Source: ChistaDATA analysis; schematic Bytes used Source: ChistaDATA analysis; illustrative, 3 of 100 equal-width columns FIELD HUMOUR A row store answering an analytics query is reading the whole phone book to count the people called Iyer. ChistaDATA™ S1 OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 10 / 65

S1 · HOW DASHBOARDS GET SLOW Row-store dashboards degrade quietly,
then all at once EXHIBIT 7 How a reporting database ages: latency against data volume p95 latency Row store, scaled up Columnar engine, scaled out Month 9 "Add another index. And a replica." 2 min Month 20 "The dashboard now emails a PDF." Month 3 "The dashboard is a bit slow." 10 s Month 14 "We bought the biggest box they sell." 1s 100 GB 1 TB 10 TB 100 TB data volume → Source: ChistaDATA field observations; curves illustrative, log scales FIELD NOTE ChistaDATA™ S1 Indexes and bigger hardware buy months, not years. The fix that lasts is changing how the data is laid out. OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 11 / 65

S1 · WHAT OLAP NEEDS OLAP engines are judged on
six things, and throughput leads EXHIBIT 8 Six requirements of an analytic workload Scan throughput Cost per query Compression NEED WHAT IT MEANS IN PRACTICE Scan throughput GB/s per core, not rows/s Compression Store less, read less, cache more Parallelism Every core and every node on one query Concurrency Hundreds of dashboard users at once Freshness Seconds from event to chart Cost per query The CFO reads this column FIELD HUMOUR Freshness Parallelism Freshness is the one row stores win. It's also the one people forget to ask the columnar vendor about. Concurrency Row store used for analytics Columnar OLAP engine Source: ChistaDATA assessment framework; scores illustrative ChistaDATA™ S1 OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 12 / 65

S1 · A SHORT HISTORY Columnar analytics took thirty years
to become the default EXHIBIT 9 Milestones in columnar analytics 1990s 2005 2012 2014 2017+ Sybase IQ C-Store Amazon Redshift Snowflake Iceberg / Delta first commercial column store Stonebraker et al., becomes Vertica columnar warehouse as a service storage and compute separated open table formats, lakehouse 1990s 2010 2013 2016 MonetDB Dremel paper Apache Parquet ClickHouse CWI research, vectorised columns Google; nested columnar at scale open columnar file format open-sourced by Yandex Source: Public papers and vendor announcements; ChistaDATA compilation FIELD NOTE ChistaDATA™ S1 None of these ideas is new. What changed is that cheap cores, fast NVMe and object storage made them the default. OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 13 / 65

S1 · THE LANDSCAPE Every OLAP engine today is columnar;
they differ in where they run EXHIBIT 10 OLAP systems by category (examples, not a ranking) Real-time columnar Cloud data warehouse Lakehouse Embedded / in-process self-managed or managed fully managed service open formats on object storage runs inside the app ClickHouse Snowflake Databricks DuckDB Apache Druid Google BigQuery Trino + Iceberg chDB Apache Pinot Amazon Redshift Spark SQL Typical fit Enterprise BI, elastic capacity pay per use Typical fit Data engineering and ML on one copy of the data StarRocks Typical fit Sub-second queries on fresh data at high concurrency Typical fit Local analysis, notebooks and edge devices Source: ChistaDATA market map, October 2026 FIELD NOTE ChistaDATA™ S1 Most enterprises we work with run two or three of these side by side. Picking the right one per workload matters more than picking a winner. OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 14 / 65

S1 · QUIZ: WHICH WORKLOAD IS THIS? Name the workload
before you name the database Six real requests we see in client estates. Cover the tags and ask the room. Authorise a card swipe at the point of sale OLTP One account, one row, under 100 ms. Revenue by region and month for the CFO's pack OLAP Add an item to a shopping cart OLTP OLAP A point lookup that needs an aggregate behind it. Scan a year of orders, group by two columns. Live operations wall showing orders this minute Few columns, every row, many GROUP BYs. SPEAKER NOTE Ask the room first, then reveal. Somebody always says the fraud score is OLTP. They are half right, which is S1 HTAP Funnel conversion over 2 billion click events A single small write. Postgres is happy. ChistaDATA™ Fraud score at checkout using 90 days of history OLAP Database Systems: Architecture, Engineering & Operations Real-time OLAP Analytic query, data seconds old. exactly the point. © 2026 ChistaDATA Inc. All rights reserved. 15 / 65

S1 · SECTION SUMMARY If the work is scans and
aggregates, the storage must be columnar EXHIBIT 11 The three-question shape test Reads many rows per query? no millions or more 01 Workload shape decides the engine 02 Row stores fail slowly 03 The market has converged on columnar Point reads and writes want rows. Scans and aggregates want columns. Stay on OLTP tune the row store yes Uses a few of many columns? aggregates, filters, GROUP BY Indexes and hardware hide the problem until data passes a few terabytes. yes Data mostly appended? yes Columnar OLAP engine fits events, logs, facts Two "no" answers out of three: ask whether you need an analytics engine at all. The real choice now is where it runs and how fresh the data has to be. Source: ChistaDATA assessment practice FIELD NOTE ChistaDATA™ S1 Next: what a columnar engine actually does differently, from bytes on disk to CPU registers. OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 16 / 65

Columnar Storage: The Five Unfair Advantages Store each column on
its own and an analytic query gets to read less, compress harder, and run tight loops the CPU likes. This section covers the mechanics, one picture at a time. S2 Row layout vs column layout Vectorized execution and SIMD The I/O arithmetic Late materialization (PREWHERE) Dictionary, RLE and delta encoding Sparse indexes, granules and data skipping Codecs: LZ4, ZSTD, Gorilla Why ORDER BY is the real index SECTION 2 · SLIDES 17–30 ChistaDATA™ S2 OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 17 / 65

S2 · LAYOUT Row stores keep records together; column stores
keep attributes together EXHIBIT 12 The same 24 values, laid out two ways on disk Logical table: sales Row store: rows written one after another id date country revenue 1 2026-10-01 IN 120 2 2026-10-01 US 75 3 2026-10-02 IN 210 4 2026-10-02 DE 40 id.bin 5 2026-10-03 IN 95 1 6 2026-10-03 US 130 6 rows × 4 columns, 24 values 1 10-01 IN 120 2 10-01 row 1 US 75 3 10-02 row 2 IN 210 4 10-02 row 3 DE 40 5 row 4 10-03 IN 95 6 10-03 row 5 US 130 row 6 SUM(revenue) reads all 24 values to use 6 of them Column store: one file per column 2 date.bin 3 4 5 6 country.bin 10-01 10-01 10-02 10-02 10-03 10-03 IN US IN revenue.bin DE IN US 120 75 210 40 95 130 SUM(revenue) opens one file and reads exactly 6 values Same data and the same bytes. Only the order on disk changed. Source: ChistaDATA illustration FIELD NOTE ChistaDATA™ S2 An OLTP query wants one whole row. An analytic query wants one column across millions of rows. Pick the layout that matches the question. OLAP Database Systems: Architecture, Engineering & Operations FIELD HUMOUR A row store answering SUM(revenue) is reading the whole phone book to count the people called Iyer. © 2026 ChistaDATA Inc. All rights reserved. 18 / 65

S2 · THE I/O ARITHMETIC Reading 3 of 100 columns
cuts I/O by two orders of magnitude EXHIBIT 13 Bytes read for one query that touches 3 columns ≈ 333× less data read for the same answer Row store reads all 100 columns 800 GB 400 s → 1.2 s time to read it at 2 GB/s sequential, a single fast NVMe drive (illustrative) Column store 24 GB reads 3 of 100 columns ÷ 33 FIELD HUMOUR The row store isn't slow. It's diligently reading 97 columns nobody asked for. Columns + compression same 3 columns at ~10× ≈ 2.4 GB ÷ 10 1 billion rows × 100 columns × 8 bytes per value on average Source: ChistaDATA analysis; illustrative arithmetic ChistaDATA™ S2 OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 19 / 65

S2 · COMPRESSION, PART 1 Columns compress well because neighbouring
values look alike One country column, encoded twice EXHIBIT 14 1 · Raw String column 2 · Dictionary + codes India 3 · Sorted, run-length encoded India × 4 0 → India USA USA × 2 Germany × 2 1 → USA India One type per column 3 (value, count) pairs instead of 8 values 2 → Germany dictionary India Germany 1 0 0 2 1 0 2 USA 8 one-byte codes India Few distinct values sort + RLE 0 Why it works plus a 3-entry dictionary Germany Neighbours look alike after sorting Raw text ≈ 46 B Dictionary codes ≈ 8 B + dict Sorted RLE ≈ 6 B + dict ≈ 46 bytes of text Source: ChistaDATA illustration; byte counts illustrative In ClickHouse, LowCardinality(String) is dictionary encoding you declare in the schema. It pays off below roughly 10,000 distinct values per column. ChistaDATA™ S2 OLAP Database Systems: Architecture, Engineering & Operations FIELD HUMOUR Storing "India" a billion times is a choice. Usually not a deliberate one. © 2026 ChistaDATA Inc. All rights reserved. 20 / 65

S2 · COMPRESSION, PART 2 Specialised codecs turn timestamps and
metrics into near-zero bytes EXHIBIT 15 Codec chains: reshape first, then compress ts DateTime DoubleDelta ZSTD(1) ts.bin on disk 1727740800, …860, …920, …981 deltas 60, 60, 61 → 0, 1 general-purpose pass specialised codec, then general cpu Float64 Gorilla ZSTD(1) cpu.bin on disk 41.2, 41.3, 41.3, 41.1 XOR with neighbour: few bits general-purpose pass specialised codec, then general Specialised codecs reshape the numbers so the general codec has repetition to find. Source: ChistaDATA illustration CREATE TABLE metrics ( ts DateTime CODEC(DoubleDelta, ZSTD(1)), host LowCardinality(String), cpu Float64 CODEC(Gorilla, ZSTD(1)), bytes UInt64 CODEC(T64, ZSTD(3)) ) ENGINE = MergeTree ORDER BY (host, ts); ChistaDATA™ S2 OLAP Database Systems: Architecture, Engineering & Operations CODEC TRADE-OFF LZ4 Fastest to decompress, lower ratio ZSTD(1–3) Smaller files, more CPU on insert Default LZ4 through 26.8 LTS; ZSTD(3) from 26.9 © 2026 ChistaDATA Inc. All rights reserved. 21 / 65

S2 · COMPRESSION RATIOS Compression depends on the data more
than on the engine EXHIBIT 16 MEASURE YOUR OWN, PER COLUMN Typical compression ratio by column type Sorted timestamps SELECT name, formatReadableSize(data_compressed_bytes) AS disk, round(data_uncompressed_bytes / data_compressed_bytes, 1) AS ratio FROM system.columns WHERE database = 'prod' AND table = 'events' ORDER BY data_compressed_bytes DESC LIMIT 10; 10–50× DoubleDelta + ZSTD Low-cardinality strings 8–30× dictionary Sorted integer keys 4–12× Delta / T64 Free text, log lines 3–8× ZSTD Float metrics FIELD HUMOUR UUIDs are random by design. Trying to compress them is how engineers learn humility. 2–6× Gorilla UUIDs and hashes ≈ 1× nothing helps 1× 2× 5× 10× 20× 50× 100× compression ratio, log scale Source: ChistaDATA field observations; illustrative ranges, your data will disagree ChistaDATA™ S2 OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 22 / 65

S2 · VECTORIZED EXECUTION Vectorized engines process columns in blocks,
not one row at a time EXHIBIT 17 Same plan, two ways of moving data through it Tuple at a time: the classic iterator model next() is called once per row Scan Filter Aggregate ≈ 1,000,000,000 calls for 1B rows Branchy code, cold caches Block at a time: vectorized execution One call per block of up to ~65k rows Scan Filter Aggregate ≈ 15,000 calls for 1B rows Tight loops over plain arrays = data handed between operators Source: ChistaDATA illustration; block size from ClickHouse max_block_size default Why it matters: per-row overhead (virtual calls, type checks, branches) is paid once per block instead of once per row. The work left over is a loop the compiler can vectorize. ChistaDATA™ S2 OLAP Database Systems: Architecture, Engineering & Operations FIELD HUMOUR Calling a function a billion times to add up a column is the database equivalent of paying for groceries one coin at a time. © 2026 ChistaDATA Inc. All rights reserved. 23 / 65

S2 · SIMD SIMD lets one instruction work on many
column values at once Adding eight pairs of integers, scalar vs SIMD EXHIBIT 18 Scalar: one value per instruction AVX2: one 256-bit register holds 8 × Int32 add a[0] + b[0] → 4 add a[1] + b[1] → 8 a 3 7 1 9 4 2 8 5 b 1 1 2 2 3 3 4 4 3 11 7 5 12 9 add a[2] + b[2] → 3 add a[3] + b[3] → 11 add a[4] + b[4] → 7 vpaddd ymm0, ymm1, ymm2 add a[5] + b[5] → 5 add a[6] + b[6] → 12 a+b 4 8 add a[7] + b[7] → 9 8 instructions 1 instruction, 8 results Source: ChistaDATA illustration Columns make SIMD easy: one type, stored contiguously, with the same operation applied to every value. ChistaDATA™ S2 ClickHouse x86 builds require AVX2 from 26.6. Check older hosts before an upgrade: grep -c avx2 /proc/cpuinfo OLAP Database Systems: Architecture, Engineering & Operations FIELD HUMOUR Your CPU has been able to do eight things at once for a decade. Row stores just never asked. © 2026 ChistaDATA Inc. All rights reserved. 24 / 65

S2 · LATE MATERIALIZATION Filter on narrow columns first, then
fetch the wide ones EXHIBIT 19 How PREWHERE avoids reading wide columns Read the narrow filter columns for every row country, event_date: a few GB Evaluate the filter granule by granule SELECT url, user_agent FROM hits WHERE country = 'IN' AND event_date = today(); -- ClickHouse moves selective WHERE -- conditions to PREWHERE on its own -- (optimize_move_to_prewhere = 1) drop granules with no matches 98% ≈ 2% of granules survive of the rows never have their url or user_agent decompressed 20M of 1B rows FIELD HUMOUR Read wide columns only there Late materialization: the database version of not unpacking the suitcase until you know you're staying. Source: ChistaDATA illustration; selectivity illustrative ChistaDATA™ S2 OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 25 / 65

S2 · SPARSE PRIMARY INDEX A sparse index points to
blocks of rows, not to rows EXHIBIT 20 Inside one data part: index → granule → column block primary.idx (held in memory) Granules in the part Column files mark 0 (7, 10-01 00:00) granule 0 · rows 0–8,191 block 0 block 0 mark 1 (7, 10-01 06:12) granule 1 · rows 8,192–16,383 block 1 block 1 mark 2 (7, 10-02 03:40) granule 2 · rows 16,384–24,575 block 2 block 2 mark 3 (9, 10-01 01:05) granule 3 · rows 24,576–32,767 block 3 block 3 mark 4 (9, 10-02 11:30) granule 4 · rows 32,768–40,959 block 4 block 4 mark 5 (12, 10-01 00:20) granule 5 · rows 40,960–49,151 block 5 block 5 event_type.bin .mrk2, one per column mark n → byte offset of granule n inside the .bin file, so a read can seek straight to it ORDER BY (tenant_id, ts) one index entry per granule, not one per row revenue.bin Source: ChistaDATA illustration of the ClickHouse MergeTree format 8,192 10 MiB ≈ 122k rows per granule ( index_granularity ) cap per granule for wide rows ( index_granularity_bytes , adaptive) index entries for 1 billion rows, small enough to keep in RAM ChistaDATA™ S2 OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 26 / 65

S2 · DATA SKIPPING Data skipping means most of the
table is never opened EXHIBIT 21 Granules left to read after each pruning stage EXPLAIN INDEXES = 1 (ABRIDGED) ReadFromMergeTree (prod.events) Indexes: MinMax Granules: 9800/32000 Partition Granules: 9800/9800 PrimaryKey Keys: tenant_id, ts Granules: 160/9800 Skip Name: idx_status Granules: 140/160 All granules in the table 32,000 granules After partition pruning 9,800 granules After the primary key FIELD HUMOUR If EXPLAIN says Granules: 9800/9800 at the primary key, your index is decorative. 160 granules After the skip index 140 granules WHERE tenant_id = 42 AND ts >= now() - INTERVAL 1 DAY AND status = 500 Source: ChistaDATA illustration; counts illustrative ChistaDATA™ S2 OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 27 / 65

S2 · SORTING The sort order is the real index
in a column store EXHIBIT 22 The same 24 country values, sorted two ways into 4 granules ORDER BY (event_time) IN US DE IN FR for WHERE country = 'DE' US IN DE granule 1 US IN FR IN DE US granule 2 IN IN FR US DE IN granule 3 US FR IN DE 4 of 4 granules 23 runs: RLE gains nothing US US 1 of 4 granules 4 runs: compresses to almost nothing granule 4 ORDER BY (country, event_time) DE DE DE DE granule 1 DE FR FR FR FR IN granule 2 IN IN IN IN IN IN IN IN granule 3 US US US US granule 4 Source: ChistaDATA illustration One decision, two effects. The sort key decides which granules a filter can skip, and how long the runs of repeated values are for the codecs. Put the columns you filter on most, lower cardinality first, at the front of ORDER BY . ChistaDATA™ S2 OLAP Database Systems: Architecture, Engineering & Operations FIELD HUMOUR Changing ORDER BY later means rebuilding the table. Choose it like you're going to live with it. © 2026 ChistaDATA Inc. All rights reserved. 28 / 65

S2 · CPU CACHES AND MEMORY BANDWIDTH Columnar engines win
by keeping data close to the CPU EXHIBIT 23 IF AN L1 HIT TOOK ONE SECOND Approximate access latency by level L1 cache ~1 ns L2 cache ~4 ns LEVEL WOULD TAKE L1 1 second L2 4 seconds L3 10–20 seconds DRAM about 2 minutes NVMe about 28 hours L3 cache ~10–20 ns DRAM Why columns win here: a column chunk is a contiguous array. The prefetcher sees it coming, and every cache line is full of values the query needs. A row store pulls 99 unwanted columns through the same cache. ~100 ns NVMe SSD ~100 µs Source: Widely cited approximate figures; vary by CPU and device generation ChistaDATA™ S2 OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 29 / 65

S2 · SUMMARY Five mechanisms compound into the columnar advantage
EXHIBIT 24 The five mechanisms behind columnar performance 1 2 3 4 5 Read less Compress more Process in blocks Skip what's not needed Keep the CPU busy MECHANISM MECHANISM MECHANISM MECHANISM MECHANISM Only the columns the query names Same-type values, sorted and encoded Up to ~65k values per operator call Sparse index, min-max and partitions SIMD over contiguous, cache-friendly arrays TYPICAL EFFECT TYPICAL EFFECT TYPICAL EFFECT TYPICAL EFFECT TYPICAL EFFECT 3 of 100 columns, about 3% of the bytes 5–20× smaller on typical event data Per-row interpreter overhead disappears Most granules are never opened Many values per instruction Source: ChistaDATA analysis; effects illustrative FIELD NOTE ChistaDATA™ S2 None of the five is exotic on its own. Columnar engines are fast because the storage layout lets all five apply to the same query, and the gains multiply. OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 30 / 65

Architecture of OLAP Database Systems Every analytical engine solves the
same problem: scan a lot of columns, fast, across many machines. This section explains the parts that do the work, and what each design choice costs you later. S3 Reference architecture, layer by layer Sharding keys and hot shards The write path: immutable parts and merges Replication and coordination Shared-nothing MPP Pre-aggregation Separated storage and compute Joins in OLAP Distributed query execution Lakehouse and open table formats SECTION 3 · SLIDES 31–42 ChistaDATA™ S3 OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 31 / 65

S3 · ARCHITECTURE Every OLAP engine is the same five
layers, arranged differently EXHIBIT 25 Five layers plus an ops plane: the same shape across every serious OLAP engine Serving BI dashboards Embedded analytics APIs Notebooks / ML Ad-hoc SQL Ops plane runs across every layer Query engine Parser Planner Optimizer Vectorized executor Monitoring and SLOs Backup and DR Metadata Catalog: schemas, parts, stats Coordination (Raft) Access policies Upgrades Security and access Storage Columnar parts (local NVMe) Object storage (S3 / GCS / ADLS) Caches: marks, pages, results Cost governance Capacity planning Ingestion Batch files (Parquet, CSV) Streams (Kafka) CDC from OLTP Incident response Source: ChistaDATA reference architecture How to use this slide: when two vendors pitch you, map both onto these boxes. The differences sit in the storage row and the ops plane. The query layer rarely decides anything. ChistaDATA™ S3 OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 32 / 65

S3 · ARCHITECTURE Columnar engines write once, then tidy up
in the background The write path explains most of the operational surprises people meet in their first year. EXHIBIT 26 Writes never modify a part. Each insert creates a new, sorted, compressed part. Inserts land as small immutable parts; merges fold them into larger ones INSERT batches batch 1 · 50k rows New parts (immutable) Merges run in the background and keep the part count sane. They cost CPU and disk I/O. Background merges all_1_1_0 Updates and deletes rewrite parts or add patch parts. Either way, they are not free. all_1_2_1 merged batch 2 · 50k rows batch 3 · 50k rows all_1_4_2 Batch size is the main control: thousands of rows per insert, not one. fewer, larger parts FIELD HUMOUR all_2_2_0 all_3_3_0 One-row inserts are the database equivalent of posting a novel one page per envelope. It all arrives. The postman resigns. all_3_4_1 merged batch 4 · 50k rows milliseconds: part written all_4_4_0 seconds to minutes hours: settled Queries read every active part. Merged-away parts are dropped after a short grace period. Source: ClickHouse MergeTree behaviour; the pattern is LSM-like across columnar engines ChistaDATA™ S3 OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 33 / 65

S3 · ARCHITECTURE Shared-nothing MPP puts the data next to
the CPU The classic design. Still the fastest for hot data, and still the hardest to resize. EXHIBIT 27 Each node owns a slice of the data and scans it locally, in parallel Why it is fast Data sits next to the CPU on local disk BI tool / client Four nodes scan four slices at once No network hop for the heavy reading Coordinator: plan, fan out, merge results What it costs you Compute and storage scale together Adding nodes means moving data Shard 1 Shard 2 Shard 3 Shard 4 CPU + RAM CPU + RAM CPU + RAM CPU + RAM local NVMe local NVMe local NVMe local NVMe ¼ of the data scans its own slice ¼ of the data scans its own slice ¼ of the data scans its own slice ¼ of the data scans its own slice Every node is a pet: disks, upgrades, failures Typical of self-managed ClickHouse, Druid, Pinot and classic MPP warehouses. Source: ChistaDATA analysis ChistaDATA™ S3 OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 34 / 65

S3 · ARCHITECTURE Separating storage from compute trades latency for
elasticity The cloud-native design. You stop managing disks and start managing caches and invoices. EXHIBIT 28 SHAREDNOTHING SEPARATED Scale compute alone No Yes Hot query latency Best Good once cache is warm Cold query latency Same as hot Slower: object store reads Workload isolation Shared nodes Separate pools Idle cost Nodes keep running Can scale to zero Ops headache Disks, rebalancing Cache sizing, API and egress bills Compute pools come and go; the data stays put in object storage Compute A · BI Compute B · ETL Compute C · ML stateless, elastic stateless, elastic stateless, elastic SSD cache SSD cache SSD cache Metadata and transaction service fetch on cache miss Object storage (S3 / GCS / ADLS) Examples: Snowflake, BigQuery, Redshift RA3, Databricks SQL, ClickHouse Cloud. columnar files · eleven nines durable · cheap per TB Source: ChistaDATA analysis ChistaDATA™ S3 OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 35 / 65

S3 · ARCHITECTURE Distributed queries move small answers, not big
data EXHIBIT 29 Two-stage aggregation: every shard does the heavy lifting, the coordinator only adds up SELECT country, count() AS events FROM events GROUP BY country 2. Scatter: same plan fragment to every shard 5. Final result Coordinator IN 1,310M US 1,120M 1. plan · 4. merge partial states DE 285M Shard 1 · 3. scan 1.2B rows, aggregate locally Shard 2 · 3. scan 1.2B rows, aggregate locally Shard 3 · 3. scan 1.2B rows, aggregate locally IN 412M IN 451M IN 447M US 377M US 402M US 341M DE 96M DE 88M DE 101M Partial aggregate states travel back: a few kilobytes, not 3.6 billion rows Source: ChistaDATA analysis; illustrative row counts Push work down. Filters, aggregation and LIMIT run on the shards, close to the data. ChistaDATA™ S3 Ship states, not rows. Counts, sums and HyperLogLog sketches merge cheaply. OLAP Database Systems: Architecture, Engineering & Operations Watch the exceptions. Exact DISTINCT, big JOINs and ORDER BY without LIMIT pull rows to one node. © 2026 ChistaDATA Inc. All rights reserved. 36 / 65

S3 · ARCHITECTURE The sharding key decides whether all your
nodes actually work EXHIBIT 30 Load per shard under three sharding strategies (rows written today) Hash Range Random cityHash64(user_id) % 4 event_date → shard rand() % 4 S1 S2 S3 S4 S1 S2 S3 S4 S1 S2 S3 Even spread. The sane default. Hot shard: today lives on S4. Perfectly even, perfectly scattered. A user's rows stay together on one shard. All writes and most reads hit one node. No shard-local joins or dedup by key. S4 Source: ChistaDATA analysis; illustrative distribution Rule we apply on every engagement: shard by a high-cardinality key that matches how you join and deduplicate (user, tenant, account). Time belongs in the partition key and the sort key, never in the shard key. Most engines do not rebalance for you, so this is a multi-year decision. ChistaDATA™ S3 OLAP Database Systems: Architecture, Engineering & Operations FIELD HUMOUR Sharding by date gets you a four-node cluster where one node does the work and three attend the meetings. © 2026 ChistaDATA Inc. All rights reserved. 37 / 65

S3 · ARCHITECTURE Replication keeps copies; consensus keeps them honest
Lose a replica and queries carry on. Lose Keeper quorum and replicated tables stop taking writes. EXHIBIT 31 Replicas hold the data; a small Raft ensemble agrees on what the data should be Shard 1 QUORUM ARITHMETIC ENSEMBLE QUORUM CAN LOSE What lives where 1 node 1 0 Table data: on the replicas 3 nodes 2 1 Part list and op log: in Keeper 4 nodes 3 1 5 nodes 3 2 Replica A Replica B full copy of the shard full copy of the shard serves reads and writes serves reads and writes Writes need Keeper quorum fetches parts from peer fetches parts from peer Reads keep working without it Quorum = ⌊n / 2⌋ + 1. Odd numbers only. Three dedicated nodes for most estates, five when you span three sites. FIELD HUMOUR Four Keeper nodes survive exactly as many failures as three. The fourth is there for moral support. Coordination ensemble: ClickHouse Keeper (Raft) keeper-1 keeper-2 keeper-3 leader follower follower Source: ClickHouse ReplicatedMergeTree and Keeper ChistaDATA™ S3 OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 38 / 65

S3 · ARCHITECTURE The fastest query is the one you
answered at insert time Materialized views, projections and rollups trade a little write cost for a lot of read speed. EXHIBIT 32 THE MATERIALIZED VIEW BEHIND IT Aggregate at insert time, read the small table at query time events (raw) events_hourly 1.2B rows a day Materialized view AggregatingMergeTree every click, kept fires on each insert about 2M rows a day CREATE MATERIALIZED VIEW mv_hourly TO events_hourly AS SELECT toStartOfHour(ts) AS hour, country, countState() AS events, uniqState(user_id) AS users FROM events GROUP BY hour, country; -- read: countMerge(events), -uniqMerge(users) Aggregate states, not numbers, so hourly rows can be merged into days and months later. Projection (the in-table option) Second sort order or rollup inside the same table, kept in step on insert, picked by the optimizer. Dashboard reads 600× fewer rows Cost: every rollup adds insert and merge work. Build the ones dashboards use. Source: ChistaDATA analysis; illustrative volumes ChistaDATA™ S3 OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 39 / 65

S3 · ARCHITECTURE OLAP joins are cheap only when one
side is small A star schema, and three ways an engine brings the dimensions to the fact rows EXHIBIT 33 Star schema dim_date Broadcast join small dim dim_store fact row: product_id = 4711 node 1 node 2 node 3 Dictionary, in RAM on every node copy the small side to every node fact_sales 4711 → 'Espresso machine' Shuffle join billions of rows dim_product Dictionary lookup dim_customer small dimensions, thousands to millions of rows 4712 → 'Grinder' node 1 node 2 node 3 reloaded on a schedule key % 3 = 0 key % 3 = 1 key % 3 = 2 dictGet(): a lookup, not a join both sides re-sent over the network by join key Source: ChistaDATA analysis Our default: denormalise what you filter on, use dictionaries for small lookups, broadcast the medium ones, and treat a shuffle join between two billion-row tables as a design review, not a query. ChistaDATA™ S3 OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 40 / 65

S3 · ARCHITECTURE The lakehouse lets several engines share one
copy of the data Open formats moved the storage layer out of the database. The query engine became a choice per workload. EXHIBIT 34 Why buyers like it: no copy per engine, no lock-in on the storage layer. Many engines, one set of Parquet files, governed by a table format and a catalog Spark Trino ClickHouse Snowflake DuckDB What the format adds: transactions and time travel on top of plain files. Where it falls short: sub-second serving and second-level freshness still favour engine-native storage. Open table format: Apache Iceberg / Delta Lake Catalog snapshots · schema evolution · time travel · atomic commits Iceberg REST, Unity, Glue FIELD HUMOUR One copy of the data, five engines, and five opinions about how to read it. Object storage: one copy of the data 00001.parquet 00002.parquet 00003.parquet 00004.parquet 00005.parquet Source: Apache Iceberg and Delta Lake documentation; ChistaDATA analysis ChistaDATA™ S3 OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 41 / 65

S3 · ARCHITECTURE Representative OLAP systems differ most in coupling,
freshness and licence SYSTEM DEPLOYMENT STORAGE AND COMPUTE REAL-TIME INGEST LATENCY SWEET SPOT LICENCE ClickHouse Self-managed, or ClickHouse Cloud Shared-nothing self-managed; separated in Cloud Native: high-rate inserts, Kafka engine Sub-second on billions of rows, high concurrency Apache 2.0 Snowflake SaaS on AWS, Azure, GCP Separated: virtual warehouses over object storage Snowpipe and Snowpipe Streaming Seconds; BI and ELT Proprietary, credit-based BigQuery Serverless on Google Cloud Separated: slots over managed storage Storage Write API streaming Seconds on very large scans Proprietary; per TB scanned or slots Amazon Redshift Managed on AWS: provisioned or Serverless RA3: managed storage with local cache Streaming ingestion from Kinesis and MSK Seconds; warehouse BI Proprietary Databricks SQL SaaS on AWS, Azure, GCP Separated: Delta tables in your object storage Structured Streaming pipelines Seconds; lakehouse BI Proprietary platform; Delta Lake open source Apache Druid / Pinot Self-managed, or vendor cloud Segments in deep storage, cached on serving nodes Native from Kafka Sub-second, user-facing, high concurrency Apache 2.0 DuckDB Embedded, in-process, one machine Local files, Parquet on object storage Batch Milliseconds to seconds on one machine MIT Source: vendor documentation as of October 2026; ChistaDATA analysis. Architecture summaries are simplified; editions and features change, so check current documentation before a decision. Reading the table: there is no winner, only a fit. Choose by freshness, concurrency and who runs it, then by price. Section 4 covers how we test that fit with your own queries. ChistaDATA™ S3 OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 42 / 65

Engineering Columnar Systems That Stay Fast The engine is fast
out of the box. Most slow clusters we're called into were made slow by design decisions taken in the first month: the sort key, the partitions, the types and the way data arrives. S4 Modelling: wide, star or dictionary Batching and the too-many-parts trap Sort key design Updates and deletes Partitioning for lifecycle Query optimisation and EXPLAIN Types and codecs Anti-patterns Ingestion patterns Benchmarking honestly SECTION 4 OF 5 · ENGINEERING ChistaDATA™ S4 OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 43 / 65

DATA MODELLING Columnar engines reward wide tables and punish join-heavy
habits EXHIBIT 35 Three modelling shapes, chosen by how the data is queried, not by habit Wide denormalised table Star schema dim_date Fact table + dictionaries dim_store events product_id store_id amount fact_sales dim_product dictionary in memory id → name, category reloads on schedule dim_customer one table, 100+ columns, read only what you need facts narrow and long, dimensions small dictGet() instead of a JOIN at query time Best for: event, log and clickstream data Best for: BI tools, changing dimensions Best for: lookups on hot dashboards Cost: repeated values (compression eats them) Cost: joins at query time, plan carefully Cost: dictionary must fit in RAM Source: ChistaDATA analysis FIELD NOTE ChistaDATA™ S4 Our default for event data is wide and denormalised, with dictionaries for the handful of dimensions that change. Use a star schema when a BI tool expects one. OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 44 / 65

SORT KEY DESIGN The sort key decides how much of
the table every query reads EXHIBIT 36 Same data, same query, 60× less I/O from one line of DDL RULE: LOW → HIGH CARDINALITY, MATCHING THE FILTERS YOU ACTUALLY RUN Query: WHERE tenant_id = 42 AND event_time > now() - 1 day ORDER BY (event_time, tenant_id) ≈ 9,800 of 10,000 granules read. Tenant rows are scattered. ORDER BY (tenant_id, event_type, event_time) CREATE TABLE events ( tenant_id UInt32, event_type LowCardinality(String), event_time DateTime, user_id UInt64 -- more columns ) ENGINE = MergeTree ORDER BY (tenant_id, event_type, event_time); First 1–3 columns do almost all the pruning. Timestamp first only if every query is a time slice across all tenants. Changing it later means rebuilding the table. Decide in week one. ≈ 160 of 10,000 granules read. Tenant rows sit together. Each square ≈ 100 granules of 8,192 rows (illustrative) Source: ChistaDATA analysis, illustrative 82M-row table ChistaDATA™ S4 OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 45 / 65

PARTITIONING Partition for lifecycle and retention, never for query speed
EXHIBIT 37 Use for: TTL, dropping or detaching old data, moving cold months to cheaper disks. Partitions are a lifecycle tool. Used as an index, they become a liability Target: tens to low hundreds of partitions per table, each about 1 GB or more. PARTITION BY toYYYYMM(event_time) 10 months, each one a folder you can drop Not for: speeding up queries. The sort key does that job. Jan Feb Mar Apr May Jun Jul Aug Sep TTL 90 days: Jan to Mar dropped as whole partitions. Instant. No rewrite, no merge. PARTITION BY user_id Oct Monthly is the safe default for event data. Daily only at very high volume. FIELD HUMOUR A customer once partitioned by user_id to make user lookups fast. It made them fast to never finish. The merge pool filed a complaint with HR. millions of partitions, each with its own parts Merges can't cross partitions, so nothing ever gets bigger. Inserts touching many partitions create many parts at once. Source: ChistaDATA analysis ChistaDATA™ S4 OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 46 / 65

DATA TYPES AND CODECS Right-sized types and codecs are the
cheapest performance work you'll ever do EXHIBIT 38 A type review typically shrinks a table several-fold before anyone touches hardware Compressed size on disk, GB per billion rows (illustrative) country 1.8 · String 0.15 · LowCardinality(String) event_time 1.6 · DateTime64(9) CREATE TABLE events ( event_time DateTime CODEC(Delta, ZSTD(3)), country LowCardinality(String), status UInt8, latency_ms UInt32 CODEC(T64, ZSTD(3)), temp_c Float32 CODEC(Gorilla, ZSTD(3)) ) ENGINE = MergeTree ORDER BY (country, event_time); 0.25 · DateTime, Delta + ZSTD status LowCardinality for strings under ~10k distinct values. 1.2 · Nullable(String) Nullable adds a hidden column. Use a default value unless NULL means something. 0.05 · UInt8 (enum code) Smallest integer that fits. DateTime unless you need sub-second. latency_ms 1.4 · UInt64 ZSTD(3) is the default codec from 26.9. Expect smaller parts and a bit more insert CPU. 0.35 · UInt32, T64 + ZSTD user_id 2.2 · String (as text) 0.9 · UInt64 as first loaded after type and codec review Source: ChistaDATA analysis, illustrative figures ChistaDATA™ S4 OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 47 / 65

INGESTION PATTERNS Ingestion is where correctness is designed in, or
quietly designed out EXHIBIT 39 Three ways data arrives, each with its own freshness, cost and failure mode SOURCE MECHANISM LANDS IN FRESHNESS Batch files Parquet / CSV on S3, GCS, HDFS INSERT INTO … SELECT FROM s3() or an orchestrated load job MergeTree large parts, few merges minutes to hours Event stream Kafka, Kinesis, Pub/Sub Kafka engine → materialised view or a sink connector / ClickPipes MergeTree parts every few seconds seconds OLTP database (CDC) PostgreSQL, MySQL, Oracle Debezium → Kafka → MV op + version column per row ReplacingMergeTree latest version wins at merge seconds, with updates Source: ChistaDATA analysis FIELD NOTE ChistaDATA™ S4 Pick the pattern per source, not per company. And decide at-least-once or exactly-once before the first message flows. Kafka engine consumption is at-leastonce, so plan for duplicates. OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 48 / 65

INSERT BATCHING Tiny inserts are the most common way teams
break a healthy cluster EXHIBIT 40 Every INSERT creates a part. How many parts you create is a design choice 1 row per INSERT, 1,000 inserts a second a new part for every insert; merges can't keep up 1,000 3,000 active parts in a partition: inserts start being delayed active parts: inserts fail with TOO_MANY_PARTS Batch 10k–1M rows per insert, or about one insert a second per table. async_insert is on by default since 26.3. The server buffers small inserts for you. Set wait_for_async_insert = 1 where an acknowledgement must mean the data is stored. parts per partition: 1,000 → inserts delayed 3,000 → TOO_MANY_PARTS (252), inserts rejected FIELD HUMOUR ClickHouse merges in the background and it is very good at it. It is not a miracle worker. Ten thousand inserts of one row each is not a workload, it's a hostage situation. 100k rows per INSERT, every 2 seconds part 1 2.1M rows part 2 1.4M rows part 3 0.6M a handful of parts, merged into fewer, larger ones Thresholds: parts_to_delay_insert, parts_to_throw_insert Source: ChistaDATA analysis ChistaDATA™ S4 OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 49 / 65

UPDATES AND DELETES In an immutable store, every update is
either a rewrite, a patch or a new version EXHIBIT 41 Three ways to change data in an engine whose parts never change ALTER … UPDATE classic mutation part A 10M rows rewrite all part A′ 10M rows, new copy DELETE / UPDATE lightweight part A untouched + patch / mask only changed rows applied on read folded in at merge Fixes and GDPR deletes. Patch parts since 25.7. ReplacingMergeTree versioned rows part 1 id 7, v1 part 2 id 7, v2 merge keeps v2 FINAL / argMax Frequent updates, CDC. Queries must dedupe. Repair tool only. Rewrites whole parts. Source: ChistaDATA analysis FIELD NOTE ChistaDATA™ S4 If the business updates rows every minute, design for it with versioned rows. Running mutations as an update path is the most expensive habit we see. OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 50 / 65

QUERY OPTIMISATION Query tuning is a checklist, and the first
item fixes most queries EXHIBIT 42 The six questions we ask of every slow query, in order 1 2 3 4 5 6 Filter on the sort key? PREWHERE helping? Only needed columns? Right JOIN? Pre-aggregated? Checked EXPLAIN? Lead with the first ORDER BY columns Cheap filter column read first No SELECT *. Each column is I/O Small side on the right, or a dictionary Materialised view or projection for hot paths Granules selected vs total Work left to right. Most slow queries we review fail at step 1 or step 3. Hardware is step 7, and it rarely gets that far. Source: ChistaDATA analysis Typical gain at step 1 Typical gain at step 3 Typical gain at step 5 10–100× fewer granules read when the filter matches the sort key. Proportional to the columns you stop reading. Wide tables make this large. Dashboard queries go from seconds to milliseconds, at extra insert cost. ChistaDATA™ S4 OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 51 / 65

READING EXPLAIN EXPLAIN tells you how much of the table
a query will touch before you run it EXPLAIN indexes = 1 SELECT count() FROM events WHERE tenant_id = 42 AND event_time >= now() - INTERVAL 1 DAY; EXHIBIT 43 Abridged plan output, with the three numbers that matter 1 MinMax on event_time kept 12 of 40 parts. The time filter is doing its job. 2 Partition pruned nothing extra here. That's fine: partitions exist for lifecycle. 3 PrimaryKey took 9,800 granules down to 160. About 1.3M rows read instead of 80M. That is the sort key working. Read it top to bottom. Each stage shows selected / total. A primary-key stage that barely moves the number means the filter doesn't match the leading ORDER BY columns. ReadFromMergeTree (prod.events) Indexes: FIELD HUMOUR MinMax EXPLAIN is free. Guessing is also free, which is why so many teams prefer it. Keys: event_time Parts: 12/40 Granules: 9800/32000 1 Partition Keys: toYYYYMM(event_time) Parts: 12/12 Granules: 9800/9800 2 PrimaryKey Keys: tenant_id, event_time Parts: 6/12 Granules: 160/9800 3 Source: ChistaDATA analysis ChistaDATA™ S4 OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 52 / 65

ANTI-PATTERN GALLERY Six anti-patterns that make a fast engine look
slow SELECT * Row-by-row inserts Partition by a high-cardinality key On a 300-column table this reads 300 columns to show 4. The disks would like a word. One row, one part, one merge. Repeat a million times and call it a pipeline. Millions of folders, none of them ever merge. Confetti, not a table. Instead: name the columns. Instead: batch, or rely on async inserts. Instead: partition by month. Nullable everything Two billion-row JOINs, no plan Using it as an OLTP database An extra hidden column per field, and slower filters, to store values that are never NULL. The right side goes into memory. Both sides are huge. You can guess the ending. Point updates, single-row lookups, transactions. It will try. It will not enjoy it. Instead: defaults, unless NULL means something. Instead: pre-join, dictionaries, or filter first. Instead: keep OLTP in PostgreSQL or MySQL. FIELD NOTE ChistaDATA™ S4 Every one of these has appeared in a production review we ran in the last year. None of them needed a bigger cluster to fix. OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 53 / 65

BENCHMARKING A benchmark is only as honest as its data,
its cache state and its percentiles EXHIBIT 44 Your users feel the p95, not the mean. Report the tail. Five rules for a benchmark you can defend, and why the average lies Real data your schema, your skew, your volume Cold and warm report both cache states separately Concurrency 1, 10 and 100 parallel users Percentiles p50 / p95 / p99, never only the mean Publish config versions, settings, hardware, DDL Cold cache is the first query of the morning. Warm is the rest of the day. Both matter. Single-user tests hide queueing. Real dashboards are concurrent. Anyone should be able to rerun it from what you publish. FIELD HUMOUR mean 120 ms p95 900 ms Every vendor benchmark is won by the vendor who ran it. That's not a conspiracy, it's just who chose the queries. query latency, 10,000 runs (illustrative) Source: ChistaDATA benchmarking method, illustrative distribution ChistaDATA™ S4 OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 54 / 65

Operating OLAP in Production A columnar engine that is fast
on day one can still be a pager magnet by month six. Running it well comes down to a few signals, a few rehearsed procedures and a calendar. S5 Golden signals for OLAP Cost governance (FinOps) HA and DR topology Incident severity model Capacity planning and SLOs Operational maturity model Upgrades and LTS lifecycle SECTION 5 OF 6 · SLIDES 55–62 ChistaDATA™ S5 OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 55 / 65

S5 · OBSERVABILITY Seven signals cover most of what goes
wrong in an OLAP cluster Watch parts and replication as closely as query latency. Most incidents show up there first. EXHIBIT 45 WHERE EACH NUMBER COMES FROM Seven numbers tell you whether an OLAP cluster is healthy SIGNAL SOURCE Query latency p95 Active parts / partition Merge pool busy Replication delay Latency, errors system.query_log 1.8 s 240 64% 380 s Parts, sizes system.parts SLO ≤ 2 s for dashboards P1 > 300 · P0 ≥ 3000 P2 > 70% sustained P1 > 60 s · P0 > 300 s system.query_log system.parts system.merges system.replicas Merge backlog system.merges , pool metrics Replication system.replicas : absolute_delay , queue_size Memory, disk system.asynchronous_metrics , system.disks Keeper mntr on each Keeper node Memory used Disk free Keeper latency Status key 58% 17% 12 ms healthy P1 if error 241 recurs P1 < 20% · P0 < 10% P1 > 10 ms · quorum 3/3 watch, fix soon asynchronous_metrics system.disks mntr (zk_avg_latency) page someone now FIELD HUMOUR If the only alert you have is the CFO asking why the board dashboard is slow, you have exactly one golden signal, and it's the expensive one. Sample values, one cluster Source: ChistaDATA operating thresholds; values illustrative ChistaDATA™ S5 OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 56 / 65

S5 · HA AND DR Replicas survive hardware, only backups
survive people EXHIBIT 46 Zones handle hardware failure, backups handle human failure, and the DR region handles bad days Primary region Zone A DR region Zone B Standby cluster, smaller footprint Zone C Seeded from backups, or kept warm ch-s1-r1 · shard 1 ch-s1-r2 · shard 1 no data replicas by an async copy of the hot tables tie-breaker only ch-s2-r1 · shard 2 Promotion is a runbook, not a button: ch-s2-r2 · shard 2 DNS, Distributed table configs, users keeper-1 keeper-2 keeper-3 ReplicatedMergeTree, 2 replicas per shard · 3-node Keeper, one per zone Object storage, versioned and immutable RPO ≤ 1 h RTO ≤ 4 h BACKUP … TO S3 · nightly full + hourly incremental · restores tested monthly last good backup region loss, rehearsed Source: ChistaDATA reference topology; RPO/RTO targets illustrative Lose one zone: queries and inserts carry on, because each shard still has a replica and Keeper still has 2 of 3 for quorum. Lose Keeper quorum: replicated tables go read-only. ChistaDATA™ S5 OLAP Database Systems: Architecture, Engineering & Operations FIELD HUMOUR Replication is not backup. Replication faithfully copies your accidental DROP TABLE to every replica, in under a second, with excellent consistency. © 2026 ChistaDATA Inc. All rights reserved. 57 / 65

S5 · CAPACITY AND SLOS Size from measured compression and
concurrency, then add headroom EXHIBIT 47 A capacity model fits on one page if you measure the inputs instead of guessing them WORKLOAD TARGET Dashboards p95 ≤ 2 s +30% headroom Ad hoc analysis p95 ≤ 30 s ~190 TB Ingest freshness ≤ 60 s behind source Availability 99.9% monthly Storage raw ingest/day compression ÷ 2 TB replicas × 10× retention × 2 365 d = PERFORMANCE SLOS, WRITTEN DOWN Example targets. Agree yours with the business before the first incident, not during it. CPU peak concurrent queries cores each, measured p95 × 40 cores per replica set, before merges = 4 160 + 25% merges Measure, then multiply. Compression ratio and cores per query come from system.parts and system.query_log on your data, not from a vendor benchmark. Memory largest GROUP BY / JOIN state, measured 2× headroom, or plan spill to disk × 48 GiB ≥ 96 GiB per node Source: ChistaDATA sizing method; numbers illustrative ChistaDATA™ S5 OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 58 / 65

S5 · UPGRADES AND LIFECYCLE Upgrade on the LTS calendar,
in stages, with behaviour pinned Two LTS lines are supported at any time; 25.8 estates are already past the edge EXHIBIT 48 Jan 2026 Jul 2026 Jan 2027 Jul 2027 25.8 LTS · unsupported after 29 Aug 2026 26.3 LTS · to ~Mar 2027 26.8 LTS · to ~Aug 2027 today, Oct 2026 Staged rollout, one rung at a time 1 · Staging 2 · Pin behaviour 3 · Canary replica 4 · Replica per shard 5 · Unpin replay a day of compatibility='26.3' one replica, one shard, then the second replica, remove compatibility query_log traffic in the default profile soak 48 hours never both at once setting by setting Source: ClickHouse release calendar, verified Oct 2026; ChistaDATA upgrade runbook Plan two upgrades a year. 26.3 to 26.8 crosses about 57 backward-incompatible changes, so the compatibility pin is what keeps query results stable while you test. ChistaDATA™ S5 OLAP Database Systems: Architecture, Engineering & Operations Skipping an LTS doesn't save work. It comes back next year with more breaking changes and a shorter deadline. © 2026 ChistaDATA Inc. All rights reserved. 59 / 65

S5 · COST GOVERNANCE Cost and performance are the same
problem, so tune them together Idle compute and unpruned scans show up on the bill and in p95 latency at the same time. EXHIBIT 49 LEVERS, ROUGHLY IN ORDER OF PAYBACK Where an analytics platform budget typically goes Self-managed ClickHouse 45% 20% 5% 10% 20% LEVER WHAT IT DOES EFFORT Kill idle compute Auto-suspend, idling replicas, schedule non-prod off at night Low Right-size Size to measured p95 concurrency, not to the launch-day guess Low TTL to cold tier Move old partitions to object storage; delete what nobody queries Medium System-log retention TTL on query_log, trace_log, text_log; they grow quietly Low Fix the top 20 queries Pruning, sort keys and pre-aggregation cut CPU at the source Medium Cap egress Keep compute next to storage; stop cross-region dashboard reads Medium Cloud warehouse, consumption pricing 55% Compute Storage 10% Egress 8% Idle 22% 5% People Idle is the line nobody owns: warehouses left running, over-sized replicas, dashboards refreshing for empty rooms. FIELD HUMOUR The cheapest query is the one a browser tab didn't run every 30 seconds all weekend for nobody. Source: ChistaDATA FinOps reviews; shares illustrative ChistaDATA™ S5 OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 60 / 65

S5 · INCIDENT SEVERITY MODEL Agree what a P0 is
before the first one happens SEVERITY EXAMPLE TRIGGERS RESPONSE P0 Keeper quorum lost · TOO_MANY_PARTS (252) on inserts · disk under 10% free · serving replica read-only or more than 300 s behind Page now. Work it until it's fixed. P1 More than 300 active parts in a partition · replication delay over 60 s · memory limit error 241 recurring · disk under 20% Fix today. P2 Merge pool over 70% sustained · system logs without TTL · parts 100–300 and growing week on week Fix this sprint. EXHIBIT 50 Escalation path for a P0 Alert fires threshold from system tables Severity follows business impact, not how loud the alert is. A P2 left for three sprints tends to come back as a P0 on a Friday evening. On-call engineer acknowledges P0 within 15 min · triage with runbook Principal engineer joins P0 within 30 min, or a P1 that stalls Bridge with the business updates every 30 min until resolved Written RCA within 5 business days cause, fix, and the alert that was missing Source: Example response targets; contract SLAs vary by tier ChistaDATA™ S5 OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 61 / 65

S5 · MATURITY MODEL Most of the value is in
climbing from monitored to managed EXHIBIT 51 Five levels of OLAP operational maturity Each step up is cheaper L5 than the incident it prevents. L4 L3 L2 L1 Optimised Cost per query tracked Engineered DR rehearsed, drills each quarter Sort keys, TTL, SLOs owned Upgrades on the LTS calendar Managed P0/P1/P2 alerts, runbooks Monitored Backups restored on schedule Dashboards exist Reactive Alerts on CPU, not on parts Users report outages No runbooks, no backups tested Source: ChistaDATA operating model Getting from L2 to L3 is the step that matters most. It mostly means writing down severities and runbooks and restoring a backup on purpose. None of it needs new hardware. ChistaDATA™ S5 OLAP Database Systems: Architecture, Engineering & Operations FIELD HUMOUR Most teams rate themselves one level higher than their last incident review would. The review is usually right. © 2026 ChistaDATA Inc. All rights reserved. 62 / 65

KEY TAKEAWAYS Five habits separate fast analytics platforms from expensive
ones EXHIBIT 52 What to take back to your team 1 2 3 4 5 Pick by workload Model for scans Batch the writes Watch the parts Rehearse recovery Columnar for analytics, row stores for OLTP. HTAP only when proven. Sort key matches the dominant filters. Partitions for lifecycle. Thousands of rows per insert, not one. Async inserts are a tool. Parts, merges, lag and memory, with P0/P1/P2 thresholds agreed. Backups tested, upgrade path planned, runbooks practised before needed. Source: ChistaDATA engagement experience The short version: columnar systems are fast by design. When one gets slow, the cause is nearly always modelling or operations, and both can be fixed before they hurt. ChistaDATA™ Close OLAP Database Systems: Architecture, Engineering & Operations FIELD HUMOUR If you remember one thing: stop typing SELECT *. Your storage bill will send a thank-you card. © 2026 ChistaDATA Inc. All rights reserved. 63 / 65

NEXT STEPS Ninety days is enough to move from guesswork
to an operated platform EXHIBIT 53 A 90-day path from first assessment to steady operations DAY 0 DAY 30 DAY 60 DAY 90 Assess Workload profile, top queries, parts health Design and fix Sort keys, types, ingestion, pre-aggregation Operate Monitoring, HA/DR drills, upgrade and cost plan Source: ChistaDATA engagement model ClickHouse Fit & TCO Assessment Architecture and migration Enterprise support and managed services Fixed scope. Parts and merge health, top query fingerprints, sort-key review, LTS position, and a selfmanaged vs cloud cost model. Findings ranked P0/P1/P2. Schema rebuilds, LTS upgrades with compatibility pinning, and migrations onto ClickHouse from legacy warehouses and row stores. 24×7 consultative support and remote DBA from principallevel engineers, on the LTS cadence, with quarterly health reviews. ChistaDATA™ Close OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 64 / 65

ChistaDATA™ Thank you. Questions? Bring us your slowest query. We’ll
bring the EXPLAIN output and, if we have to, the whiteboard. SPEAKER ONLINE NOTICE Shiv Iyer Founder and CEO, ChistaDATA Inc. [email protected] chistadata.com minervadb.com · fsyncdata.io linkedin.com/in/thewebscaledba © 2026 ChistaDATA Inc. All rights reserved. ChistaDATA is a trademark of ChistaDATA Inc. ChistaDATA™ Close OLAP Database Systems: Architecture, Engineering & Operations © 2026 ChistaDATA Inc. All rights reserved. 65 / 65

OLAP Database Systems: Architecture, Engineerin...

OLAP Database Systems: Architecture, Engineering & Operations

More Decks by Shiv Iyer

Other Decks in Technology

Featured

Transcript