Running ClickHouse?

ClickHouse merges backing up or queries crawling? We clear it.

ClickHouse scales from a single node to petabytes without most teams noticing the transition, until it does not. We are called in when merges start stacking, when the mutation queue grows faster than it drains, when a replicated table splits brain, or when the storage bill for raw logs crosses what the analytics was supposed to save. We trim the schema, retune the merges and get the cluster back into a steady state.

  • Since 2015Fixing technology
  • FreeEstimates
  • US teamReal engineers

clickhouse-server.log

still broken

What it is

ClickHouse at a glance

ClickHouse is a columnar OLAP database first released as open source by Yandex in 2016 and now developed by ClickHouse, Inc., founded in 2021. It aims at analytical queries over very large tables: sum, count, group by and window functions on billions of rows, returned in milliseconds to seconds.

The storage engine is MergeTree, which writes sorted parts to disk and merges them in the background. Replication is coordinated through ZooKeeper or its drop in ClickHouse Keeper. There is a managed ClickHouse Cloud offering, and self hosted deployments on bare metal and Kubernetes are both common. Teams typically arrive at ClickHouse when Postgres analytics have run out of headroom and Snowflake or BigQuery look too expensive.

Type
Columnar OLAP database
First released
2016 open source release
License
Apache 2.0, with a managed ClickHouse Cloud
Maintained by
ClickHouse, Inc.
Typically used for
Product analytics, observability storage, ad tech, log and event warehouses, user facing analytics

The honest version

ClickHouse pros and cons

No technology is all upside. This is where ClickHouse earns its place and where it tends to cause trouble.

Where ClickHouse is strong

  • Very fast aggregationsColumnar storage, vectorised execution and primary key skipping indexes make count, sum and group by on billions of rows a solved problem on commodity hardware.
  • Economical storageCodecs and compression bring typical event data down to a fraction of its JSON size, which keeps the cold storage bill reasonable even for high volume pipelines.
  • SQL dialect with analytical extensionsClickHouse SQL supports window functions, arrays, maps, nested types and useful analytical aggregates, so most reporting translates over without exotic rewrites.
  • Scales horizontallySharded tables across a cluster let you grow with ingestion volume rather than paying for a bigger single node, and the replica protocol handles failover.
  • Good ingest throughputBatch inserts of millions of rows per second are routine on a tuned node, which matters when a pipeline produces events faster than Postgres wants to accept them.
  • Open source with a credible managed optionSelf host for control, or move to ClickHouse Cloud when the operational load is more than you want on your plate, with the same engine underneath.

Where ClickHouse bites

  • MergeTree needs attentionIf inserts outrun merges you accumulate too many parts and the whole table slows down. Batch sizes, partitioning and merge settings have to be chosen for your workload rather than left at defaults.
  • Updates and deletes are expensiveMutations rewrite whole parts in the background and can take hours. Workloads that assume row level updates like a transactional database will struggle until the schema is adjusted.
  • ZooKeeper is a dependencyReplicated tables require either ZooKeeper or Keeper to be healthy. A session expiry under load leaves replicas read only and needs careful recovery.
  • Observability has sharp edgesThe system tables are powerful once you know them, but working out why a specific query spent its time on merges, network or decoder is not obvious from the error alone.
  • JOIN performance needs planningHash joins are fast when the right side fits in memory and slow otherwise. Large to large joins often need to be restructured as subqueries or dictionaries rather than written as the SQL textbook would suggest.
  • Fewer trained engineers than PostgresThe talent pool knows SQL but not ClickHouse specifics such as part management, replicated DDL and codec choice. Expect to invest in training or hire specifically for it.

Sound familiar?

Common ClickHouse problems we fix

The ClickHouse problems that bring people to us, and what you have once each one is fixed.

Too many parts (300). Merges are processing significantly slower than inserts

Inserts are outrunning merges

Clients are inserting single rows or very small batches, or the partitioning scheme creates thousands of parts per day and the merger cannot keep up with them.

Batched inserts, partitioning rethought

Memory limit (for query) exceeded

Queries OOM at peak

A GROUP BY over very high cardinality keys, or a JOIN whose right side no longer fits in memory, taking out whatever node happened to receive the query.

Query rewritten or spilled to disk

Mutation is still in progress for 4h

A mutation queue that will not drain

An UPDATE or DELETE issued across a huge partition is now rewriting parts in the background and blocking later mutations, slowing every replica in the process.

Mutation handled, pattern changed

ZooKeeperException: SessionExpired

Replicas going read only

ZooKeeper under network pressure, or Keeper underprovisioned, letting sessions expire and leaving replicated tables unable to accept writes until the session recovers.

Coordination layer healthy again

Part 20261101_1_42_3 is broken

Broken parts after a hardware event

A disk hiccup, a crash during merge, or a copy that went wrong leaving a part the server cannot read. The table works, but queries that touch the part fail.

Part restored from a replica or dropped

cost: $14,200 (was $3,800)

Storage bill climbing month over month

Raw JSON events written without codecs, partitions kept forever with no TTL, and materialised views that duplicate the underlying data four or five times over.

Schema tightened, TTLs set, bill cut

Cannot parse input

Ingestion failing on malformed rows

Upstream producers emitting slightly different schemas, a null that was supposed to be a zero, or a TSV with an extra tab. The ingest stops and the queue backs up until someone fixes the input.

Pipeline resilient to bad rows

no merge policy

Cluster inherited with no documentation

A previous team set up replication, chose a partitioning scheme and left. The schema works but nobody knows why the merges run at 3am or what the backup story is.

Cluster mapped and owned by your team

What we do

ClickHouse help, from rescue to tuning

Clear one stuck merge queue, or let us run the whole cluster.

Rescue
Merges backing up, replicas stuck read only, or queries no longer returning. We get the cluster accepting writes again and then explain what led up to it.
Schema and query review
Primary keys, partitioning, codecs and projections chosen for your actual queries, with EXPLAIN plans to show the difference before and after.
Cost reduction
TTLs applied, cold data moved to object storage, duplicate materialised views consolidated, and the bill tracked month by month so the savings are visible.
Postgres or Snowflake migration
Moving analytical workloads off a transactional database that is buckling, or off a per query warehouse that has become uneconomical, onto ClickHouse without breaking reports.
Cloud or self hosted decision
Costing ClickHouse Cloud against a self hosted cluster for your traffic, with a frank view of operational load either way.
Managed Tech
Monitoring of parts and mutations, backup verification, upgrade planning and an engineer on call who knows your tables when something starts behaving oddly.

How it works

From first message to fixed

  1. Look Free estimate

    Tell us what's going wrong. We look at it, then tell you plainly what's wrong and what it will cost.

  2. Fix

    We fix the cause, not the symptom. If something is down, getting it back up comes first.

  3. Harden

    Backups, monitoring, updates and access, so the same problem can't quietly come back.

  4. Hand back or stay on

    You get a plain-language write-up. Or we stay on for hosting, upkeep and new work.

Often found together

Running ClickHouse alongside something else?

We work on the whole stack, so the problem can be wherever it actually is.

Questions

ClickHouse questions

Something else? Call+1 737 727 7327

Why do we keep hitting 'Too many parts'?

This almost always means inserts are too small or too frequent. ClickHouse wants batches of at least several thousand rows per insert, ideally per partition. If a client is streaming rows one at a time, a buffer table or a batching layer in front of it will stop the problem cold. The second place to look is the partitioning scheme, which sometimes creates far more parts than intended.

Our ClickHouse storage bill keeps climbing. What should we do?

Three levers. Apply codecs like Delta, DoubleDelta or ZSTD on columns where they fit, add TTL expressions so cold partitions roll off or move to object storage automatically, and consolidate materialised views that are quietly tripling the data footprint. We measure the saving per lever so you know what paid back.

Can we use ClickHouse instead of Snowflake or BigQuery?

For most product analytics and observability workloads, yes, and the per query cost model usually comes out cheaper. The trade off is that you now run or pay for the cluster. If your queries are ad hoc and bursty, a per query warehouse may still win. If they are predictable and high volume, ClickHouse almost always wins. We will cost the two against your actual traffic.

Should we use ClickHouse Cloud or self host?

Cloud takes merges, upgrades and replication off your plate for a markup. Self hosted gives control and lower cost at scale, but the operational knowledge is real and the on call pager becomes yours. For small teams without a database specialist, the managed service pays for itself. For larger teams or specific residency needs, self hosted still wins.

How do we migrate heavy analytics off Postgres onto ClickHouse?

We identify the tables that are actually driving the slowness, design the ClickHouse schema for how those queries read rather than how they write, build an ingest pipeline that keeps both stores in sync during the transition, and cut reports over one by one. Postgres stays for the transactional work it is good at, and ClickHouse takes the aggregations off its back.

How much does ClickHouse help cost?

Free estimates. Describe your cluster size and what it is doing, paste a slow query or an error from the server log, and a price lands before any work is booked.

Free estimate

ClickHouse giving you trouble?

Tell us what you're running and what's going wrong. A person reads it and comes back with an estimate.

  • No charge to find out what it will take
  • If it's urgent, say so

Replies come from a person. By sending, you authorize us to email you back. No marketing.