ClickHouse, a columnar database for analytics

ClickHouse, a columnar database for analytics

ClickHouse is a columnar database built around a single job: computing aggregates across billions of rows in hundreds of milliseconds. The price of that speed is a data model in which changing or removing a single row is an expensive operation rather than a routine one. This text covers when that trade is worth making.

What ClickHouse is built for, and what it is not

The difference between a row store and a column store sounds like an implementation detail, yet it decides everything above it. In PostgreSQL a row sits in a file as one contiguous stretch of bytes, so fetching a whole record by primary key is a single read. In ClickHouse every column is a separate file, compressed independently of the others. A query summing one column across ten billion rows touches only that column and never reads a byte of the rest. A query fetching an entire row, by contrast, has to visit as many files as the table has columns.

That is where the whole split of use cases comes from. ClickHouse suits analytical events, logs, metrics, telemetry and anything that arrives in bulk and gets queried in aggregate. It does not suit storing application state, a shopping basket, a user session or anything requiring reads and writes of individual records by identifier. For that second set you have Postgres, Supabase or Redis, and none of those are replaced by ClickHouse.

A typical production arrangement stands both databases side by side. The transactional database holds state, ClickHouse receives the event stream and answers analytical questions. That is how PostHog works, keeping metadata in Postgres and product events in ClickHouse, and it is a good example of what this split looks like in a real product.

Things whose presence in a SQL database is taken for granted are also missing here. There are no transactions spanning multiple statements. The primary key is not unique and enforces no uniqueness, because it plays an entirely different role than in a row store. Foreign keys do not exist. Joins work, but they are a much weaker part of the system than aggregations and can disappoint when both sides are large tables.

Versions, licence and distribution

Release numbering follows a year dot month scheme. The newest stable release as this is written is 26.6.3.62, published to the image registry on 19 August 2026 under the latest tag. Long-term lines are maintained in parallel: images 26.3.20.7 and 25.8.31.9 landed in the registry on 20 August 2026, a day after the current release. If you run your own deployment, that second track is the more sensible one, because the pace of major releases is very fast.

The licence is that rare case where three checked sources say the same thing. The LICENSE file on the main branch of the repository holds the full Apache License 2.0 text with a ClickHouse, Inc. copyright notice covering 2016 to 2026. The license field in the npm registry for the @clickhouse/client package reads Apache-2.0. The unpacked package published to the registry contains a LICENSE file with the same Apache 2.0 text, except that its copyright notice covers 2016 to 2024. That is a discrepancy in the date rather than in the terms, so it changes nothing for a dependency audit, but it shows the licence file inside the package is not refreshed on every release. The Python ecosystem gives the same result: the clickhouse-connect package at version 1.7.2 declares Apache-2.0 both in its license field and in its classifiers.

The Node driver declares version 1.23.1. Keep those numbers apart from the server number, because client libraries have their own release cycle and 1.23.1 says nothing about which server it talks to.

The practical conclusion from the licence is short. The whole core, including replication, storage on object stores and every table engine in the MergeTree family, is under a permissive licence and free to use commercially. The paid and free divide runs elsewhere: not through the code licence, but through what is available exclusively inside the managed service.

The data model, meaning MergeTree and the sorting key

The MergeTree engine and its derivatives are the foundation everything else stands on. Data inserted into a table lands in immutable fragments called parts, and a background process merges small parts into larger ones. Every part is sorted by the sorting key given in the ORDER BY clause.

CREATE TABLE analytics.events
(
    project_id   UInt32,
    event_type   LowCardinality(String),
    event_time   DateTime,
    user_id      UInt64,
    session_id   UUID,
    country      LowCardinality(String),
    duration_ms  UInt32,
    properties   Map(String, String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (project_id, event_type, event_time)
TTL event_time + INTERVAL 90 DAY DELETE
SETTINGS index_granularity = 8192;

The ORDER BY clause is the most important design decision here and simultaneously the hardest one to change later. It sets the physical order of rows on disk along with the sparse primary index, which instead of a pointer per row keeps one entry per index_granularity rows, 8192 by default. A query filtering on a prefix of that key reads only the granules that can hold matching data. A query filtering on a column outside the key reads everything.

From that follows the rule for column order in ORDER BY: low cardinality columns and those you always filter on go first, high cardinality columns, usually time, go last. The reverse order, with a user identifier at the front, effectively voids the index for most queries and ruins compression at the same time, because neighbouring values stop resembling each other.

The LowCardinality(String) type in the definition above is not decoration. It swaps repetitive strings for a dictionary with integer indexes, which for a column like an event name or a country code shrinks storage and speeds up grouping. On a column with many unique values the same type makes things worse, so it is not applied to identifiers or URLs.

Partitioning behaves differently from what Postgres intuition suggests. The PARTITION BY clause exists mainly for data management, meaning fast removal of whole periods through DROP PARTITION and for TTL rules. Speeding up queries is the job of ORDER BY. An excess of partitions, for instance a daily split with three-year retention, creates thousands of directories and genuinely slows the system down.

Updates and deletes, the most expensive part

This is where habits from a transactional database end most painfully. Data parts are immutable, so there is no such thing as overwriting a row in place. Every change means writing new data and reconciling it with the old during a merge or during a read.

Historically the only route was mutations, meaning ALTER TABLE ... UPDATE and ALTER TABLE ... DELETE. A mutation rewrites entire columns in every part the condition touches. On a table of a few hundred gigabytes, changing one field in one row can mean rewriting a substantial share of the table. A mutation runs asynchronously, and its progress is tracked in a system table.

-- mutation: rewrites entire columns in affected parts
ALTER TABLE analytics.events
    UPDATE duration_ms = 0
    WHERE event_type = 'heartbeat';

-- progress and any failure
SELECT mutation_id, command, parts_to_do, is_done, latest_fail_reason
FROM system.mutations
WHERE database = 'analytics' AND table = 'events' AND is_done = 0;

A lighter route came later and is still marked as a beta feature. The DELETE FROM ... WHERE statement marks rows as deleted without immediately rewriting columns, and physical removal happens at the next merge. That means the data still sits on disk for an unspecified period and merely stops appearing in results. For deletion on a user's request, for instance under a right to erasure, that detail carries legal weight, and there is a table setting min_age_to_force_merge_seconds that forces a merge within a predictable time.

The UPDATE ... SET ... WHERE statement works on a similar principle, writing so-called patch parts instead of rewriting columns. It carries hard preconditions that are easy to trip over.

-- required precondition for lightweight updates
ALTER TABLE analytics.events
    MODIFY SETTING enable_block_number_column = 1,
                   enable_block_offset_column = 1;

-- lightweight update, MergeTree family only
UPDATE analytics.events
    SET duration_ms = 0
    WHERE event_type = 'heartbeat' AND event_time >= '2026-08-01';

-- delete marks rows, they vanish physically at merge time
DELETE FROM analytics.events
    WHERE user_id = 918273;

The limits are concrete. A lightweight update supports the MergeTree engine family only. It requires materialisation of the _block_number and _block_offset columns, switched on through the table settings enable_block_number_column and enable_block_offset_column. Columns belonging to the primary key or the partition key may not be changed. Behaviour under concurrency is governed by the update_sequential_consistency and update_parallel_mode settings, and the format of written patch parts by patch_parts_version, which has to be pinned to v1 during a rolling cluster upgrade.

If updates are part of the normal data flow rather than an exception, the right answer is none of the statements above but a different table engine. ReplacingMergeTree accepts successive versions of the same key and keeps one of them at merge time.

CREATE TABLE analytics.subscriptions
(
    subscription_id UInt64,
    plan            LowCardinality(String),
    status          LowCardinality(String),
    updated_at      DateTime,
    is_deleted      UInt8
)
ENGINE = ReplacingMergeTree(updated_at, is_deleted)
ORDER BY subscription_id;

-- FINAL merges versions on the fly, at query time cost
SELECT subscription_id, plan, status
FROM analytics.subscriptions FINAL
WHERE status = 'active';

The engine's first argument is the version column, the second a deletion marker, and the second cannot be used without the first. The key trap is that merging happens eventually rather than immediately, so without the FINAL modifier a query may see several versions of the same record. The FINAL modifier gives a correct result but moves the merge cost to query time, and on large tables that is noticeable.

Inserting data and materialized views

Insertion has one rule whose violation ends in an outage in every deployment. Every INSERT statement creates a new part on disk, and the merge process only keeps up to a point. Inserting a row at a time floods the system with thousands of tiny parts and ends with an error about too many parts in a partition. Data goes in batches, in practice anywhere from a few thousand to a few hundred thousand rows at once.

import { createClient } from '@clickhouse/client'

const client = createClient({
  url: process.env.CLICKHOUSE_URL,
  username: process.env.CLICKHOUSE_USER,
  password: process.env.CLICKHOUSE_PASSWORD,
  database: 'analytics',
  max_open_connections: 10,
  request_timeout: 30_000,
  clickhouse_settings: {
    async_insert: 1,
    wait_for_async_insert: 1
  }
})

await client.insert({
  table: 'events',
  values: batch,
  format: 'JSONEachRow',
  columns: ['project_id', 'event_type', 'event_time', 'user_id', 'duration_ms']
})

The async_insert setting moves buffering to the server side, which saves the situation when the application has no natural place for a queue. Enabling wait_for_async_insert makes the call return only once the buffer has been written, preserving write acknowledgement at the cost of latency. Turning it off gives a faster response and a risk of data loss on node failure, so it is a deliberate choice rather than a setting to copy without thinking.

Materialized views in ClickHouse are something different from their Postgres namesakes, and that is one of the most common misunderstandings. They are not a periodically refreshed snapshot of a query. They are a trigger firing on insert: every new part in the source table passes through the view's query, and the result goes into a target table. Data inserted before the view existed will not be processed.

CREATE TABLE analytics.events_hourly
(
    project_id   UInt32,
    event_type   LowCardinality(String),
    hour         DateTime,
    events_count UInt64,
    unique_users AggregateFunction(uniq, UInt64)
)
ENGINE = AggregatingMergeTree
ORDER BY (project_id, event_type, hour);

CREATE MATERIALIZED VIEW analytics.events_hourly_mv
TO analytics.events_hourly
AS SELECT
    project_id,
    event_type,
    toStartOfHour(event_time) AS hour,
    count() AS events_count,
    uniqState(user_id) AS unique_users
FROM analytics.events
GROUP BY project_id, event_type, hour;

SELECT project_id, sum(events_count), uniqMerge(unique_users)
FROM analytics.events_hourly
WHERE hour >= now() - INTERVAL 7 DAY
GROUP BY project_id;

The pattern with the State and Merge suffixes is mandatory here. The uniqState function writes an intermediate aggregate state into a column of type AggregateFunction, and uniqMerge combines those states on read. Skipping that step and storing a plain number gives a result that does not add up correctly across periods, because counts of unique users from two hours cannot simply be summed. Populating tables in batches on a daily rhythm is more often handled by an external scheduler such as Apache Airflow, leaving materialized views for aggregates computed on the fly.

ClickHouse Cloud, pricing and cloud-only features

The managed service bills actual usage across four items: compute, storage, data transfer and the ClickPipes ingestion mechanism. There is no subscription for plan availability itself, there is a price per unit. The figures below come from the billing documentation for the AWS region in Northern Virginia and are examples rather than list rates.

The Basic plan starts at 66.52 USD a month for a service with one replica of 8 GiB memory and 2 virtual cores, 500 GB of compressed data and a backup of the same size, running six hours a day. That amount splits into 39.91 USD for compute, 25.30 USD for storage, 1.15 USD for public internet egress and 0.16 USD for cross-region traffic. At twelve hours a day the same service costs 106.44 USD, and running continuously 186.27 USD, because only the compute component grows.

The Scale plan starts at 499.38 USD a month with two replicas of 8 GiB running around the clock. Raising the replicas to 16 GiB lifts the compute component alone to 873.89 USD. For the Enterprise plan the vendor publishes no entry price, only examples: two replicas of 32 GiB with 5 TB of data and one backup come to 2,669.40 USD a month.

Transfer rates are given per region and can be checked. For the Northern Virginia region the pricing page lists 0.1152 USD per gigabyte of public internet egress and 0.0312 USD per gigabyte of cross-region traffic, which multiplied by the volumes in the Basic example produces exactly the 1.15 and 0.16 USD figures from the table. That line of the bill is easy to overlook when planning, and with dashboards querying the database straight from the browser it can grow.

Billing runs in units called ClickHouse Credits, where one credit equals one dollar. With payments through Stripe the invoice shows one credit as 0.01 USD, because that processor does not handle fractional quantities. It is a discrepancy purely in presentation, but anyone parsing invoices automatically needs to know about it. A new account gets a thirty-day trial period.

The split of features between plans matters as much as the prices. The Basic plan caps storage at 1 TB and memory at a range of 8 to 12 GiB, runs in a single availability zone and takes backups every 24 hours with one-day retention, with no configuration options. The Scale plan removes the storage cap and adds configurable memory, two or more availability zones, private networking, automatic vertical scaling and compute-compute separation. The Enterprise plan adds SAML single sign-on, private regions, customer-managed encryption keys, HIPAA and PCI compliance, scheduled upgrades and a thirty-minute response time for the highest severity issues.

Cloud-only features exist and need to be known before choosing a deployment route. The SharedMergeTree engine family, designed as a replacement for ReplicatedMergeTree running on object storage, powers the managed service and is not part of the open release. ClickPipes, the managed ingestion from Kafka, Kinesis, S3 and other sources, works only in the cloud. Compute-compute separation is available only from the Scale and Enterprise plans, so it requires not just the cloud but a specific tier. Developed separately is a managed Postgres integrated with ClickHouse, in beta, priced from 0.125 USD per unit per hour.

ClickHouse against the alternatives

The choice comes down to a few questions. If the data fits on one machine and the analysis is a one-off, DuckDB handles it with no server and no maintenance. If the questions concern the content of text rather than numbers, the right tool is Elasticsearch. If the volume fits within a few hundred gigabytes and the queries are not especially heavy, columnar extensions for Postgres are enough and save a whole separate database to maintain. ClickHouse starts paying off where row counts run into billions, queries aggregate a few columns out of many, and response time is meant to be measured in hundreds of milliseconds against a constantly growing write stream.

Common mistakes

The first is treating the primary key as you would in a transactional database. In ClickHouse ORDER BY enforces no uniqueness and prevents no duplicates. Reinserting the same data yields two sets of rows rather than a constraint violation error, so idempotency has to come from the ingestion layer or from the ReplacingMergeTree engine.

The second is inserting a row at a time, usually in the code handling an HTTP request. Every such write creates its own part, the merge process falls behind, and the database starts rejecting writes with a message about too many parts. Buffer in the application or switch on async_insert on the server side.

The third is using mutations as an everyday tool. The ALTER TABLE ... UPDATE and ALTER TABLE ... DELETE statements rewrite entire columns in affected parts, run asynchronously and can load the cluster for hours. If you are writing them in a loop, the data model is designed wrong.

The fourth is appending the FINAL modifier to every query to avoid duplicates from ReplacingMergeTree. That works, but it moves merging to read time and on a large table cancels the performance advantage that motivated choosing ClickHouse. A saner arrangement uses aggregation with the latest version picked through argMax, or accepts a short window of inconsistency.

The fifth is bad partitioning. A daily split with multi-year retention creates thousands of partitions, each of them a separate set of files. A monthly split is a sensible starting point, and query speed is the job of the sorting key rather than the partition.

The sixth is SELECT * queries carried over from Postgres. In a column store the asterisk means reading every column file and is the most expensive possible form of query. List columns explicitly, even when you need most of them.

The seventh is underestimating transfer cost in the cloud. A dashboard querying the database straight from the browser, at a rate around eleven cents per gigabyte of egress, can produce a bill line comparable to the compute component.

FAQ

Can ClickHouse replace PostgreSQL?

Not in the role of a transactional database. It lacks transactions spanning multiple statements, unique primary keys, foreign keys and cheap single row updates. The standard arrangement puts both databases side by side: Postgres for application state, ClickHouse for events and analytics.

How do I delete one user's data on request?

With the DELETE FROM ... WHERE statement, remembering that it marks rows as deleted and they vanish physically only at the next merge. Under legal requirements set the table's min_age_to_force_merge_seconds so the merge happens within a predictable time, and check whether the data is duplicated in materialized views.

Does the open version have every cloud feature?

No. The SharedMergeTree engine family, the ClickPipes ingestion mechanism and compute-compute separation are available only in the managed service, and the last of those additionally only from the Scale and Enterprise plans. The core, replication and every engine in the MergeTree family are open under Apache 2.0.

What does ClickHouse Cloud actually cost?

It depends on how long the service is active and how much data it holds. The documentation gives a Basic example from 66.52 USD a month at six hours of activity a day and 186.27 USD when running continuously, plus a Scale plan from 499.38 USD a month. Transfer comes on top, priced separately for each region.

Can the sorting key be changed after a table is created?

Practically no. Columns can be appended to the end of the key, but the order of existing ones cannot be changed and a column cannot be removed from the key. Changing it means creating a new table with the right ORDER BY and rewriting the data, which is why it is a decision to settle before the first insert.

When should I use a materialized view instead of a plain query?

A materialized view pays off when the same aggregate gets queried repeatedly and the write stream is steady. It will not process data inserted before it existed and it adds cost to every insert, so for queries run occasionally a plain query against the source table is cheaper.

Documentation lives on the ClickHouse site, billing details on the pricing page, and the source code in the GitHub repository.