DEV Community: Kanishga Subramani

Day 76/100 - Working with ClickHouse® LowCardinality and Enum Types

Kanishga Subramani — Mon, 13 Jul 2026 13:47:33 +0000

Introduction

Choosing the right data types is one of the simplest yet most effective ways to optimize a ClickHouse® database. While query tuning and hardware upgrades often receive the most attention, selecting appropriate column types can significantly reduce storage requirements, improve compression, accelerate query execution, and lower memory consumption—all without changing application logic.

Two powerful optimization features available in ClickHouse® are LowCardinality and Enum. Although both are designed to store repeated values efficiently, they address different use cases and should not be considered interchangeable.

LowCardinality uses dictionary encoding to optimize columns containing many repeated string values, while Enum stores predefined string values as compact integer codes. Choosing the right one depends on factors such as cardinality, schema flexibility, and how frequently values change over time.

In this article, we'll explore how both data types work, compare their advantages and limitations, examine common use cases, and discuss best practices for designing efficient ClickHouse® schemas.

Why Data Types Matter in ClickHouse®

ClickHouse® is a column-oriented analytical database.

Because each column is stored independently, the selected data type directly influences:

Storage utilization
Compression efficiency
Query execution speed
Memory usage
Network transfer during distributed queries
CPU utilization

Even small schema improvements can have a significant impact.

For columns containing highly repetitive string values, replacing a standard String type with an optimized alternative often improves compression ratios by 3× to 5× while accelerating GROUP BY operations by 2× to 4×.

These improvements become increasingly valuable as datasets grow into hundreds of millions or billions of rows.

Understanding LowCardinality

LowCardinality is a special wrapper type that applies dictionary encoding to columns containing many repeated values.

Instead of storing the complete string in every row, ClickHouse stores each unique value once in a dictionary and replaces repeated occurrences with compact integer identifiers.

For example:

CREATE TABLE events
(
    event_time DateTime,
    country LowCardinality(String),
    browser LowCardinality(String)
)
ENGINE = MergeTree
ORDER BY event_time;

Internally, the dictionary might look like:

Dictionary ID	Value
1	India
2	Germany
3	United States
4	France

Rows store only the integer IDs instead of repeatedly storing full strings.

This significantly reduces storage and improves query performance.

The 10,000-Value Rule

LowCardinality performs best when a data part contains fewer than approximately 10,000 unique values.

When the number of unique values becomes significantly larger, dictionary encoding becomes less effective.

In such situations, ClickHouse may internally fall back to treating the column similarly to a regular String, reducing the performance benefits.

This makes LowCardinality particularly well suited for columns such as:

Country names
Browser names
Device types
Product categories
Application names
Status values

Late Deserialization

One of the major reasons LowCardinality improves query performance is a technique known as late deserialization.

Instead of immediately converting dictionary IDs back into strings, ClickHouse performs filtering, grouping, and aggregation directly on the compact integer values.

Only when returning the final query results does ClickHouse translate the IDs back into human-readable strings.

The workflow looks like this:

Rows
   │
   ▼
Dictionary IDs
   │
   ▼
Filtering / GROUP BY / Aggregation
   │
   ▼
Dictionary Lookup
   │
   ▼
Final Output

Processing integers instead of long strings reduces CPU usage, memory bandwidth, and comparison costs.

How LowCardinality Improves Performance

Dictionary encoding provides several advantages:

Smaller storage footprint
Better compression ratios
Reduced memory consumption
Faster integer comparisons
Improved GROUP BY performance
Lower disk I/O
Better cache efficiency

These improvements become increasingly noticeable as data volume grows.

Understanding Enum Types

Enum stores string values internally as fixed integer codes.

Unlike LowCardinality, every possible value must be defined when the table is created.

ClickHouse provides two Enum types:

Enum8

Uses 1 byte per value.

Supports up to 256 unique string mappings.

Enum16

Uses 2 bytes per value.

Supports up to 65,536 unique string mappings.

Example:

CREATE TABLE orders
(
    order_id UInt64,
    status Enum8(
        'Pending' = 1,
        'Processing' = 2,
        'Completed' = 3,
        'Cancelled' = 4
    )
)
ENGINE = MergeTree
ORDER BY order_id;

Although integer values are stored internally, queries remain easy to read.

SELECT *
FROM orders
WHERE status = 'Completed';

Developers continue working with descriptive strings while ClickHouse stores compact numeric values behind the scenes.

LowCardinality vs Enum

Although both optimize repeated values, they solve different problems.

Feature	LowCardinality	Enum
Dictionary encoding	Yes	No
Stores integer IDs	Yes	Yes
Schema flexibility	High	Low
Easy to add new values	Yes	Requires ALTER
Best suited for	Medium-cardinality strings	Small fixed value sets
Compression	Excellent	Excellent
Query readability	Excellent	Excellent

A simple guideline is:

Use LowCardinality when values change over time.
Use Enum when values are fixed and rarely change.

Choosing Between LowCardinality and Enum

Good candidates for LowCardinality

Countries
Cities
Browsers
Device models
Product categories
Application names
Languages

Good candidates for Enum

Order status
Payment status
User roles
Approval states
Workflow stages
Boolean-like states

Selecting the correct type improves both performance and long-term schema maintainability.

When Not to Use LowCardinality

Although powerful, LowCardinality isn't suitable for every column.

Avoid using it for values that are almost always unique.

Examples include:

UUIDs
Email addresses
Session IDs
Customer IDs
Transaction IDs
Random strings

When nearly every value is unique, maintaining a dictionary introduces overhead without meaningful benefits.

When Not to Use Enum

Enum works best when values remain stable over time.

It's usually a poor choice for:

Product names
Customer names
Tags
Dynamic categories
Free-text labels
User-generated values

If new values are introduced frequently, schema modifications become inconvenient.

In these cases, LowCardinality is generally the better option.

Operational Considerations

One important aspect of Enum involves schema modifications.

Adding a new value to the end of an existing Enum definition is typically a lightweight metadata operation.

For example:

ALTER TABLE orders
MODIFY COLUMN status Enum8(
    'Pending' = 1,
    'Processing' = 2,
    'Completed' = 3,
    'Cancelled' = 4,
    'Refunded' = 5
);

However, modifying or reordering existing integer mappings forces ClickHouse to rewrite underlying data parts.

On large production tables, this can become an expensive operation.

Careful planning of Enum values helps avoid unnecessary maintenance.

Measuring the Impact

The effectiveness of these optimizations depends on your workload.

Useful metrics include:

Column storage size
Compression ratio
Query latency
GROUP BY execution time
Memory consumption
CPU utilization

ClickHouse system tables make it easy to evaluate storage efficiency.

Example:

SELECT
    name,
    type,
    formatReadableSize(data_compressed_bytes) AS compressed_size,
    formatReadableSize(data_uncompressed_bytes) AS uncompressed_size,
    round(
        data_uncompressed_bytes /
        data_compressed_bytes,
        2
    ) AS compression_ratio
FROM system.columns
WHERE table = 'events'
  AND database = 'default';

Always benchmark representative production workloads before applying schema changes broadly.

Best Practices

When designing ClickHouse® schemas:

Use LowCardinality for repeated string values.
Use Enum only for small, stable value sets.
Avoid wrapping high-cardinality columns with LowCardinality.
Benchmark before deploying schema changes.
Monitor storage savings using system.columns.
Review cardinality as datasets evolve.
Plan Enum definitions carefully to minimize future ALTER operations.

Small schema decisions can produce substantial long-term performance improvements.

Common Decision Guide

If your column contains...	Recommended Type
Countries	LowCardinality
Browsers	LowCardinality
Device types	LowCardinality
Product categories	LowCardinality
Order status	Enum
Payment status	Enum
User role	Enum
Workflow state	Enum
UUIDs	String
Email addresses	String

This simple checklist can help when designing new schemas.

Conclusion

Both LowCardinality and Enum are valuable optimization features in ClickHouse®, but they address different challenges. LowCardinality provides flexible dictionary encoding for columns containing repeated string values, reducing storage requirements while accelerating filtering and aggregation. Enum, on the other hand, offers highly compact storage for predefined value sets by mapping strings to fixed integer codes.

Rather than treating these data types as interchangeable, evaluate each column based on its cardinality, expected growth, and schema stability. Applying the appropriate type can significantly improve compression, reduce memory usage, and speed up analytical queries—all without requiring changes to application logic.

As with any optimization, benchmarking representative workloads and monitoring system metrics remain the best way to validate performance improvements and ensure your schema continues to scale efficiently as your data grows.

Day 75/100 - Bypassing the Page Cache for ClickHouse® I/O Operations

Kanishga Subramani — Mon, 13 Jul 2026 13:31:40 +0000

Bypassing the Page Cache for ClickHouse® I/O Operations

Introduction

When discussing database performance, one recommendation often appears:

"Bypass the operating system's page cache."

While this advice can be valuable in specific scenarios, it's frequently presented without explaining what the page cache actually does, why ClickHouse® relies on it by default, or when bypassing it improves performance.

The Linux page cache is designed to accelerate file access by keeping recently read data in memory. For many workloads, this significantly reduces disk I/O and improves query latency. However, for large analytical scans that read vast amounts of data only once, populating the page cache may consume valuable memory without providing any future benefit.

ClickHouse® offers the ability to bypass the Linux page cache using Direct I/O, allowing qualifying reads to access storage directly. This behavior is controlled through the min_bytes_to_use_direct_io setting.

In this article, we'll explore how the Linux page cache works, how ClickHouse® interacts with it, what Direct I/O changes, and when enabling it can improve analytical workloads.

Understanding the Linux Page Cache

Before examining ClickHouse®, it's important to understand the role of the Linux page cache.

Whenever an application performs a standard buffered file read, Linux stores the retrieved data in an in-memory cache known as the page cache.

If the same data is requested again:

Linux serves it directly from memory if it already exists in the cache.
Otherwise, it reads the data from disk and stores it in the page cache for future access.

This mechanism is completely transparent to applications. Most software doesn't need to implement its own file caching because the operating system automatically manages frequently accessed data.

For many workloads, this dramatically reduces disk access and improves performance.

How ClickHouse® Reads Data by Default

Contrary to a common misconception, ClickHouse® does not bypass the Linux page cache by default.

Instead, ClickHouse performs buffered file reads, allowing Linux to decide what should remain cached.

The default read path looks like this:

          Query
            │
            ▼
      ClickHouse®
            │
     Buffered Read
            │
            ▼
   Linux Page Cache
            │
            ▼
     Storage Device

If another query accesses the same data shortly afterward, Linux may satisfy the request directly from memory instead of reading from storage again.

For workloads involving frequently accessed data, this behavior significantly improves performance.

Why the Page Cache Isn't Always Ideal

Although the page cache is extremely useful, it isn't always the optimal choice.

Imagine a query scanning several hundred gigabytes of historical data exactly once.

Linux loads every accessed page into memory—even if those pages are unlikely to be read again.

This creates two potential problems.

1. Cache Pollution

Large sequential scans can fill the page cache with data that has little chance of being reused.

As a result, frequently accessed pages may be evicted from memory.

This phenomenon is known as cache pollution.

2. Memory Competition

The Linux page cache consumes available RAM.

At the same time, ClickHouse also requires memory for operations such as:

Query execution
Hash tables
Aggregations
Sorting
JOIN operations
Internal caches

Allowing very large scans to populate the page cache reduces the memory available for these operations.

What Does "Bypassing the Page Cache" Mean?

Bypassing the page cache means reading data directly from storage instead of allowing Linux to cache file contents.

On Linux, this is achieved using the O_DIRECT file flag.

Instead of the traditional read path:

Application
     │
     ▼
Page Cache
     │
     ▼
Disk

the data path becomes:

Application
     │
     ▼
Disk

In this mode:

File data isn't stored in the Linux page cache.
Future reads won't benefit from cached file pages.
RAM is preserved for other database operations.

It's important to understand that Direct I/O bypasses the page cache only for the specific file operations that use O_DIRECT.

It does not disable the page cache for the entire operating system.

How ClickHouse® Enables Direct I/O

ClickHouse controls Direct I/O using the following setting:

min_bytes_to_use_direct_io

This setting defines the minimum amount of data a query must read before ClickHouse switches from buffered I/O to Direct I/O.

Example:

SET min_bytes_to_use_direct_io = 104857600;

This configures ClickHouse to use Direct I/O whenever a query is expected to read more than 100 MB of data.

If the setting remains:

Direct I/O is disabled, which is also the default behavior.

Why Isn't Direct I/O Enabled by Default?

A common question is:

If Direct I/O reduces memory usage, why doesn't ClickHouse always use it?

The answer is simple.

The Linux page cache is extremely effective for workloads involving repeated access to the same data.

Examples include:

BI dashboards
Frequently executed reports
Hot partitions
Interactive analytics
Repeated ad-hoc queries

In these situations, Linux serves many reads directly from RAM.

If Direct I/O were always enabled, every query would need to fetch data from storage again, potentially increasing latency.

For this reason, ClickHouse uses buffered I/O by default and allows administrators to enable Direct I/O only when appropriate.

Direct I/O Does Not Disable ClickHouse® Caches

Another common misconception is that enabling Direct I/O disables all caching inside ClickHouse.

It does not.

ClickHouse includes several independent caching mechanisms, including:

Mark Cache
Uncompressed Block Cache
Query Cache (when enabled)

These caches operate independently of the Linux page cache.

Enabling Direct I/O changes only how file data is read from storage.

ClickHouse's internal caches continue functioning normally.

When Direct I/O Makes Sense

Direct I/O is most beneficial for workloads that perform:

Very large sequential scans
One-time analytical queries
Bulk historical analysis
Large ETL operations
Data migration workloads

It's particularly useful when:

Data is unlikely to be read again
Cache pollution would reduce performance
Predictable memory usage is more important than maximizing cache hits

Many large-scale analytical workloads fall into this category.

When You Should Avoid Direct I/O

Direct I/O isn't a universal optimization.

It may actually reduce performance when:

The same data is queried repeatedly
The working dataset fits comfortably into memory
BI dashboards frequently access hot partitions
Storage latency is significantly higher than RAM latency

In these scenarios, the Linux page cache provides substantial performance benefits by serving repeated reads directly from memory.

Choosing Between Buffered I/O and Direct I/O

The right choice depends on your workload.

Buffered I/O	Direct I/O
Repeated queries	Large sequential scans
Hot datasets	One-time scans
Dashboards	Historical analytics
High cache reuse	Low cache reuse
Faster repeated reads	Lower cache pollution
Relies on Linux page cache	Bypasses Linux page cache

There is no universally "better" option.

The optimal configuration depends on access patterns, available memory, storage performance, and workload characteristics.

Performance Considerations

Before enabling Direct I/O, consider the following:

Measure query latency before and after changes.
Monitor memory usage.
Observe page cache behavior using Linux monitoring tools.
Benchmark representative production workloads.
Avoid enabling Direct I/O solely because it's perceived as faster.

Performance tuning should always be guided by real workload measurements rather than general recommendations.

Key Takeaways

ClickHouse® uses the Linux page cache by default.
Direct I/O is enabled using min_bytes_to_use_direct_io.
Direct I/O relies on Linux's O_DIRECT mechanism.
Only qualifying reads bypass the page cache.
ClickHouse's internal caches continue working with Direct I/O enabled.
Direct I/O is best suited for very large sequential scans.
Buffered I/O often performs better for frequently accessed datasets.
Choosing between the two depends entirely on workload characteristics.

Conclusion

The Linux page cache plays an important role in accelerating file access by keeping frequently used data in memory, and ClickHouse® leverages this behavior by default through buffered I/O. For many analytical workloads—particularly those involving repeated queries, dashboards, or hot datasets—this provides significant performance benefits.

However, workloads that perform large one-time sequential scans can benefit from Direct I/O, which bypasses the page cache using Linux's O_DIRECT mechanism. This helps prevent cache pollution and preserves memory for query execution, aggregations, joins, and ClickHouse's own caching layers.

Ultimately, bypassing the page cache is not a universal performance optimization. It's a workload-specific tuning technique. Understanding your query patterns, memory usage, and storage characteristics is essential before deciding whether buffered I/O or Direct I/O is the better choice for your ClickHouse deployment.

Day 74/100 - Cross-Datacenter Replication in ClickHouse®

Kanishga Subramani — Sun, 12 Jul 2026 13:46:41 +0000

Introduction

Modern applications increasingly operate across multiple geographical regions to improve availability, reduce latency, and ensure business continuity. Whether you're building a disaster recovery strategy, enabling multi-region analytics, or serving users closer to their location, replicating data across data centers has become a critical architectural requirement.

ClickHouse® supports cross-datacenter replication through its ReplicatedMergeTree table engine. Combined with a distributed ClickHouse cluster and a coordination service such as ClickHouse Keeper, it allows organizations to maintain synchronized copies of data across multiple data centers while continuing to deliver high-performance analytical queries.

This architecture ensures that if one data center becomes unavailable due to hardware failures, network outages, or maintenance, other replicas continue serving requests with minimal disruption.

In this article, you'll learn how cross-datacenter replication works in ClickHouse®, the components involved, how to configure a replicated cluster, monitor replication health, troubleshoot common issues, and follow best practices for reliable deployments.

Why Use Cross-Datacenter Replication?

Replicating data across multiple data centers offers several operational and business advantages.

Some of the primary benefits include:

High availability
Disaster recovery
Regional redundancy
Business continuity
Reduced query latency for geographically distributed users
Improved fault tolerance
Maintenance with minimal downtime
Increased read scalability

Cross-datacenter replication is commonly used in industries such as:

Financial services
Telecommunications
E-commerce
Healthcare
SaaS platforms
Media and streaming services

How Replication Works in ClickHouse®

A single-node ClickHouse deployment has one major limitation—if the server becomes unavailable, so does access to your data.

Replication addresses this challenge by maintaining synchronized copies of data across multiple nodes.

ClickHouse replication is powered by the ReplicatedMergeTree table engine.

Instead of copying entire tables repeatedly, ClickHouse replicates only the newly created data parts. Metadata about these parts is coordinated using ClickHouse Keeper, while each replica maintains its own local copy of the data.

This architecture minimizes network traffic while keeping replicas synchronized efficiently.

Some key benefits include:

Fault tolerance—if one replica fails, others continue serving queries.
High availability with no single point of failure.
Read scalability by distributing SELECT queries across replicas.
Zero-downtime maintenance by taking individual nodes offline without interrupting service.

Cluster Overview

For this example, consider a cluster consisting of one shard with three replicas distributed across three different nodes.

Node	IP Address	Role
Node 1	10.x.x.1	Replica
Node 2	10.x.x.2	Replica
Node 3	10.x.x.3	Replica

Each node stores a complete copy of the data.

If any single node becomes unavailable, the remaining replicas continue processing queries without data loss.

The overall architecture looks like this:

                Application
                     │
                     ▼
            Distributed Table
                     │
     ┌───────────────┼───────────────┐
     ▼               ▼               ▼
Node 1          Node 2          Node 3
Replicated      Replicated      Replicated
MergeTree       MergeTree       MergeTree
     └───────────────┼───────────────┘
                     ▼
            ClickHouse® Keeper

Core Components

Cross-datacenter replication relies on three primary components working together.

1. ReplicatedMergeTree

This is the storage engine responsible for replication.

Each replica stores its own copy of the table while automatically synchronizing newly inserted data with other replicas.

2. ClickHouse Keeper

ClickHouse Keeper is the coordination service responsible for managing replication metadata.

It tracks:

Available replicas
Newly created data parts
Replication queues
Leader election
Replica synchronization

Keeper uses the Raft consensus algorithm to ensure fault-tolerant coordination.

3. Distributed Table

Applications should interact with a Distributed table instead of local replicated tables.

The Distributed table automatically routes queries and inserts to the appropriate replicas while hiding the underlying cluster topology.

Configuration Overview

Setting up replication requires several configuration files under:

/etc/clickhouse-server/config.d/

Each file serves a different purpose.

Configuration File	Purpose
`cluster.xml`	Defines shards and replicas
`keeper.xml`	Configures ClickHouse Keeper and RAFT
`zookeeper.xml`	Specifies Keeper endpoints
`macros.xml`	Defines shard and replica identifiers

After updating these configuration files, restart ClickHouse on every node before creating replicated tables.

Important Notes

cluster.xml is identical across all nodes.
zookeeper.xml is identical across all nodes.
keeper.xml contains node-specific server_id values.
macros.xml contains node-specific replica names.

Creating Replicated Tables

Once the cluster is configured and ClickHouse has been restarted on every node, create the database and tables.

Step 1 — Create the Database

CREATE DATABASE analytics
ON CLUSTER cluster_1S_3R;

Step 2 — Create the Local Replicated Table

CREATE TABLE analytics.orders_local
ON CLUSTER cluster_1S_3R
(
    order_id UInt32,
    customer String,
    amount Float64,
    order_date Date
)
ENGINE = ReplicatedMergeTree(
    '/clickhouse/tables/{shard}/orders_local',
    '{replica}'
)
ORDER BY order_id;

The placeholders:

{shard}
{replica}

are automatically replaced using values from macros.xml.

Step 3 — Create the Distributed Table

CREATE TABLE analytics.orders_distributed
ON CLUSTER cluster_1S_3R
AS analytics.orders_local
ENGINE = Distributed(
    cluster_1S_3R,
    analytics,
    orders_local,
    rand()
);

Applications should perform all inserts and queries using this Distributed table.

Verifying Replication

Insert sample records.

INSERT INTO analytics.orders_distributed VALUES
(1,'Alice',1200.00,'2024-01-01'),
(2,'Bob',450.00,'2024-01-02'),
(3,'Charlie',890.00,'2024-01-03'),
(4,'Diana',670.00,'2024-01-04');

Now query the local table from each node.

SELECT *
FROM analytics.orders_local
ORDER BY order_id;

Expected output on every replica:

order_id	customer	amount	order_date
1	Alice	1200.00	2024-01-01
2	Bob	450.00	2024-01-02
3	Charlie	890.00	2024-01-03
4	Diana	670.00	2024-01-04

Seeing identical data across all replicas confirms that replication is functioning correctly.

How Replication Works Internally

When an application inserts data into the Distributed table, ClickHouse performs the following sequence automatically:

Application
      │
      ▼
Distributed Table
      │
      ▼
Selected Replica
      │
      ▼
Writes Data Part
      │
      ▼
ClickHouse Keeper Updates Metadata
      │
      ▼
Remaining Replicas Fetch Data
      │
      ▼
Replication Complete

The process consists of these steps:

The Distributed table routes the insert to one replica.
That replica writes a new data part locally.
ClickHouse Keeper records the new data part.
Other replicas detect the update.
Replicas download the new part.
Keeper confirms successful synchronization.
Every replica now contains identical data.

This synchronization occurs automatically without requiring manual intervention.

Monitoring Replication

Monitoring replica health is essential for production clusters.

Query the system.replicas table.

SELECT
    database,
    table,
    replica_name,
    is_leader,
    is_readonly,
    total_replicas,
    active_replicas,
    queue_size
FROM system.replicas
WHERE table='orders_local';

Example output:

database	table	replica_name	is_leader	total_replicas	active_replicas
analytics	orders_local	replica_1	1	3	3
analytics	orders_local	replica_2	0	3	3
analytics	orders_local	replica_3	0	3	3

Healthy replication typically shows:

active_replicas = total_replicas
is_readonly = 0
queue_size = 0

Checking Replication Errors

To identify replication failures:

SELECT
    database,
    table,
    replica_name,
    last_exception
FROM system.replicas
WHERE last_exception != '';

Any returned rows indicate replication issues that require investigation.

Verifying ClickHouse Keeper

You can verify Keeper availability using:

echo "ruok" | nc 10.x.x.1 9181

Expected response:

imok

If Keeper does not respond, replication will eventually stop because replicas can no longer coordinate metadata.

Common Issues and Solutions

Issue	Likely Cause	Recommended Fix
Replica becomes read-only	Keeper unavailable	Verify Keeper connectivity
Replication queue continues growing	Network latency or firewall	Check network connectivity
Data isn't replicating	Incorrect ReplicatedMergeTree path	Verify ZooKeeper/Keeper paths
Lost Keeper quorum	Too many Keeper nodes offline	Restore quorum by bringing nodes back online

Performance and Operational Best Practices

To build a reliable replicated ClickHouse deployment:

Deploy an odd number of Keeper nodes (3 or 5) to maintain quorum.
Assign every Keeper node a unique server_id.
Give every replica a unique name in macros.xml.
Always execute DDL statements using ON CLUSTER.
Perform inserts through the Distributed table instead of local tables.
Monitor system.replicas regularly for replication lag.
Restart cluster nodes one at a time.
Ensure stable, low-latency connectivity between data centers.
Batch inserts to reduce replication overhead.
Continuously monitor Keeper health and replication queues.

When Should You Use Cross-Datacenter Replication?

Cross-datacenter replication is an excellent choice when your environment requires:

High availability
Disaster recovery
Multi-region deployments
Read scalability
Business continuity
Low-latency regional analytics
Fault-tolerant analytical platforms

Organizations operating globally often combine replicated tables with distributed queries to provide seamless analytics regardless of where users connect.

Conclusion

Cross-datacenter replication enables ClickHouse® to maintain synchronized copies of data across geographically distributed environments, improving availability, fault tolerance, and disaster recovery capabilities. By combining ReplicatedMergeTree, ClickHouse Keeper, and Distributed tables, organizations can build resilient analytical platforms that continue serving queries even when individual nodes or data centers become unavailable.

Although network latency and bandwidth should be considered when replicating across regions, following best practices such as batching inserts, maintaining a healthy Keeper cluster, monitoring replication queues, and using Distributed tables for application traffic helps ensure reliable and efficient synchronization.

For organizations requiring both high-performance analytics and resilient multi-region deployments, ClickHouse provides a scalable and robust replication architecture that minimizes operational complexity while maximizing availability.

Day 73/100 - Using ClickHouse® for Vector Search and AI Workloads

Kanishga Subramani — Sun, 12 Jul 2026 13:27:44 +0000

Using ClickHouse® for Vector Search and AI Workloads

Artificial Intelligence (AI) applications are changing how organizations search, analyze, and interact with data. Instead of relying only on exact keyword matches, modern AI systems understand the meaning behind text, images, audio, and other content using vector embeddings and semantic search.

Traditionally, implementing vector search required deploying a dedicated vector database alongside your operational systems. While effective, this approach increases infrastructure complexity, data synchronization overhead, and operational costs.

Recent versions of ClickHouse® introduce native support for vector similarity search, allowing you to store embeddings alongside structured data and perform high-performance semantic searches on millions or even billions of vectors. This enables AI applications such as Retrieval-Augmented Generation (RAG), recommendation engines, semantic document search, image similarity search, and intelligent chatbots—all within the same analytical database.

In this article, we'll explore what vector search is, how ClickHouse stores vector embeddings, and how you can build scalable AI workloads using ClickHouse.

What Is Vector Search?

Traditional search engines rely on keyword matching.

Suppose your database contains the following product description: ""

"Lightweight running shoes for marathon training."

A user searches for:

"Best sneakers for long-distance running"

Although both sentences describe the same concept, a keyword search may miss the result because the exact words don't match.

Vector search solves this problem.

Instead of comparing words, it compares semantic meaning.

Both pieces of text are converted into numerical vectors by an embedding model.

"Lightweight running shoes for marathon training."

↓

[0.21, -0.67, 0.44, ...]

"Best sneakers for long-distance running"

↓

[0.18, -0.71, 0.40, ...]

Since these vectors are close together in vector space, ClickHouse recognizes that they represent similar concepts and returns the relevant product.

What Are Vector Embeddings?

A vector embedding is a numerical representation of the meaning of an object.

Embeddings can represent:

Text
Images
Audio
Videos
Documents
Products

Example:

"ClickHouse is a fast analytics database"

↓

[0.13, -0.52, 0.78, 0.41, ...]

Modern embedding models commonly generate vectors with dimensions such as:

384
768
1024
1536
3072

Each value captures part of the semantic meaning learned during model training. Similar objects generate vectors that are positioned close together within the embedding space.

Why AI Applications Need Vector Search

Modern AI systems rarely depend solely on keyword matching. Instead, they retrieve information based on semantic similarity.

Common applications include:

Use Case	Example
Semantic Search	Find documents by meaning
Recommendation Systems	Recommend similar products or movies
Retrieval-Augmented Generation (RAG)	Retrieve context for LLMs
Image Search	Find visually similar images
AI Chatbots	Retrieve relevant knowledge before responding
Fraud Detection	Detect similar behavioral patterns

Without vector search, these systems become slower and less accurate.

Why Use ClickHouse for Vector Search?

Rather than maintaining separate databases for analytics and AI workloads, ClickHouse allows both to coexist within a single platform.

Key benefits include:

Store structured data and embeddings together
High-performance analytical SQL
Excellent compression
Horizontal scalability
Real-time ingestion
Familiar SQL interface
Support for billions of vectors

This unified architecture eliminates synchronization challenges between analytical databases and dedicated vector stores.

How ClickHouse Stores Vector Embeddings

Embeddings are typically stored using the Array(Float32) data type.

CREATE TABLE documents
(
    id UInt64,
    title String,
    content String,
    embedding Array(Float32)
)
ENGINE = MergeTree
ORDER BY id;

Example record:

id	title	embedding
1	AI Basics	[0.24, -0.11, 0.56, ...]

Each embedding can contain hundreds or even thousands of floating-point values.

How Vector Similarity Search Works

A typical workflow looks like this:

User Query
     │
     ▼
Embedding Model
     │
     ▼
Query Vector
     │
     ▼
ClickHouse Vector Search
     │
     ▼
Most Similar Records

For example:

A user asks:

"How does ClickHouse replication work?"

The embedding model converts the question into a vector.

ClickHouse compares that vector with stored document embeddings and returns the most semantically relevant results.

Distance Metrics Used in Vector Search

Vector similarity is calculated using mathematical distance metrics.

Cosine Similarity

Measures the angle between vectors.

Best suited for:

Text embeddings
Semantic search
LLM applications

Euclidean Distance (L2)

Measures straight-line distance between vectors.

Commonly used for:

Numerical features
Image embeddings

Dot Product

Measures similarity based on vector magnitude and direction.

Frequently used by transformer-based embedding models.

Approximate Nearest Neighbor (ANN) Search

Comparing a query against every stored vector becomes increasingly expensive as datasets grow.

Approximate Nearest Neighbor (ANN) algorithms dramatically improve performance by searching only the most promising candidates instead of performing exhaustive comparisons.

Benefits include:

Millisecond-level search latency
High recall
Efficient CPU utilization
Scalability to billions of vectors

ClickHouse supports ANN indexing to accelerate similarity searches while maintaining excellent accuracy.

Example Workflow

Consider an AI-powered documentation assistant.

Documents
     │
     ▼
Embedding Model
     │
     ▼
ClickHouse
(Store Documents + Embeddings)
     │
     ▼
User Query
     │
     ▼
Embedding Model
     │
     ▼
Vector Search
     │
     ▼
Relevant Documents
     │
     ▼
Large Language Model
     │
     ▼
Final Answer

This architecture forms the foundation of Retrieval-Augmented Generation (RAG).

Example Table

CREATE TABLE knowledge_base
(
    id UInt64,
    title String,
    content String,
    embedding Array(Float32)
)
ENGINE = MergeTree
ORDER BY id;

After generating embeddings using models such as OpenAI Embeddings, Sentence Transformers, or other embedding models, store the vectors inside the embedding column for semantic retrieval.

AI Workloads That Benefit from ClickHouse

Semantic Search

Retrieve documents based on meaning instead of exact keywords.

Recommendation Systems

Recommend products, movies, music, or articles using vector similarity.

RAG Pipelines

Retrieve relevant context before sending prompts to a Large Language Model.

AI Chatbots

Fetch accurate knowledge base articles before generating responses.

Image Similarity Search

Search for visually similar images using image embeddings.

Log Analysis

Perform semantic searches across operational logs instead of exact-text matching.

Performance Best Practices

For optimal performance:

Store embeddings as Float32
Maintain consistent vector dimensions
Use ANN indexes for large datasets
Partition data appropriately
Separate metadata from embedding storage
Batch insert vectors
Eliminate duplicate embeddings
Continuously monitor query latency

Advantages of Using ClickHouse for AI

Feature	Benefit
SQL Interface	Easy integration with existing applications
High Compression	Lower storage costs
Fast Analytics	Combine AI retrieval with analytical queries
Real-Time Ingestion	Continuously ingest new embeddings
Horizontal Scaling	Handle billions of vectors
Unified Platform	Analytics and AI in one database

When Should You Use ClickHouse for Vector Search?

ClickHouse is an excellent choice when you need to:

Combine analytics and AI workloads in a single platform
Build semantic search applications
Implement Retrieval-Augmented Generation (RAG)
Store millions or billions of embeddings
Analyze structured data alongside vector data
Support real-time ingestion with fast similarity searches

If your application requires both analytical processing and semantic retrieval, ClickHouse provides a unified architecture that reduces operational complexity while delivering exceptional performance.

Conclusion

Vector search enables applications to understand the meaning behind data instead of relying solely on exact keyword matches. By storing vector embeddings alongside structured data, ClickHouse makes it possible to build semantic search engines, recommendation systems, AI chatbots, image similarity search, and Retrieval-Augmented Generation (RAG) pipelines without introducing a separate vector database.

With efficient storage, SQL-based querying, real-time ingestion, and support for Approximate Nearest Neighbor (ANN) indexing, ClickHouse provides a scalable foundation for AI-powered applications that combine large-scale analytics with semantic retrieval. As AI workloads continue to grow, ClickHouse offers a practical and high-performance platform for unifying analytical and vector search capabilities within a single database.

Day 72 - ClickHouse® Internals: How the Query Analyzer Works

Kanishga Subramani — Sat, 11 Jul 2026 13:36:59 +0000

When you execute a SQL query in ClickHouse®, it doesn't immediately start scanning data or reading storage files. Instead, the query passes through several stages that transform it from a raw SQL string into an executable pipeline.

One of the most critical stages in this system is the Query Analyzer. It bridges the gap between parsing a SQL statement and generating an execution plan by understanding what the query actually means.

In this article, we'll explore where the Query Analyzer fits into the query execution pipeline, what problems it solves, and why it's a critical component of the ClickHouse® query engine.

The Query Execution Pipeline

A simplified view of the ClickHouse® query execution pipeline flows as follows:

       SQL Query
           │
           ▼
        Parser
           │
           ▼
  Abstract Syntax Tree (AST)
           │
           ▼
     Query Analyzer
           │
           ▼
       Query Tree
           │
           ▼
     Query Planner
           │
           ▼
   Execution Pipeline
           │
           ▼
    Data Processing

Each stage has a highly specific responsibility:

The Parser: Verifies that the SQL syntax is valid.
The Query Analyzer: Resolves the semantic meaning of the query.
The Query Planner: Determines how to execute the resolved query efficiently.
The Execution Pipeline: Performs the actual work of reading, filtering, aggregating, and returning data.

Understanding this separation helps explain why parsing and analysis are treated as independent architectural layers.

Parsing: Understanding Query Structure

The parser is responsible only for recognizing SQL syntax. Consider the following query:

SELECT
    customer_id,
    SUM(amount) AS total
FROM sales
GROUP BY customer_id;

The parser checks strictly grammatical constraints:

Are SELECT, FROM, and GROUP BY keywords used correctly?
Are parentheses balanced in SUM(amount)?
Do punctuation marks (like commas) appear in valid positions?

After successful parsing, ClickHouse® creates an Abstract Syntax Tree (AST). The AST represents the purely syntactic structure of the query; every clause, expression, function call, and identifier becomes a node in this tree.

⚠️ What the Parser Ignores: At this stage, ClickHouse® still hasn't answered basic semantic questions: Does the sales table exist? Is customer_id a valid column? Is SUM() a known function? Are the data types compatible? Those questions are deferred to the Analyzer.

What Is the Query Analyzer?

The Query Analyzer gives semantic meaning to the parsed query. While the parser understands how the query is written, the analyzer determines what data and operations the query actually references.

Instead of passing raw syntax further down the chain, the analyzer builds a richer internal representation called the Query Tree that encapsulates the validated logic, type systems, and database metadata.

7 Major Responsibilities of the Query Analyzer

1. Resolving Tables

The parser sees only the identifier sales. The analyzer queries the system catalog to verify that the sales table exists, identifies its underlying storage engine (e.g., MergeTree), and fetches its physical schema definition.

2. Resolving Columns

The analyzer checks if the requested columns actually exist within the resolved table. It also checks for ambiguity—if a query joins multiple tables that share identical column names without explicit qualifiers, the analyzer throws an error before resources are spent on planning.

3. Resolving Aliases

Aliases simplify complex SQL text, but they must be mapped back to concrete expressions. If you write price * quantity AS revenue, the analyzer ensures that any later references to revenue point directly to the underlying arithmetic expression (price * quantity).

4. Function Resolution

ClickHouse® features a massive library of built-in functions. The analyzer maps a string like lower(name) to its actual C++ execution kernel implementation, verifies that the function exists, checks that the argument count is correct, and ensures the argument types are valid.

5. Type Inference

If a query evaluates price * quantity, where price is a Decimal32 and quantity is a UInt32, the analyzer computes the exact output data type. This precise type signature is mandatory for the Query Planner and the vectorized execution engine.

6. Expression Validation

The analyzer catches logical SQL violations early. For instance, executing a aggregate function (SUM(amount)) alongside an unaggregated column (customer_id) without a matching GROUP BY clause is syntactically valid but semantically illegal. The analyzer halts this query immediately.

7. Building the Query Tree

The final and most crucial output of the analyzer is the Query Tree. Unlike the AST, which mimics the user's literal text, the Query Tree contains nodes bound to actual metadata objects, verified functions, and concrete data types.

Deep Dive: AST vs. Query Tree

Feature	Abstract Syntax Tree (AST)	Query Tree
Focus	SQL Grammar & Syntax	Query Semantics & Meaning
Generated By	Parser	Query Analyzer
Structure	Closely resembles raw SQL text	Represents resolved objects and operations
Contents	Raw text identifiers and strings	Validated tables, columns, functions, and explicit data types
Core Question	"What did the user write?"	"What does this query actually mean?"

Why Doesn't the Planner Work Directly on the AST?

The planner's sole job is optimization—determining the fastest way to read and process data.

If the planner operated directly on the AST, its code would be heavily bogged down. It would have to repeatedly look up metadata tables, perform type checks, and resolve aliases for every single optimization pass. By offloading these tasks to the Query Analyzer, the planner receives a clean, uniform, and fully validated Query Tree, allowing it to focus entirely on performance optimization strategies.

Inspecting the Analyzer

ClickHouse® exposes its internal analyzer mechanics via diagnostic commands. If you want to see exactly how ClickHouse® translates your query semantics, you can run:

EXPLAIN QUERY TREE
SELECT
    customer_id,
    SUM(amount)
FROM sales
GROUP BY customer_id;

Rather than printing a physical execution plan (which shows read steps and thread allocations), this command outputs the structured Query Tree. It is incredibly useful for debugging complex queries, checking unexpected alias behavior, or validating nested subquery behavior.

Conclusion

The Query Analyzer is the unsung hero of the ClickHouse® query engine. By cleanly separating syntactic validation from semantic resolution, ClickHouse® keeps its pipeline modular, predictable, and highly performant.

The next time you execute a query, remember that long before a single byte of data is read from disk, the Query Analyzer has already mapped out the exact DNA of your SQL statement.

References

ClickHouse® Documentation: Understanding Query Execution with the Analyzer

Day 71 - Advanced Partitioning Strategies for Petabyte-Scale Tables

Kanishga Subramani — Sat, 11 Jul 2026 11:20:29 +0000

As datasets grow into the petabyte range, choosing the right partitioning strategy becomes one of the most important architectural decisions in ClickHouse®. Well-designed partitions improve query performance, simplify data lifecycle management, reduce maintenance overhead, and make large analytical workloads significantly more efficient. On the other hand, poor partitioning choices can lead to excessive numbers of parts, slower merges, increased storage overhead, and degraded query performance.

Unlike traditional relational databases, ClickHouse® partitions are not indexes. They are primarily a data management mechanism that determines how data is physically organized on disk. Understanding this distinction is essential when designing schemas for large-scale deployments.

In this article, we'll explore advanced partitioning strategies, discuss common design patterns used in production environments, and examine best practices for managing petabyte-scale tables.

Understanding Partitions in ClickHouse®

Every MergeTree table stores data as immutable parts.

When a PARTITION BY expression is defined, ClickHouse® groups parts into logical partitions.

For example:

sql
CREATE TABLE events
(
event_time DateTime,
user_id UInt64,
country LowCardinality(String),
event_type String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_time, user_id);

Each month becomes its own partition.

This allows ClickHouse® to:

Drop old data instantly
Optimize merges within partitions
Prune irrelevant partitions during queries
Manage storage more efficiently

However, partitioning alone does not make queries fast. The sorting key (ORDER BY) is still the primary mechanism for efficient data retrieval.

Why Partitioning Becomes Critical at Petabyte Scale

At small scales, inefficient partitioning may only waste some storage.

At petabyte scale, it can impact:

Merge performance
Metadata size
Disk utilization
Query planning
Background processing
Backup and restore times
Replication efficiency

A poor partitioning strategy may generate millions of tiny parts, overwhelming background merge operations and increasing query overhead.

Conversely, partitions that are too large can make data management tasks such as retention and deletion more expensive.

Finding the right balance is essential.

Choosing the Right Partition Key

Selecting an appropriate partition key is the most important design decision.

A good partition key should:

Align with common query patterns
Support retention policies
Produce a manageable number of partitions
Avoid excessive fragmentation
Simplify operational maintenance

Time-based partitioning is the most common strategy because many analytical workloads naturally query data over time ranges.

Examples include:

sql
PARTITION BY toYYYYMM(event_time)

sql
PARTITION BY toYYYYWW(event_time)

sql
PARTITION BY toDate(event_time)

The appropriate granularity depends on data volume and retention requirements.

Daily partitions may work well for very high-ingestion workloads, while monthly partitions are often sufficient for moderate data volumes.

Multi-Dimensional Partitioning

Large organizations frequently manage data across multiple regions, business units, or tenants.

In such cases, composite partition keys can reduce operational complexity.

Example:

sql
PARTITION BY (
region,
toYYYYMM(event_time)
)

sql
PARTITION BY (
tenant_id,
toYYYYMM(event_time)
)

This enables independent lifecycle management for each tenant or region while preserving efficient pruning for time-based queries.

However, high-cardinality columns should be avoided in partition keys. Using values such as user_id or device_id can create an excessive number of partitions, increasing metadata overhead and reducing merge efficiency.

Partition Pruning

One of the primary performance benefits of partitioning is partition pruning.

When a query includes filters that match the partition key, ClickHouse® can skip entire partitions without reading them from disk.

For example:

sql
SELECT count()
FROM events
WHERE event_time >= '2026-01-01'
AND event_time < '2026-02-01';

If the table is partitioned by month, ClickHouse® only scans the relevant monthly partition instead of the entire dataset.

Partition pruning significantly reduces disk I/O and query execution time, particularly for time-series workloads.

Partition Lifecycle Management

Partitions simplify large-scale data management.

Instead of deleting billions of individual rows, entire partitions can be removed instantly.

Examples include:

Data retention
Archiving
Tiered storage
Backup strategies

For example:

sql
ALTER TABLE events
DROP PARTITION '202501';

This metadata operation is significantly faster than executing large DELETE statements.

Similarly, partitions can be moved between storage volumes using storage policies, allowing older data to reside on lower-cost disks while keeping recent data on faster storage.

Monitoring Partition Health

Large deployments should continuously monitor partition-related metrics.

Useful system tables include:

system.parts
system.part_log
system.merges
system.detached_parts

Key indicators include:

Active part count
Partition count
Average part size
Merge backlog
Detached parts
Disk utilization

Monitoring these metrics helps identify partitioning problems before they impact production workloads.

Common Partitioning Mistakes

Even experienced teams encounter partitioning issues.

Common mistakes include:

Partitioning by high-cardinality columns
Creating daily partitions for low-volume tables
Ignoring retention policies
Using partitioning instead of an appropriate sorting key
Creating thousands of tiny partitions
Frequently dropping small partitions

Understanding workload characteristics before designing a schema helps avoid these pitfalls.

Best Practices for Petabyte-Scale Deployments

For very large ClickHouse® clusters, consider the following recommendations:

Keep partition keys low in cardinality.
Design partitions around retention requirements.
Use the sorting key to optimize query performance.
Monitor active part counts regularly.
Avoid over-partitioning.
Test partitioning strategies with realistic production workloads.
Use storage policies for hot and cold data.
Review partition distribution as data volumes evolve.

Partitioning decisions made early in a project's lifecycle can have long-term implications for performance and operational efficiency.

Final Thoughts

Partitioning is one of the foundational design decisions in ClickHouse®, especially for petabyte-scale analytical systems. A well-designed partition strategy enables efficient data lifecycle management, reduces maintenance costs, and improves query performance through effective partition pruning. At the same time, choosing the wrong partition key can create operational challenges that become increasingly difficult to correct as data grows.

Rather than treating partitioning as a performance optimization in isolation, it should be considered alongside sorting keys, storage policies, merge behavior, and expected query patterns. Together, these design choices determine how efficiently ClickHouse® scales to handle massive datasets.

To learn more about operating and monitoring ClickHouse® clusters, explore CH-Ops.

References

Official ClickHouse® Documentation
When DROP PARTITION Fails: A Hidden Data Duplication Risk in ClickHouse®
Date Partitioning Strategies
Why Too Many Parts Hurt ClickHouse Performance

Day 70- Extending ClickHouse® with C++ UDFs

Kanishga Subramani — Sat, 11 Jul 2026 10:07:51 +0000

Introduction
ClickHouse® comes with hundreds of built-in functions covering string manipulation, date/time
operations, mathematical calculations, aggregations, and more. For most use cases, these
built-in functions are more than sufficient.
However, there are situations where you need custom logic that no built-in function covers such
as a proprietary scoring algorithm, a domain-specific transformation, or a complex encoding
scheme. In these cases, ClickHouse® allows you to extend its functionality through
User-Defined Functions (UDFs).
While ClickHouse® supports SQL User-Defined Functions (UDFs) and Executable UDFs,
developers can also extend ClickHouse® by implementing custom C++ functions directly
within the ClickHouse source code. This approach delivers the highest performance because
the function becomes part of the ClickHouse execution engine.
In this blog, we'll focus on C++ UDFs - the most performant approach for extending
ClickHouse® with custom functions.
What is a UDF?
A User-Defined Function (UDF) is a custom function you write yourself and register with
ClickHouse® so it can be used in SQL queries - just like any built-in function.
sql
-- Using a built-in function
SELECT upper(name) FROM users;
-- Using a custom UDF
SELECT my_custom_function(name) FROM users;
Once registered, a UDF behaves exactly like a built-in function - it can be used in SELECT,
WHERE, GROUP BY, and any other SQL clause.

Ways to Extend ClickHouse®
ClickHouse® offers three approaches for adding custom functionality.
Method Language Performance Best For

SQL UDF SQL High Simple reusable expressions
Executable UDF Python, Shell,

etc.

Moderate External business logic

Custom C++ Function C++ Highest Performance-critical functionality
For most use cases, SQL UDFs or Executable UDFs are sufficient. Custom C++ functions are
typically reserved for advanced scenarios where maximum performance is required.

Why Use C++ UDFs?
C++ UDFs are the highest performance option because:
● They are compiled to native machine code — no interpreter overhead
● They run inside the ClickHouse® process — no inter-process communication
● They have direct access to ClickHouse® column types — no serialization overhead
● They can process entire columns at once using vectorized execution
This makes C++ UDFs suitable for performance-critical custom logic that needs to run over
billions of rows.

Prerequisites
Before writing a C++ UDF, make sure you have:
● ClickHouse® installed and running
● A C++ compiler (g++ or clang++)
● Basic familiarity with C++
● ClickHouse® source code (for header files)
● cmake installed

SQL-Based UDFs (Lambdas)
Before writing a C++ UDF, consider whether a SQL-based UDF (available from ClickHouse®
v21.11+) can solve your problem. These are much simpler to write and maintain.
Create a simple SQL UDF:

CREATE FUNCTION multiply_by_two AS (x) -> x * 2;
Use it in a query:
SELECT
amount
multiply_by_two(amount) AS doubled_amount
FROM default.orders;
Output:
| amount | doubled_amount |
|------------|------------------------|
| 1200.00 | 2400.00 |
| 450.00 | 900.00 |
| 890.00 | 1780.00 |

Use SQL UDFs first - they are simpler to write, easier to maintain, and perform well for
most use cases. Only reach for C++ UDFs when SQL expressions are not sufficient or
performance is critical.
Executable UDFs - External Scripts
Executable UDFs allow you to call external scripts written in any language( Python, Go, etc..,).
ClickHouse® communicates with the script via stdin/stdout.
Example: Python UDF
Step 1: Write the Python script (categorize_amount.py)
python

!/usr/bin/env python3

/usr/local/bin/categorize_amount.py

import sys
for line in sys.stdin:
amount = float(line.strip())
if amount >= 1000:
print("Premium")
elif amount >= 500:
print("Standard")
else:
print("Basic")

Make it executable:
bash
chmod +x /usr/local/bin/categorize_amount.py
Step 2: Register the UDF in ClickHouse®
Create a configuration file in /etc/clickhouse-server/:
xml

executable
categorize_amount
String

Float64
amount

TabSeparated
categorize_amount.py

Step 3: Restart ClickHouse®
bash
sudo systemctl restart clickhouse-server
Step 4: Use the UDF in a query
SELECT
order_id,
amount,
categorize_amount(amount) AS tier
FROM default.orders;
Output:
| order_id | amount | tier |
|---------- |--------- |----------|
| 1 | 1200.00 | Premium |
| 2 | 450.00 | Basic |
| 3 | 890.00 | Standard |

C++ UDFs - Custom Native Functions
When maximum performance is required, ClickHouse® can be extended by implementing
custom C++ functions directly in its source code. Unlike Executable UDFs, these functions
become part of the ClickHouse execution engine and run natively without external processes.
Note: ClickHouse® does not currently support loading arbitrary C++ UDFs as
standalone plugins. Instead, custom C++ functions are added to the ClickHouse
source code, registered with the function factory, and become available after
rebuilding the server.
How Custom C++ Functions Work

Write C++ Function
│
▼

Implement IFunction Interface

│
▼

│
▼
Build ClickHouse
│
▼
Restart ClickHouse
│
▼
Use in SQL Queries

Step 1: Set Up the Build Environment
you must build ClickHouse from source to add a native function, clone the repository
along with its submodules:
Install the required development tools and clone the ClickHouse® source code.

Install required build tools

sudo apt install -y build-essential cmake git

Clone the ClickHouse codebase

git clone https://github.com/ClickHouse/ClickHouse.git
cd ClickHouse
git submodule update --init --recursive
Step 2: Implement the IFunction Interface
ClickHouse functions process data column-by-column using vectorized execution. You
must inherit from the IFunction interface and implement its core methods.
Create your function file (e.g., src/Functions/FunctionMultiplyByTwo.cpp):
cpp

include

namespace DB
{
class FunctionMultiplyByTwo : public IFunction
{
public:
static constexpr auto name = "multiplyByTwo";
String getName() const override
{
return name;
}
...
};
Inside the execution logic, process the input column and return the transformed result.
Step 3: Register the Function
Register the function with ClickHouse's FunctionFactory.

At the bottom of your implementation file, you must register your class with ClickHouse's
FunctionFactory so that the parser can map the SQL string to your execution logic.

REGISTER_FUNCTION(MultiplyByTwo)
{
factory.registerFunction();
}
Step 4: Compile and Run
Once your files are in place, compile the code base and spin up your newly
extended server.
Rebuild ClickHouse so the new function becomes part of the server.

Generate the build files via CMake

mkdir build && cd build
cmake .. -G Ninja

Compile the ClickHouse binary

ninja clickhouse

Start your custom compiled server

sudo systemctl restart clickhouse-server
Step 5: Execute via SQL

The function is natively embedded within the query engine and can be used
immediately without any prior registration or DLL loading statements:
SELECT multiplyByTwo(25);
Output
multiplyByTwo
50

Comparing UDF Types
| Feature | SQL UDF | Executable UDF | C++ UDF |
|----------------------|----------------|-----------------|-----------------|
| Ease of writing | Very easy | Easy | Complex |
| Performance | High | Moderate | Highest |
| Language | SQL | Any | C++ |
| Setup complexity | None | Low | High |
| Restart required | No | Yes | Yes |
| Best for | Simple logic | Prototyping | Max performance |
| Available since | v21.11 | v21.11 | Always |

When to Use Each UDF Type
Use SQL UDF when:
● The logic can be expressed in SQL
● You need quick iteration without restarting ClickHouse®
● The transformation is simple (case statements, arithmetic, string operations)
Use Executable UDF when:
● You need logic that SQL cannot express
● You want to use Python, R, or another language
● Performance is not the primary concern
● Prototyping a new function before optimizing
Use C++ UDF when:
● Maximum performance is required
● The function will run over billions of rows
● You need direct access to ClickHouse® internal column types
● The logic is complex and cannot be expressed in SQL or external scripts

Best Practices
● Start with SQL UDFs — they are the simplest and often sufficient.
● Test executable UDFs before investing in C++ implementation — validate the logic first.
● Always restart ClickHouse® after registering executable or C++ UDFs.
● Use meaningful function names — avoid names that conflict with built-in functions.
● Document your UDFs — add comments explaining what the function does, its inputs,
and outputs.

● Monitor performance — use system.query_log to compare query times before and
after adding a UDF.
● Version control your UDF code — treat UDF source files like application code.

Quick Reference
| Task | Command / Action |
|-----------------------------|-----------------------------------------------|
| Create SQL UDF | CREATE FUNCTION name AS (args) -> expression |
| Drop SQL UDF | DROP FUNCTION name |
| List all UDFs | SELECT * FROM system.functions |
| Register executable UDF | Add XML config to /etc/clickhouse-server/ |
| Register C++ UDF | Copy .so file + add XML config |
| Reload after config change | sudo systemctl restart clickhouse-server |
| UDF storage directory | /var/lib/clickhouse/user_defined/ |

Final Thoughts
ClickHouse® UDFs provide a powerful way to extend the database with custom logic when
built-in functions are not sufficient. For most use cases, SQL UDFs offer the best balance of
simplicity and performance. For more complex logic, executable UDFs allow you to use any
language. When maximum performance is required, C++ UDFs deliver native speed with direct
access to ClickHouse® internals.
Start simple — write a SQL UDF first. If performance becomes an issue or the logic is too
complex for SQL, move to executable UDFs or C++ as needed.

References
● ClickHouse® Documentation — User Defined Functions
● ClickHouse® Documentation — Executable UDFs
● ClickHouse® Documentation — CREATE FUNCTION
● ClickHouse® Documentation — system.functions
● ClickHouse® GitHub — Function Examples

Day 69 - Anatomy of a ClickHouse® Vertical Merge

Kanishga Subramani — Fri, 10 Jul 2026 16:10:18 +0000

Anatomy of a ClickHouse® Vertical Merge

Introduction

ClickHouse is designed to process analytical workloads at exceptional speed. One of the key reasons behind this performance is its background merge mechanism, which continuously combines smaller data parts into larger ones. These merges improve query performance, reduce the number of files on disk, and optimize data storage without interrupting ongoing queries.

For most tables, ClickHouse performs a Horizontal Merge, where all columns are processed together. However, when dealing with wide tables containing hundreds of columns, this approach can consume a significant amount of memory. To address this challenge, ClickHouse introduces Vertical Merge, an optimization that processes columns in multiple stages instead of all at once.

In this article, we'll explore how Vertical Merge works, why it was introduced, how ClickHouse decides when to use it, and how it helps reduce memory consumption during background merge operations.

Prerequisites

Before exploring Vertical Merge, it's helpful to understand a few core ClickHouse concepts:

MergeTree tables
Data parts
ORDER BY (sorting key)
Background merge operations

Understanding Background Merges

ClickHouse stores data in immutable data parts. A data part is called immutable because once it is written to disk, ClickHouse never modifies it directly. Instead, when optimization is needed, ClickHouse creates a new merged part and removes the old ones after the merge completes successfully.

Since every INSERT operation creates a new data part, a busy table can quickly accumulate hundreds or even thousands of small parts. Having too many parts increases storage metadata, makes background maintenance more expensive, and can negatively impact query performance.

To keep storage efficient and maintain fast query execution, ClickHouse continuously performs background merges that combine smaller parts into larger ones.

For example, if you insert data five times into the same MergeTree table, ClickHouse creates five separate data parts. These parts are stored independently until the background merge process combines them into a larger part.

INSERTS
  │  Part A
  │  Part B
  │  Part C
  ▼
Background Merge
  │
  ▼
Merged Part

During a merge, ClickHouse:

Reads multiple source parts.
Combines and sorts rows according to the table's sorting key.
Applies engine-specific processing.
Writes a new merged part.
Removes the old parts after the merge completes successfully.

Horizontal Merge vs. Vertical Merge

ClickHouse supports two approaches for merging data parts:

Feature	Horizontal Merge	Vertical Merge
Column Processing	Processes all columns together	Processes key columns first, then remaining columns separately
Memory Usage	Higher memory usage	Lower memory usage
Best For	Narrow tables	Wide tables
Workflow Complexity	Simpler merge process	Multi-stage merge process
Scalability	Faster for smaller tables	Better scalability for large tables

Horizontal Merge remains the default merge strategy in ClickHouse because it performs efficiently for most workloads. Vertical Merge is used only when ClickHouse determines that processing all columns together would require excessive memory, making a staged merge more efficient.

Why Was Vertical Merge Introduced?

Horizontal Merge works efficiently for tables containing a relatively small number of columns. However, as analytical datasets grow wider—with hundreds of columns and millions or billions of rows—the amount of data that must be processed during a merge also increases. Processing every column simultaneously can consume a considerable amount of memory.

Imagine an analytics table containing:

250 columns
Millions of rows
Several large String, Array, or JSON columns
Continuous data ingestion

A traditional merge must read data from every column at the same time. As the number of columns increases, memory consumption grows significantly.

Instead of loading every column simultaneously, Vertical Merge divides the work into smaller stages, allowing ClickHouse to process only a small portion of the data at a time. This dramatically reduces peak memory usage while still producing the same final merged part.

How Vertical Merge Works (Step-by-Step Example)

Before understanding the internal workflow, let's look at a simple example. We'll use two small data parts to demonstrate how ClickHouse performs a Vertical Merge. The same process is used internally for much larger datasets.

Let's assume we have two data parts that need to be merged:

Part 1

id	city	sales
1	Chennai	500
3	Delhi	700

Part 2

id	city	sales
2	Mumbai	600
4	Pune	900

Assume the table is created with:

ORDER BY id

This means the final merged data must be ordered by the id column.

Step 1: Read Only the Sorting Key

Every MergeTree table defines a sorting key using the ORDER BY clause. Instead of loading every column (id, city, and sales) into memory at once, ClickHouse first reads only the sorting key columns to determine the correct row order for the merged part.

Part 1 Key: id (1, 3)
Part 2 Key: id (2, 4)

At this stage, ClickHouse completely ignores the city and sales columns.

Step 2: Determine the Correct Row Order

Using the id values, ClickHouse determines how the rows should appear in the final merged part. The correct order is:

Final Position	id
1	1
2	2
3	3
4	4

Internally, ClickHouse remembers where every row belongs. This information is called a row mapping.

Once the row mapping has been created, ClickHouse no longer needs to recalculate the row order while merging the remaining columns, making the process both efficient and memory-friendly. You can think of it as a set of instructions saying:

Row from Part 1 $\rightarrow$ Position 1
Row from Part 2 $\rightarrow$ Position 2
Row from Part 1 $\rightarrow$ Position 3
Row from Part 2 $\rightarrow$ Position 4

This mapping is created only once and is reused while processing every remaining column. It exists only for the duration of the merge operation and is discarded after the final merged part has been written.

Step 3: Merge the Remaining Columns

Now ClickHouse starts processing the remaining columns one at a time.

1. Merge the `city` column

Using the row mapping, ClickHouse reads the city values from both parts and writes them in the correct position:

Part 1: Chennai, Delhi
Part 2: Mumbai, Pune

Resulting Output:

city
----
Chennai
Mumbai
Delhi
Pune

2. Merge the `sales` column

Next, ClickHouse clears the previous column from memory and processes the sales column using the exact same row mapping:

Part 1: 500, 700
Part 2: 600, 900

Resulting Output:

sales
-----
500
600
700
900

Notice that ClickHouse never loads every column into memory simultaneously. Instead, it processes one column (or a small group of columns) at a time using the previously created row mapping.

Step 4: Build the Final Merged Part

Finally, ClickHouse combines all processed columns into a single, comprehensive merged part on disk:

id	city	sales
1	Chennai	500
2	Mumbai	600
3	Delhi	700
4	Pune	900

The old data parts are then unlinked and removed automatically.

Why Does This Save Memory?

Imagine a table with 500 columns:

Horizontal Merge

ClickHouse tries to process all 500 columns together.

[ Memory Buffer ] ──► Contains: id, city, sales, price, quantity, ... (All 500 columns simultaneously)

Result: Requires a massive chunk of RAM.

Vertical Merge

ClickHouse processes columns sequentially, using the mapping as a guide.

[ Memory Buffer ] ──► id ──► (Done)
[ Memory Buffer ] ──► city ──► (Done)
[ Memory Buffer ] ──► sales ──► (Done)
...

At any given moment, only a small portion of the overall row data is in memory, which drastically minimizes peak memory usage.

When Does ClickHouse Use Vertical Merge?

ClickHouse automatically determines whether a Vertical Merge is more efficient than a Horizontal Merge. The decision is based on several internal factors, including:

Number of columns in the table
Number of rows being merged
Size of the source data parts
Estimated memory required for the merge
MergeTree configuration thresholds (e.g., vertical_merge_algorithm_min_columns, vertical_merge_algorithm_min_rows)

If ClickHouse estimates that a Horizontal Merge would consume excessive memory, it automatically switches to a Vertical Merge. In most production environments, users do not manually choose between Horizontal Merge and Vertical Merge; ClickHouse handles this choice completely under the hood.

⚠️ Note: Vertical Merge optimizes the merge process itself to prevent Out-Of-Memory (OOM) errors. It does not directly improve SELECT query performance. However, by reducing memory pressure during background operations and keeping parts organized, it contributes to overall system stability.

Advantages & Limitations

Advantages

Reduces Peak Memory: Slashing RAM footprint during heavy background activity.
Improves Wide Table Scalability: Perfect for massive schemas with hundreds of attributes.
Handles Complex Types Efficiently: Prevents large String, Array, and JSON columns from hogging memory.
Maintains Server Stability: Minimizes the resource impact of background operations on running production queries.

Limitations

Processing Overhead: Additional management stages (like generating and reading row mappings) introduce a minor amount of compute overhead.
Inefficient for Narrow Tables: Small schemas or narrow tables generally perform better with a fast, direct Horizontal Merge.
Workload Dependent: Overall gains rely heavily on proper table design and data characteristics.

Monitoring Merge Activity

ClickHouse provides several system tables to help administrators track and audit merge strategies:

1. View Active Merges

To monitor what the background merge pool is working on right now:

SELECT
    database,
    table,
    elapsed,
    progress,
    num_parts
FROM system.merges;

2. View Active Parts

To view how parts are currently organized on disk after being merged:

SELECT
    partition,
    name,
    rows,
    bytes_on_disk
FROM system.parts
WHERE active;

3. View Merge History

To evaluate how frequently ClickHouse utilizes vertical vs. horizontal merge tracks, look into the part log:

SELECT 
    event_time,
    table,
    event_type,
    rows_read,
    memory_usage
FROM system.part_log
WHERE event_type = 'MergeParts'
ORDER BY event_time DESC;

Real-World Use Cases

Vertical Merge is the quiet hero behind several high-scale database architectures:

Workload	Why Vertical Merge Helps
IoT Sensor Data	Multi-metric schemas frequently span hundreds of specialized metric columns.
Log Analytics	Massive text fields (`String`) would otherwise trigger memory spikes if loaded concurrently.
Clickstream Analytics	Event tables tracking granular user interactions scale widely.
Data Warehouses	Denormalized tables containing wide schemas with embedded `JSON` objects merge cleanly.

Key Takeaways

Every INSERT creates a brand-new, immutable data part.
Background merges continuously combine small parts into larger, sorted parts to keep query performance fast.
Horizontal Merge processes all columns at the same time.
Vertical Merge maps out the row layout using the sorting key columns first, then handles remaining columns piece by piece.
ClickHouse intelligently and automatically picks the right merge strategy based on your schema size and system configurations.

Conclusion

Vertical Merge is an intelligent optimization within ClickHouse that improves the efficiency of background merge operations for wide tables. By separating the processing of sorting key columns from the remaining columns, ClickHouse significantly reduces memory consumption without affecting the correctness of the final merged data.

Although Vertical Merge works automatically behind the scenes, understanding its workflow helps database administrators and data engineers interpret merge behavior, troubleshoot performance issues, and design schemas that scale efficiently as datasets continue to grow.

Day 68 of #100DaysOfClickHouse: Benchmarking ClickHouse® – Tools and Methodologies for Reliable Performance Testing

Kanishga Subramani — Thu, 09 Jul 2026 11:04:45 +0000

Performance claims are everywhere.

One benchmark reports a query finishing in 20 milliseconds. Another claims millions of rows processed per second. Meanwhile, someone else publishes completely different numbers using what appears to be the same hardware.

The reality is that benchmarking ClickHouse® involves much more than running a query and measuring its execution time. Hardware, storage, dataset size, caching, query patterns, concurrency, and system configuration all influence the final results. Without a structured methodology, benchmark numbers can quickly become misleading.

Whether you're evaluating ClickHouse® for a new analytics platform, comparing infrastructure, or validating performance improvements after optimization, consistent benchmarking techniques are essential for producing meaningful and repeatable results.

In this article, we'll explore common benchmarking tools, discuss different benchmarking methodologies, and review best practices for measuring ClickHouse® performance with confidence.

Why Benchmarking Matters

Benchmarking provides objective data that helps answer important operational questions, such as:

Can our infrastructure handle the expected workload?
Did the latest optimization actually improve performance?
Is storage becoming a bottleneck?
Which hardware or configuration performs better?
How much concurrency can our cluster sustain?

Without benchmarking, performance tuning becomes guesswork.

Reliable benchmarks provide measurable evidence, allowing engineering teams to make informed decisions instead of relying on assumptions.

Define Your Benchmark Goals First

Before choosing a benchmarking tool, clearly define what you're trying to measure.

Different workloads require different metrics.

Query Latency

Query latency measures how quickly analytical queries complete.

This metric is particularly important for:

Interactive dashboards
Business Intelligence (BI) platforms
Ad-hoc analytics

Lower latency generally translates into a better user experience.

Throughput

Throughput measures how much work the system can process over a period of time.

This is especially useful when evaluating:

Enterprise reporting platforms
Multi-user analytics
Production workload simulations

High throughput ensures the system continues performing well under sustained demand.

Insert Performance

Many ClickHouse® deployments ingest data continuously.

Measuring insert performance is critical for:

Streaming pipelines
Kafka ingestion
ETL workflows
Log analytics platforms

A fast analytical database must also support efficient data ingestion.

Concurrent Workloads

Real production systems rarely execute a single query at a time.

Testing concurrent workloads helps determine how performance changes as multiple users submit queries simultaneously.

Concurrency testing provides a much more realistic picture of production behavior than isolated single-query benchmarks.

Choosing Representative Data

One of the most common benchmarking mistakes is using datasets that are too small.

ClickHouse® is designed for analytical workloads involving millions—or even billions—of rows.

Testing with only a few thousand records often produces unrealistic results because:

Everything fits into memory.
Compression behaves differently.
Storage isn't stressed.
Index efficiency is not representative.
Background merges have little impact.

Instead, benchmark using datasets that closely resemble your production environment.

Popular public datasets include:

UK Property Price Data
NYC Taxi Dataset
Stack Overflow
GitHub Events
Amazon Reviews
Wikimedia Pageviews

Large datasets expose realistic query patterns and produce far more meaningful benchmark results.

Popular Benchmarking Tools

Several tools are commonly used to benchmark ClickHouse®.

clickhouse-benchmark

The official benchmarking utility included with ClickHouse®.

It repeatedly executes SQL queries and reports metrics such as:

Queries per second
Average latency
Percentile latency
Throughput

Because it communicates directly with ClickHouse®, it is ideal for measuring SQL execution performance.

Example:

clickhouse-benchmark \
  --query="SELECT count() FROM events"

clickhouse-compressor

Although it doesn't benchmark SQL queries, clickhouse-compressor is useful for evaluating storage efficiency.

It measures:

Compression ratio
Compressed size
Compression speed
Decompression performance

Understanding compression performance helps optimize storage usage and query efficiency.

Apache JMeter

Apache JMeter is commonly used to simulate many concurrent users.

Typical use cases include:

REST APIs
HTTP interfaces
Authentication testing
Dashboard traffic

JMeter is well suited for evaluating performance under sustained concurrent load.

k6

k6 has become one of the most popular modern load-testing frameworks.

Its advantages include:

JavaScript-based scripting
Cloud execution support
HTTP benchmarking
CI/CD integration

For API-driven ClickHouse® workloads, k6 offers a flexible and developer-friendly testing experience.

sysbench

sysbench focuses primarily on benchmarking hardware rather than databases.

It can evaluate:

CPU performance
Storage performance
File I/O
Memory throughput

Since hardware significantly influences ClickHouse® performance, benchmarking infrastructure separately helps identify bottlenecks before evaluating database performance.

Cold Cache vs Warm Cache

Caching is one of the most overlooked factors in database benchmarking.

Warm Cache

In a warm cache scenario, frequently accessed data has already been loaded into memory.

Because disk reads are minimized, query execution is often significantly faster.

Cold Cache

A cold cache benchmark clears the operating system's file cache before testing.

This measures first-time query performance and better reflects workloads where data is not already cached.

Both scenarios are valuable because real production systems experience both cache hits and cache misses.

Whenever benchmark results are published, always specify whether they represent warm-cache or cold-cache performance.

Measure More Than Query Time

Execution time alone rarely tells the complete story.

A comprehensive benchmark should also monitor system resources, including:

CPU utilization
Memory usage
Disk throughput
Network bandwidth
Read amplification
Background merges
Compression efficiency

ClickHouse® exposes many of these metrics through its system tables, making it easier to correlate query performance with resource consumption.

Looking beyond query latency often reveals the real cause of performance bottlenecks.

Keep the Environment Consistent

Reliable benchmarking depends on repeatable conditions.

Maintain consistency across:

ClickHouse® version
Hardware
Storage type
Dataset
Query workload
Configuration settings
Number of execution threads

Changing multiple variables simultaneously makes it difficult to determine which factor actually influenced the results.

Consistency is essential for fair comparisons.

Avoid Common Benchmarking Mistakes

Many published benchmarks unintentionally produce misleading conclusions.

Common mistakes include:

Benchmarking tiny datasets
Measuring only cached queries
Ignoring concurrent workloads
Comparing different hardware configurations
Using different table schemas
Changing compression codecs
Reporting only average latency
Running too few benchmark iterations

Instead of relying solely on average latency, analyze percentile latency (P95, P99) and repeat each benchmark multiple times to reduce variability.

Automating Benchmarks

Performance testing should become part of your development workflow rather than an occasional task.

Many engineering teams integrate benchmarking into their CI/CD pipelines by automatically:

Loading representative datasets
Executing benchmark suites
Collecting performance metrics
Comparing previous benchmark results
Generating performance reports

Automated benchmarking helps detect regressions early and ensures that performance improvements remain consistent across software releases.

Final Thoughts

Benchmarking ClickHouse® isn't about producing impressive numbers—it's about generating reliable, reproducible measurements that reflect real-world workloads.

Defining clear objectives, selecting representative datasets, using appropriate benchmarking tools, and maintaining consistent testing conditions all contribute to trustworthy performance evaluations.

Looking beyond simple query execution time to include latency, throughput, concurrency, and resource utilization provides a much more complete understanding of system behavior.

As analytical workloads continue to grow, disciplined benchmarking becomes an essential engineering practice for validating optimizations, planning infrastructure, and ensuring that improvements translate successfully into production.

For teams operating ClickHouse® at scale, combining systematic benchmarking with strong operational visibility enables more confident tuning decisions, better capacity planning, and more predictable long-term performance.

Day 67 of #100DaysOfClickHouse: Securing ClickHouse® with SSL/TLS and LDAP Authentication

Kanishga Subramani — Thu, 09 Jul 2026 10:40:26 +0000

Security is often treated as something to configure after a ClickHouse® deployment is already serving queries. In practice, that approach leaves databases exposed to unnecessary risks such as unencrypted credentials, unauthorized access, and inconsistent user management.

ClickHouse® provides built-in capabilities to address these challenges:

SSL/TLS encrypts communication between clients and the server.
LDAP integration centralizes user authentication using an existing enterprise directory.

Although these technologies are often deployed together, they solve different problems. SSL/TLS protects data while it is being transmitted, whereas LDAP verifies whether a user is allowed to authenticate with the database.

In this article, you'll learn what each feature does, when to use it, and the practical considerations for deploying them securely in production.

Why Security Matters in ClickHouse®

ClickHouse® powers many business-critical analytical workloads, including:

Business Intelligence (BI) platforms
Customer analytics
Log analytics
Observability platforms
Financial reporting
Enterprise data warehouses

These systems frequently store customer information, operational metrics, financial records, and other sensitive business data.

Without proper security controls:

Login credentials may be intercepted.
Query traffic can be monitored.
Sensitive query results may be exposed.
Managing user accounts across multiple clusters becomes increasingly difficult.

A secure ClickHouse® deployment focuses on three key objectives:

Encrypt every network connection.
Authenticate users through a centralized identity provider.
Enforce least-privilege access using ClickHouse® roles and permissions.

Understanding SSL/TLS in ClickHouse®

SSL (Secure Sockets Layer) and its successor TLS (Transport Layer Security) encrypt communication between:

Applications
BI tools
ClickHouse® clients
ClickHouse® servers

Without encryption, information travels across the network in plain text.

Client
   │
   ├── Username
   ├── Password
   ├── SQL Queries
   └── Query Results

Anyone with access to the network could potentially inspect this traffic.

When TLS is enabled:

Client
   │
   └──── Encrypted Connection ───► ClickHouse® Server

Only the client and the server can decrypt the transmitted data.

What SSL/TLS Protects

TLS secures data in transit.

This includes:

Login credentials
SQL queries
Query results
HTTP API traffic
Native ClickHouse® protocol traffic
Inter-server communication (when configured)

However, TLS does not protect:

Data stored on disk
User permissions
Access control policies
Authentication logic

Those responsibilities are handled by other security mechanisms.

A Typical Production Deployment

A secure production architecture often resembles the following:

BI Tool
      │
   HTTPS / TLS
      │
 Application
      │
      TLS
      │
ClickHouse®

Every network connection is encrypted before user authentication begins.

Configuring TLS

Deploying TLS typically involves four steps:

Create or obtain server certificates.
Configure ClickHouse® to use those certificates.
Configure clients to trust the Certificate Authority (CA).
Verify encrypted connectivity.

For production environments, certificates issued by a trusted Certificate Authority or an organization's internal Public Key Infrastructure (PKI) are recommended.

Self-signed certificates are useful for development, testing, and learning, but they are generally not suitable for production deployments.

Client Certificate Authentication

ClickHouse® also supports authentication using X.509 client certificates.

Instead of transmitting passwords, the client presents a certificate during the TLS handshake.

Benefits include:

Passwordless authentication
Strong identity verification
Reduced credential management
Better integration with enterprise PKI environments

Client certificate authentication is supported through:

HTTPS interface
Native ClickHouse® protocol

It is not supported through:

gRPC interface
PostgreSQL compatibility interface
MySQL compatibility interface

Introducing LDAP

LDAP (Lightweight Directory Access Protocol) is a centralized directory service used by organizations to manage user identities.

Rather than creating separate user accounts for every application, organizations typically maintain identities in systems such as:

Microsoft Active Directory
OpenLDAP
Other LDAP-compatible directory services

Applications authenticate users against this central directory.

Why LDAP Is Useful

Imagine an organization with:

600 employees
25 internal applications
Multiple ClickHouse® clusters

Managing separate user accounts for every database quickly becomes difficult.

Using LDAP provides several advantages:

Employees keep a single corporate account.
Password policies remain centrally managed.
Disabling an employee's directory account immediately prevents future authentication.
Administrators avoid maintaining duplicate credentials across multiple systems.

How LDAP Works in ClickHouse®

ClickHouse® supports LDAP in two primary ways.

1. External Authentication

Local ClickHouse® users authenticate using an LDAP server instead of locally stored passwords.

User
   │
Username + Password
   │
ClickHouse®
   │
LDAP Server
   │
Authentication Result

ClickHouse® validates the supplied credentials against the LDAP directory before allowing access.

2. LDAP User Directory

ClickHouse® can also use LDAP as a remote user directory.

In this configuration, users do not need to be created locally inside ClickHouse®. Instead, user information is retrieved directly from LDAP during authentication.

This simplifies user administration for large organizations.

LDAP Security Considerations

LDAP authentication does not automatically encrypt network traffic.

If ClickHouse® communicates with an LDAP server over plain LDAP (ldap://), credentials travel without transport encryption.

For production deployments, secure communication should always be used.

ClickHouse® supports:

LDAPS (ldaps://)
StartTLS

The server configuration includes options for:

Enabling TLS
Minimum TLS protocol version
Certificate verification
Trusted CA certificates
Client certificates
Cipher suite selection

Using encrypted LDAP connections is the recommended production approach.

Plain LDAP should generally be limited to isolated development or testing environments.

SSL/TLS and LDAP Solve Different Problems

Although often deployed together, SSL/TLS and LDAP serve different purposes.

Feature	Purpose
SSL/TLS	Encrypts communication
LDAP	Authenticates user identity
ClickHouse® Roles	Controls authorization and permissions

A simplified security flow looks like this:

TLS
   │
Creates a secure connection
   │
LDAP
   │
Verifies user identity
   │
ClickHouse® Roles
   │
Determines what the user is allowed to access

Each layer complements the others to create a secure deployment.

Production Best Practices

When deploying ClickHouse® in production, consider the following recommendations:

Enable TLS for all client connections.
Use certificates issued by a trusted CA or enterprise PKI.
Always verify server certificates.
Use LDAPS or StartTLS for LDAP integration.
Never transmit credentials over unencrypted LDAP.
Apply the principle of least privilege using ClickHouse® roles.
Rotate certificates before they expire.
Monitor authentication failures and certificate expiration dates.
Test security configuration changes in non-production environments before rollout.

Common Misconceptions

"Enabling TLS secures the entire database."

Not entirely.

TLS protects network communication but does not replace:

Access control
Disk encryption
Backups
Auditing
User permissions

These controls remain essential for a secure deployment.

"LDAP replaces ClickHouse® permissions."

No.

LDAP is responsible for authentication.

Authorization—what a user is allowed to access—is still managed inside ClickHouse® through:

Roles
Privileges
Quotas
Row policies
Other access control mechanisms

"Self-signed certificates are sufficient for production."

Generally, no.

Self-signed certificates are appropriate for development and testing environments.

Production deployments should typically use certificates issued by a trusted Certificate Authority or an internal enterprise PKI so clients can verify the server's identity automatically.

Final Thoughts

Security in ClickHouse® is built through multiple complementary layers rather than a single feature.

SSL/TLS protects data while it travels across the network, reducing the risk of intercepted credentials and query traffic. LDAP simplifies identity management by allowing organizations to authenticate users through a centralized directory instead of maintaining separate credentials for every database server.

Neither technology replaces authorization. A secure ClickHouse® deployment combines encrypted communication, centralized authentication, and carefully managed roles and permissions to provide defense in depth.

By implementing SSL/TLS alongside LDAP and following security best practices, organizations can build ClickHouse® environments that are both secure and easier to manage as they scale.

Day 66/100 - ClickHouse Keeper vs ZooKeeper: Migration and Benchmarks

Kanishga Subramani — Wed, 08 Jul 2026 11:49:21 +0000

Introduction

Distributed ClickHouse® clusters rely on a coordination service to manage replication, distributed DDL execution, leader election, and metadata consistency across nodes. Without a reliable coordination layer, replicas can become inconsistent, distributed queries may fail, and cluster management becomes significantly more complex.

For many years, Apache ZooKeeper has served as the default coordination service for ClickHouse®. While ZooKeeper is proven and widely adopted across the big data ecosystem, it introduces additional infrastructure, operational complexity, and resource consumption because it must be deployed and maintained as a separate cluster.

To simplify distributed deployments, ClickHouse® introduced ClickHouse Keeper—a native coordination service built specifically for ClickHouse workloads. It implements the ZooKeeper protocol, allowing it to serve as a drop-in replacement while offering lower operational overhead and improved performance for ClickHouse clusters.

In this article, we'll compare ZooKeeper and ClickHouse Keeper, understand their architectural differences, walk through a migration process, and examine why Keeper has become the recommended choice for modern ClickHouse deployments.

What Is Apache ZooKeeper?

Apache ZooKeeper is an open-source distributed coordination service used by many large-scale distributed systems, including Kafka, Hadoop, HBase, and ClickHouse®.

Within ClickHouse®, ZooKeeper is responsible for coordinating operations across cluster nodes.

Its primary responsibilities include:

Tracking replicated table metadata
Coordinating replication between replicas
Executing distributed DDL queries
Maintaining cluster topology information
Supporting leader election
Synchronizing metadata changes across nodes

ZooKeeper provides reliable coordination, but it was designed as a general-purpose coordination system rather than one optimized specifically for ClickHouse.

Limitations of ZooKeeper

Although ZooKeeper has been the standard coordination service for years, it comes with several operational challenges.

Common limitations include:

Requires a dedicated cluster of at least three nodes for fault tolerance
Introduces additional infrastructure to manage
Consumes extra CPU and memory resources
Requires separate monitoring and maintenance
Adds operational complexity during upgrades
Can become a bottleneck in heavily replicated ClickHouse clusters
Requires administrators to manage another distributed system alongside ClickHouse®

As clusters grow larger, these operational costs become increasingly significant.

What Is ClickHouse Keeper?

ClickHouse Keeper is ClickHouse®'s native coordination service.

Rather than relying on an external ZooKeeper installation, Keeper is integrated directly into ClickHouse and is designed specifically for ClickHouse replication workloads.

Most importantly, Keeper implements the ZooKeeper protocol, making it compatible with existing ClickHouse replication mechanisms without requiring application changes.

Key characteristics include:

Native integration with ClickHouse®
ZooKeeper protocol compatibility
Uses the Raft consensus algorithm
Can run embedded inside ClickHouse servers
Can also run as a standalone service
Lower latency for ClickHouse coordination workloads
Reduced resource consumption
Simplified deployment and operations

For most new ClickHouse deployments, Keeper is now the recommended coordination service.

ZooKeeper vs ClickHouse Keeper

Feature	ZooKeeper	ClickHouse Keeper
Built into ClickHouse	❌	✅
Consensus Algorithm	ZAB	Raft
ZooKeeper Protocol Compatibility	Native	Compatible
Separate Installation Required	✅	❌
Operational Complexity	Higher	Lower
Resource Usage	Higher	Lower
Performance	Good	Optimized for ClickHouse
Recommended for New Deployments	❌	✅

Why Migrate to ClickHouse Keeper?

Migrating to Keeper provides several operational and performance benefits.

These include:

Simplified architecture
Lower infrastructure costs
Fewer servers to maintain
Reduced operational overhead
Faster startup and recovery
Lower CPU and memory usage
Native ClickHouse integration
Easier configuration management

Organizations deploying new ClickHouse clusters typically benefit from using Keeper unless they already depend heavily on an existing ZooKeeper ecosystem.

Typical Cluster Architecture

ZooKeeper-Based Deployment

ClickHouse Node 1 ──┐
ClickHouse Node 2 ──┤──── ZooKeeper Cluster
ClickHouse Node 3 ──┘

                    ├── ZooKeeper Node 1
                    ├── ZooKeeper Node 2
                    └── ZooKeeper Node 3

A ZooKeeper deployment requires an entirely separate coordination cluster that must be monitored, upgraded, and maintained independently.

ClickHouse Keeper Deployment

ClickHouse Node 1 (+ Keeper) ──┐
ClickHouse Node 2 (+ Keeper) ──┤── Keeper Raft Cluster
ClickHouse Node 3 (+ Keeper) ──┘

No external ZooKeeper installation is required.

The coordination service becomes part of the ClickHouse infrastructure itself.

Deploying ClickHouse Keeper

Keeper supports two deployment models.

Mode 1: Embedded Deployment

In embedded mode, Keeper runs inside the ClickHouse server process.

Example configuration:

<keeper_server>
    <tcp_port>9181</tcp_port>
    <server_id>1</server_id>

    <log_storage_path>
        /var/lib/clickhouse/coordination/log
    </log_storage_path>

    <snapshot_storage_path>
        /var/lib/clickhouse/coordination/snapshots
    </snapshot_storage_path>

    <coordination_settings>
        <operation_timeout_ms>10000</operation_timeout_ms>
        <session_timeout_ms>30000</session_timeout_ms>
        <raft_logs_level>warning</raft_logs_level>
    </coordination_settings>

    <raft_configuration>
        <server>
            <id>1</id>
            <hostname>clickhouse-node-1</hostname>
            <port>9234</port>
        </server>

        <server>
            <id>2</id>
            <hostname>clickhouse-node-2</hostname>
            <port>9234</port>
        </server>

        <server>
            <id>3</id>
            <hostname>clickhouse-node-3</hostname>
            <port>9234</port>
        </server>
    </raft_configuration>

</keeper_server>

This deployment is ideal for most production clusters because it eliminates the need for additional coordination servers.

Mode 2: Standalone Deployment

Keeper can also run as an independent service.

clickhouse-keeper --config /etc/clickhouse-keeper/config.xml

Standalone mode is often preferred in larger enterprise deployments where coordination services are managed independently from database nodes.

Migrating from ZooKeeper to ClickHouse Keeper

Migration can usually be completed with minimal disruption by following a structured process.

Step 1: Verify Replication Health

Before making any changes, ensure all replicas are synchronized.

SELECT
    database,
    table,
    is_readonly,
    queue_size
FROM system.replicas;

A queue_size of 0 indicates that replication is fully caught up.

Step 2: Configure Keeper

Add the Keeper configuration to config.xml on every ClickHouse node.

Each node must have:

A unique server_id
Correct Raft configuration
Shared cluster membership information

Step 3: Update Cluster Configuration

Replace ZooKeeper endpoints with Keeper endpoints.

Old configuration:

<zookeeper>
    <node>
        <host>zk1</host>
        <port>2181</port>
    </node>
</zookeeper>

New configuration:

<zookeeper>
    <node>
        <host>keeper1</host>
        <port>9181</port>
    </node>
</zookeeper>

Because Keeper implements the ZooKeeper protocol, most replication settings remain unchanged.

Step 4: Restart ClickHouse

Restart each ClickHouse server individually.

sudo systemctl restart clickhouse-server

Restarting one node at a time minimizes disruption and keeps the cluster available throughout the migration.

Step 5: Validate the Migration

Verify that replication remains healthy.

SELECT
    database,
    table,
    queue_size,
    is_session_expired
FROM system.replicas;

Healthy output should show:

queue_size = 0
is_session_expired = 0

This confirms successful migration.

Performance Comparison

The following table summarizes typical operational characteristics.

Metric	ZooKeeper	ClickHouse Keeper
Startup Time	Higher	Lower
Memory Usage	Higher	Lower
CPU Usage	Higher	Lower
Coordination Latency	Good	Lower
Deployment Complexity	High	Low
Maintenance Effort	High	Low
ClickHouse Integration	External	Native

In many production environments, Keeper also demonstrates:

Faster write coordination
Lower memory consumption
More consistent latency under heavy workloads
Faster replica synchronization
Faster recovery after failures

Actual performance varies depending on workload, hardware, cluster size, and configuration.

Common Migration Challenges

Administrators may encounter several issues during migration.

Common examples include:

Duplicate or incorrect server_id values
Missing Raft configuration
Firewall rules blocking ports 9181 or 9234
Unsynchronized replicas before migration
Version incompatibilities
Incorrect hostname resolution

Always verify replication health before redirecting production workloads to Keeper.

Best Practices

To ensure a smooth migration:

Schedule maintenance windows whenever possible.
Back up ZooKeeper metadata.
Verify replica synchronization before migration.
Restart one node at a time.
Monitor system.replicas continuously.
Validate distributed DDL execution.
Ensure compatible ClickHouse versions across the cluster.
Test failover after migration completes.

Choosing Between ZooKeeper and Keeper

Choose ZooKeeper if:

You already maintain a mature ZooKeeper infrastructure.
Multiple applications depend on ZooKeeper.
Operational processes are already built around ZooKeeper clusters.

Choose ClickHouse Keeper if:

You're deploying a new ClickHouse cluster.
You want a simpler architecture.
You want fewer infrastructure components.
Your coordination workload is primarily ClickHouse replication.
You want lower operational overhead.

For most modern ClickHouse deployments, Keeper is the preferred option.

Conclusion

ClickHouse Keeper simplifies distributed ClickHouse® deployments by replacing the need for a dedicated ZooKeeper cluster with a lightweight, native coordination service.

By implementing the ZooKeeper protocol while optimizing specifically for ClickHouse workloads, Keeper reduces infrastructure complexity, lowers operational costs, improves resource efficiency, and simplifies cluster administration.

Migrating from ZooKeeper is generally straightforward because existing replication configurations require minimal changes. By validating replication health, carefully updating cluster configurations, and following a phased migration process, organizations can transition safely with minimal downtime.

As ClickHouse continues to evolve, Keeper has become the recommended coordination service for most new deployments, offering a simpler, more efficient, and highly reliable foundation for managing distributed ClickHouse clusters.

Key Takeaways

ClickHouse® clusters require a coordination service for replication and distributed operations.
Apache ZooKeeper has traditionally provided this functionality but requires separate infrastructure.
ClickHouse Keeper is a native coordination service optimized specifically for ClickHouse.
Keeper implements the ZooKeeper protocol, making migration straightforward.
Embedded deployment simplifies infrastructure by eliminating external coordination servers.
Migration typically involves verifying replication, updating configuration, restarting nodes, and validating cluster health.
Keeper generally offers lower latency, lower resource usage, and reduced operational complexity.
For most new ClickHouse® deployments, ClickHouse Keeper is the recommended coordination service.

Day 65/100 - Implementing Exact-Once Semantics with ClickHouse® and Kafka

Kanishga Subramani — Wed, 08 Jul 2026 11:34:07 +0000

Introduction

Real-time data pipelines power modern applications such as analytics dashboards, fraud detection systems, IoT platforms, recommendation engines, financial systems, and event-driven applications. Organizations depend on these pipelines to process massive volumes of streaming data with minimal latency while maintaining high accuracy.

One of the biggest challenges in any streaming architecture is ensuring that every event is processed exactly once.

If the same event is processed multiple times, duplicate records can inflate business metrics, produce inaccurate analytics, trigger duplicate downstream actions, and reduce trust in data. Conversely, losing events results in incomplete datasets, missing insights, and unreliable reporting.

While Apache Kafka provides several mechanisms that greatly reduce duplicate message delivery, achieving true end-to-end exactly-once processing requires coordination between producers, consumers, and the destination database.

ClickHouse® provides powerful capabilities that help build highly reliable Kafka ingestion pipelines by minimizing duplicate records, handling retries safely, and maintaining data consistency at scale.

In this article, we'll explore exactly-once semantics, understand why duplicate records occur, and learn how to build a reliable Kafka-to-ClickHouse® ingestion pipeline.

What Is Exactly-Once Semantics?

Exactly-once semantics (EOS) is a processing guarantee that ensures every event is processed one and only one time, even when failures, retries, or system restarts occur during data ingestion.

In practical terms, exactly-once semantics means:

Every event is successfully processed exactly once.
Failed operations can be safely retried without creating duplicate records.
No valid events are unintentionally lost.
Downstream analytical results remain accurate and consistent.

This level of reliability is especially important for financial transactions, user activity tracking, inventory management, fraud detection, billing systems, and other applications where duplicate or missing data can have significant business consequences.

Why Do Duplicate Records Occur?

Streaming systems operate across distributed components where temporary failures are inevitable. Even when the application behaves correctly, duplicates can still appear because of retries and recovery operations.

Some of the most common causes include:

A consumer crashes after writing data to ClickHouse but before committing Kafka offsets.
Network interruptions occur during ingestion.
Producers retry sending messages after temporary failures.
Consumer applications restart and reprocess previously consumed messages.
Kafka topics are manually replayed during recovery or historical backfilling.
Temporary infrastructure failures interrupt normal processing.

Without proper deduplication mechanisms, these scenarios can result in multiple copies of the same event being stored in ClickHouse.

Kafka's Role in Exactly-Once Processing

Apache Kafka includes several features designed to improve the reliability of streaming applications.

Although these capabilities significantly reduce duplicate processing, they work best when combined with database-side safeguards.

Idempotent Producers

Idempotent producers assign sequence numbers to outgoing messages, allowing Kafka brokers to detect and discard duplicate writes caused by retries.

Benefits include:

Preventing duplicate messages during producer retries
Improving fault tolerance
Increasing delivery reliability
Eliminating accidental duplicate writes from the producer

Idempotent producers should be enabled for nearly all production streaming workloads.

Kafka Transactions

Kafka transactions allow multiple operations to be grouped into a single atomic unit of work.

For example, a consumer application can write multiple records while committing offsets within the same transaction.

This ensures that either:

Every operation succeeds, or
None of the operations are committed.

Transactions prevent partial writes that could otherwise produce inconsistent results across systems.

Offset Management

Consumer offsets determine which messages have already been processed.

To avoid data loss, offsets should only be committed after ClickHouse has successfully stored the corresponding records.

Proper offset management provides two important guarantees:

Failed insert operations can be safely retried.
Successfully processed events are not accidentally skipped.

Incorrect offset management is one of the most common reasons for duplicate processing in streaming applications.

Building a Kafka-to-ClickHouse® Pipeline

A typical real-time ingestion architecture consists of several components working together.

Kafka Producer
        │
        ▼
Apache Kafka Topic
        │
        ▼
ClickHouse Kafka Engine
        │
        ▼
Materialized View
        │
        ▼
MergeTree Table

Each component contributes to creating a scalable, reliable, and low-latency streaming pipeline.

Using the ClickHouse Kafka Engine

ClickHouse provides the Kafka Engine, allowing the database to consume Kafka topics directly without requiring a separate ingestion application.

Example:

CREATE TABLE kafka_events
(
    event_id UUID,
    user_id UInt64,
    event_type String,
    event_time DateTime
)
ENGINE = Kafka
SETTINGS
    kafka_broker_list = 'localhost:9092',
    kafka_topic_list = 'events',
    kafka_group_name = 'clickhouse-consumer',
    kafka_format = 'JSONEachRow';

The Kafka Engine continuously polls Kafka topics and exposes incoming messages as rows inside ClickHouse.

This greatly simplifies streaming architectures because ClickHouse becomes a native Kafka consumer.

Automating Ingestion with Materialized Views

After creating the Kafka Engine table, a Materialized View can automatically move incoming events into a persistent MergeTree table.

Example:

CREATE MATERIALIZED VIEW mv_events
TO events
AS
SELECT *
FROM kafka_events;

As new messages arrive, the Materialized View continuously inserts them into the destination table without requiring custom ingestion code.

This enables near real-time analytics with very little operational complexity.

Preventing Duplicate Records

Although Kafka minimizes duplicate message delivery, duplicate records may still occur because of retries, consumer failures, or replay operations.

ClickHouse provides several techniques to reduce or eliminate duplicates.

1. Use Globally Unique Event IDs

Every event should include a globally unique identifier.

Examples include:

UUID
Transaction ID
Order ID
Payment ID
Event ID

Example:

{
  "event_id": "3d9d34be-3f12-4c89-aef2-d53e5faad0c",
  "user_id": 42,
  "event_type": "purchase"
}

A unique event identifier makes it possible to recognize duplicate events regardless of how many times they are retried.

This is one of the most important building blocks for implementing exactly-once processing.

2. Store Data Using ReplacingMergeTree

One of the most commonly used ClickHouse table engines for retry-safe ingestion is ReplacingMergeTree.

Example:

CREATE TABLE events
(
    event_id UUID,
    user_id UInt64,
    event_time DateTime,
    version UInt64
)
ENGINE = ReplacingMergeTree(version)
ORDER BY event_id;

When multiple rows share the same primary key (event_id), ClickHouse keeps the row with the highest version value during background merge operations.

This makes ReplacingMergeTree particularly useful for:

Retry scenarios
Event updates
Slowly changing dimensions
Idempotent ingestion pipelines

It is important to note that duplicate removal does not happen immediately after insertion. Instead, deduplication occurs gradually during background merges.

3. Enable Insert Deduplication

For replicated tables, ClickHouse supports insert deduplication.

Example:

SET insert_deduplicate = 1;

When enabled, ClickHouse compares insert block checksums.

If an identical insert block has already been processed, ClickHouse ignores the duplicate instead of inserting it again.

This provides additional protection against duplicate inserts caused by retries or temporary infrastructure failures.

Best Practices for Reliable Kafka-to-ClickHouse® Pipelines

To build robust streaming pipelines, consider the following recommendations:

Enable Kafka idempotent producers.
Use Kafka transactions whenever appropriate.
Generate globally unique event IDs for every message.
Commit Kafka consumer offsets only after successful ClickHouse inserts.
Use ReplacingMergeTree for retry-safe ingestion.
Enable insert deduplication for replicated tables.
Design tables with stable primary keys.
Continuously monitor Kafka consumer lag.
Monitor ingestion failures and retry behavior.
Regularly test recovery scenarios, including consumer restarts and Kafka topic replays.
Validate data consistency after infrastructure failures.
Build monitoring and alerting around ingestion pipelines.

Following these practices significantly improves data reliability and minimizes operational issues in production environments.

Limitations

Although these techniques greatly improve reliability, exactly-once semantics is not achieved automatically.

There are several important limitations to understand.

ReplacingMergeTree performs deduplication during background merge operations rather than immediately after insertion. As a result, duplicate records may temporarily exist until merges complete.

Applications are still responsible for managing Kafka offsets correctly. Incorrect offset commits can lead to either duplicate processing or lost events.

Replaying Kafka topics without globally unique event identifiers can still introduce duplicate records.

Finally, achieving true end-to-end exactly-once behavior depends on the combined implementation of Kafka producers, Kafka consumers, ClickHouse, and any additional processing components within the streaming pipeline.

Exactly-once semantics should therefore be viewed as a system-wide design goal rather than a feature provided by a single technology.

Conclusion

Building reliable real-time data pipelines involves much more than simply moving events from Kafka into ClickHouse®.

A robust streaming architecture combines Kafka's idempotent producers, transactions, and careful offset management with ClickHouse's Kafka Engine, Materialized Views, unique event identifiers, ReplacingMergeTree, and insert deduplication capabilities.

Together, these features allow ingestion pipelines to gracefully handle retries, consumer restarts, infrastructure failures, and replay operations while maintaining accurate analytical results.

Although achieving true end-to-end exactly-once semantics requires coordination across every stage of the pipeline, following these best practices provides a strong foundation for building scalable, fault-tolerant, and duplicate-resistant real-time analytics systems with ClickHouse® and Kafka.

Key Takeaways

Exactly-once semantics ensures every event is processed only once, even during retries and failures.
Duplicate records commonly result from consumer crashes, retries, network interruptions, and replay operations.
Kafka improves reliability through idempotent producers, transactions, and proper offset management.
The ClickHouse Kafka Engine enables direct streaming ingestion from Kafka topics.
Materialized Views automate continuous ingestion into MergeTree tables.
Globally unique event IDs are essential for reliable deduplication.
ReplacingMergeTree minimizes duplicate records by keeping the latest version of each event during background merges.
Insert deduplication adds another layer of protection against repeated insert operations.
Correct offset management is critical for preventing both duplicate processing and data loss.
Combining Kafka and ClickHouse® best practices enables reliable, scalable, and duplicate-resistant real-time analytics pipelines.