ClickHouse: Open-Source Column-Oriented OLAP Database
GitHub Repo
Apache-2.0
November 19, 2026 at 01:55 PM
0 views

ClickHouse: Open-Source Column-Oriented OLAP Database

@ClickHouseProject Author

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), benchmark and compressor.
  • SQL over files. clickhouse-local queries CSV, JSON, Parquet and other formats locally, over HTTP(S) or in S3, with schema inference.
  • MergeTree engine family. Plain MergeTree plus 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_selection MergeTree 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 TimeSeries engine.

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

CheckResult
Binary size892,610,576 bytes (about 851 MiB), not stripped; ldd shows only glibc libraries (libc, libm, libdl, librt, libpthread)
clickhouse local running SELECT version(), 1+126.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
Listeners127.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 memoryMemoryResident 658 MiB, of which MemoryCode (the mapped binary) was 501 MiB; max_server_memory_usage defaulted to 6,173,039,001 bytes
HTTPGET / 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.

StepResult
Write 5M rows as CSVWithNames0.80 s, 276,678,784 bytes
Convert CSV to Parquet3.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 runsCSV 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).

ColumnDefault codec, ORDER BY (user_id, ts)Explicit codecs, same orderDefault codec, ORDER BY (source, ts)
ts60.65 MiB (ratio 1.26)Delta, ZSTD(1): 44.20 MiB (1.73)3.95 MiB (19.32)
user_id282 KiB (276.9)ZSTD(1): 282 KiB (276.9)50.86 MiB (1.50)
source6.27 MiB (3.05)ZSTD(1): 6.05 MiB (3.16)17 KiB (1,141)
path51.02 MiB (7.77)ZSTD(3): 51.02 MiB (7.77)51.02 MiB (7.77)
status6.15 MiB (6.21)T64, ZSTD(1): 7.86 MiB (4.85)6.15 MiB (6.21)
duration_ms33.27 MiB (2.29)T64, ZSTD(1): 23.93 MiB (3.19)33.27 MiB (2.29)
amount64.06 MiB (2.38)ZSTD(1): 62.94 MiB (2.42)64.06 MiB (2.38)
Table on disk221.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 columnCompressed 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 tableGranules readTime (3 runs)Peak query memory
GROUP BY source, status with count, avg, quantile(0.95), sum (16 groups)2,442 / 2,4420.62, 0.53, 0.51 s4.5 MiB
WHERE user_id = 4242 (first key column, 178 matching rows)1 / 2,442 (8,192 rows)0.004 to 0.005 s271 KiB
One-hour ts range on ORDER BY (user_id, ts) (72,000 rows)2,442 / 2,4420.05 to 0.06 s645 KiB
Same range on ORDER BY (source, ts)11 / 2,442 (90,113 rows)0.005 s249 KiB
GROUP BY user_id (100,000 groups)all0.20 s13.7 MiB
GROUP BY path, user_id (high cardinality)all1.81 s1.10 GiB
uniqExact(path) vs uniq(path)all0.47 s vs 0.28 s3.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.

ReadRowsTime
Plain SELECT count()1,100,000 (duplicates visible)0.002 s
SELECT count() ... FINAL1,000,0000.014 s
argMax(plan, updated_at) grouped by user_id1,000,000, of which 100,000 on the new plan-
After SYSTEM START MERGES and a 5 s waitstill 1,100,000 in 2 parts-
After OPTIMIZE TABLE ... FINAL1,000,000 in 1 part0.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

OperationObserved
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 afterwards7.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 = 4242Failed: "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 = 1Same 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 pending0.31 to 0.33 s
ALTER TABLE ... UPDATE mutation with mutations_sync = 12.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 INSERT statements 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 du reported 4.7 GB under store/ against 636 MiB of active parts: 379 inactive parts (2.16 GiB) were waiting out old_parts_lifetime = 480 seconds, and dropped tables were kept for database_atomic_delay_before_drop_table_sec = 480. Plan disk headroom accordingly.
  • The embedded config has no query log: system.query_log failed with UNKNOWN_TABLE, so the rows-read and memory figures above came from clickhouse client --print-profile-events and --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 Kafka table 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 TimeSeries engine for Prometheus-style data, and Grafana Labs maintains a ClickHouse data-source plugin, so Grafana dashboards can query it directly.
  • File querying. clickhouse-local for 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.

ProjectHow its README describes itDeployment modelLicence
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-localApache-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 CLIMIT
PostgreSQL"advanced object-relational database management system" with "transactions, foreign keys, subqueries, triggers"Row-store serverPostgreSQL licence
Apache Druid"high performance real-time analytics database" for "powering UIs, running operational (ad-hoc) queries, or handling high concurrency"Distributed clusterApache-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 ingestionDistributed clusterApache-2.0
StarRocks"open query engine for sub-second, ad-hoc analytics both on and off the data lakehouse", MySQL-protocol compatibleDistributed clusterApache-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 INSERT into 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 ReplacingMergeTree needs FINAL or 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 ts range 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 INSERT creates a part; the defaults parts_to_delay_insert = 1000 and parts_to_throw_insert = 3000 throttle and then reject inserts when parts pile up.
  • Memory-hungry queries. A high-cardinality GROUP BY used 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 seccomp default and arrayCount returning UInt64. The upgrade guide says to keep custom config in config.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 ZXC codec 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 CODEC reference page still says "By default, ClickHouse applies lz4 compression in the self-managed version". Since 26.9 the changelog and the test above show a size-dependent default: LZ4 below 100 MB per part, ZSTD(3) at or above.
  • The same page marks the ZXC codec as beta, while system.codecs in this build reports its tier as Experimental, and the 26.10 changelog also calls it experimental. The page also documents an experimental GDeflate codec that does not appear in this build's system.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 EXISTS ran and returned correct results.
  • The CHANGELOG.md shipped inside the 26.10.7.24 package stops at 26.9; the copy on master includes 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.

Project
clickhouse
Created
November 19
Last Updated
November 19, 2026 at 01:55 PM

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.

Featured on TechLogHub
[![Featured on TechLogHub](https://techloghub.com/badge-neutral.svg)](https://techloghub.com/open-source/clickhouse?utm_source=techloghub_badge&utm_medium=referral&utm_campaign=readme)

Find more projects like this

One email a week: new and trending developer tools, fresh comparisons, and what shipped. Unsubscribe in one click.