DEV Community: Kanishga Subramani

Day 30 of 100 Days of ClickHouse® - Using ClickHouse® Arrays for Complex Data Structures

Kanishga Subramani — Mon, 22 Jun 2026 17:12:04 +0000

Introduction

Modern analytical workloads often involve working with multi-valued and semi-structured data. Traditional relational database designs typically represent these relationships using separate tables connected through joins. While this approach works well, it can increase schema complexity and slow down analytical queries as datasets grow.

ClickHouse® provides a powerful Array data type that allows multiple values to be stored within a single column. Combined with a rich collection of built-in array functions, arrays make it possible to efficiently store, query, and transform complex datasets while maintaining the high-performance analytics that ClickHouse® is known for.

In this article, you'll learn how to create tables with array columns, insert and query array data, explore commonly used array functions, and understand real-world scenarios where arrays simplify data modeling and improve performance.

Why Use Arrays in ClickHouse®?

Arrays allow you to store multiple related values inside a single column instead of spreading them across multiple rows or tables.

This approach offers several advantages:

Reduces the need for joins
Simplifies database schema design
Improves query performance
Efficiently stores lists, tags, events, and user activities
Supports a wide range of optimized built-in array functions

Arrays are widely used in applications such as:

Website analytics
User behavior tracking
IoT monitoring
Recommendation systems
Product catalogs
Event logging

Understanding the Array Data Type

An Array in ClickHouse® is a collection of values of the same data type stored together in a single column.

The general syntax is:

Array(DataType)

Examples include:

Array(String)

Array(Int32)

Array(Float64)

Arrays are commonly used for storing:

Product lists
User interests
Website page visits
Sensor readings
Event sequences
Tags and categories

Instead of storing product names across multiple rows, they can be stored together in a single array:

['Laptop', 'Mouse', 'Keyboard']

This keeps related information together and often makes analytical queries much simpler.

Creating a Table with Array Columns

Let's create a table that stores each user's purchase history.

CREATE TABLE user_purchases
(
    user_id UInt32,
    purchased_items Array(String)
)
ENGINE = MergeTree
ORDER BY user_id;

Table Structure

Column	Description
user_id	Unique user identifier
purchased_items	Array containing purchased products

This design works well because all purchased items naturally belong to the same user.

Inserting Array Data

Insert a single record:

INSERT INTO user_purchases VALUES
(
    1,
    ['Laptop', 'Mouse', 'Keyboard']
);

Insert multiple records:

INSERT INTO user_purchases VALUES
(2, ['Monitor', 'Webcam']),
(3, ['SSD', 'RAM', 'CPU']);

Querying Array Data

Retrieve all records:

SELECT *
FROM user_purchases;

Example output:

user_id	purchased_items
1	['Laptop','Mouse','Keyboard']
2	['Monitor','Webcam']
3	['SSD','RAM','CPU']

Accessing Individual Array Elements

ClickHouse® arrays use 1-based indexing, meaning the first element starts at index 1.

Retrieve the first purchased item:

SELECT
    purchased_items[1] AS first_item
FROM user_purchases;

Output:

Laptop

You can access any position using the corresponding index.

Common Array Functions

ClickHouse® includes many optimized functions for working with arrays.

1. arrayJoin()

The arrayJoin() function expands every array element into a separate row.

Example:

SELECT
    user_id,
    arrayJoin(purchased_items) AS item
FROM user_purchases;

Output:

user_id	item
1	Laptop
1	Mouse
1	Keyboard

Common Use Cases

Product analytics
Event tracking
Aggregations
Reporting
Visualization

2. arrayMap()

arrayMap() applies a transformation to every element in an array.

Example:

SELECT
    arrayMap(x -> upperUTF8(x), purchased_items)
FROM user_purchases;

Output:

['LAPTOP','MOUSE','KEYBOARD']

Common Use Cases

Data transformation
String formatting
Mathematical operations
Data standardization

3. arrayFilter()

arrayFilter() returns only the elements that satisfy a condition.

Example:

SELECT
    arrayFilter(x -> x LIKE '%o%', purchased_items)
FROM user_purchases;

Output:

['Laptop','Mouse']

Common Use Cases

Product filtering
Event selection
Preference analysis
Data cleansing

4. arrayExists()

arrayExists() checks whether at least one element satisfies a condition.

Example:

SELECT
    arrayExists(x -> x = 'Mouse', purchased_items)
FROM user_purchases;

Output:

Where:

1 = True
0 = False

Common Use Cases

Membership testing
Product existence checks
User activity validation

5. arrayDistinct()

arrayDistinct() removes duplicate values.

Example:

SELECT
    arrayDistinct(
        ['Laptop','Mouse','Laptop','Keyboard']
    );

Output:

['Laptop','Mouse','Keyboard']

Common Use Cases

Removing duplicates
Data cleaning
Extracting unique values

6. arrayCount()

arrayCount() counts elements matching a condition.

Example:

SELECT
    arrayCount(x -> x > 10, [5,15,20,8]);

Output:

Only 15 and 20 satisfy the condition.

Common Use Cases

Threshold monitoring
User activity analysis
Counting qualifying values
Analytical calculations

Working with Related Arrays

Arrays become even more useful when multiple arrays represent related information.

Consider website analytics:

CREATE TABLE website_sessions
(
    session_id UInt64,
    pages Array(String),
    durations Array(UInt32)
)
ENGINE = MergeTree
ORDER BY session_id;

Insert sample data:

INSERT INTO website_sessions VALUES
(
    1,
    ['/home','/products','/checkout'],
    [10,25,15]
);

This represents:

Page	Duration (Seconds)
/home	10
/products	25
/checkout	15

Keeping these arrays together preserves both sequence and context, making session analysis much easier.

Common applications include:

Website navigation tracking
User journey analysis
IoT sensor monitoring
Event stream processing
Application monitoring

Performance Considerations

To get the best performance when using arrays:

Keep arrays reasonably sized.
Avoid storing thousands of elements in a single row unless necessary.
Use optimized functions such as arrayMap(), arrayFilter(), and arrayExists().
Minimize the use of arrayJoin() since it expands rows and can significantly increase query processing.
Store related values together when they are frequently queried.
Benchmark queries on production-like datasets before deployment.

Following these recommendations helps maintain ClickHouse®'s excellent analytical performance.

Real-World Use Cases

E-Commerce Analytics

Store purchased products in a transaction.

['Laptop','Mouse','Keyboard']

Benefits:

Simplified schema
Fewer joins
Faster analysis

User Activity Tracking

Track user actions during a session.

['Login','Search','AddToCart','Checkout']

Useful for:

Customer journey analysis
Behavioral analytics
Funnel analysis

Website Navigation Analysis

Store page visit sequences.

['Home','Products','Pricing','Contact']

Useful for:

Clickstream analysis
Conversion optimization
Navigation insights

IoT Sensor Monitoring

Store multiple sensor readings.

[23.1, 23.4, 22.9, 24.0]

Useful for:

Time-series analytics
Monitoring systems
Device telemetry

Recommendation Systems

Store user interests.

['Technology','Programming','AI']

Useful for:

Personalized recommendations
Content suggestions
Product recommendations

Best Practices

When working with arrays:

Use arrays only for naturally grouped data.
Avoid extremely large arrays.
Prefer built-in array functions over custom processing.
Avoid excessive nesting of arrays.
Monitor query performance using ClickHouse® system tables and query logs.
Document array structures, including:
- Element ordering
- Data types
- Business meaning
- Expected size limits

Good documentation helps maintain consistency as applications evolve.

Conclusion

Arrays are one of the most powerful data types available in ClickHouse®. They provide an efficient way to store and analyze multi-valued data without relying heavily on additional tables or complex joins.

Functions such as arrayJoin(), arrayMap(), arrayFilter(), arrayExists(), arrayDistinct(), and arrayCount() make it easy to manipulate and analyze array data while taking advantage of ClickHouse®'s optimized execution engine.

Whether you're building analytics for e-commerce platforms, monitoring IoT devices, tracking website sessions, or powering recommendation systems, arrays help create cleaner schemas, simplify queries, and deliver fast analytical performance.

Understanding when and how to use arrays effectively will allow you to model complex datasets more naturally while getting the most out of ClickHouse®'s high-performance architecture.

Day 29 of 100 Days of ClickHouse® - Understanding Distributed Tables

Kanishga Subramani — Sun, 21 Jun 2026 16:09:42 +0000

Introduction

As datasets grow, a single ClickHouse® server may eventually become insufficient for handling storage, ingestion, or query workloads.

At that point, scaling horizontally becomes necessary.

This is where Distributed tables come into play.

Distributed tables provide a unified way to query data spread across multiple servers while maintaining a familiar SQL experience. Instead of worrying about which node contains specific data, users interact with a single logical table while ClickHouse® handles query routing and result aggregation behind the scenes.

In this article, we'll explore what Distributed tables are, how they work, when to use them, and the architectural concepts every ClickHouse® user should understand.

Why Distributed Tables Exist

A single ClickHouse® server is capable of processing billions of rows efficiently. However, as applications grow, organizations often encounter requirements such as:

More storage capacity
Higher ingestion throughput
Increased query processing power
High availability
Geographic distribution

Rather than continually upgrading a single machine, it's often more practical to distribute data across multiple servers.

The challenge then becomes simple:

How can users query data spread across several servers without manually connecting to each one?

Distributed tables solve this problem by providing a single logical entry point for the entire cluster.

Understanding the Distributed Engine

The Distributed engine acts as a logical layer over existing local tables.

Unlike MergeTree engines, a Distributed table does not store any data.

Instead, it routes queries and inserts to the appropriate servers.

Example:

CREATE TABLE events_distributed
AS events_local
ENGINE = Distributed(
    analytics_cluster,
    analytics,
    events_local,
    cityHash64(user_id)
);

Here:

analytics_cluster is the cluster configuration defined in ClickHouse.
analytics is the database name.
events_local is the underlying local table.
cityHash64(user_id) is the sharding key.

The Distributed table itself contains no rows. All data remains inside the local MergeTree tables on each server.

Local Tables vs Distributed Tables

This distinction is one of the most important concepts to understand.

Suppose you have three shards.

Shard 1
└── events_local

Shard 2
└── events_local

Shard 3
└── events_local

Each server stores its own portion of the data inside its local table.

Above them sits a Distributed table.

events_distributed
        │
 ┌──────┼──────┐
 │      │      │
Shard1 Shard2 Shard3

Applications query the Distributed table:

SELECT *
FROM events_distributed;

ClickHouse automatically sends the query to the relevant shards, collects the partial results, merges them, and returns a single response.

From the application's perspective, it behaves like querying a normal table.

Understanding Sharding

Distributed tables are typically used together with sharding.

Sharding means splitting data across multiple servers.

For example:

User 1 → Shard 1
User 2 → Shard 2
User 3 → Shard 3

A sharding key determines where each row is stored.

Example:

cityHash64(user_id)

A good sharding key helps:

Balance storage across servers
Distribute query workloads
Improve scalability
Prevent bottlenecks
Keep related data together

Choosing the right sharding strategy is one of the most important architectural decisions in a distributed ClickHouse deployment.

How Distributed Queries Work

Suppose a user runs:

SELECT
    country,
    count()
FROM events_distributed
GROUP BY country;

ClickHouse performs the following steps:

Sends the query to each participating shard.
Executes the query locally on every shard.
Collects the partial aggregation results.
Merges the results.
Returns the final output.

Because multiple servers process the query simultaneously, distributed queries can scale efficiently as data grows.

Distributed Inserts

Distributed tables can also receive inserts.

Example:

INSERT INTO events_distributed
VALUES (...);

The Distributed engine evaluates the sharding key and automatically forwards each row to the appropriate shard.

Applications don't need to know where data belongs, making distributed ingestion much simpler.

Choosing the Right Sharding Key

Although a Distributed table can be created without a meaningful sharding strategy, doing so often leads to problems such as:

Uneven data distribution
Hot shards
Storage imbalance
Poor query performance
Limited scalability

Common sharding keys include:

user_id
customer_id
tenant_id
organization_id

The best choice depends on how your application queries data and how evenly values are distributed.

Distributed Tables vs Replication

These concepts are frequently confused, but they solve different problems.

Sharding

Sharding distributes data across multiple servers.

Goal: Scale horizontally.

Replication

Replication creates multiple copies of the same data.

Goal: High availability and fault tolerance.

A production cluster commonly combines both.

Shard 1
├── Replica A
└── Replica B

Shard 2
├── Replica A
└── Replica B

Distributed tables provide a single interface that allows applications to query the entire cluster regardless of where the data is stored.

Query Optimization Tips

Distributed queries involve network communication between nodes.

To achieve the best performance:

Filter data as early as possible.
Choose efficient sorting keys.
Minimize unnecessary data movement.
Design effective sharding keys.
Avoid excessive cross-shard operations.

The less data transferred across the network, the faster distributed queries become.

Common Mistakes

Assuming Distributed Tables Store Data

They don't.

The data always resides in local MergeTree tables.

Choosing a Poor Sharding Key

An uneven sharding strategy can overload specific nodes while others remain underutilized.

Ignoring Query Patterns

Your sharding strategy should align with how the application queries data.

Confusing Replication with Distribution

Replication improves availability.

Distribution improves scalability.

Both are important, but they serve different purposes.

Example Architecture

A typical ClickHouse deployment may look like this:

analytics_cluster

Shard 1
├── events_local
└── Replica

Shard 2
├── events_local
└── Replica

events_distributed

Applications interact only with:

events_distributed

ClickHouse automatically handles query routing, distributed execution, and result aggregation.

When Should You Use Distributed Tables?

Distributed tables are useful when:

Your data no longer fits comfortably on a single server.
You need horizontal scalability.
Your cluster contains multiple shards.
Applications require a unified SQL interface.
Large analytical workloads need to be distributed across several nodes.

For single-node deployments, Distributed tables usually aren't necessary.

Best Practices

When designing distributed ClickHouse clusters:

Choose sharding keys carefully.
Understand your query patterns before distributing data.
Keep local table schemas identical across all nodes.
Minimize cross-shard communication whenever possible.
Combine sharding with replication for both scalability and availability.
Monitor cluster health regularly.
Validate data distribution before moving to production.

Final Thoughts

Distributed tables are one of the core building blocks that enable ClickHouse® to scale beyond a single server.

Rather than storing data themselves, they act as a routing layer that allows applications to query and insert data across multiple shards using a single SQL interface.

Understanding the relationship between local tables, Distributed tables, sharding, and replication is essential for designing efficient ClickHouse clusters.

With a well-planned sharding strategy and thoughtful cluster design, Distributed tables make it possible to scale analytical workloads across multiple servers while keeping the SQL experience simple and familiar.

Day 28 of 100 Days of ClickHouse® – Understanding TTL (Time To Live) for Automated Data Lifecycle Management

Kanishga Subramani — Sat, 20 Jun 2026 14:51:59 +0000

Introduction

As data grows over time, storing every row forever becomes increasingly expensive and often unnecessary. Application logs, IoT events, monitoring metrics, audit records, and clickstream data typically have different retention requirements depending on their age.

ClickHouse® provides TTL (Time To Live), a powerful built-in feature that automates data lifecycle management. Instead of relying on scheduled cleanup scripts or manual DELETE operations, TTL rules can be defined directly within a table schema.

With TTL, ClickHouse can automatically:

Delete expired data
Move older data to lower-cost storage
Recompress aging data using stronger codecs
Aggregate historical records into summarized data

These capabilities make TTL one of the most effective storage optimization features available in ClickHouse.

What is TTL?

TTL (Time To Live) defines what should happen to data after a specified period of time.

A TTL expression is usually based on a Date or DateTime column.

TTL event_time + INTERVAL 30 DAY

After the specified interval expires, ClickHouse performs the configured action during background merge operations.

Unlike scheduled jobs, TTL is completely integrated into the MergeTree engine, making lifecycle management automatic and maintenance-free.

How TTL Works Internally

When new data is inserted into a MergeTree table, the following process occurs:

Data is written into immutable parts.
Background merge operations combine smaller parts into larger ones.
During each merge, ClickHouse evaluates TTL expressions.
Expired rows are deleted, moved, recompressed, or aggregated depending on the configured rule.

Because TTL works during merges:

Expired data may remain visible for a short period.
Cleanup is asynchronous.
Execution depends on the merge schedule.

For testing purposes, you can force a merge using:

OPTIMIZE TABLE table_name FINAL;

1. Automatically Deleting Expired Data

The most common TTL use case is automatic data deletion.

CREATE TABLE logs
(
    timestamp DateTime,
    message String
)
ENGINE = MergeTree
ORDER BY timestamp
TTL timestamp + INTERVAL 30 DAY;

What happens?

Newly inserted data remains available.
After 30 days, rows become eligible for removal.
During future background merges, ClickHouse automatically deletes expired rows.

This eliminates the need for cron jobs or periodic cleanup scripts while reducing storage usage automatically.

2. Column-Level TTL

TTL isn't limited to entire rows. It can also be applied to individual columns.

CREATE TABLE user_events
(
    event_time DateTime,
    user_id UInt64,
    session_data String TTL event_time + INTERVAL 7 DAY
)
ENGINE = MergeTree
ORDER BY event_time;

Result

After seven days:

session_data is removed.
The remaining columns stay intact.

This is particularly useful when temporary or sensitive information only needs to be retained for a limited time.

3. Moving Data to Cold Storage

Many organizations use multiple storage tiers.

Storage Tier	Purpose
SSD	Frequently accessed recent data
HDD / Object Storage	Historical or infrequently accessed data

TTL can automatically move older data between storage volumes.

CREATE TABLE logs
(
    timestamp DateTime,
    message String
)
ENGINE = MergeTree
ORDER BY timestamp
TTL timestamp + INTERVAL 90 DAY
TO VOLUME 'cold_storage';

Result

Recent data remains on fast storage.
Older data is moved to lower-cost storage.

This helps reduce infrastructure costs while keeping historical data available when needed.

4. Recompressing Older Data

Recent data is queried frequently and benefits from fast compression codecs such as LZ4.

Historical data is accessed less often, making stronger compression more efficient.

TTL supports automatic recompression.

CREATE TABLE events
(
    event_time DateTime,
    user_id UInt64,
    value Float64
)
ENGINE = MergeTree
ORDER BY event_time
TTL event_time + INTERVAL 30 DAY
RECOMPRESS CODEC(ZSTD(17));

Compression lifecycle

Data Age	Compression
0–30 Days	Default (LZ4)
After 30 Days	ZSTD(17)

Benefits

Lower storage consumption
Better disk utilization
Fully automated optimization
No manual recompression required

5. Aggregating Historical Data with GROUP BY TTL

One of the most powerful TTL capabilities is automatic rollup aggregation.

Instead of deleting old data, ClickHouse can summarize historical records into aggregated values.

This is particularly useful for:

Monitoring systems
IoT platforms
Clickstream analytics
Time-series databases

Example

CREATE TABLE hits
(
    timestamp DateTime,
    id String,
    hits Int32,
    max_hits Int32 DEFAULT hits,
    sum_hits Int64 DEFAULT hits
)
ENGINE = MergeTree
PRIMARY KEY (id, toStartOfDay(timestamp), timestamp)
TTL timestamp + INTERVAL 1 DAY
GROUP BY
    id,
    toStartOfDay(timestamp)
SET
    max_hits = max(max_hits),
    sum_hits = sum(sum_hits);

Before Rollup

Timestamp	ID	Hits
10:00	A	5
11:00	A	8
12:00	A	12

After one day, the TTL rule executes:

TTL timestamp + INTERVAL 1 DAY
GROUP BY
    id,
    toStartOfDay(timestamp)
SET
    max_hits = max(max_hits),
    sum_hits = sum(sum_hits);

After Rollup

Day	ID	Max Hits	Sum Hits
2026-06-20	A	12	25

Instead of storing three separate rows, ClickHouse stores one summarized row.

This significantly reduces storage while preserving meaningful historical insights.

Best Practices for Using TTL

Use TTL for predictable retention policies instead of scheduled DELETE statements.
Remember that TTL actions occur during background merges, not immediately after expiration.
Apply column-level TTL when only specific fields need limited retention.
Use storage tiering to reduce infrastructure costs without losing historical data.
Combine TTL with recompression to maximize disk efficiency.
Consider GROUP BY TTL for time-series workloads where summarized historical data is sufficient.
Use OPTIMIZE TABLE ... FINAL only for testing, not as a regular maintenance task.

Conclusion

TTL is one of ClickHouse's most valuable automation features for managing data throughout its lifecycle. With a single declarative rule, you can automatically delete expired data, move historical records to lower-cost storage, apply stronger compression to older data, or aggregate detailed records into summarized insights.

By leveraging TTL effectively, organizations can reduce storage costs, simplify maintenance, improve long-term performance, and build scalable analytical systems without relying on external cleanup jobs or manual intervention.

Whether you're managing log data, monitoring metrics, IoT telemetry, or large-scale analytical workloads, TTL provides a clean and efficient approach to implementing automated data retention policies in ClickHouse.

Day 27 of 100 Days of ClickHouse® - Optimizing ClickHouse® Queries for Faster Execution

Kanishga Subramani — Sat, 20 Jun 2026 11:19:10 +0000

Introduction

As datasets grow larger and analytical workloads become more demanding, query performance becomes increasingly important. While ClickHouse® is built for lightning-fast analytical processing, poorly optimized schemas and inefficient queries can still waste CPU cycles, consume excessive memory, and increase execution times.

The good news is that ClickHouse® provides numerous features to help you optimize both your data model and query patterns. From choosing the right ORDER BY key to using skip indexes, projections, materialized views, and efficient filtering techniques, a few design decisions can dramatically improve performance.

In this article, we'll explore practical techniques for optimizing ClickHouse® queries so your analytical workloads remain fast and scalable—even when working with billions of rows.

Why Query Optimization Matters

ClickHouse® is a column-oriented analytical database that processes data differently from traditional row-based databases.

It achieves exceptional performance through:

Columnar storage
Vectorized query execution
Parallel processing
Data compression
Sparse primary indexes
Intelligent data skipping

However, these optimizations are only effective when tables are designed correctly and queries take advantage of them.

Poorly optimized queries typically result in:

Increased query latency
Higher CPU utilization
Excessive memory consumption
Longer dashboard loading times
Reduced throughput
Higher infrastructure costs

Understanding how ClickHouse® reads and skips data is the foundation of writing efficient queries.

1. Choose a Good ORDER BY Key

The ORDER BY clause in MergeTree tables defines the physical sort order of data on disk.

Unlike many databases, this is far more than just a sorting preference—it determines how efficiently ClickHouse® can locate data using its sparse primary index.

A well-designed ORDER BY key allows ClickHouse® to skip large portions of data during query execution.

CREATE TABLE events
(
    user_id UInt32,
    event_type LowCardinality(String),
    timestamp DateTime,
    value Float64
)
ENGINE = MergeTree()
ORDER BY (user_id, event_type, timestamp);

Now queries filtering on these columns become extremely efficient.

SELECT avg(value)
FROM events
WHERE user_id = 1001
  AND event_type = 'purchase'
  AND timestamp >= now() - INTERVAL 7 DAY;

Best Practices

Put frequently filtered columns first.
Place high-selectivity columns earlier.
Avoid random or highly unique first columns unless appropriate.
Design ORDER BY around your most common queries.

2. Partition Large Tables

Partitioning divides data into separate physical parts.

When a query filters on the partition key, ClickHouse® skips entire partitions without reading them.

CREATE TABLE events
(
    user_id UInt32,
    event_type LowCardinality(String),
    timestamp DateTime,
    value Float64
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(timestamp)
ORDER BY (user_id, event_type, timestamp);

Query:

SELECT count()
FROM events
WHERE timestamp >= now() - INTERVAL 30 DAY;

Instead of scanning years of data, ClickHouse® only reads recent monthly partitions.

Common Partition Keys

toYYYYMM(timestamp)
toYYYYMMDD(timestamp)
Region
Tenant ID (for multi-tenant systems)

Avoid over-partitioning, as too many small partitions can negatively impact performance.

3. Avoid SELECT *

ClickHouse® only reads the columns referenced in your query.

Using SELECT * forces the database to read and decompress every column, increasing I/O and CPU usage.

Slow:

SELECT *
FROM events
WHERE user_id = 1001;

Better:

SELECT
    user_id,
    event_type,
    value
FROM events
WHERE user_id = 1001;

Always retrieve only the columns your application actually needs.

4. Use LowCardinality for String Columns

Columns with relatively few unique values should use LowCardinality.

Examples include:

status
country
region
event_type
device_type

Instead of:

event_type String

Use:

event_type LowCardinality(String)

Benefits include:

Smaller storage
Better compression
Faster filtering
Faster GROUP BY
Reduced memory usage

As a rule of thumb, use LowCardinality for string columns with fewer than roughly 10,000 distinct values.

5. Filter on ORDER BY Columns First

ClickHouse® can only use its sparse primary index efficiently when filtering on the leading columns of the ORDER BY key.

Given:

ORDER BY (user_id, event_type, timestamp)

Fast:

SELECT count()
FROM events
WHERE user_id = 1001
  AND event_type = 'purchase';

Slow:

SELECT count()
FROM events
WHERE value > 100;

If you frequently filter on non-indexed columns, consider adding a skip index.

6. Use Skip Indexes

Skip indexes help ClickHouse® skip granules when filtering on columns outside the primary key.

ALTER TABLE events
ADD INDEX idx_value value TYPE minmax GRANULARITY 4;

Materialize it:

ALTER TABLE events
MATERIALIZE INDEX idx_value;

Now:

SELECT count()
FROM events
WHERE value > 500;

ClickHouse® skips granules whose minimum and maximum values fall outside the requested range.

Common skip index types include:

minmax
set
bloom_filter
tokenbf_v1

7. Avoid Functions on Filtered Columns

Applying functions to indexed columns prevents ClickHouse® from using the primary index efficiently.

Avoid:

SELECT count()
FROM events
WHERE toYear(timestamp) = 2024;

Instead use range filters:

SELECT count()
FROM events
WHERE timestamp >= '2024-01-01'
  AND timestamp < '2025-01-01';

Range conditions allow ClickHouse® to leverage index skipping effectively.

8. Use PREWHERE

PREWHERE is a ClickHouse® optimization that filters rows before loading all requested columns.

SELECT
    user_id,
    value
FROM events
PREWHERE value > 100
WHERE user_id = 1001;

Benefits:

Reads fewer columns initially
Reduces disk I/O
Lowers memory consumption
Speeds up wide-table queries

Although ClickHouse® automatically applies PREWHERE in many cases, specifying it manually gives you greater control.

9. Use SAMPLE for Exploratory Queries

For approximate analytics, SAMPLE dramatically reduces execution time.

SELECT avg(value)
FROM events
SAMPLE 0.1
WHERE event_type = 'purchase';

This reads only 10% of the data while producing statistically representative results.

Remember that SAMPLE requires a table created with a SAMPLE BY clause.

10. Insert Data in Batches

Large batch inserts are far more efficient than numerous single-row inserts.

Slow:

INSERT INTO events VALUES
(1001,'purchase',now(),99.99);

INSERT INTO events VALUES
(1002,'view',now(),0.0);

Better:

INSERT INTO events VALUES
(1001,'purchase',now(),99.99),
(1002,'view',now(),0.0),
(1003,'click',now(),15.20);

Batch inserts reduce merge overhead and improve ingestion throughput.

11. Avoid Expensive JOINs

Although JOIN performance has improved significantly, joins remain expensive compared to scanning a single table.

Whenever possible:

Denormalize frequently queried data.
Join only after filtering.
Join smaller tables to larger tables.
Consider Dictionaries for dimension lookups.

Instead of:

SELECT *
FROM orders o
JOIN customers c
ON o.customer_id = c.id;

Filter first:

SELECT *
FROM
(
    SELECT *
    FROM orders
    WHERE order_date >= today() - 7
) o
JOIN customers c
ON o.customer_id = c.id;

Reducing rows before the JOIN often leads to substantial performance improvements.

12. Choose the Right MergeTree Engine

Different workloads benefit from different MergeTree engines.

Examples include:

MergeTree → General-purpose analytics
ReplacingMergeTree → Deduplication
SummingMergeTree → Incremental aggregation
AggregatingMergeTree → Precomputed aggregates
CollapsingMergeTree → Event state transitions
VersionedCollapsingMergeTree → Slowly changing records

Choosing the right engine can significantly reduce query complexity and execution time.

13. Monitor Query Performance

Use system tables to identify slow queries.

SELECT
    query_duration_ms,
    read_rows,
    read_bytes,
    memory_usage
FROM system.query_log
ORDER BY event_time DESC
LIMIT 20;

Monitor:

Query duration
Rows read
Bytes read
Memory usage
Scan efficiency

Regular monitoring helps detect regressions before they affect production workloads.

14. Use LIMIT Efficiently

If users only require a subset of rows, don't scan the entire dataset.

SELECT *
FROM events
ORDER BY timestamp DESC
LIMIT 100;

Combined with a suitable ORDER BY key, ClickHouse® can stop reading data earlier.

15. Use Appropriate Data Types

Smaller data types improve:

Compression
Cache efficiency
Memory utilization
Query speed

Instead of:

user_id UInt64

Use:

user_id UInt32

if appropriate.

Other recommendations:

UInt8 instead of UInt32 where applicable
Date instead of DateTime if time isn't needed
Enum or LowCardinality for categorical values

16. Use Compression Codecs

Column-specific codecs can further reduce storage while improving read performance.

Examples:

temperature Float32 CODEC(Gorilla)

timestamp DateTime CODEC(DoubleDelta)

message String CODEC(ZSTD)

Choosing the right codec depends on the data distribution and access patterns.

17. Avoid Large OFFSET Pagination

OFFSET pagination forces ClickHouse® to scan and discard rows.

Avoid:

SELECT *
FROM events
ORDER BY timestamp
LIMIT 100 OFFSET 500000;

Prefer keyset pagination:

SELECT *
FROM events
WHERE timestamp > '2026-01-01 12:00:00'
ORDER BY timestamp
LIMIT 100;

Keyset pagination scales much better for large datasets.

18. Use Projections

Projections store alternative sorted layouts inside the same table.

ALTER TABLE events
ADD PROJECTION by_event_type
(
    SELECT *
    ORDER BY event_type
);

Queries filtering by event_type can automatically use the projection, reducing scan time without changing application queries.

19. Use Materialized Views

Repeatedly calculating the same aggregations is expensive.

Materialized Views compute aggregates during data insertion.

CREATE MATERIALIZED VIEW events_mv
TO events_hourly_summary
AS
SELECT
    toStartOfHour(timestamp) AS hour,
    event_type,
    count() AS total_count,
    sum(value) AS total_value
FROM events
GROUP BY hour, event_type;

Now dashboards can query the summary table instead of billions of raw rows.

SELECT
    hour,
    event_type,
    total_count,
    total_value
FROM events_hourly_summary
WHERE hour >= now() - INTERVAL 24 HOUR
ORDER BY hour;

20. Use EXPLAIN to Understand Query Execution

The EXPLAIN statement helps visualize how ClickHouse® executes queries.

Basic example:

EXPLAIN
SELECT count()
FROM events
WHERE user_id = 1001;

Useful options include:

EXPLAIN PIPELINE
SELECT count()
FROM events;

EXPLAIN indexes = 1
SELECT count()
FROM events
WHERE user_id = 1001;

These commands reveal:

Query pipeline
Index usage
Estimated rows
Execution stages

They are invaluable when diagnosing slow queries.

Final Thoughts

ClickHouse® is already one of the fastest analytical databases available, but achieving consistent sub-second performance requires thoughtful schema design and efficient query patterns.

Start with the fundamentals: choose an effective ORDER BY key, partition wisely, avoid unnecessary column reads, and write filters that leverage the primary index. As your datasets grow, take advantage of advanced features such as skip indexes, PREWHERE, projections, compression codecs, materialized views, and the EXPLAIN statement to fine-tune performance.

By combining these best practices, ClickHouse® can efficiently process billions of rows with minimal latency, making it an exceptional platform for modern analytical workloads.

Small optimizations compound over time. The more closely your table design aligns with your query patterns, the greater the performance gains you'll achieve

Day 26 of 100 Days of ClickHouse: Understanding Distributed Tables

Kanishga Subramani — Fri, 19 Jun 2026 10:26:37 +0000

As your data grows, a single ClickHouse server may eventually reach its limits. Whether it's storage, ingestion speed, or query performance, there comes a point where scaling vertically is no longer enough.

That's where Distributed Tables come in.

Distributed tables allow ClickHouse to scale horizontally across multiple servers while providing a single logical interface for querying your data. Instead of worrying about which server stores which records, you query one table, and ClickHouse takes care of the rest.

In this article, we'll explore what Distributed tables are, how they work, and why they're a key component of large-scale ClickHouse deployments.

Why Do We Need Distributed Tables?

ClickHouse is incredibly fast, and a single server can handle billions of rows. But as applications grow, organizations often need to:

Store more data than a single machine can hold
Increase data ingestion throughput
Speed up query execution
Improve fault tolerance
Scale across multiple servers

Once data is spread across several nodes, querying each server individually becomes impractical.

Distributed tables solve this problem by providing a single entry point for the entire cluster.

What Is a Distributed Table?

A Distributed table is a logical table that sits on top of existing local tables.

Unlike MergeTree engines, it does not store any data.

Instead, it forwards queries to the appropriate servers, collects the results, merges them, and returns a unified response.

For example:

CREATE TABLE events_distributed
AS events_local
ENGINE = Distributed(
    analytics_cluster,
    analytics,
    events_local,
    cityHash64(user_id)
);

Here:

analytics_cluster is the cluster configuration.
analytics is the database name.
events_local is the table that actually stores the data.
cityHash64(user_id) determines how rows are distributed across shards.

The important thing to remember is that the Distributed table contains no data of its own.

Local Tables vs Distributed Tables

Every server in the cluster stores data inside its own local table.

Shard 1
└── events_local

Shard 2
└── events_local

Shard 3
└── events_local

The Distributed table simply sits above them.

events_distributed
        │
 ┌──────┼──────┐
 │      │      │
Shard1 Shard2 Shard3

When you run:

SELECT *
FROM events_distributed;

ClickHouse automatically sends the query to the required shards, gathers the partial results, merges them, and returns the final output.

From the application's perspective, it feels like querying a single table.

Understanding Sharding

Distributed tables are commonly used with sharding.

Sharding means splitting data across multiple servers.

For example:

User 1 → Shard 1
User 2 → Shard 2
User 3 → Shard 3

The server that stores each row is determined by a sharding key.

A common choice is:

cityHash64(user_id)

A good sharding key helps:

Balance storage across servers
Distribute query workload
Prevent bottlenecks
Improve scalability

Choosing the right sharding key is one of the most important design decisions in a distributed ClickHouse cluster.

How Distributed Queries Work

Suppose you execute:

SELECT
    country,
    count()
FROM events_distributed
GROUP BY country;

ClickHouse performs several steps automatically:

Sends the query to each participating shard.
Executes the query locally on every server.
Collects partial results.
Merges those results.
Returns the final aggregated response.

Because every shard processes its own data simultaneously, queries can scale efficiently as your cluster grows.

Distributed Inserts

Distributed tables can also be used for inserting data.

INSERT INTO events_distributed
VALUES (...);

Instead of the client deciding which server should receive each row, ClickHouse evaluates the sharding key and forwards the data to the appropriate shard automatically.

This greatly simplifies application development.

Sharding vs Replication

Many beginners confuse sharding with replication, but they solve different problems.

Sharding

Splits data across multiple servers
Improves scalability
Increases storage capacity
Distributes workload

Replication

Creates copies of data
Provides high availability
Protects against server failures

A production ClickHouse cluster usually combines both techniques.

Shard 1
├── Replica A
└── Replica B

Shard 2
├── Replica A
└── Replica B

The Distributed table then acts as the single entry point for the cluster.

Best Practices

When working with Distributed tables:

Design your sharding key carefully.
Keep local table schemas identical across all nodes.
Filter data early to reduce network traffic.
Minimize unnecessary cross-shard queries.
Monitor shard balance and cluster health regularly.
Test your data distribution before moving to production.

Common Mistakes

Here are a few common misconceptions:

Distributed tables store data

They don't. Data is stored only in the local tables.

Replication and distribution are the same

Replication improves availability.

Distribution improves scalability.

Any sharding key will work

A poor sharding key can create uneven data distribution, overloaded servers, and slower queries.

Always choose a key that aligns with your application's query patterns.

Final Thoughts

Distributed tables are one of the core building blocks that make ClickHouse capable of handling massive analytical workloads.

They provide a simple abstraction that lets applications interact with a single logical table while ClickHouse manages query routing, distributed execution, and result aggregation behind the scenes.

If you're planning to build large-scale analytics platforms with ClickHouse, understanding the relationship between local tables, Distributed tables, sharding, and replication is essential.

Once these concepts click, scaling ClickHouse across multiple servers becomes much easier to understand and implement.

Thanks for reading! If you're following along with my 100 Days of ClickHouse series, stay tuned for Day 27, where we'll continue exploring another key ClickHouse concept.

Day 25 of 100 Days of ClickHouse: Mastering the ClickHouse HTTP API

Kanishga Subramani — Fri, 19 Jun 2026 06:19:52 +0000

ClickHouse HTTP API: A Complete Beginner's Guide

Introduction

When most people think about interacting with a database, they usually imagine connecting through a database client or application. However, ClickHouse also provides a simple and powerful HTTP API that allows you to query and manage your database using standard HTTP requests.

The ClickHouse HTTP API provides a universal interface for communicating with your ClickHouse server. Since almost every programming language and automation tool supports HTTP, it becomes an excellent choice for integrations, monitoring, scripting, and lightweight applications.

In this guide, you'll learn what the ClickHouse HTTP API is, why it's useful, and how to perform common database operations using simple HTTP requests.

What Is the ClickHouse HTTP API?

The ClickHouse HTTP API is a built-in interface that enables clients to communicate with a ClickHouse server using the HTTP protocol.

Instead of connecting through the native TCP protocol, you simply send HTTP requests and receive responses in formats such as JSON, CSV, TSV, XML, or plain text.

The HTTP interface is:

Language agnostic
Easy to integrate with web applications
Firewall friendly
Simple to test using tools like cURL, Postman, or a web browser

Because of its simplicity, the HTTP API is widely used for automation, dashboards, data pipelines, and monitoring systems.

Why Use the HTTP API?

The ClickHouse HTTP API offers several advantages:

No dedicated database driver is required.
Works with virtually every programming language.
Easy integration with REST-based applications.
Supports multiple output formats.
Ideal for automation and scripting.
Perfect for cloud-native applications and microservices.

Common Operations

Using the HTTP API, you can:

Execute SQL queries
Insert data
Create and modify tables
Retrieve query results
Export data in different formats
Automate database operations

Authentication Options

ClickHouse supports multiple authentication methods when using the HTTP API.

You can authenticate using:

URL parameters
HTTP Basic Authentication
HTTP request headers (recommended)

Using request headers is generally considered the most secure and maintainable approach for production environments.

Best Practices

To get the most out of the ClickHouse HTTP API:

Always authenticate your requests.
Use HTTPS in production environments.
Validate user input before executing queries.
Limit permissions using appropriate user roles.
Monitor API usage and query performance.
Use JSON output for applications that consume API responses.

When Should You Use the HTTP API?

The HTTP API is an excellent choice for:

Web applications
REST services
Automation scripts
Monitoring tools
ETL pipelines
Serverless applications
Data integrations
Analytics dashboards

If your application already communicates over HTTP, integrating with ClickHouse becomes straightforward.

Conclusion

The ClickHouse HTTP API provides a simple, flexible, and powerful way to interact with your database using standard HTTP requests. Whether you're querying data, inserting records, or automating workflows, it eliminates the need for specialized database drivers while remaining easy to use across different platforms and programming languages.

For developers building modern analytics applications, automation workflows, or cloud-native services, the HTTP API is one of the easiest ways to integrate ClickHouse into existing systems.

Learning how to use the HTTP API effectively is an essential step toward becoming proficient with ClickHouse and building scalable, high-performance data solutions.

Day 24 of 100 Days of ClickHouse: Working with the ClickHouse HTTP API

Kanishga Subramani — Fri, 19 Jun 2026 05:48:21 +0000

When most people think about interacting with a database, they imagine using a client library, a graphical interface, or a command-line tool. While these are common approaches, ClickHouse also provides a built-in HTTP API that enables you to communicate with the database using standard HTTP requests.

This means that if your application, script, or service can send an HTTP request, it can interact with ClickHouse. Whether you're using Python, JavaScript, Go, Java, cURL, Postman, or even a simple shell script, the HTTP API provides a universal and lightweight way to execute queries, insert data, and manage your database.

In this article, we'll explore how the ClickHouse HTTP API works, how to use it effectively, and why it's such an important feature for modern applications.

What Is the ClickHouse HTTP API?

The ClickHouse HTTP API is a built-in interface that allows applications to communicate with ClickHouse over the HTTP protocol.

Instead of connecting through the native ClickHouse protocol, your application simply sends an HTTP request containing an SQL query, and ClickHouse executes it before returning the result.

By default, the HTTP API runs on port 8123 and supports both GET and POST requests.

Unlike the native protocol, the HTTP interface is:

Language independent
Easy to integrate with any application
Firewall friendly
Ideal for automation
Simple to test using browsers, cURL, or Postman

This flexibility makes it popular for scripting, automation, lightweight services, and REST-based applications.

How the HTTP API Works

The communication flow is straightforward:

Application
     │
     ▼
HTTP Request
     │
     ▼
ClickHouse Server
     │
     ▼
SQL Execution
     │
     ▼
HTTP Response

The response can be returned in many formats including JSON, CSV, TSV, Parquet, Arrow, and more.

Default Ports

ClickHouse exposes multiple interfaces.

Interface	Default Port
Native TCP	9000
HTTP API	8123

For HTTP-based communication, you'll primarily work with port 8123.

Prerequisites

Before following along, ensure you have:

A running ClickHouse server
cURL installed
Basic SQL knowledge
Access to port 8123

Step 1: Verify the HTTP API

The simplest way to verify that the HTTP API is available is by checking the built-in ping endpoint.

curl http://localhost:8123/ping

Expected response:

Ok.

If you receive this response, your HTTP interface is working correctly.

Step 2: Execute Your First Query

One of the easiest ways to query ClickHouse is by passing SQL as a URL parameter.

Using GET:

curl "http://localhost:8123/?query=SELECT+version()"

Output:

25.3.1.1

Although GET works well for simple queries, POST requests are generally preferred for larger SQL statements.

Using POST:

curl -X POST http://localhost:8123/ \
  --data-binary "SELECT version()"

Output:

25.3.1.1

POST requests are cleaner and avoid URL length limitations.

Step 3: Authentication

If authentication is enabled, ClickHouse supports several methods.

URL Parameters

curl "http://localhost:8123/?user=default&password=yourpassword&query=SELECT+1"

HTTP Basic Authentication

curl -u default:yourpassword \
"http://localhost:8123/?query=SELECT+1"

HTTP Headers (Recommended)

curl http://localhost:8123/ \
-H "X-ClickHouse-User: default" \
-H "X-ClickHouse-Key: yourpassword" \
--data-binary "SELECT 1"

Using request headers is recommended because credentials aren't exposed in URLs or logs.

Step 4: Selecting a Database

Queries execute against the default database unless another database is specified.

Specify a database using the request parameter:

curl -u default:yourpassword \
"http://localhost:8123/?database=my_database" \
--data-binary "SELECT count() FROM my_table"

Or reference the database directly inside the SQL statement:

SELECT count()
FROM my_database.my_table;

Step 5: Querying Data

Executing SELECT statements is straightforward.

curl -u default:yourpassword \
http://localhost:8123/ \
--data-binary "SELECT * FROM default.products LIMIT 5"

By default, ClickHouse returns data in TabSeparated format.

Output Formats

One of the strengths of the HTTP API is its support for multiple output formats.

JSON

SELECT *
FROM default.products
FORMAT JSON

Useful for:

REST APIs
Web applications
JavaScript clients

CSV

SELECT *
FROM default.products
FORMAT CSV

Ideal for:

Excel
Data exports
ETL pipelines

JSONEachRow

SELECT *
FROM default.products
FORMAT JSONEachRow

Each row is returned as an individual JSON document, making it perfect for:

Streaming
Kafka
Log processing
Large datasets

Other Supported Formats

ClickHouse supports numerous formats including:

JSON
JSONCompact
JSONEachRow
CSV
TSV
Pretty
PrettyCompact
Parquet
Apache Arrow
TabSeparated

Choosing the appropriate format depends on how downstream systems consume the data.

Step 6: Inserting Data

The HTTP API also supports data ingestion.

Insert TabSeparated Data

curl -u default:yourpassword \
"http://localhost:8123/?query=INSERT+INTO+default.products+FORMAT+TabSeparated" \
--data-binary $'5\tMechanical Keyboard\tAccessories\n6\tMonitor 4K\tElectronics\n'

Insert CSV

curl -u default:yourpassword \
"http://localhost:8123/?query=INSERT+INTO+default.products+FORMAT+CSV" \
--data-binary @products.csv

Insert JSON

curl -u default:yourpassword \
"http://localhost:8123/?query=INSERT+INTO+default.products+FORMAT+JSONEachRow" \
--data-binary '
{"product_id":7,"name":"Webcam HD","category":"Accessories"}
{"product_id":8,"name":"Desk Lamp","category":"Furniture"}
'

This flexibility allows applications to ingest data using the format most convenient for them.

Step 7: Creating and Managing Tables

The HTTP API supports all standard Data Definition Language (DDL) operations.

Create a table:

CREATE TABLE default.logs
(
    server_id LowCardinality(String),
    level LowCardinality(String),
    message String,
    timestamp DateTime
)
ENGINE = MergeTree()
ORDER BY timestamp;

Drop a table:

DROP TABLE IF EXISTS default.logs;

Similarly, ALTER, TRUNCATE, OPTIMIZE, and other administrative statements are fully supported.

Useful HTTP Parameters

Several query parameters can simplify common tasks.

Parameter	Purpose
query	SQL query
user	Username
password	Password
database	Database name
default_format	Default response format
max_rows_to_read	Scan limit
max_execution_time	Query timeout
compress	Enable response compression

These parameters help control execution behavior without modifying SQL itself.

Best Practices

To get the most out of the ClickHouse HTTP API:

Use POST requests for longer SQL statements.
Send credentials using HTTP headers instead of URLs.
Use JSONEachRow for streaming large datasets.
Set max_execution_time to prevent runaway queries.
Enable compression for large responses.
Always specify the database explicitly.
Use official ClickHouse client libraries in production when advanced features like connection pooling and automatic retries are required.

Following these practices results in more secure, reliable, and efficient integrations.

HTTP API vs Native Protocol

Feature	HTTP API	Native Protocol
Ease of Use	⭐⭐⭐⭐⭐	⭐⭐⭐⭐
Performance	⭐⭐⭐⭐	⭐⭐⭐⭐⭐
Driver Required	No	Yes
Universal Support	Yes	Driver Dependent
Automation	Excellent	Good
Bulk Inserts	Good	Excellent

The HTTP API is ideal for integrations, scripting, and lightweight services, while the native protocol remains the best option for performance-critical applications.

Real-World Use Cases

The ClickHouse HTTP API is commonly used for:

Building REST APIs
Internal dashboards
Automation scripts
Monitoring systems
CI/CD pipelines
Serverless applications
ETL workflows
Data export services
Microservices
Third-party integrations

Because HTTP is universally supported, integrating ClickHouse into existing systems becomes remarkably simple.

Final Thoughts

The ClickHouse HTTP API demonstrates how powerful database interactions can also be simple. By exposing SQL operations over standard HTTP, ClickHouse removes the dependency on specialized drivers and enables seamless integration with virtually any programming language or platform.

Whether you're executing queries, loading data, automating administrative tasks, or building cloud-native applications, the HTTP API offers a flexible and reliable interface that scales from quick experiments to production workloads.

As your ClickHouse deployments grow, understanding the HTTP API becomes increasingly valuable. It's a feature that bridges the gap between databases and modern software architectures, making automation, integration, and analytics significantly easier.

In the next article of this 100 Days of ClickHouse series, we'll continue exploring another powerful capability that helps you build faster, more scalable analytical systems.

Day 23 of 100 Days of ClickHouse: Backups Explained – A Complete Guide to BACKUP and RESTORE

Kanishga Subramani — Thu, 18 Jun 2026 08:33:59 +0000

ClickHouse Backups Explained: How to Protect Your Data with BACKUP and RESTORE

Data is one of the most valuable assets for any organization. Whether you're using ClickHouse for observability, log analytics, real-time dashboards, or business intelligence, losing data can lead to downtime, inaccurate reporting, and difficult recovery efforts.

Although ClickHouse is designed for high availability and performance, no database is immune to accidental deletions, hardware failures, software bugs, or human error. That's why every production deployment should include a reliable backup strategy.

In this article, we'll explore why backups matter, the different backup types available, and how to use ClickHouse's built-in BACKUP and RESTORE commands to safeguard your data.

Why Backups Are Essential

A backup is a recoverable copy of your database's data and metadata. It allows you to restore your environment to a known good state after data loss or corruption.

One common misconception is that replication replaces backups.

It doesn't.

Replication protects against server or node failures by maintaining copies of data across multiple replicas. However, if someone accidentally drops a table, deletes important records, or inserts incorrect data, those changes are replicated as well.

Typical scenarios where backups become essential include:

Accidental table deletion
Incorrect UPDATE or DELETE operations
Hardware or storage failures
Software bugs
Disaster recovery
Compliance and long-term retention

A backup provides an independent recovery point that replication alone cannot offer.

Understanding Backup Types

Full Backup

A full backup contains a complete copy of the selected database, including both metadata and data.

It's commonly used:

Before major upgrades
Before migrations
As the baseline for future backups
For long-term archival

Although full backups require more storage, they provide the fastest recovery.

Incremental Backup

Incremental backups store only the data that has changed since the previous backup.

Benefits include:

Faster backup creation
Lower storage consumption
Ideal for large production datasets

Many organizations schedule weekly full backups with daily incremental backups.

Differential Backup

A differential backup stores all changes made since the last full backup.

Compared to incremental backups, they require more storage but simplify the restore process because only the latest differential backup and the original full backup are required.

Backup Methods in ClickHouse

ClickHouse supports several approaches to protecting data.

Native BACKUP and RESTORE

Modern ClickHouse versions include built-in backup functionality, making it easy to create and restore backups without external tools.

File System Backups

Administrators can also back up the underlying data directories directly.

While simple, this method requires additional care to ensure consistency during backup operations.

Storage Snapshots

Infrastructure providers often support volume snapshots, allowing administrators to capture the state of storage at a specific point in time.

These are commonly used in cloud deployments.

Third-Party Tools

Utilities such as clickhouse-backup provide advanced capabilities including:

Scheduled backups
Cloud storage integration
Retention management
Backup automation

These tools are popular in larger production environments.

Creating Backups Using Native Commands

Before using the backup feature, configure a backup disk in the ClickHouse configuration.

Example:

<storage_configuration>
    <disks>
        <backups>
            <type>local</type>
            <path>/backups/</path>
        </backups>
    </disks>
</storage_configuration>

Backup an Entire Database

BACKUP DATABASE analytics
TO Disk('backups', 'analytics_backup.zip');

Backup a Single Table

BACKUP TABLE analytics.events
TO Disk('backups', 'events_backup.zip');

Create an Incremental Backup

BACKUP DATABASE analytics
TO Disk('backups', 'analytics_incremental.zip')
SETTINGS base_backup = Disk('backups', 'analytics_full_backup.zip');

Only the changes since the base backup are stored.

Restoring Data

Restoring an entire database is equally simple.

RESTORE DATABASE analytics
FROM Disk('backups', 'analytics_backup.zip');

To restore a single table:

RESTORE TABLE analytics.events
FROM Disk('backups', 'events_backup.zip');

ClickHouse recreates the metadata and restores the associated data from the backup archive.

Verify Your Backups

Creating backups is only part of the process.

A backup that has never been tested shouldn't be assumed to work.

After every backup, consider:

Verifying backup files exist
Reviewing ClickHouse logs
Performing periodic test restores
Validating restored data

Regular testing ensures your recovery process works when it matters most.

Backup Best Practices

A few practices can significantly improve backup reliability:

Automate backup schedules instead of relying on manual execution.
Store backups in multiple locations, including remote or cloud storage.
Test restore procedures regularly.
Define retention policies based on business and compliance requirements.
Monitor backup jobs and storage utilization.

These practices reduce operational risk and improve recovery readiness.

Final Thoughts

High availability and backups solve different problems.

Replication keeps your ClickHouse cluster running during infrastructure failures, while backups protect against data loss, corruption, and human error.

By combining native BACKUP and RESTORE commands with automated scheduling, retention policies, and regular restore testing, you can build a recovery strategy that keeps your analytical platform resilient.

No backup strategy is complete until you've successfully restored from it. The ability to recover quickly is what ultimately determines the value of every backup.

Why ClickHouse Merges and Mutations Are Difficult to Track in Production

Kanishga Subramani — Thu, 18 Jun 2026 07:25:40 +0000

One of the reasons ClickHouse delivers exceptional analytical performance is its ability to optimize data in the background. While users focus on writing fast SQL queries, ClickHouse is continuously performing maintenance tasks such as merges and mutations to keep storage efficient and queries fast.

These background operations are essential, but they're also one of the least visible aspects of running ClickHouse in production. Without proper monitoring, they can silently become bottlenecks, leading to slower queries, delayed data processing, and even production errors.

In this article, we'll explore how merges and mutations work, why they're difficult to monitor, and what teams can do to improve observability.

Understanding Merges

ClickHouse stores data in immutable parts. Every INSERT creates a new data part instead of modifying existing files.

As more data is ingested, the number of parts grows. To prevent excessive fragmentation, ClickHouse automatically merges smaller parts into larger ones in the background.

This process helps:

Reduce the total number of parts
Improve query performance
Lower metadata overhead
Optimize disk usage
Keep MergeTree tables healthy

Without regular merges, thousands of small parts can accumulate, making both queries and inserts less efficient.

Understanding Mutations

Operations such as UPDATE and DELETE work differently in ClickHouse than they do in traditional transactional databases.

Instead of modifying rows immediately, ClickHouse schedules these operations as mutations, which are processed asynchronously in the background.

For example:

ALTER TABLE events
DELETE WHERE event_date < '2025-01-01';

ALTER TABLE users
UPDATE status = 'inactive'
WHERE last_login < '2024-01-01';

This architecture keeps write performance high but means data modifications may take time to complete, especially on large tables.

Why Monitoring Is Challenging

Limited Historical Visibility

ClickHouse provides system tables such as:

system.merges
system.mutations
system.parts

These tables are extremely useful for checking the current state of background operations.

The limitation is that they primarily provide a snapshot of what's happening now. Once a merge or mutation completes, much of that operational history disappears unless you've collected it yourself.

This makes post-incident analysis significantly more difficult.

The "Too Many Parts" Problem

One of the most common production issues is the "Too many parts" error.

It usually indicates that new parts are being created faster than background merges can combine them.

When this happens, organizations may experience:

Slower inserts
Higher query latency
Increased storage overhead
Overloaded merge queues
Reduced cluster stability

Unfortunately, by the time this error appears, the underlying problem has often been developing for hours or days.

Mutation Backlogs

Mutations are executed sequentially.

A large DELETE, UPDATE, or schema-related operation can remain active for a long time, preventing subsequent mutations from being processed.

As the backlog grows, teams may notice:

Delayed data cleanup
Growing storage consumption
Slower maintenance tasks
Longer processing times

Without continuous monitoring, these queues often remain unnoticed until they begin affecting production workloads.

Reactive Troubleshooting

Many administrators investigate issues by manually querying:

SELECT * FROM system.merges;

SELECT * FROM system.mutations;

SELECT * FROM system.parts;

Although these queries are useful, they don't provide:

Historical trends
Long-term metrics
Automatic alerts
Centralized dashboards
Anomaly detection

As a result, troubleshooting often becomes reactive rather than proactive.

Best Practices

To maintain a healthy ClickHouse cluster, consider monitoring background operations alongside traditional infrastructure metrics.

Useful metrics include:

Active merge count
Merge duration
Mutation queue size
Mutation progress
Number of table parts
Background thread utilization
Resource consumption during merges

Storing these metrics over time enables trend analysis, capacity planning, and faster root-cause analysis.

Creating dashboards and alerts for merge delays, increasing part counts, or mutation backlogs can help identify issues before they impact users.

Final Thoughts

Merges and mutations are fundamental to ClickHouse's performance and storage efficiency, but they often receive far less attention than query optimization.

While ClickHouse provides excellent visibility into current background activity, long-term observability requires additional monitoring and historical metrics.

By treating merges and mutations as first-class operational metrics, teams can reduce downtime, improve cluster health, and avoid many of the production issues that arise from unseen background processes.

A well-monitored ClickHouse cluster isn't just one that answers queries quickly—it's one where the background maintenance processes are just as visible as the queries themselves.

Link -> https://quantrail-data.com/clickhouse-merges-and-mutations-the-hidden-performance-monitoring-challenge/

Day 22 of 100 Days of ClickHouse: Exploring High-Speed Analytics

Kanishga Subramani — Wed, 17 Jun 2026 09:10:28 +0000

One of the most common questions developers ask when working with ClickHouse® is:

"How should I store JSON data?"

The answer isn't as simple as choosing between a String column and the native JSON data type. It depends on how your application ingests, stores, and queries data.

JSON has become the standard format for modern applications. Every API request, application log, event stream, telemetry record, and user interaction is typically represented as JSON before it reaches a database. Its schema flexibility makes it ideal for evolving systems where new attributes can appear without requiring immediate database migrations.

However, analytical databases are built for speed, and speed comes from structure.

Historically, the most common approach in ClickHouse® was storing JSON as a String and extracting fields during query execution using functions like JSONExtractString(), JSONExtractUInt(), and JSONExtractBool(). This approach is incredibly flexible because the original document is preserved exactly as it was received.

But there's a trade-off.

Every query that accesses a field has to parse the JSON document again. On small datasets, this overhead is negligible. On billions of rows, repeatedly parsing JSON becomes expensive and increases CPU utilization.

This is one of the reasons why ClickHouse introduced the native JSON data type.

Instead of treating JSON as plain text, ClickHouse understands the document's structure internally. More importantly, it uses lazy parsing, meaning only the fields referenced in a query are processed. If your query only needs user_id, ClickHouse doesn't waste time parsing every nested attribute in the document.

This significantly improves efficiency for many semi-structured workloads while preserving the flexibility developers expect from JSON.

That said, native JSON isn't a silver bullet.

One of the biggest misconceptions is believing that once native JSON is available, every attribute should remain inside a JSON object.

In reality, query patterns should drive schema design.

If a field is frequently used in WHERE clauses, GROUP BY operations, joins, dashboards, or reports, it usually deserves its own dedicated column. Structured columns allow ClickHouse to optimize storage, indexing, and query execution far better than repeatedly navigating JSON paths.

This leads to what many production systems adopt: a hybrid approach.

Core business attributes—such as user_id, event_type, or timestamp—are stored as dedicated columns because they're queried constantly. Additional metadata that changes frequently or isn't accessed often remains inside a JSON column.

This provides the best of both worlds:

Fast analytical queries
Flexible schemas
Simpler ingestion pipelines
Lower maintenance as applications evolve

Another important lesson is that ingestion patterns and query patterns are rarely the same.

Just because data arrives as JSON doesn't mean it should be stored exactly that way forever. Designing your schema around how analysts and applications actually consume data often leads to much better long-term performance.

As developers, it's easy to focus on making ingestion simple. But in analytical systems, query performance is usually what determines the overall user experience.

My biggest takeaway from today's learning is that JSON is a tool—not a schema design strategy.

ClickHouse gives us multiple options:

Store raw JSON as String
Use the native JSON data type for evolving schemas
Model frequently accessed attributes as dedicated columns

Choosing the right combination depends on your workload, your data, and your query patterns.

Understanding these trade-offs is what separates a database that simply works from one that scales efficiently as your data grows from millions to billions of records.

How are you handling JSON in your analytics stack? Are you using native JSON, traditional extraction functions, or a hybrid schema?

I'd love to hear about your experience.

Day 21 : Time-Series Data in ClickHouse®

Kanishga Subramani — Wed, 17 Jun 2026 06:49:57 +0000

Time-series data is one of the most common types of data generated by modern applications. Every log entry, API request, metric, transaction, sensor reading, or user interaction is recorded with a timestamp, making time the primary dimension for analysis. As organizations collect billions of these records, efficiently storing and querying them becomes increasingly challenging.

This is where ClickHouse® excels.

Although ClickHouse is not a dedicated time-series database, its columnar storage architecture, vectorized query execution, high compression ratios, and massively parallel processing make it an excellent choice for time-series analytics at scale. It is capable of ingesting large volumes of data while delivering analytical queries in milliseconds.

The article begins by explaining the fundamentals of time-series data and highlighting common real-world use cases such as application monitoring, IoT sensor data, financial market analysis, server metrics, user activity tracking, and business analytics. These workloads typically involve continuous data ingestion, time-based filtering, aggregations, and trend analysis.

One of ClickHouse's biggest strengths is its optimization for analytical workloads. Since data is stored column-wise rather than row-wise, only the required columns are read during query execution. Combined with compression and vectorized processing, this significantly reduces I/O and improves query performance over massive datasets.

The article also demonstrates how to create an optimized table for time-series workloads using the MergeTree engine. Proper partitioning by month and ordering data by dimensions and timestamps help ClickHouse prune unnecessary partitions and efficiently locate relevant data during queries.

Several practical SQL examples are covered, including:

Filtering records within a specific time range
Aggregating metrics by hour, day, week, or month
Calculating averages, sums, minimums, and maximums
Grouping events over time
Working with ClickHouse date and time functions
Performing efficient trend and time-window analysis

A dedicated section introduces essential ClickHouse date and time functions such as toStartOfHour(), toStartOfDay(), toStartOfMonth(), toYYYYMM(), dateDiff(), toUnixTimestamp(), and other utilities that simplify time-based transformations and reporting.

The article concludes with several best practices for building scalable time-series solutions:

Include the timestamp column in the ORDER BY key for efficient range scans.
Partition tables by month or day to improve partition pruning.
Use LowCardinality for string columns with limited distinct values.
Prefer DateTime64 when millisecond precision is required.
Choose appropriate partitioning and sorting keys based on query patterns.

Overall, this article demonstrates why ClickHouse has become a popular choice for large-scale time-series analytics. Its combination of SQL compatibility, high ingestion throughput, efficient storage, and exceptional query performance allows developers and data engineers to analyze massive volumes of time-stamped data without the complexity of learning a proprietary time-series database.

Day 20 of 100 Days of ClickHouse® - Introduction to Materialized Views

Kanishga Subramani — Wed, 17 Jun 2026 05:37:53 +0000

One of the most common performance bottlenecks in analytics is running the same expensive aggregations over and over again.

Imagine querying billions of rows every time a dashboard loads just to calculate daily events, page views, or user activity. As your data grows, those queries become slower and consume more CPU.

This is exactly the problem Materialized Views solve in ClickHouse®.

Instead of performing aggregations during query execution, a Materialized View processes new data as it's inserted and writes the transformed or aggregated results into a separate target table.

A few important concepts every ClickHouse developer should know:

• Materialized Views process only newly inserted data blocks—they don't rescan the entire source table.
• Existing data isn't included automatically. While the POPULATE option exists, it's generally not recommended for large production datasets. A manual backfill is usually the safer approach.
• The target table engine matters:
• MergeTree for transformed data
• SummingMergeTree for simple numeric aggregations
• AggregatingMergeTree for aggregate states and advanced analytics
• Materialized Views aren't limited to aggregations—they can also clean, enrich, and transform incoming data before it's stored.

Like every optimization, there's a trade-off.

You're shifting work from query time to insert time, which means inserts do a little more work, but reads become dramatically faster. For reporting systems, dashboards, and analytical workloads, that's often a worthwhile trade.

In today's article, I cover the architecture of Materialized Views, how they work internally, common use cases, engine selection, limitations, and production best practices.

Have you used Materialized Views in production? What kind of workloads have they helped you optimize?

100DaysOfClickHouse #ClickHouse #DataEngineering #Database #SQL #OLAP #BackendEngineering #Performance #DistributedSystems #BigData