ClickHouse: Open-Source Column-Oriented OLAP Database
What ClickHouse is
ClickHouse is a column-oriented SQL database for analytics. The README describes it as "an open-source column-oriented database management system that allows generating analytical data reports in real-time", and the introduction in the docs calls it a "high-performance, column-oriented SQL database management system (DBMS) for online analytical processing (OLAP)". The target workload is the opposite of a typical application database: queries that scan, filter and aggregate millions or billions of rows, rather than ones that read and write a few rows by primary key.
It suits dashboards, observability backends, product analytics and other reporting over append-heavy event data. One executable provides a replicated, sharded server, a client, and clickhouse-local, a serverless mode that runs SQL directly over files.
The repository is ClickHouse/ClickHouse, written in C++, created on 2 June 2016 and developed on the master branch. It is licensed under Apache-2.0; the LICENSE file opens with "Copyright 2016-2026 ClickHouse, Inc." ClickHouse, Inc. also sells a hosted service, ClickHouse Cloud, which the install docs list first; this write-up covers only the open-source server.
How it works
Parts, sorting keys and the sparse index
Most tables use a MergeTree-family engine. Per the docs on table parts, every INSERT creates an immutable data part: rows are sorted by the sorting key, split into columns, compressed and written as column files with a sparse primary index. Background merges combine small parts into larger ones.
The index is sparse: columns are divided into granules of 8,192 rows and the index stores the key of each granule's first row, keeping it "small enough to fit entirely in memory". Queries filtering on key columns use it to skip granules. The same docs list the flip side among ClickHouse's disadvantages: "the sparse index makes ClickHouse not so efficient for point queries retrieving single rows by their keys."
Compression and codecs
The compression guide names three factors that drive compression: the ordering key, the data types and the codecs. General-purpose codecs (LZ4, ZSTD) can be chained after specialised ones such as Delta, DoubleDelta, Gorilla and T64, per column with CODEC(...). Release 26.9 changed the built-in default for MergeTree data: per the changelog it is now "size-aware: LZ4 for parts below 100 MB and ZSTD(3) at or above it". The hands-on section below confirms this on disk.
Merges do the slow work
Several features finish their work during merges. ReplacingMergeTree removes duplicate sorting keys only when parts merge and "does not guarantee the absence of duplicates". Lightweight DELETE writes a hidden _row_exists mask; lightweight UPDATE writes "patch parts" applied at read time until merged. Incremental materialized views are "just a trigger that runs a query on blocks of data as they're inserted into a table".
Key features
- One binary, many tools. The 26.10.7.24 executable lists 34 sub-commands, including
server,client,local,keeper(the replication coordination service, an alternative to ZooKeeper),benchmarkandcompressor. - SQL over files.
clickhouse-localqueries CSV, JSON, Parquet and other formats locally, over HTTP(S) or in S3, with schema inference. - MergeTree engine family. Plain
MergeTreeplus Replacing, Summing, Aggregating, Collapsing and VersionedCollapsing variants, with partitions, TTLs, projections and data-skipping indexes. - Per-column codecs. General and specialised codecs, plus beta adaptive codec selection controlled by the
enable_adaptive_codec_selectionMergeTree setting. - Replication and sharding. The docs describe "asynchronous multi-master replication" and queries that run on all shards in parallel, coordinated by ClickHouse Keeper.
- Materialized views. Incremental insert-time triggers for rollups, plus refreshable views.
- Access control. SQL-managed users, roles, quotas and settings profiles.
- Integrations. Table engines and functions for Kafka, S3, MySQL, PostgreSQL and lakehouse formats; recent changelogs also mention PromQL support and a
TimeSeriesengine.
Getting started
The README's install command for Linux, macOS and FreeBSD downloads a single binary into the current directory:
curl https://clickhouse.com/ | sh
On Linux and macOS this also installs the clickhousectl CLI; to get only the database binary:
curl https://clickhouse.com/ | CLICKHOUSE_ONLY=1 sh
The binary then runs clickhouse-local, a server storing data in the current directory, and a client:
./clickhouse
./clickhouse server
./clickhouse client
For production on Debian or Ubuntu, after adding the packages.clickhouse.com repository:
sudo apt-get install -y clickhouse-server clickhouse-client
sudo service clickhouse-server start
clickhouse-client
The Docker page uses the official image:
docker run -d --name some-clickhouse-server --ulimit nofile=262144:262144 clickhouse/clickhouse-server
docker exec -it some-clickhouse-server clickhouse-client
In the image the default user "doesn't have the network access unless the password is set" via CLICKHOUSE_PASSWORD.
Ad-hoc file queries with clickhouse-local, from its documentation:
./clickhouse local -q "SELECT * FROM 'reviews.tsv'"
./clickhouse local -q "DESCRIBE file('reviews.tsv')"
Hands-on: ClickHouse 26.10.7.24 on a Linux VM
Everything in this section was run on 19 November 2026 in a cloud VM: Ubuntu 24.04.5 LTS, Linux 6.18, x86_64, 2 vCPUs, about 7.8 GiB RAM and no swap. The binary came from the official clickhouse-common-static-26.10.7.24 amd64 package, extracted without installing. Data was generated with numbers() and cityHash64, so it is synthetic and deterministic. Timings are single-machine wall-clock figures on a 2 vCPU VM and only indicative.
Binary and startup
| Check | Result |
|---|---|
| Binary size | 892,610,576 bytes (about 851 MiB), not stripped; ldd shows only glibc libraries (libc, libm, libdl, librt, libpthread) |
clickhouse local running SELECT version(), 1+1 | 26.10.7.24, 2 in 0.10 s |
Built-in catalogue (from system.*) | 2,008 functions, 81 input and 99 output formats, 85 table engines, 18 codecs |
clickhouse server in an empty directory | "There is no file 'config.xml', will use embedded config."; "Ready for connections" about 1 s after launch |
| Listeners | 127.0.0.1 only: HTTP 8123, native 9000, MySQL 9004, PostgreSQL 9005. The [::1] listeners failed with "Address family not supported" because the VM has no IPv6; the server logged a warning and carried on |
| Sandbox | "Applied a seccomp policy to this process, allowing 237 system calls", in log mode under the embedded config |
| Idle memory | MemoryResident 658 MiB, of which MemoryCode (the mapped binary) was 501 MiB; max_server_memory_usage defaulted to 6,173,039,001 bytes |
| HTTP | GET / and /ping return Ok.; /play serves the built-in web UI (a 1,225,565-byte page) |
The server was started in the background from a scratch directory, where it created data, metadata, store and access directories. It ran as root because the VM has no other account, but nothing in this setup (ports above 1024, a scratch data path) should require root. The default user had no password, tolerable only on loopback. The 26.10 changelog says the packaged config.xml sets seccomp to trap instead, which can stop a server in a sandbox that refuses the seccomp call.
clickhouse-local over CSV and Parquet
A 5-million-row events file (id, timestamp, source, user, amount) was generated with clickhouse local and written as CSV, then converted to Parquet with an explicit schema so both files hold identical data.
| Step | Result |
|---|---|
Write 5M rows as CSVWithNames | 0.80 s, 276,678,784 bytes |
| Convert CSV to Parquet | 3.63 s, 727 MB peak RSS, 39,406,506 bytes (7 times smaller than the CSV) |
DESCRIBE file('events.csv') | Every column inferred as Nullable: Nullable(Int64), Nullable(DateTime), Nullable(String), Nullable(Float64) |
DESCRIBE file('events.parquet') | UInt64, DateTime64(3, 'UTC'), String, String, Float64 |
GROUP BY source with count(), sum(), uniqExact(user), 3 runs | CSV 1.89 to 2.08 s at about 530 MB RSS; Parquet 0.60 to 0.85 s at about 370 to 390 MB |
Filter WHERE user = 'user_42' | 96 rows; CSV 1.59 s, Parquet 0.18 s |
Two ETL details: CSV inference wraps every type in Nullable (as in the docs' examples), so pass an explicit structure before loading into a table; and the Parquet round trip turned DateTime into DateTime64(3, 'UTC') and LowCardinality(String) into String.
A 20-million-row MergeTree table
The main table had seven columns (ts DateTime, user_id UInt32, source LowCardinality(String), path String, status UInt16, duration_ms UInt32, amount Float64), no explicit codecs, and ORDER BY (user_id, ts). One INSERT ... SELECT ... FROM numbers(20000000) took 6.02 s and arrived as 18 parts of about 1.1 million rows; merges reduced them to 3 within about 20 seconds, and OPTIMIZE TABLE ... FINAL to one. The rows were then copied into a table with hand-picked codecs and one sorted by (source, ts).
| Column | Default codec, ORDER BY (user_id, ts) | Explicit codecs, same order | Default codec, ORDER BY (source, ts) |
|---|---|---|---|
ts | 60.65 MiB (ratio 1.26) | Delta, ZSTD(1): 44.20 MiB (1.73) | 3.95 MiB (19.32) |
user_id | 282 KiB (276.9) | ZSTD(1): 282 KiB (276.9) | 50.86 MiB (1.50) |
source | 6.27 MiB (3.05) | ZSTD(1): 6.05 MiB (3.16) | 17 KiB (1,141) |
path | 51.02 MiB (7.77) | ZSTD(3): 51.02 MiB (7.77) | 51.02 MiB (7.77) |
status | 6.15 MiB (6.21) | T64, ZSTD(1): 7.86 MiB (4.85) | 6.15 MiB (6.21) |
duration_ms | 33.27 MiB (2.29) | T64, ZSTD(1): 23.93 MiB (3.19) | 33.27 MiB (2.29) |
amount | 64.06 MiB (2.38) | ZSTD(1): 62.94 MiB (2.42) | 64.06 MiB (2.38) |
| Table on disk | 221.75 MiB (3.77 overall) | 196.33 MiB (4.25) | 209.37 MiB (3.99) |
All three hold 835.04 MiB uncompressed. The sorting key mattered more than codecs: behind a high-cardinality user_id, timestamps barely compressed, while sorting by (source, ts) shrank ts 15-fold at the cost of user_id. Delta and T64 helped, but T64, ZSTD(1) made status larger than the default.
The identical path sizes were the most useful finding: the default-codec table's path.bin was byte-for-byte identical to the explicit ZSTD(3) one, with ZSTD method bytes in its block headers. A second test wrote one 20-million-row String column in parts under 100 MB:
| Codec on the column | Compressed bytes |
|---|---|
| None declared (default) | 97,841,428 |
CODEC(LZ4) | 97,841,428 |
CODEC(ZSTD(1)) | 58,687,869 |
CODEC(ZSTD(3)) | 53,494,255 |
CODEC(ZSTD(9)) | 53,538,185 |
CODEC(NONE) | 415,683,037 (raw data: 415,560,937) |
So the default is size-dependent, as the 26.9 changelog says: LZ4 in small parts, ZSTD(3) in the large seven-column parts. Ratios measured on small test tables understate production-sized ones, and codec benchmarks should state part sizes.
Indexes and query timings
| Query on the 20M-row table | Granules read | Time (3 runs) | Peak query memory |
|---|---|---|---|
GROUP BY source, status with count, avg, quantile(0.95), sum (16 groups) | 2,442 / 2,442 | 0.62, 0.53, 0.51 s | 4.5 MiB |
WHERE user_id = 4242 (first key column, 178 matching rows) | 1 / 2,442 (8,192 rows) | 0.004 to 0.005 s | 271 KiB |
One-hour ts range on ORDER BY (user_id, ts) (72,000 rows) | 2,442 / 2,442 | 0.05 to 0.06 s | 645 KiB |
Same range on ORDER BY (source, ts) | 11 / 2,442 (90,113 rows) | 0.005 s | 249 KiB |
GROUP BY user_id (100,000 groups) | all | 0.20 s | 13.7 MiB |
GROUP BY path, user_id (high cardinality) | all | 1.81 s | 1.10 GiB |
uniqExact(path) vs uniq(path) | all | 0.47 s vs 0.28 s | 3.4 vs 2.6 MiB |
EXPLAIN indexes = 1 showed the difference clearly. For the user_id lookup it printed Condition: (user_id in [4242, 4242]), Granules: 1/2442 and Search Algorithm: binary search. For the ts range it printed Granules: 2442/2442 with generic exclusion search: a filter on the second key column alone skipped nothing. Approximate uniq(user_id) returned 100,315 against an exact 100,000, and Float64 sums produced values such as 136557.56999999995; use Decimal for money.
ReplacingMergeTree: deduplication waits for a merge
A ReplacingMergeTree(updated_at) table ordered by user_id received 1,000,000 rows and then 100,000 newer versions of existing users, with merges stopped to make the state visible.
| Read | Rows | Time |
|---|---|---|
Plain SELECT count() | 1,100,000 (duplicates visible) | 0.002 s |
SELECT count() ... FINAL | 1,000,000 | 0.014 s |
argMax(plan, updated_at) grouped by user_id | 1,000,000, of which 100,000 on the new plan | - |
After SYSTEM START MERGES and a 5 s wait | still 1,100,000 in 2 parts | - |
After OPTIMIZE TABLE ... FINAL | 1,000,000 in 1 part | 0.09 s |
As the docs warn, merges happen "at an unknown time", so reads need FINAL or an argMax-style aggregation.
Materialized view rollup
A SummingMergeTree target table and an incremental materialized view computing hourly orders and revenue per source were created on top of the 20M-row table. The target then held 0 rows: existing data is not backfilled. Two 1,000,000-row inserts took 0.28 s and 0.21 s including the view, leaving 112 target rows in 2 parts. A naive SELECT returned two rows of 18,000 orders for the same hour and source; sum(orders) ... GROUP BY gave the correct 36,000. Reads from such targets must aggregate again.
Lightweight DELETE and UPDATE
| Operation | Observed |
|---|---|
DELETE FROM events WHERE status = 500 (1,999,456 of 22,000,000 rows) | 0.22 s; count() then returned 20,000,544, but the main part still physically held 20,000,000 rows with has_lightweight_delete = 1, a new 2,508,220-byte _row_exists.bin, and other column files hard-linked (link count 2). system.mutations recorded it as UPDATE _row_exists = 0 WHERE status = 500 |
OPTIMIZE TABLE events FINAL afterwards | 7.5 s; one part of 20,000,544 rows, 217.7 MB instead of 242.8 MB across 3 parts |
UPDATE events SET amount = 0 WHERE user_id = 4242 | Failed: "Lightweight updates are supported only for tables with materialized _block_number column. Run 'MODIFY SETTING enable_block_number_column = 1' command to enable it." (code 48, NOT_IMPLEMENTED) |
After MODIFY SETTING enable_block_number_column = 1, enable_block_offset_column = 1 | Same UPDATE succeeded in 0.008 s, writing a 180-row patch part of 2,088 bytes; the new value was visible immediately |
UPDATE of about 10% of rows (status 404 to 410) | 0.48 s, two patch parts (1,048,864 and 951,654 rows, about 14 MB) |
GROUP BY status with patches pending | 0.31 to 0.33 s |
ALTER TABLE ... UPDATE mutation with mutations_sync = 1 | 2.57 s; the same GROUP BY then took 0.045 to 0.052 s, and the patch parts were removed shortly after |
This matches the docs: deletes are cheap but leave data on disk until a merge, and updates slow reads until patches are materialised; the docs suggest them for "up to about 10% of the table".
Small inserts and disk accounting
- 300 single-row
INSERTstatements in one client session, with merges stopped, took 18.3 s (about 61 ms each) and created 300 compact parts totalling 214,841 bytes. After merges resumed they collapsed to one within 8 seconds. Batch rows per insert. - Late in the session
dureported 4.7 GB understore/against 636 MiB of active parts: 379 inactive parts (2.16 GiB) were waiting outold_parts_lifetime = 480seconds, and dropped tables were kept fordatabase_atomic_delay_before_drop_table_sec = 480. Plan disk headroom accordingly. - The embedded config has no query log:
system.query_logfailed withUNKNOWN_TABLE, so the rows-read and memory figures above came fromclickhouse client --print-profile-eventsand--memory-usage.
The server was shut down with SYSTEM SHUTDOWN at the end. Its peak resident memory over the whole session (VmHWM) was about 1.65 GB, driven by the 1.10 GiB high-cardinality GROUP BY.
Use cases
- Event analytics. Append-only events sorted by tenant or time, with materialized-view rollups; teams wanting a finished product analytics app rather than a database may prefer PostHog.
- Streaming ingestion. Consuming topics from Apache Kafka through ClickHouse's
Kafkatable engine and a materialized view into MergeTree tables, instead of a separate stream processor for simple transforms. - Observability storage. Logs, traces and metrics in wide tables with aggressive codecs. The changelog shows ongoing work on PromQL and a
TimeSeriesengine for Prometheus-style data, and Grafana Labs maintains a ClickHouse data-source plugin, so Grafana dashboards can query it directly. - File querying.
clickhouse-localfor one-off questions about Parquet or CSV files and format conversion in pipelines. - Offloading analytics from an OLTP database. Keeping Postgres as the system of record and replicating into ClickHouse; the transactions guide itself recommends a Postgres database (ClickHouse's managed offering) for OLTP.
How ClickHouse compares
Descriptions come from each project's README or licence file as read on 19 November 2026; none of the alternatives were run here.
| Project | How its README describes it | Deployment model | Licence |
|---|---|---|---|
| ClickHouse | "open-source column-oriented database management system that allows generating analytical data reports in real-time" | Server with replication and sharding; also serverless clickhouse-local | Apache-2.0 |
| DuckDB | "high-performance analytical database system" that "runs in-process inside your application, so there is no separate server to manage" | Embedded library and CLI | MIT |
| PostgreSQL | "advanced object-relational database management system" with "transactions, foreign keys, subqueries, triggers" | Row-store server | PostgreSQL licence |
| Apache Druid | "high performance real-time analytics database" for "powering UIs, running operational (ad-hoc) queries, or handling high concurrency" | Distributed cluster | Apache-2.0 |
| Apache Pinot | "designed to execute real-time OLAP queries with low latency on massive amounts of data and events", for stream and batch ingestion | Distributed cluster | Apache-2.0 |
| StarRocks | "open query engine for sub-second, ad-hoc analytics both on and off the data lakehouse", MySQL-protocol compatible | Distributed cluster | Apache-2.0 |
DuckDB is the nearest comparison on one machine: both query Parquet and CSV in place, and clickhouse-local overlaps with the DuckDB CLI. DuckDB is embedded in a process; ClickHouse centres on a long-running, replicated server queried by many clients. For lakehouse storage on DuckDB, see DuckLake.
PostgreSQL is a general-purpose transactional database; ClickHouse's docs list "no full-fledged transactions" among its own disadvantages. A common arrangement keeps Postgres, or a platform on it such as Supabase, as the system of record and copies data into ClickHouse for analytics.
Druid, Pinot and StarRocks are the closest distributed real-time OLAP peers. Their READMEs emphasise high-concurrency serving (Druid, Pinot) or lakehouse querying with a cost-based optimiser and MySQL compatibility (StarRocks). Benchmark claims there, such as StarRocks' "3x faster", are vendor statements not checked here.
Limits and when not to use it
- Not an OLTP database. The transactions guide says an
INSERTinto one partition of one MergeTree table is atomic, but multi-statement transactions are experimental, inserts into Buffer tables are "neither atomic nor isolated nor consistent nor durable", and inserts into Distributed tables are only transactional per shard. - Updates and deletes are eventual on disk. As measured above, lightweight deletes leave rows on disk until a merge, lightweight updates need table settings and slow reads until patches are materialised, and
ReplacingMergeTreeneedsFINALor careful queries. Workloads with frequent row-level changes fit poorly. - Point lookups are coarse. A primary-key hit still reads a full 8,192-row granule, and filters on a non-leading key column can scan everything, as the
tsrange test showed. - Schema design decides performance. Sort order changed one column's size 15-fold in the test, and decided whether a time filter read 11 granules or all 2,442.
- Small inserts hurt. Each
INSERTcreates a part; the defaultsparts_to_delay_insert = 1000andparts_to_throw_insert = 3000throttle and then reject inserts when parts pile up. - Memory-hungry queries. A high-cardinality
GROUP BYused 1.10 GiB for 20M rows; large joins and aggregations need memory limits and spill settings. - Fast-moving, with breaking changes. The 26.10 changelog lists 37 backward-incompatible changes, including removal of the old query analyzer, the
seccompdefault andarrayCountreturningUInt64. The upgrade guide says to keep custom config inconfig.d/and read every changelog in between. - Experimental features are not stable. Their compatibility "can be broken at any moment in any way", per the upgrade guide; 26.10 cannot read data written with the experimental
ZXCcodec by earlier versions. - Operational weight for clusters. Replication needs ClickHouse Keeper or ZooKeeper, and the upgrade guide advises against mixing versions more than a year apart.
- Vendor gravity. The install docs put ClickHouse Cloud first, and defaults can differ between self-managed and Cloud (the codec page says so for the default codec).
Releases, support and documentation notes
Feature releases are monthly: the changelog lists 26.1 (29 January 2026) through 26.10 (22 October 2026), with 26.3 (26 March) and 26.8 (27 August) as LTS. SECURITY.md lists 26.10, 26.9, 26.8 and 26.3 as receiving security updates and everything else, including all of 25.x, as unsupported. Reports go through Bugcrowd, with acknowledgement promised within 5 working days. Patch releases are frequent: on 17, 18 and 19 November 2026 new patch builds were published for 26.3 LTS, 26.8 LTS and 26.10.
The old clickhouse-docs repository now opens with "We have moved": docs live under docs/ in the main repository, built with Mintlify, with most of the reference generated from code. Cross-checking them against the 26.10.7.24 binary found mismatches:
- The
CODECreference page still says "By default, ClickHouse applieslz4compression in the self-managed version". Since 26.9 the changelog and the test above show a size-dependent default:LZ4below 100 MB per part,ZSTD(3)at or above. - The same page marks the
ZXCcodec as beta, whilesystem.codecsin this build reports its tier asExperimental, and the 26.10 changelog also calls it experimental. The page also documents an experimentalGDeflatecodec that does not appear in this build'ssystem.codecs. - The "Distinctive features" page says "Correlated (dependent) subqueries aren't supported at the time of writing". In 26.10.7.24 both a correlated scalar subquery and a correlated
EXISTSran and returned correct results. - The
CHANGELOG.mdshipped inside the 26.10.7.24 package stops at 26.9; the copy onmasterincludes the 26.10 section.
Project activity
Checked 19 November 2026: latest release v26.10.7.24-stable (published 19 November 2026), latest LTS v26.8.40.7-lts (18 November 2026) with v26.3.66.4-lts (17 November 2026) also maintained, per the GitHub releases API and the supported-versions table in SECURITY.md. Licence Apache-2.0. The ClickHouse/ClickHouse repository had about 51,000 stars (51,013 at the time of writing) and 9,291 forks. It is written in C++, was created on 2 June 2016 and develops on master. Release notes are in the changelog, and the homepage and documentation are at clickhouse.com. The README links monthly release calls, Slack and Telegram communities, and a meetup calendar.
Enjoying this project?
Discover more amazing open-source projects on TechLogHub. We curate the best developer tools and projects.
Repository:https://github.com/ClickHouse/ClickHouse
GitHub - ClickHouse/ClickHouse: ClickHouse: Open-Source Column-Oriented OLAP Database
ClickHouse is the Apache-2.0 column-oriented SQL database for real-time analytics. Version 26.10.7.24 tested on Linux: clickhouse-local over Parquet, 20M-row Me...
github - clickhouse/clickhouse
Related Projects
llama.cpp: LLM inference in plain C/C++
Dependency-free C/C++ LLM inference with quantization, many GPU backends and an OpenAI-compatible server.
OpenCV: Open Source Computer Vision Library
C++ computer vision library with Python bindings for image processing, video, 3D geometry and ONNX inference.
rclone: rsync for cloud storage
Command-line tool to copy, sync, mount and encrypt files across S3, Google Drive, SFTP and 100+ storage providers.
Maintain ClickHouse: Open-Source Column-Oriented OLAP Database? Add the badge to your README
Show that the project is featured on TechLogHub and send readers to this write-up.
[](https://techloghub.com/open-source/clickhouse?utm_source=techloghub_badge&utm_medium=referral&utm_campaign=readme)