Learn
How real-time analytical databases actually work
Eight topics covering the engine, the architecture around it, the operational reality and the commercial framing — including a full section on where the technology is the wrong choice.
- Column storage means a query reads only the columns it needs, not whole rows.
- Data is stored sorted by a primary key expression, so range scans skip most of the dataset via sparse indexes.
- Compression typically ranges from 5x to 30x depending on cardinality and sort order.
- Vectorized execution processes data in blocks, using CPU cache and SIMD efficiently.
- It is append-optimized: inserts are batched into immutable parts that merge in the background.
In business terms
It answers questions over huge volumes of event data in the time it takes a page to load, and stores that data at a fraction of the cost of general-purpose warehouses.
Glossary
- OLAP
- Analytical query workload: aggregate many rows, return few.
- OLTP
- Transactional workload: read/write few rows, strict consistency.
- Part
- An immutable on-disk chunk of a table that merges over time.
- ORDER BY defines physical sort order; the sparse primary index lets the engine skip granules.
- Put low-cardinality, frequently filtered columns first, high-cardinality identifiers later.
- PARTITION BY is for data lifecycle (usually by month), not for query speed.
- Skip indexes (minmax, set, bloom_filter) help secondary filters, but never rescue a wrong ORDER BY.
- TTL expressions move or delete old data automatically, including tiering to object storage.
In business terms
Design decisions made in the first week determine whether queries take 50 milliseconds or 50 seconds — this is why a short design review beats a long bake-off.
- Insert-time triggers, not periodic refreshes — results stay fresh within seconds.
- AggregatingMergeTree and SummingMergeTree store partial aggregate states that merge over time.
- Multiple views can fan out from one raw table for different access patterns.
- Refreshable materialized views cover periodic full recomputation when needed.
- Cost model shifts work from read time to write time, which is what makes customer-facing analytics affordable.
In business terms
Heavy calculations happen once as data arrives, so thousands of users can hit dashboards at the same time without extra compute cost.
- Batch inserts, not row-by-row: aim for tens of thousands of rows or a few seconds of buffering per insert.
- Kafka table engine, ClickPipes or an external consumer all work; pick based on ops appetite.
- CDC from Postgres/MySQL via Debezium keeps operational state queryable alongside events.
- ReplacingMergeTree handles late-arriving updates for CDC-style upserts.
- Iceberg, Delta and Parquet on object storage can be queried directly or loaded incrementally.
In business terms
It plugs into the pipelines you already have — you rarely need to rebuild data collection to adopt it.
- Shared-nothing self-managed clusters use replication (ReplicatedMergeTree) plus sharding.
- Cloud model puts data in object storage with local cache, so compute scales independently.
- Compute-compute separation isolates ingest from query workloads.
- Open-source core means no lock-in on the query engine or data format story.
- Operational burden is real for self-managed: upgrades, rebalancing, backups, monitoring.
In business terms
You can start managed and move, or run it yourself — the engine and SQL stay the same either way.
- Not a transactional system of record: no multi-statement ACID transactions across tables.
- Frequent single-row updates and deletes are expensive relative to a row store.
- Highly normalized star schemas with many large-table joins need careful denormalization or a dictionary strategy.
- Under roughly a few hundred GB with relaxed latency, Postgres or an existing warehouse is usually fine.
- ML training, notebooks and feature engineering pipelines remain better served by Spark/Databricks.
In business terms
It is a specialist. Where the specialist is not needed, keeping your current tool is the cheaper answer.
- Compression reduces the storage bill and the bytes each query must read.
- Pre-aggregation collapses expensive repeated scans into cheap lookups.
- Retention becomes affordable, so teams stop deleting data they later need.
- Concurrency scales without linear cost growth in per-query billing models.
- Migration and re-modelling cost is real and should be included in any business case.
In business terms
You typically keep more data, for longer, with faster answers, at lower total run rate — but budget for a one-time modelling effort.
- OpenTelemetry collector as the ingest standard, avoiding proprietary agents.
- Logs, metrics and traces in one SQL-queryable store instead of three silos.
- Wide events and high-cardinality attributes stay queryable rather than being dropped.
- Retention economics change: months of raw telemetry instead of days.
- Trade-off: less turnkey than commercial APM; you own more of the experience.
In business terms
Keep more telemetry for longer, query it freely, and stop being billed by the custom tag.
Switch to Seller mode in the header to see the objection library, talk tracks and competitive cautions.