ClickHouse

Overview

ClickHouse is an open-source, column-oriented database built for fast analytical queries over very large tables. It stores each column separately, compresses it heavily, and scans only the columns a query needs. This lets it answer questions such as "how many events per country per hour" over billions of rows in a fraction of a second on modest hardware.

It is not a general purpose database. It is tuned for appending large batches of rows and reading them back in aggregate. If you need many small updates, strict transactions, or lookups of single rows by key, a different system fits better. If you need dashboards, ad hoc exploration, log and event analytics, or a fast feature source for tabular machine learning, ClickHouse is a strong choice. This page explains how it works, how it compares with its neighbors, and how it relates to models that learn directly from raw tables, such as those described on /model/research.

What it is

ClickHouse is a database management system for online analytical processing, often shortened to OLAP. You talk to it with a dialect of SQL. You create tables, insert rows, and run SELECT queries with filters, joins, group-bys, window functions, and many special aggregate functions.

The main traits are:

• Column storage. Values of one column sit together on disk, not values of one row.
• Vectorized execution. The engine processes blocks of values at a time, which keeps CPU caches busy and uses SIMD instructions.
• Heavy compression. Similar values next to each other compress very well, and codecs can be chosen per column.
• Sparse primary index. The table is sorted by a key, and a small index points to blocks of rows, not to every row.
• Distributed by design. A cluster can shard data across nodes and replicate it for safety.
• Many table engines. The engine of a table decides how data is stored, merged, replicated, or read from outside sources.

Everyday analogy

Think of a large paper ledger of sales. A row database is like a stack of index cards, one card per sale, with every field on the card. To add up the "amount" field for a year, you must pick up every card. A column database is like a set of separate notebooks: one notebook lists only the amounts, another only the dates, another only the countries. To add up the amounts you open one notebook and read straight down. You skip the rest.

Where it came from

ClickHouse began as an internal engine at a large web analytics company in Russia. The goal was to build reports over a huge stream of web traffic events in real time, without precomputing every possible report in advance. The system was later released as open source and grew a wide community. A separate company now develops and offers it as a managed service as well as the open-source server.

The design draws on a long line of ideas:

• Column stores, studied in database research since the 1980s and popularized by academic systems of the 2000s.
• Log-structured merge ideas, where data is written in small sorted pieces and merged in the background.
• Vectorized query execution, a well-known technique from research on modern CPUs.
• Practical lessons from running real-time analytics at scale.

Over time it added joins that scale better, window functions, materialized views, projections, object storage support, and tools to read and write many external formats. The pace of change is high, so check the documentation for the version you run.

How it works

Step 1: Writing data in parts

When you insert a batch of rows, the server sorts them by the table key and writes them as a new immutable "part" on disk. A part holds one file set per column, plus the small primary index. Because parts are written in one go, inserts are fast, and the system prefers fewer, larger inserts over many tiny ones.

Analogy: instead of squeezing each new page into the middle of a bound book, you add a new sorted booklet to a shelf. Later, a librarian merges small booklets into bigger ones.

Step 2: Background merges

A background process merges parts into larger ones. In the common MergeTree family of engines, the merge keeps the data sorted by the key. Some engines do extra work during a merge, such as collapsing duplicate keys or summing numbers. Merges keep the number of parts small, which keeps reads fast.

Step 3: The sparse primary index

The primary index does not hold one entry per row. It holds one entry per block of rows, called a granule, by default several thousand rows. When a query filters on the key columns, the engine uses the index to skip granules that cannot match. This is a bit like the thumb tabs on a dictionary. You do not find the exact word, but you jump close and scan a small range.

Choosing the sort key is the most important design decision. Put the columns you filter on most often first, and order them from low to high cardinality in many cases.

Step 4: Reading only what is needed

A query that touches three columns out of a hundred reads only three column files. Within those files, only the granules that survive the index are read. Data is decompressed in blocks and processed in a vectorized way.

Step 5: Parallel execution

A single query uses many CPU cores. Each core works on a different range of blocks, and partial results are combined at the end. On a cluster, the query is sent to every shard, each shard does local work, and an initiator node merges the partial results.

Step 6: Skipping indexes, projections and views

Beyond the primary key, you can add data-skipping indexes that store small summaries such as min/max or a bloom filter per block of granules. Projections store a second sorted copy of part of a table, so different query shapes can be fast. Materialized views run a query on each inserted batch and write the result into another table, which supports real-time rollups.

Step 7: Replication and distribution

Replicated table engines coordinate through a consensus service so that every replica gets the same parts. Distributed tables are a thin layer that routes inserts and queries to shards. You pick a sharding key so related rows land together.

Key concepts and terms

A worked example

Suppose you collect page view events. You want to see traffic per country per hour and keep a clean feature table for a model.

CREATE TABLE events
(
    event_time   DateTime,
    user_id      UInt64,
    country      LowCardinality(String),
    page         String,
    duration_ms  UInt32,
    is_bot       UInt8
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (country, event_time);

Insert data in large batches from your pipeline, for example from files or from a message stream. Then query:

SELECT
    country,
    toStartOfHour(event_time) AS hour,
    count() AS views,
    uniq(user_id) AS users,
    quantile(0.95)(duration_ms) AS p95_duration
FROM events
WHERE event_time >= now() - INTERVAL 7 DAY
  AND is_bot = 0
GROUP BY country, hour
ORDER BY hour, country;

What happens inside, in plain steps:

1. The partition filter drops months that cannot match.
2. The sparse index on (country, event_time) skips granules outside the time range.
3. Only five columns are read: event_time, country, user_id, duration_ms, is_bot.
4. Blocks are processed in parallel and combined into the final groups.

A rollup with a materialized view

CREATE TABLE hourly_views
(
    hour DateTime,
    country LowCardinality(String),
    views UInt64
)
ENGINE = SummingMergeTree
ORDER BY (country, hour);

CREATE MATERIALIZED VIEW hourly_views_mv TO hourly_views AS
SELECT toStartOfHour(event_time) AS hour, country, count() AS views
FROM events
GROUP BY hour, country;

Each insert into events also adds partial sums into hourly_views. The summing engine adds rows with the same key during merges. When you query this table, you still sum again with a GROUP BY, because merges are eventual.

A feature table for a model

SELECT
    user_id,
    count() AS n_events,
    avg(duration_ms) AS avg_duration,
    countIf(page = '/pricing') AS pricing_views,
    max(event_time) AS last_seen
FROM events
WHERE event_time >= now() - INTERVAL 30 DAY
GROUP BY user_id;

The result is a plain table with one row per user and mixed numeric, count, and time columns. You can export it to a file and give it to a tabular model with no further work.

Reading it from Python

import clickhouse_connect

client = clickhouse_connect.get_client(host="example.internal", port=8123,
                                       username="reader", password="placeholder")
df = client.query_df("SELECT user_id, n_events, avg_duration FROM features_30d")
print(df.shape)

Use a read-only user for such access, and keep the credentials in a secret store, not in code.

A numeric walk-through of why columns win

Imagine a table with 100 columns and 1 billion rows. Assume each value takes about 8 bytes before compression.

• Raw size: 100 columns x 1 billion rows x 8 bytes is about 800 GB.
• A query that needs 3 columns reads about 24 GB in a plain layout of columns.
• A row layout must read all 800 GB, because rows are stored whole.
• With typical compression of a few times, the 24 GB may shrink to under 10 GB on disk.
• With a sort key filter that skips 90 percent of granules, the read drops again to about 1 GB.

The exact numbers depend on data, but the shape of the saving is the point. Skipping columns, compressing, and skipping blocks multiply together.

A small data-quality profile

Before sending a table to any model, profile it. One query can cover the key checks.

SELECT
    count() AS total_rows,
    countIf(duration_ms = 0) AS zero_duration,
    countIf(country = '') AS empty_country,
    uniqExact(user_id) AS distinct_users,
    min(event_time) AS first_seen,
    max(event_time) AS last_seen
FROM events;

Read the output like a doctor reads a blood test. A high share of empty values, a time range that is shorter than expected, or a surprising number of distinct users all deserve a look before training or scoring.

Splitting train and test by time

For tabular prediction, a random split can leak the future into the past. A time split is safer.

-- training slice
SELECT * FROM features_daily WHERE day < '2026-06-01';
-- test slice
SELECT * FROM features_daily WHERE day >= '2026-06-01';

Keep the cut date in a config file, so that every run uses the same boundary.

Exporting for a model

SELECT * FROM features_30d
INTO OUTFILE 'features_30d.parquet'
FORMAT Parquet;

Columnar file formats keep types, which means the model side does not need to guess whether a column is a number or a string.

Strengths and limitations

Strengths

• Very fast scans and aggregations over wide, large tables.
• Strong compression, so storage cost per row is low.
• Efficient inserts of large batches, and fast ingestion from streams.
• SQL interface with rich analytical functions, arrays, maps, and approximate algorithms.
• Scales from a laptop to a cluster, and can read and write many file formats directly.
• Works well with object storage for cold data.
• Open source with an active community.

Limitations

• Not a transactional system. There are no general multi-statement transactions in the sense of a classic relational database.
• Updates and deletes are heavier operations. They rewrite data parts and are not meant for frequent single-row changes.
• Primary key is not unique. Duplicates are possible unless you design for it.
• Point lookups by arbitrary key are not its best case.
• Joins work, but large joins need care in table order and memory use.
• Many small inserts create many parts and can overload merges.
• Schema and key design matter a lot. A poor sort key can make queries slow.
• Operating a cluster needs skill, though managed offerings reduce this.

How it compares with alternatives

A common pattern is to keep operational data in a transactional database and copy it to ClickHouse for analytics. Another is to send events straight from a stream into ClickHouse and keep only a short history in the operational system. Tools such as Kafka Connect and dbt often sit around it, moving data in and shaping tables.

Role in tabular foundation models

A tabular foundation model learns from raw tables: rows of mixed types, with columns that may be unnamed or poorly named, with missing values, and with relationships that differ from table to table. ClickHouse is not a model. It is a place where many such tables live and are prepared. Its role is to move, shape, and serve tabular data at scale. See /model/architecture for how a model consumes a table.

Row and column structure

A tabular model treats a table as a set of rows and a set of columns. A column store is built the same way in storage: each column is stored as its own typed array. Pulling a sample of rows or a subset of columns for a model is a natural, cheap query. Pulling only the columns a model needs avoids reading the rest.

Mixed types

ClickHouse has strong types: integers of several widths, floats, decimals, strings, dates, enums, arrays, and nullable versions of each. These types map well to the numeric, categorical, and datetime inputs a tabular model expects. Careful typing also catches mistakes early, such as numbers stored as text.

Missing values

A Nullable type marks a value as missing. Be careful here: in a raw table, a missing value may be a null, an empty string, or a sentinel such as -1 or 9999. A good preparation query makes these explicit, for example turning sentinels into nulls, so that a model or an imputation step can treat them properly. Because ClickHouse is fast, you can profile the share of missing values in every column across billions of rows in seconds.

No column names

Some real tables arrive with generic headers like col1, col2. A tabular foundation model that does not rely on names can still use them, but a human needs a way to track what each column means. A database table with a catalog of columns, comments, and types helps keep meaning attached. You can store comments per column and keep a data dictionary alongside.

Pre-training data and sampling

Models are pre-trained on large collections of tables, often synthetic ones, and sometimes on real tables as well. When real tables are used, an analytical store is a convenient place to build the collection: it can sample rows with a repeatable seed, cap table size, and compute basic profile statistics. Sampling clauses and hash-based filters give stable subsets.

SELECT *
FROM events
WHERE cityHash64(user_id) % 100 < 5   -- stable 5 percent sample by user
LIMIT 100000;

In-context learning

A model that does in-context learning reads a labeled table as a prompt and predicts for new rows without further training. The size of that context is limited, so you need a good selection of rows. ClickHouse can build that context: stratified samples by label, nearest-neighbor style filters on a few columns, or the most recent rows. The query is the "retrieval" step, and the model is the "reader".

Continual learning

When new data arrives each day, a model may be updated or re-evaluated. A store that keeps time-partitioned data makes it simple to build "last week" or "since the last release" slices, and to compare feature distributions between periods. A drift check can be one query that compares histograms of a column over two windows.

Imputation

Imputation fills in missing cells. A database can measure the problem first: which columns miss most, whether missingness depends on another column, whether it is concentrated in a period. It can also hold the results: a table of imputed values with a flag that says which cells were filled, so that nothing is mixed up with real observations.

Anomaly detection

Analytical engines suit anomaly work well. Simple rules, such as rolling quantiles, z-scores per group, or rare category counts, are one query each with window functions. Model scores can be written back to a table next to the original rows, and then dashboards or alerts read them.

Sector and semantic classification

Classifying what a column or a table means, for example whether a column holds an amount, an identifier, a date, or a postal code, or which industry a table belongs to, starts from profile data. ClickHouse can compute distinct counts, length stats, value patterns, and top values per column quickly. These profiles are exactly the compact evidence that a classifier needs, and they avoid sending raw sensitive values to another system.

SELECT
    uniqExact(country) AS distinct_values,
    avg(length(country)) AS avg_len,
    topK(5)(country) AS top_values
FROM events;

Serving predictions

Predictions from a model can be written back as columns or a table, and queried together with the source data. Batch scoring jobs read a feature table, call a model, and insert scores. For low-latency per-request scoring, an analytical database is usually not the right place for feature lookups; a key-value store such as Redis is a better fit, with ClickHouse as the offline source.

For benchmark context on how tabular models are judged, see /model/benchmarks.

Practical guidance and best practices

Schema design

• Pick the ORDER BY key from the filters you use most. Put selective, common filter columns first.
• Use the smallest numeric type that fits. UInt8, UInt16, and so on save space.
• Use LowCardinality for string columns with a modest number of distinct values.
• Use Nullable only where needed. It adds a hidden mask column and some cost.
• Use DateTime or Date types, not strings, for time.
• Partition by month or day, not by a high-cardinality column. Too many partitions hurt.

Ingestion

• Insert in batches of tens of thousands of rows or more, not one row at a time.
• If your source produces small events, buffer them in a stream or use the built-in async insert option.
• Keep inserts idempotent where you can, by using a deduplication token or a deterministic key, so retries do not create duplicates.
• Validate types before insert to avoid rejected batches.

Querying

• Select only the columns you need.
• Filter on the key columns and on the partition column.
• Prefer approximate functions, such as uniq, when exact counts are not required.
• Pre-aggregate with materialized views for dashboards that repeat the same query.
• Check query plans with EXPLAIN and look at how many rows and granules were read.

Operations

• Monitor part counts, merge backlog, disk, memory, and slow queries.
• Set TTL rules to expire or move old data.
• Use replication for availability and test restores.
• Use quotas, profiles, and read-only users to protect the cluster.
• Plan upgrades, since versions change quickly.

Data governance

• Mask or hash personal fields before they reach analytics tables.
• Use row policies and column grants where several teams share a cluster.
• Keep an audit trail of which table version fed which model run.

A short launch checklist

Use this list before you put a new ClickHouse table into production for analytics or model features.

1. Write down the three most common queries and their filters.
2. Choose the ORDER BY key to match those filters.
3. Choose partitions that match how you expire data.
4. Pick types, using LowCardinality and small integers where they fit.
5. Decide how duplicates are handled, and document it.
6. Set a TTL for raw data and keep rollups longer.
7. Create a read-only user for analysts and another for model jobs.
8. Test a bulk insert of realistic size and watch the part count.
9. Run each key query with EXPLAIN and record rows read.
10. Add monitoring for merges, disk space, and slow queries.
11. Write a restore test and run it once.
12. Record the table definition in version control.

Sizing intuition

• Memory matters for large group-bys and joins, since intermediate state is held in RAM.
• Disk throughput matters for cold scans, so fast local drives help.
• CPU cores speed up almost every query, since work is parallel.
• Network matters on clusters when large intermediate results move between nodes.

Working with time series

• Use a DateTime column as part of the sort key, usually after a low-cardinality grouping column.
• Use functions such as toStartOfInterval to bucket time.
• Keep raw events for a short period and rollups for a long one.
• Fill gaps in time series with the WITH FILL clause so charts do not skip missing hours.

Working with semi-structured data

• Store a few known fields as real columns and the rest as a JSON string or map.
• Promote fields to real columns when they are used in filters often.
• Arrays and nested types can hold lists of values per row, which helps for tags and event properties.

Common mistakes

• Treating it like a row database and doing many single-row updates.
• Inserting one row at a time, which creates a flood of parts.
• Choosing a sort key that does not match the query filters.
• Assuming the primary key enforces uniqueness.
• Forgetting that summing or collapsing engines are eventual, and querying without a final aggregation.
• Joining a huge table to another huge table without thinking of the order or memory.
• Storing everything as String, losing compression and speed.
• Over-partitioning, ending up with thousands of tiny partitions.
• Leaving the default user open to the network.
• Copying raw sensitive values into feature tables for a model without masking.
• Evaluating a model on features that include information from the future, because the query did not cut at a time boundary.

Frequently asked questions

What is ClickHouse used for?

ClickHouse is used for fast analytics over large tables of events, logs, metrics, and facts. Typical uses include product analytics, observability data, financial and ad-tech reporting, and building feature tables for machine learning. It shines when data is written in large batches and read in aggregate.

Why is a column database faster for analytics?

A query usually touches a few columns out of many. A column store reads only those columns, and each column compresses well because similar values sit together. The engine also processes blocks of values with vectorized code. Together these reduce both disk reads and CPU work.

Is ClickHouse a replacement for a transactional database?

No. It does not aim to provide the kind of multi-row transactions and fast single-row updates that an application database provides. Most teams keep an operational database for the application and copy data to ClickHouse for analysis. The two systems have different jobs.

Does ClickHouse support SQL?

Yes. It uses a SQL dialect with joins, subqueries, window functions, and many extra functions for arrays, dates, strings, and statistics. Some syntax differs from other databases, so queries may need small changes when you move them over.

How does the primary key work?

The primary key defines the sort order and a sparse index with one entry per block of rows. It helps the engine skip blocks that cannot match a filter. It does not make rows unique, and it is not used to find a single row the way a classic index is.

Can ClickHouse handle updates and deletes?

It can, but they are heavy operations that rewrite parts of the data, and they are best used rarely. For data that changes, designs such as versioned rows with a replacing engine, or append-only logs with later aggregation, are more common. Plan for this early.

How does ClickHouse relate to tabular machine learning?

It stores, profiles, samples, and serves the tables that tabular models learn from. You can compute features, build train and test slices, measure missing values, and write predictions back. The model itself runs elsewhere, but the data work is often done in the database.

Can it be used with tabular foundation models?

Yes, as a data source and a sink. A query can produce a clean table or a context sample that a tabular foundation model reads in context, and the scores can be inserted back for dashboards. See the pages on /model/research and /model/architecture for how such models read tables.

How does it compare with a cloud data warehouse?

Both are analytical systems with columnar storage. A cloud warehouse is usually fully managed with elastic compute and a broad ecosystem, while ClickHouse can be self hosted and tuned for very low latency on event data. Many teams use both for different parts of their stack. See Snowflake for the warehouse side.

How do I load data from a stream?

Common routes are a connector that reads from a message topic and inserts batches, a table engine that reads from a topic directly, or a small service that buffers events and inserts them in bulk. Whichever you choose, aim for large batches and idempotent inserts. See Kafka Connect for one such approach.

Glossary

• Aggregation: Combining many rows into a summary such as a count or an average.
• Batch insert: Writing many rows in one operation.
• Cardinality: The number of distinct values in a column.
• Column store: A storage layout that keeps each column together.
• Compression codec: An algorithm that makes stored data smaller.
• Dictionary: In ClickHouse, an in-memory lookup table loaded from an external source.
• Granule: A small block of rows covered by one primary index entry.
• Materialized view: A query that runs on each insert and stores the result.
• MergeTree: The main family of ClickHouse table engines.
• OLAP: Analytical workloads that scan and aggregate lots of data.
• Part: An immutable sorted chunk of table data.
• Partition: A coarse division of a table, such as by month.
• Projection: An alternate sort order stored inside a table.
• Replica: A copy of data kept on another node.
• Shard: A horizontal slice of a table on one node group.
• Sparse index: An index with one entry per block, not per row.
• TTL: A time-based rule for deleting or moving data.
• Vectorized execution: Processing blocks of values at a time with CPU-friendly code.

Summary

ClickHouse is an open-source column-oriented database for real-time analytics over huge tables. It stores columns separately, compresses them well, uses a sparse index on a sorted key, and runs queries in parallel with vectorized code. It is strongest for dashboards, event and log analytics, and feature building, and weakest for transactions and frequent row updates.

For tabular foundation models, it plays the role of data workbench and sink. It holds typed tables, profiles columns, measures missing values, samples rows for pre-training or for in-context learning, slices data by time for continual learning, stores imputed values and anomaly scores, and supplies compact column profiles for semantic classification. Good schema design, batch ingestion, and careful governance make it a reliable partner to such models. To go deeper into the models themselves, read /model/research, /model/architecture, and /model/benchmarks.

Related

It belongs to Databases and sync in my stack, under Data engineering and lakehouse. Others in the same group: