Most databases start to wheeze somewhere around the point where someone asks, “How many unique visitors hit each page, broken down by country, for the last ninety days?” The table has a few billion rows, the query touches three columns out of forty, and a row-oriented engine still has to drag every full row off disk to answer it. ClickHouse was built to make that exact question boring.

If you’ve followed our DuckDB tutorial, you already know what a fast analytical engine feels like on a single laptop. ClickHouse plays in the same OLAP category, but it takes the opposite deployment path: a proper server you connect to over the network, built to ingest millions of rows per second and answer queries over billions of them. This tutorial covers where ClickHouse came from, how its MergeTree storage engine and sparse index actually work, and a hands-on quickstart you can run in Docker in about ten minutes.

What Is ClickHouse?

(cite index=”113-1″>ClickHouse is a free, open-source, column-oriented database built for online analytical processing, or OLAP. Instead of storing each row’s values together on disk, it stores each column separately, which means a query that only needs three columns reads only those three columns, and each column compresses far better because similar values sit next to each other.

The origin story is a good illustration of why it exists. (cite index=”113-1″>Alexey Milovidov started the technology at Yandex in 2009 to power Yandex.Metrica, a web analytics product that processes traffic data at very large scale. Think of it as the engine behind something similar in spirit to Google Analytics, where the workload is overwhelmingly “append a huge stream of events, then slice and aggregate them in every way imaginable.” (cite index=”113-1″>In 2021, Aaron Katz, Alexey Milovidov, and Yury Izrailevsky founded ClickHouse Inc. to build a company around it.

One distinction is worth settling early, because beginners often lump all fast analytical databases together. (cite index=”100-1″>ClickHouse is an OLAP database like DuckDB, but it uses a client-server setup similar to Postgres. DuckDB lives inside your own process and shines on a single machine. ClickHouse runs as its own service, serves many users at once, and scales out across a cluster when one machine stops being enough.

How ClickHouse Works: The Core Ideas

Almost everything that makes ClickHouse fast traces back to a small number of design decisions in its main storage engine, MergeTree. It’s worth understanding these before you write a single CREATE TABLE, because the choices you make in that statement are the ones you can’t cheaply undo later.

Parts: Every Insert Creates a New Chunk of Data

(cite index=”108-1″>MergeTree storage is built from immutable, append-only parts, with background merges that consolidate and optimize them over time. Each time you run an INSERT, ClickHouse writes the incoming rows as a brand-new part on disk, sorted by your chosen key. (cite index=”108-1″>You can think of each part as a tiny, sorted, columnar database with its own index.

Nothing is updated in place. Instead, a background process quietly merges small parts into bigger ones, and that name, MergeTree, is the whole story in one word. This append-and-merge design is what lets ClickHouse ingest data so fast, since writing a new sorted chunk is far cheaper than finding and modifying existing rows. It’s also the source of the most common beginner error, which we’ll cover in the mistakes section.

The Sorting Key Is Your Index

When you create a MergeTree table, the ORDER BY clause isn’t a cosmetic detail. (cite index=”114-1″>It determines the physical sort order of data on disk, and queries that filter on those columns can skip whole blocks of rows using the primary index. (cite index=”119-1″>ORDER BY is required for MergeTree tables, and it defines both the sort order and the primary index.

Picking this key is the single highest-impact schema decision you’ll make. (cite index=”107-1″>Choosing the right ORDER BY and PARTITION BY keys for your query patterns is the most impactful optimization when designing a ClickHouse schema. A reasonable rule of thumb for beginners: put the columns you filter on most often first, and generally prefer lower-cardinality columns before higher-cardinality ones.

The Sparse Primary Index and Granules

This is the part that surprises people coming from Postgres or MySQL. A traditional B-tree index has an entry for every row. ClickHouse deliberately does not.

(cite index=”111-1″>Instead of indexing every row, the primary index for a part holds one entry, called a mark, per group of rows called a granule, which is why it’s described as a sparse index. (cite index=”106-1″>A granule is a block of 8,192 rows by default. (cite index=”110-1″>The index maps the minimum value of each granule to its position in the column files, so when you filter on the sorting key, ClickHouse finds the candidate granules and reads only those, skipping everything else.

The payoff is memory. (cite index=”109-1″>This sparse design is what lets ClickHouse handle billions of rows while keeping the index small enough to fit entirely in RAM. A B-tree on a billion rows is enormous. A sparse index with one entry per 8,192 rows is tiny, and it still eliminates the vast majority of disk reads for a selective query.

The tradeoff is precision. (cite index=”112-1″>Because the index works at granule level, ClickHouse may read up to 8,191 extra rows at the edges of a range scan. For analytics that’s a trivial cost. For “fetch exactly one row by ID” it’s one reason ClickHouse isn’t a great OLTP database.

Primary Key Versus Sorting Key

Here’s a subtle point that trips people up. (cite index=”109-1″>The sorting key controls the physical order of data, while the primary key, which must be a prefix of the sorting key, controls what actually goes into the sparse index. In most beginner tables you’ll declare only ORDER BY and let the primary key default to the same columns, which is perfectly fine.

Also note that, unlike in a transactional database, a ClickHouse primary key does not enforce uniqueness. It’s an index for skipping data, not a constraint. If you need deduplication, that’s what other engine variants are for.

The MergeTree Engine Family

Plain MergeTree keeps every row you insert. (cite index=”113-1″>Variants like ReplacingMergeTree and SummingMergeTree collapse or sum rows during background merges, which gives you a way to handle deduplication or pre-aggregation without running updates. (cite index=”114-1″>For replicated production setups, the ReplicatedMergeTree variant is used, with each table registered under a path and a replica name. As a beginner, start with plain MergeTree and reach for the variants only when a specific need appears.

Hands-On Quickstart

Time to run something. This walkthrough uses Docker so you don’t have to install anything natively, and it builds a small web-analytics table, which is exactly the workload ClickHouse was born for.

Step 1: Start a ClickHouse Server

docker run -d --name clickhouse \
  -p 8123:8123 -p 9000:9000 \
  --ulimit nofile=262144:262144 \
  clickhouse/clickhouse-server

Port 9000 is the native protocol used by the command-line client, and port 8123 is the HTTP interface, which is handy for quick checks and for tools that talk over HTTP. Give the container a few seconds to start.

Step 2: Connect With the Client

docker exec -it clickhouse clickhouse-client

You’ll land at a prompt where you can run SQL. Confirm the server is alive:

SELECT version();

Step 3: Create a MergeTree Table

CREATE TABLE web_events
(
    event_time DateTime,
    user_id    UInt32,
    url        String,
    country    LowCardinality(String),
    duration   UInt16
)
ENGINE = MergeTree
ORDER BY (country, event_time);

Two details here are worth pausing on. LowCardinality(String) is a ClickHouse-specific wrapper that stores a column with few distinct values, like country codes, using dictionary encoding, which saves space and speeds up filtering. And the ORDER BY (country, event_time) means queries filtering by country, or by country and a time range, will benefit directly from the sparse index.

Step 4: Generate Some Data

You don’t need a download to get a meaningful dataset. ClickHouse has a numbers() table function that produces a sequence of integers, and you can build realistic-looking rows from it in a single batch insert:

INSERT INTO web_events
SELECT
    toDateTime('2026-01-01 00:00:00') + (number % 2592000) AS event_time,
    rand() % 10000                                          AS user_id,
    ['/home', '/pricing', '/docs', '/blog'][1 + rand() % 4] AS url,
    ['IN', 'US', 'DE', 'BR', 'JP'][1 + rand() % 5]          AS country,
    rand() % 600                                            AS duration
FROM numbers(5000000);

That one statement inserts five million rows as a single batch, which is exactly how you should be loading data, and it’s a nice demonstration of why the batching advice later in this guide matters.

Step 5: Run Your First Analytical Queries

SELECT country, count() AS views
FROM web_events
GROUP BY country
ORDER BY views DESC;

SELECT url, avg(duration) AS avg_seconds, uniq(user_id) AS unique_users
FROM web_events
WHERE country = 'IN'
  AND event_time >= toDateTime('2026-01-10 00:00:00')
GROUP BY url
ORDER BY unique_users DESC;

Notice uniq(), ClickHouse’s fast approximate distinct-count function. Exact COUNT(DISTINCT ...) is expensive at scale, and uniq gives you a very close estimate far more cheaply, which is a trade analytics users usually take happily.

Step 6: See the Sparse Index at Work

Don’t just take the index on faith. Ask ClickHouse to show you what it skipped:

EXPLAIN indexes = 1
SELECT count()
FROM web_events
WHERE country = 'IN' AND event_time >= toDateTime('2026-01-10 00:00:00');

(cite index=”110-1″>Running EXPLAIN with indexes enabled is the recommended way to verify that a query actually skips most granules. Look at the granule counts in the output, which show how many were selected out of the total. Then try the same query filtering only on user_id, which isn’t in the sorting key, and compare. The difference is a concrete lesson in why ORDER BY design matters.

Step 7: Inspect Parts

Since parts are central to how ClickHouse behaves, it’s worth peeking at them:

SELECT table, count() AS parts, sum(rows) AS total_rows
FROM system.parts
WHERE active AND table = 'web_events'
GROUP BY table;

After your single large insert you’ll see a small number of parts. Keep this query in mind, because it’s your early-warning check for the biggest beginner pitfall.

Step 8: Add a Materialized View

Materialized views are one of ClickHouse’s signature features. (cite index=”113-1″>They precompute rollups as data arrives, rather than recalculating them on every query. Create a target table for daily page-view counts, then a view that feeds it:

CREATE TABLE daily_views
(
    day   Date,
    url   String,
    views UInt64
)
ENGINE = SummingMergeTree
ORDER BY (day, url);

CREATE MATERIALIZED VIEW daily_views_mv TO daily_views AS
SELECT toDate(event_time) AS day, url, count() AS views
FROM web_events
GROUP BY day, url;

One thing to know: a materialized view fires on new inserts only. It won’t backfill rows that were already in web_events. Insert another batch and then query the rollup:

INSERT INTO web_events
SELECT
    toDateTime('2026-02-01 00:00:00') + (number % 86400),
    rand() % 10000,
    ['/home', '/pricing', '/docs', '/blog'][1 + rand() % 4],
    ['IN', 'US', 'DE', 'BR', 'JP'][1 + rand() % 5],
    rand() % 600
FROM numbers(1000000);

SELECT day, url, sum(views) AS views
FROM daily_views
GROUP BY day, url
ORDER BY day, views DESC
LIMIT 10;

Use sum(views) with a GROUP BY rather than reading the table raw, because SummingMergeTree only collapses rows during background merges, so unmerged parts can still hold partial sums.

Features Worth Learning Next

Once the quickstart feels comfortable, a few features are worth adding to your toolkit, roughly in this order.

TTL rules for automatic data expiry. Analytics data usually has a shelf life, and ClickHouse can delete it for you instead of relying on heavy manual deletes. A TTL clause on the table lets old rows expire on a schedule, which pairs well with monthly partitions because whole partitions can be dropped cheaply:

ALTER TABLE web_events
MODIFY TTL toDate(event_time) + INTERVAL 90 DAY;

Data skipping indexes. The sparse primary index only helps for columns in your sorting key. For occasional filters on other columns, (cite index=”109-1″>you can add a secondary skipping index to a table, and a granularity setting controls how many granules are summarized in each index block. These are lightweight and worth trying before you reorganize an entire table.

Projections. If a table is queried in two quite different ways, one sorting key can’t serve both. (cite index=”119-1″>Projections let you define alternative access patterns on the same table, so ClickHouse can keep a second, differently sorted copy of the data and pick whichever one answers a query best.

Replication and sharding. When one server stops being enough, you graduate from plain MergeTree to the replicated variant, covered in the architecture section above, and distribute data across shards. That’s a topic of its own, and not something to worry about on day one.

Ingestion integrations. ClickHouse can load from files, object storage, and streaming sources, and the batching rules from the mistakes section below apply to all of them. When you stream from a queue, make sure something upstream is grouping events into reasonably sized batches before they hit the table.

Common Beginner Mistakes to Avoid

Inserting one row at a time. This is the number-one problem, and it follows directly from how parts work. (cite index=”122-1″>Unlike row-oriented databases, ClickHouse creates a new immutable data part for every INSERT statement, so inserting rows one by one at any real rate overwhelms the merge queue, leading to the infamous “Too many parts” error. (cite index=”124-1″>The guidance is to batch at least 1,000 rows per insert, with 10,000 to 100,000 rows being the sweet spot. (cite index=”121-1″>The error appears when new parts arrive faster than ClickHouse can merge them, and the fix is to reduce insert frequency and increase rows per insert.

If you genuinely can’t batch on the client, there’s a built-in escape hatch. (cite index=”120-1″>ClickHouse supports asynchronous inserts, which shift the batching work to the server. You enable it with SET async_insert = 1 before your insert.

Over-partitioning. Beginners often assume partitioning works like it does in other systems and partition by something fine-grained. (cite index=”124-1″>Choosing a high-cardinality partition key, such as a millisecond timestamp, spreads parts across thousands of folders that never become merge candidates, which triggers errors on later inserts. For most tables, partitioning by month, or not partitioning at all, is the right starting point.

Treating it like a transactional database. Frequent single-row UPDATE and DELETE statements are the wrong pattern. (cite index=”123-1″>Mutations are powerful but expensive; prefer TTL for data lifecycle, design partitions so old data can be dropped cheaply, and favor insert-based patterns like ReplacingMergeTree over repeated updates. If your workload is mostly point updates, a database like Postgres is the better tool, and you can feed ClickHouse from it for the analytical side.

Picking an ORDER BY at random. Because it defines the sparse index, a key that doesn’t match your common filters means ClickHouse scans far more granules than it should. Design it around your real queries and verify with EXPLAIN indexes = 1.

Expecting unique primary keys. As covered earlier, the primary key is an index for skipping data, not a uniqueness constraint. Duplicate rows will happily coexist unless you use an engine variant designed to collapse them.

When to Use ClickHouse

ClickHouse tends to be a strong fit when:

• You’re ingesting a continuous stream of events, logs, metrics, or clickstream data and need to query it in near real time.
• Your queries aggregate over a handful of columns across very large numbers of rows.
• You need to serve analytics to many concurrent users or power user-facing dashboards, not just one analyst’s notebook.
• Data volume has outgrown a single-node tool and you want a database that scales horizontally with replication and sharding.

It’s a weaker fit for workloads dominated by frequent single-row updates, strict relational integrity, or low-latency lookups of individual records. For those, pair it with a transactional database rather than replacing one.

ClickHouse and the Rest of Your Data Stack

ClickHouse rarely lives alone. It reads and writes common formats like CSV, JSON, and Parquet directly, and it can pull data from object storage and message queues, so it slots into pipelines built around a data lake. If you’re exploring that world, our guides on Apache Iceberg and Trino cover the table formats and federated query engines that often sit alongside it. And if your analytics needs are modest and local, our MotherDuck guide shows the lighter-weight path.

Frequently Asked Questions

Is ClickHouse free? Yes, ClickHouse is open source and free to self-host. A managed cloud offering from ClickHouse Inc. also exists if you’d rather not operate it yourself.

What is ClickHouse used for? It’s used mainly for real-time analytics over very large datasets: web and product analytics, observability and log analysis, time-series and IoT data, and user-facing dashboards.

What does MergeTree mean? MergeTree is ClickHouse’s core table engine. It writes data as immutable sorted parts and merges them in the background, which is what gives it fast ingestion and fast queries at the same time.

Why is ClickHouse so fast? Several design choices stack up: columnar storage so queries read only the columns they need, aggressive compression, data sorted by the primary key, a sparse index that skips irrelevant granules, and parallel execution across cores.

What does the “Too many parts” error mean? It means data is being inserted faster than ClickHouse can merge the resulting parts. The fix is to send fewer, larger inserts, or to use asynchronous inserts so the server batches for you.

Can I update or delete rows in ClickHouse? Yes, through mutations, but they’re heavy operations that rewrite data parts. They’re meant for occasional use, not as a regular workflow. Prefer insert-based patterns, TTL rules, and dropping old partitions.

How is ClickHouse different from DuckDB? Both are columnar OLAP engines, but DuckDB runs inside your own process and targets a single machine, while ClickHouse is a client-server database built to ingest at high volume, serve many users, and scale across a cluster.

Is ClickHouse a replacement for PostgreSQL? No. They solve different problems. PostgreSQL is a row-oriented transactional database, while ClickHouse is an analytical one. Many teams run both, keeping Postgres as the system of record and ClickHouse as the analytics layer.

Final Thoughts

ClickHouse rewards you for understanding three ideas: data lands in immutable parts, your ORDER BY key decides how well queries can skip work, and inserts should arrive in big batches. Get those right and the performance feels almost unfair. Get them wrong and you’ll meet the “Too many parts” error sooner than you’d like, which is a rite of passage most ClickHouse users share.

The best next step is to rerun the quickstart above and experiment: change the ORDER BY, rerun EXPLAIN indexes = 1, and watch the granule counts move. For the authoritative reference on every table engine, setting, and function, ClickHouse’s official documentation is the place to go deeper.

For more breakdowns of open-source data infrastructure and analytics tooling like this one, keep exploring the guides on CourseDrill.