How to Run ClickHouse Locally and Load 10M Rows
ClickHouse is an open-source columnar database built for analytical queries over billions of rows. A single laptop container can aggregate 10 million rows in under a second if the table uses a sensible sort key — a workload Postgres on the same box handles in 8 to 20 seconds.
This walkthrough covers the container, the schema decisions that determine speed, and a bulk load that finishes in about 40 seconds. No cluster, no cloud account.
What do you need to install ClickHouse?
Docker on macOS, Linux or Windows with WSL2, plus 4 GB of RAM. The official image is clickhouse/clickhouse-server. Skip the Homebrew build on an Intel Mac — it works but starts cold slowly and differs from server defaults.
docker run -d --name ch --ulimit nofile=262144:262144 \
-p 8123:8123 -p 9000:9000 \
-v $HOME/ch-data:/var/lib/ch \
clickhouse/clickhouse-server:24.8
Port 8123 is the HTTP interface, 9000 the native TCP protocol. The volume mount matters: without it, every restart wipes the data and you’ll rebuild your tables repeatedly.
Wait 10 seconds, then check:
curl 'http://localhost:8123/?query=SELECT%20version()'
You should get something like 24.8.4.13. The built-in client is easiest through the container:
docker exec -it ch clickhouse-client
Default user is default with no password, which is fine locally and unacceptable anywhere else.
How do you design a MergeTree table that stays fast?
ClickHouse only reads the columns a query touches, and the ORDER BY of your table is a sparse index over the sort key. Get that key wrong and every query scans everything.
Start with one concrete case: web events, 10 million of them.
CREATE TABLE events (
ts DateTime,
site LowCardinality(String),
country LowCardinality(String),
path String,
user_id UInt64,
duration UInt16
) ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (site, ts);
Three decisions worth explaining.
LowCardinality on site and country stores a dictionary plus integer IDs. With under 10,000 distinct values it shrinks those columns 3-10x. On a high-cardinality column like user_id it makes things worse.
ORDER BY (site, ts) means queries filtering on site and a time range touch a narrow contiguous slice. If most of your questions are “per site, last 7 days”, this is the right key. If they’re “per country, per day”, you’d order by (country, ts) instead.
PARTITION BY toYYYYMM(ts) keeps partitions around 1-5 million rows. Monthly is typical. Daily partitions on a small table create thousands of parts and the merge threads fall behind — that’s the “too many parts” error people panic about.
How do you load 10 million rows fast?
Generate the data as a CSV and stream it in. clickhouse-client with --format CSV does bulk inserts at 100k-500k rows per second; single-row INSERT statements do about 200 rows per second, which is the mistake almost everyone makes first.
python3 -c "
import random,csv,sys,datetime
w=csv.writer(sys.stdout)
sites=['shop','blog','docs'];countries=['US','DE','JP','BR','IN']
base=datetime.datetime(2026,1,1)
for i in range(10_000_000):
ts=base+datetime.timedelta(seconds=i*2)
w.writerow([ts.strftime('%Y-%m-%d %H:%M:%S'),random.choice(sites),random.choice(countries),'/p/%d'%random.randint(1,500),random.randint(1,999999),random.randint(5,900)])
" > /tmp/events.csv
docker exec ch clickhouse-client --query "INSERT INTO events FORMAT CSV" < /tmp/events.csv
Two minutes of generation, roughly 40 seconds of loading. Verify with SELECT count() FROM events — you want exactly 10,000,000.
If the load fails with a memory error, chunk the file and insert in 1M-row batches. ClickHouse buffers inserts into parts; 10M rows in one shot creates one large part and the merge is slow but works.
What queries prove the setup is right?
Run these three. The point isn’t the numbers, it’s that the third query should return in well under a second.
SELECT site, count(), round(avg(duration),1)
FROM events WHERE ts >= '2026-03-01' GROUP BY site;
SELECT toStartOfHour(ts) AS h, count()
FROM events WHERE site='shop' AND ts >= '2026-03-01'
GROUP BY h ORDER BY h;
SELECT country, quantile(0.95)(duration)
FROM events WHERE ts >= '2026-04-01' GROUP BY country ORDER BY 2 DESC;
Check what the engine actually did with EXPLAIN indexes = 1 SELECT .... You want to see Granules dropping to a fraction of the total. If it reads 100% of granules for a filtered query, your ORDER BY doesn’t match the filter.
What are the limits of a single node?
One node is genuinely useful up to a few hundred million rows — plenty for internal analytics. The ceiling shows up in three places.
Joins. ClickHouse joins are hash joins held in memory. Joining a 10M-row fact table to a 50M-row dimension will exhaust RAM on a 16 GB box. The usual fix is denormalising at insert time or using dictionaries.
Updates. ALTER TABLE ... UPDATE is a mutation that rewrites whole parts. It’s asynchronous by default and expensive on large tables. If your data changes constantly, you want ReplacingMergeTree and a dedupe strategy, not UPDATE.
Concurrency. Hundreds of simultaneous queries will queue. It’s an analytics engine, not an OLTP database.
Managed ClickHouse Cloud starts around $0.20/hour on the basic tier for small instances, which is often cheaper than the engineering time to self-host. For a one-off 10M-row analysis, self-host the container and throw it away afterwards.
Quick Summary
- Run ClickHouse locally with the official Docker image on ports 8123 (HTTP) and 9000 (native).
ORDER BYis the index — match it to your dominant filter or every query scans everything.- Bulk insert with
clickhouse-client --format CSV; row-by-row inserts are ~1000x slower. - Use
LowCardinalityonly under ~10k distinct values. - Single-node is fine to a few hundred million rows; beyond that, joins and mutations become the wall.”}
