ClickHouse · MergeTree · KeeperClickHouse, billions of rows per second.
ClickHouse is an open-source, column-oriented OLAP database management system engineered for real-time analytical queries over billions of rows. JusDB provides full-lifecycle ClickHouse reliability engineering, MergeTree table engine optimization, materialized view design, Keeper cluster operations, and low-downtime migrations, with agreed incident response (24x7 on-call available).
We design, tune, and operate ClickHouse clusters behind interactive dashboards and high-volume event pipelines. Our engineers handle schema modeling, cluster topology, ingestion, and query tuning.
ClickHouse 24 · MergeTree
Sharded + replicated · Keeper
940
8.4B
190ms
11.2×
Rows Scanned / sec
9.40B rows/s[OK] mergetree: background merge complete, 412→1 part
[INF] mutation: ALTER UPDATE on events 78% applied
[OK] replicated: keeper quorum healthy, 0 lag
[INF] mat-view: hourly_rollup refreshed in 1.2s
Example monitoring view showing database activity and engineer actions. Uses sample data, not customer data.
24
Supported database technologies
24x7
On-call coverage, under your service agreement
30 min
P1 initial-response target on 24x7 on-call
2 to 4 h
Initial-response target in covered business hours
ClickHouse engineering
We specialize in high-performance ClickHouse deployments for real-time analytics and data warehousing. The work runs from ClickHouse consulting and engine-selection advisory to ClickHouse on Kubernetes.
MergeTree Profiling & Tuning
Optimize ClickHouse MergeTree table engines, index granularities, and PREWHERE clauses for sub-second aggregations.
Materialized Views Design
Design real-time aggregating materialized views and specialized projection strategies to pre-compute queries.
ClickHouse + Kafka Integration
High-throughput data ingestion using the Kafka Engine table integration. Delivery is at-least-once. We add deduplicated writes via ReplicatedMergeTree block dedup, aiming at effectively-once semantics within the configured dedup window.
Compression Codec Optimization
Optimize ZSTD, LZ4, and specialized DoubleDelta codecs to maximize storage savings and reduce IO bottlenecks.
Sharding & ZooKeeper/Keeper
Configure ReplicatedMergeTree distributed tables, sharding keys, and ClickHouse Keeper for reliable horizontal scaling.
Observability & Profiling
Query execution plan profiling with EXPLAIN PIPELINE and deep system.query_log auditing.
ClickHouse expertise
for real-time analytics
We tune MergeTree engines, design aggregating materialized views, and dial in compression codecs. The goal is billion-row aggregations that return in under a second without a separate warehouse. Dig into our ClickHouse performance tuning work for the full methodology.
Analytical Performance
After tuningIllustrative health scores · results depend on the workload
Queries we've transformed
Illustrative examples of the problem and the fix, not client measurements.
12,000ms
200ms
Read all 6.2B rows — no PRIMARY KEY ordering
The fix
Tuned ORDER BY key + added minmax skip index on event_date
8,400ms
140ms
Partitioned by user_id — every part touched
The fix
Re-partitioned by toYYYYMM(event_date), prunes to 1 part
21,000ms
95ms
Hash JOIN of two 4B-row tables blew up memory
The fix
Replaced with dictionary lookup + denormalized fact table
3/3
Replicas online
<10s
Failover RTO
0ms
Replica Lag
Always on. Replicated by design.
ReplicatedMergeTree tables coordinate through ClickHouse Keeper, so replicas of the same shard stay in sync. In the topologies we design, each shard keeps redundant replicas with automatic recovery. A lost node rejoins and re-syncs without manual intervention.
A merge-storm P1, answered within your agreement's target.
When too-many-parts stalls inserts and a merge storm pins CPU, a named ClickHouse engineer responds. You do not wait in a ticket queue. We throttle merges, fix partitioning, and clear the backlog online. A blameless postmortem follows.
Dashboard p99 above 12s; analysts blocked
A named ClickHouse engineer responds within your agreement's initial-response target
Full scan: the filter misses the sorting key and there is no skip index
Added a minmax skip index and materialized it; query rewritten to filter on the sorting key
Granules pruned and p99 back to baseline; sorting-key redesign planned; post-incident review
ClickHouse Internals: Critical Failure Modes
High-throughput columnar engines face unique architectural bottlenecks under petabyte scale. Here are the 3 engine failure modes our ClickHouse SRE team permanently remediates:
Too Many Parts & Merge Storm Ingestion Throttling
Frequent small-batch inserts generate millions of tiny data parts faster than ClickHouse background merges can consolidate them, reaching parts_to_throw_insert limits (default 300) and halting all real-time data ingestion.
Architecting micro-batch buffers (in-memory async insert buffers or Kafka engine batching with async_insert = 1 and wait_for_async_insert = 1), tuning max_parts_in_total, and optimizing partition key granularity.
Out-Of-Memory (OOM) Exhaustion During Unbounded GROUP BY / JOINs
Distributed queries with high-cardinality GROUP BY or hash GLOBAL JOIN operations across multi-billion row tables exceed max_memory_usage_for_user, triggering abrupt query terminations with Memory limit exceeded exceptions.
Configuring external aggregation with max_bytes_before_external_group_by, replacing distributed subquery joins with dictionary lookups, and implementing pre-aggregated AggregatingMergeTree materialized views.
ReplicatedMergeTree Desynchronization & ZooKeeper/Keeper Jitter
Heavy metadata churn and synchronous mutation commands overload ZooKeeper/Keeper znodes, causing Raft consensus timeout spikes, lost Keeper sessions, read-only replica status, and stalled distributed DDL executions.
Migrating legacy ZooKeeper to native ClickHouse Keeper with dedicated SSD write logs, tuning operation_timeout_ms, and batching partition DROP/DETACH mutations.
Our ClickHouse SREs run read-only diagnostic queries to inspect unmerged parts and identify memory-intensive queries without interrupting ingestion:
SELECT table,
partition,
count() AS num_parts,
sum(rows) AS total_rows,
formatReadableSize(sum(bytes_on_disk)) AS disk_size
FROM system.parts
WHERE active = 1 AND database = currentDatabase()
GROUP BY table, partition
HAVING num_parts > 50
ORDER BY num_parts DESC
LIMIT 10;SELECT query_id,
user,
query_duration_ms,
memory_usage,
read_rows,
formatReadableSize(read_bytes) AS read_volume
FROM system.query_log
WHERE type = 'QueryFinish' AND event_time > now() - INTERVAL 1 HOUR
ORDER BY memory_usage DESC
LIMIT 5;How JusDB ClickHouse Services compare to alternative options.
Scaling ClickHouse to billions of rows requires deep mastery of MergeTree part merges, memory spillover thresholds, and Keeper Raft coordination. Here is how JusDB compares to managed cloud providers, generic MSPs, and internal dev teams.
| Engineering Dimension | JusDB ClickHouse SRE | ClickHouse Cloud | Generic MSPs | In-House Devs |
|---|---|---|---|---|
| MergeTree Partitioning & Too-Many-Parts Prevention | Granular partition pruning, automated async_insert micro-batching, and background merge throttling preventing part explosion | Basic part metrics in console; requires manual intervention when tables hit parts_to_throw_insert limits | Lacks columnar storage mechanics expertise; attempts to fix insert errors by increasing instance disk capacity | High-frequency micro-inserts trigger merge storms, locking CPU cores and throwing fatal insert rejections |
| Materialized Views & AggregatingMergeTree Design | Real-time stateful aggregations (SimpleAggregateFunction, AggregateFunction) and projections delivering sub-100ms dashboards | Managed service provides basic SQL interface; customer must architect complex stateful aggregation logic | Runs raw ad-hoc aggregations over uncompressed tables, exhausting cluster RAM on multi-billion row scans | Materialized views created without state combinators, corrupting aggregated counts and duplicate metrics |
| Distributed Queries & Vectorized Memory Safety | Memory spillover tuning (max_bytes_before_external_group_by), dictionary joins, and colocated shard distribution | Queries abort with Memory limit exceeded; recommends upgrading to larger compute tiers | Does not profile memory graphs in system.query_log; unable to diagnose OOM query terminations | Unbounded global JOINs crash ClickHouse server daemons during peak executive dashboard reporting hours |
| ClickHouse Keeper & Raft Quorum Stability | Keeper cluster deployment with dedicated SSD write logs, online ZooKeeper-to-Keeper migrations, and snapshot management | Keeper managed behind the scenes, but provides no visibility into DDL replication lags or synchronization stalls | Unfamiliar with ClickHouse Keeper internal Raft state machines and quorum failover behaviors | ZooKeeper Java GC pauses cause session expirations, throwing ReplicatedMergeTree tables into read-only mode |
| Incident Response & Escalation | Named ClickHouse engineers join your incident bridge. Initial-response targets: 2 to 4 hours in covered business hours; 30 minutes for P1 incidents with 24x7 on-call. Targets, not resolution guarantees. | Multi-tiered cloud support ticketing system with generic L1 triage queues for incident notifications | Offshore ticket dispatchers lacking knowledge of columnar storage algorithms and vectorized engines | Engineers awake at 2 AM trying to understand why distributed tables stop responding to Kafka streams |
| Compression Codec Tuning & Storage FinOps | Per-column codec engineering (ZSTD, DoubleDelta, T64, Gorilla) for higher disk compression and fewer I/O bottlenecks | Default LZ4 compression applied universally, inflating storage bills and query memory footprint | Leaves default table storage settings unoptimized across multi-terabyte analytical historical datasets | Exponential cloud storage costs from uncompressed logs and redundant raw data tables |
Pre-Migration Assessment
Postgres / Druid analytics → ClickHouse
Estimated cutover window: < 10 minutes
Move to ClickHouse without the downtime
Postgres, MySQL or a legacy warehouse → ClickHouse. We model the right MergeTree schema and backfill with parallel inserts. Live changes stream in via Kafka. We cut over once row counts reconcile. See our ClickHouse migration service for the full cutover playbook.
ClickHouse Ecosystem & Integration Tools We Work With
Complete ClickHouse ecosystem and integration tools
Common questions about ClickHouse, MergeTree & OLAP performance
Direct technical answers from our ClickHouse engineers.
What is ClickHouse best used for?
ClickHouse excels at real-time analytics, event streaming, log analysis, time-series data, and interactive dashboards over massive datasets. Its columnar MergeTree engine and vectorized execution process billions of rows per second at orders-of-magnitude lower cost than traditional data warehouses.
How do you prevent 'Too many parts in all data parts in table' errors?
We eliminate micro-batch inserts by implementing client-side batching, async_insert in ClickHouse 21.11+, and Kafka buffer engines, while tuning background merge pool threads and optimizing partition granularities.
What is the difference between ClickHouse Keeper and Apache ZooKeeper?
ClickHouse Keeper is a C++ implementation of the Raft consensus protocol built natively into ClickHouse. It provides identical coordination for ReplicatedMergeTree tables without the JVM memory footprint, garbage collection pauses, or operational complexity of Apache ZooKeeper.
Can ClickHouse replace PostgreSQL or MySQL for transactional workloads?
No. ClickHouse is an OLAP database optimized for append-only bulk inserts and fast aggregations, not row-by-row OLTP updates or ACID multi-table transactions. We typically architect CDC pipelines (using Debezium or Kafka) to stream OLTP data from PostgreSQL/MySQL into ClickHouse for real-time reporting.
How does JusDB handle ClickHouse performance tuning engagements?
We audit system.query_log to identify slow queries, optimize primary sorting keys in MergeTree tables, design AggregatingMergeTree materialized views to pre-compute metrics, apply specialized column compression codecs (DoubleDelta, Gorilla, ZSTD), and calibrate memory limits.
Ready to Accelerate Your Analytics?
Transform your data analytics with ClickHouse's lightning-fast performance. Our experts will help you build scalable OLAP solutions for real-time insights.
Explore Our ClickHouse Services
Explore more ways our ClickHouse experts can help with your database infrastructure.