DEV Community: Kanishga Subramani

Where Did the Time Go? Query Profiler in CHOps

Kanishga Subramani — Fri, 04 Sep 2026 09:46:05 +0000

A query takes 9 seconds. You read the SQL. You read EXPLAIN. Nothing looks wrong.

EXPLAIN tells you what ClickHouse® plans to do. It does not tell you where the 9 seconds actually went. For that you need to watch the query run — and that is what the CHOps Query Profiler does.

Open SQL Tools → Query Profiler, pick a query, and CHOps draws a flame graph of it.

What a flame graph actually is

A flame graph is a picture of where time went. That's it.

The bottom bar is where the query starts.
Every bar is one function that ran.
Width = time. Wider bar, more time.
Stacked bars = the call chain. A called B, B called C.

Read it bottom to top for what called what, left to right for what ran. But mostly you just find the widest bar. That's the bottleneck.

You do not need to know C++ or ClickHouse® internals for this. The function names carry enough meaning on their own:

If the widest bar says…	The query is…
`ReadBufferFromFileDescriptor`	IO-bound — reading too much from disk
`HashTable::insert`	building hash tables for GROUP BY or JOIN
`MergeTreeDataSelectExecutor`	scanning MergeTree parts — likely missing the primary index
`MemoryTracker::allocImpl`	allocating — expected at the top of a memory trace

Widest bar, read the name, act on it.

First, understand what a "sample" is

This is the part that trips people up, and it explains most of the confusion people have with the tool.

ClickHouse® does not record every function call. That would be ruinously slow. Instead it takes periodic snapshots: a timer fires, and whatever function each thread happens to be executing at that instant gets written as one row into system.trace_log. That row is one sample.

A flame graph is nothing more than those rows counted and stacked.

Bar width = how many snapshots caught that function running.

By default ClickHouse® takes one snapshot per second, per thread:

Sampling period	Snapshots/sec/thread	A 5s query on 4 threads gives
`1000000000` ns (1s — the default)	1	~20 samples
`10000000` ns (10ms)	100	~2,000 samples

Twenty samples produce a few blocky bars and percentages that swing on every re-run. Two thousand produce a graph you can trust.

So if your flame graph looks thin, the query is usually fine — the sampling rate is the problem.

Raise it for that one query:

SELECT
    ... your heavy query ...
SETTINGS
    query_profiler_real_time_period_ns = 10000000,
    query_profiler_cpu_time_period_ns  = 10000000;

Those go on the query you want to profile, when you run it.

They change how ClickHouse® records that query while it executes. Nothing on the server changes, and no other query is affected.

Memory traces work on a different trigger: instead of a timer, ClickHouse® records a stack every time the query allocates another memory_profiler_step bytes (4 MiB by default). Lower that value for a denser memory graph.

The workflow

Set From and To to when the query ran. The range is capped at 24 hours.
Click Load Queries. The header shows how many queries in that window have trace data.
Find your query. The picker searches by query text or query ID, and each row shows the ID, a SQL preview, the duration, the sample count and the timestamp.
Click it — the selected ID appears below the list, with Clear beside it.
Choose a Trace Type, then Generate Flame Graph.

The sample count in the list is the number to watch.

Single digits means you're about to get a graph that tells you nothing; go back and re-run the query with a higher sampling rate.

Above the graph you get three quick stats:

Total samples
Unique stacks
Max depth

Unique stacks is the one people overlook.

A low number means the query took very few distinct code paths, so there's little to see regardless of sample count.

If a query you just ran doesn't appear, run:

SYSTEM FLUSH LOGS;

Trace data is buffered before it's written.

Trace types: pick the one that matches your symptom

Nine options, and picking the right one matters more than anything else on the page.

Trace Type	Answers	Use when
All Types	everything at once	first look, general orientation
CPU Time	where CPU went	the default choice for a slow query
Wall Clock (Real)	where clock time went, waits included	slow query, but CPU looks idle
Memory (Watermark)	what caused the biggest allocations	query was killed for memory
Memory (Sampled)	the spread of memory use	broad memory picture
Memory Peak	what was running at the memory high point	pinning down a spike
Profile Events	which internal counters moved most	advanced
Jemalloc Samples	what the allocator is doing	advanced — fragmentation debugging
Instrumentation	XRay instrumentation traces	advanced — needs `SYSTEM INSTRUMENT`

Not all nine work out of the box.

Some rely on collectors that are off by default, and if a collector is off you get an empty graph rather than an error.

Worth knowing before you assume the tool is broken:

Trace type	On a stock server
CPU Time, Wall Clock (Real)	work — but at 1 sample/sec/thread, thin
Memory (Watermark), Memory Peak	work — a stack every 4 MiB allocated
Memory (Sampled)	empty — needs `memory_profiler_sample_probability > 0`
Profile Events	empty — needs `trace_profile_events = 1`
Jemalloc Samples	empty unless the jemalloc profiler is enabled
Instrumentation	empty unless `SYSTEM INSTRUMENT` is on

Check where you stand:

SELECT
    name,
    value
FROM system.settings
WHERE name LIKE '%profiler%'
   OR name = 'trace_profile_events';

The graph also re-labels itself for the trace type you picked.

On a CPU or All Types graph, the stats read total samples and the tooltip shows counts.

Switch to a memory type and the same line reads total bytes.

A caption next to the Generate button spells out what the current selection measures.

Memory graphs get a second dropdown

Pick a memory trace type and a Memory Context filter appears next to it, with five options.

Context	Shows
All Contexts	everything
Global (server)	server-wide allocations
User (user/merge)	user and merge allocations
Process (query)	just this query
Thread	thread-level allocations

Choose Process (query) when debugging one query.

Otherwise background merges and server caches get mixed into the graph and you end up optimising something unrelated.

Reading the shapes

One very wide bar

Easiest case.

One function owns the query. Read its name, act on it.

Many narrow towers

Normal for queries with joins, subqueries and several aggregations — no single function dominates.

Scan across all the towers for the widest single bar.

Still your best lever.

Two towers of similar width

Work split across parallel paths.

Widths are shares of the total, so two 50% towers means the cost is genuinely divided — fix both, or reduce the input feeding them.

Flat, no towers

One code path, or too few samples.

Check the unique-stacks count before concluding anything about the query.

Hover any bar for its full function name and share of the total.

Click to zoom into that subtree; the toolbox has restore, download and full-screen.

What CHOps is doing under the hood

No magic, and the UI doesn't hide it — a View Generated SQL panel under the graph shows the exact query CHOps ran:

SELECT
    arrayStringConcat(
        arrayReverse(
            arrayMap(
                x -> demangle(addressToSymbol(x)),
                trace
            )
        ),
        ';'
    ) AS stack,
    count() AS samples
FROM system.trace_log
WHERE query_id = '...'
GROUP BY stack
SETTINGS allow_introspection_functions = 1;

Here's what each part does:

addressToSymbol turns raw addresses into C++ symbols.
demangle makes those symbols readable.
arrayReverse puts the root at the bottom, where a flame graph expects it.
GROUP BY stack counts how often each call chain was sampled.

CHOps folds those stacks into a tree and renders it.

That folded-stack format is the same one every flame graph tool uses, which is why the output looks familiar if you've used perf.

Note this query only reads what was already captured.

It can't create samples after the fact — which is why the sampling settings belong on the query you profile, not here.

Query Profiler or Processors Profile?

CHOps ships two profilers.

Different tables, different questions.

	Query Profiler (flame graph)	Processors Profile (pipeline)
Source	`system.trace_log`	`system.processors_profile_log`
Shows	C++ functions inside the engine	logical pipeline steps
Best for	engine-level debugging	day-to-day query optimisation
Example finding	most of the time in a read-pool function	`ReadFromMergeTree` 7.2s, `AggregatingTransform` 0.3s

Start with Processors Profile.

Its names map directly to query plan steps, so the fix is usually obvious.

Switch to the Query Profiler when Processors Profile has told you which step is slow and you need to know why.

Before you get a graph

Requirement	Why	Check
`system.trace_log` enabled	that's where samples live	`SELECT count() FROM system.trace_log`
`allow_introspection_functions`	needed for symbol resolution	CHOps passes it for you
SELECT on `system.trace_log`, `system.query_log`	the CHOps ClickHouse® user needs read access	`GRANT SELECT ON system.trace_log TO your_user`
`clickhouse-common-static-dbg`	optional — resolves system frames to names	`dpkg -l clickhouse-common-static-dbg`

When it doesn't work

Symptom	Cause	Fix
Graph is thin — a handful of bars	default 1 sample/sec/thread	re-run with `query_profiler_*_period_ns = 10000000`
"No trace data found"	query finished before a snapshot fired	longer query, or a higher sampling rate
Empty graph on a specific trace type	that collector is off by default	see the trace-type table above
Query missing from the picker	trace data not flushed yet	`SYSTEM FLUSH LOGS;`
Bars show raw hex addresses	no debug symbols on the server	install `clickhouse-common-static-dbg`; only new traces resolve
Empty query list	nothing traced in that window	widen the range (up to 24h)

Four habits worth forming

1. Raise the sampling rate before you profile

More snapshots, better picture.

This single setting is the difference between a useless graph and a useful one.

2. Profile something heavy

A query finishing in milliseconds leaves almost nothing to sample, whatever the rate.

3. Always compare CPU against Real

Fastest way to tell computation from waiting.

4. Take a before and after

Generate a graph, add the index or projection, generate again.

The change in bar widths is your proof the fix worked — a far better artifact for a PR than "feels faster now."

That last one is the real value.

Flame graphs get sold as a diagnosis tool, but they're just as good as a verification tool.

Optimisation without a before-and-after is guessing.

Try it

CHOps is free and open source.

GitHub: https://github.com/Quantrail-Data/CH-Ops
Docs: https://www.ch-ops.io/docs/guide/query-profiler
Docker: https://hub.docker.com/r/quantrailadmin1/ch-ops

Run one slow query with the sampling rate turned up.

Profile it.

Look at the widest bar.

You'll know more in thirty seconds than in an hour of reading EXPLAIN output.

CHOps SQL Editor: A Complete Guide to Faster ClickHouse® Query Development and Optimization

Kanishga Subramani — Fri, 04 Sep 2026 06:13:08 +0000

What is CHOps?

CHOps is a browser-based operations platform built for managing ClickHouse® deployments. Instead of relying entirely on the command line or HTTP APIs, it provides a unified interface for executing SQL queries, monitoring clusters, managing users, backups, alerts, dashboards, and much more — all from a single web application.

One of its most powerful features is the SQL Editor, designed to simplify the entire query development experience.

Introducing the CHOps SQL Editor

When working with ClickHouse®, writing SQL is only one part of the job. Developers and Data Engineers often spend just as much time browsing schemas, remembering table names, checking execution plans, estimating query costs, and debugging performance issues.

With traditional SQL editors, these tasks usually involve switching between multiple tools or browser tabs. That constant context switching slows development and makes query optimization more difficult.

The CHOps SQL Editor is designed to solve these challenges by bringing everything into a single workspace. Whether you're exploring a new dataset, optimizing analytical queries, or troubleshooting production workloads, this editor provides all the tools needed to build, analyze, and optimize queries without leaving the page.

Let's explore how these features streamline the ClickHouse® query development workflow and performance.

The SQL Editor at a Glance

The SQL Editor is organized into four main areas, each designed for a specific part of the query workflow.

Left: Schema Explorer for browsing databases and tables.
Top of the center: Query Tabs for working on multiple queries simultaneously.
Center: The SQL Editor and its toolbar where you write SQL, choose how to run it, and reach the history, bookmarks, share, export, fullscreen, and AI controls.
Bottom: The Results Panel for viewing results, execution statistics, and debugging information.

This layout keeps everything within reach, eliminating the need to jump between multiple applications while developing queries.

Connecting First

The SQL Editor allows direct authentication using ClickHouse® credentials.

Simply enter your:

Username
Password

Once authenticated, the SQL editor connects securely and unlocks schema browsing, query execution, and analysis features. This design separates application login from database authentication, improving security and flexibility.

Built-in Database Explorer

Once connected, the first place you'll likely visit is the Schema Explorer.

The Schema Explorer lets you browse your ClickHouse® databases and tables directly from the left panel. Expand a database to view its tables, then select a table to insert its fully qualified name into the SQL editor.

Need to check the table structure first? The code icon beside each table opens its complete CREATE TABLE statement, making it easy to review columns, sorting keys, and table engines without leaving the editor.

For databases where AI assistance is needed, the sparkles icon provides quick access to AI SQL generation. We'll explore that workflow later in Generating SQL with AI.

Smart SQL Autocomplete

One of the biggest productivity boosters of the SQL Editor is Autocomplete.

As you type:

SELECT

the editor automatically suggests:

SQL keywords
ClickHouse® functions
Database names
Table names
Column names

Unlike a generic SQL editor, these suggestions come directly from your connected ClickHouse® cluster. Instead of showing generic SQL objects, the editor suggests only the databases, tables, functions, and columns that actually exist on your server.

Autocomplete reduces syntax mistakes while making query writing significantly faster.

Example

Instead of remembering:

SELECT * FROM sales.customer_transactions_archive

you simply type:

SELECT * FROM sales.

and choose the required table from the suggestion list.

Autocomplete also displays function signatures and descriptions, making it much easier to discover ClickHouse® functions without switching to external documentation.

Running Your Query

Once your query is ready, executing it is straightforward.

Simply click Go, or use Ctrl + Enter.

The action performed depends on the selected execution mode. You can either execute the SQL directly or choose one of the available EXPLAIN modes to analyze the query before running it.

Understand Queries with EXPLAIN

Understanding how ClickHouse® executes a query is often more valuable than simply seeing the result.

Normally, checking a query plan requires manually prefixing every query with EXPLAIN, executing it, reviewing the output, and then removing the prefix before running the actual query.

The CHOps SQL Editor simplifies this workflow. Simply select an EXPLAIN mode from the dropdown beside the Go button and execute the query. No query rewriting is required.

Explain mode	What it shows
Explain & Explain plan	Displays the execution plan in order
Explain syntax	Your query after ClickHouse®'s internal rewrites
Explain query tree	The analyzed, internal form of the query
Explain pipeline	The actual processors that will do the work
Explain estimate	Estimated rows, parts, and marks to be read
Explain AST (graph)	Visualizes the parsed query structure
Explain pipeline (graph)	Displays the execution pipeline as an interactive graph
Explain plan (JSON)	Returns the execution plan in JSON format

AI-Powered SQL Generation

Not every query needs to start from scratch. Sometimes you know what information you need but not the exact SQL syntax.

The Generate SQL feature helps convert natural language into SQL queries.

Example prompt:

Show the top 10 products by revenue during the last month.

The generated SQL can then be reviewed, modified, and executed like any manually written query, making it useful for rapid prototyping and learning new schemas.

The Debugging Tools Around the Query

A few smaller features exist specifically to shorten the time between "something feels slow" and "here is why":

Estimate Query Cost

Before executing an expensive query, it's often useful to understand how much work ClickHouse® is expected to perform.

The Cost feature estimates:

Rows to be read
Parts to be scanned
Marks to be processed
Available indexes

without actually executing the query.

This provides a quick way to identify potentially expensive queries before they impact your cluster.

Max Rows Control

Max Rows caps how many rows come back to the browser, so an accidental unbounded query does not lock up the tab while you are trying to debug something else.

When larger datasets are required, the limit can be adjusted or the results can be exported instead.

Action Buttons After a Run

Once a query finishes and ClickHouse® has assigned it a query ID, a row of buttons appears in the statistics bar to take you straight into the profiling tools for that exact query:

Query_id: copies ClickHouse®'s ID for the query to your clipboard, useful for looking it up in system tables or handing to whoever administers the cluster.
Flame Graph: opens the Query Profiler with this query loaded, showing where it spent its time. Reach for this first when something is slow.
Pipeline: opens the Processors Profile with the query loaded, rendering its execution as a diagram so you can see which step dominated.
Metrics: opens Query Metrics with the query loaded, showing a second-by-second view of how it used resources while it ran.

History

History keeps every query you have run, with its timing and success or failure.

Instead of rewriting or searching through old SQL files, you can quickly reopen previous queries, review execution results, and continue where you left off.

History keeps your most recent queries and drops the oldest as new ones arrive. The Clear button empties it.

Bookmarks

Some queries become part of your daily workflow.

Bookmarks let you save the ones you check often, like a recurring health check query, so you are not retyping the same debugging query every day.

Share Option

Need a second opinion on a query?

The Share feature generates a link containing the current SQL, allowing teammates to open the query directly in their own SQL Editor.

Since every user reconnects using their own ClickHouse® credentials, sharing a query never grants access to your database. It simply shares the SQL itself.

Compare Query Performance

Next to the connect control is a second switch: Regular and Comparison.

Regular is the single query view described above.

Comparison splits the screen into a current query on the left and an experimental rewrite on the right, each with its own cost estimate and run button.

This is the mode built for tuning.

Paste the slow query on one side, write a rewrite on the other, and check whether the rewrite actually reduces estimated cost before you touch production.

Export Option

Once your query is complete, exporting the results is just as simple.

The SQL Editor supports exporting query results in multiple formats, including:

CSV
JSON
Parquet
etc.

Exports are processed on the server, allowing large datasets to be generated without affecting the browser session.

A Simple Workflow

A typical workflow inside the CHOps SQL Editor looks like this:

Connect to your ClickHouse® deployment.
Browse databases and tables using the Schema Explorer.
Use autocomplete to discover tables, columns, and functions.
Write your SQL query.
Use EXPLAIN to understand query execution.
Estimate query cost before running expensive workloads.
Execute the query.
Review the results and execution statistics.
Use Query Profiler, Pipeline, or Metrics when troubleshooting.
Use Comparison Mode to test query improvements.
Bookmark useful queries for future use.
Share SQL with teammates when collaboration is needed.
Export results when required.

Conclusion

Each feature in the SQL Editor solves a small part of the query development process.

Autocomplete reduces typing, EXPLAIN helps you understand execution plans, Cost estimation highlights expensive queries before execution, and Comparison Mode makes performance tuning much easier.

Individually, these features improve specific tasks.

Together, they reduce context switching, simplify query optimization, and provide a smoother development experience for anyone working with ClickHouse®.

ClickHouse® 26.8 LTS Release: What's New and Why It Matters

Kanishga Subramani — Sat, 29 Aug 2026 05:45:03 +0000

ClickHouse® 26.8 is the newest LTS release, and it is a substantial one: 21 backward-incompatible changes, 49 new features, 127 performance improvements, and around 30 settings that changed their default value.

This article covers 26.8 on its own — what landed, what breaks, and what to verify before you upgrade.

Release status at time of writing (27 August 2026). ClickHouse® 26.8 has been announced, but the release is not fully published yet and the upstream changelog still marks the 26.8 section as in progress. Confirm the current release status before you plan an upgrade window.

At a glance
Breaking changes
- Ingestion and the write path
- Type and function semantics
- Query planning and output
- Security and configuration
- Removals
- Monitoring and introspection
Default settings that changed in 26.8
- Behaviour
- Security
- Performance (enabled by default)
- MergeTree - on-disk formats
What's new
- SQL surface
- Server and operations
- Observability
- Joins and text search
- Data lakes
- AI functions
Performance
Upgrade checklist
Summary

At a Glance

Category	Entries
Backward incompatible changes	21
New features	49
Experimental features	48
Performance improvements	127
Improvements	138
Bug fixes	556

Breaking Changes

All 21 breaking changes, grouped by what they affect.

Ingestion and the Write Path

`max_insert_threads` now defaults to `auto`

It resolves to the number of CPU cores available to the server, parallelising INSERT SELECT by default. It can also parallelise the writing side of a plain INSERT where the destination write path can safely fan out.

Two consequences worth planning for: the number of parts created by such queries changes, and so does the order of inserted rows.

If you have tight parts_to_throw_insert thresholds, or anything depending on insertion order — a non-deterministic ORDER BY tie-break, _part or _block_number assumptions, a ReplacingMergeTree without a proper version column — test this before rolling out.

A detail that catches people diffing configurations: the declared default is 0 in both 26.7 and 26.8.

What changed is the setting's type, from UInt64 to MaxThreads.

Under UInt64, both 0 and 1 meant single-threaded; under MaxThreads, 0 resolves to auto.

So the value column in system.settings reads 0 before and after, while the behaviour has flipped.

Upstream's own compatibility mapping records the change as 1 → 0 for this reason — 1 is what you now set to get the old behaviour, not what the declaration used to say.

Restore with:

SET max_insert_threads = 1;

Or use a compatibility version below 26.8.

Lightweight `UPDATE` patch parts use a new v2 on-disk format

They are now sorted by:

(sorting_key..., _block_number, _block_offset)

and applied with a new merging algorithm.

Peak memory during apply is bounded by the largest equal-sort-key run rather than the full patch, and updates crossing merge boundaries no longer fall back to an in-memory Join apply.

Old-format patch parts remain readable.

For replicated clusters this requires a rolling-upgrade pin.

Note that patch_parts_version is a MergeTree setting rather than a session setting:

<merge_tree>
    <patch_parts_version>v1</patch_parts_version>
</merge_tree>

Or:

ALTER TABLE my_table MODIFY SETTING patch_parts_version = 'v1';

Hold that until every replica is on 26.8, then remove it.

Object-storage disk transactions

Object-storage disk transactions now use the metadata storage's native transactions by default instead of the previous fake transactions.

The upstream changelog does not explain why this is listed as backward-incompatible, so treat it as worth testing if you run object-storage disks.

`disable_insertion_and_mutation` changes

disable_insertion_and_mutation now also stops background consumption from Kafka, RabbitMQ and NATS tables, while still permitting direct writes to external storage.

Gated Kafka2, NATS and RabbitMQ tables no longer initialise consumers for a direct SELECT.

Separately, message_queue_disable_insertion now requires a server restart to take effect.

Type and Function Semantics

Unquoted JSON numbers are now Unix timestamps

In JSONEachRow and similar formats, an unquoted number for a DateTime or DateTime64 column is read as a Unix timestamp with optional sub-second precision, consistent with Values, CAST and toDateTime64.

Previously a fractional value such as:

1703363853.035

was rejected outright, and a bare integer such as:

1703363853

was read as the raw scaled value of DateTime64, producing a 1970 timestamp.

Quoted strings and ClickHouse®'s own JSON output are unaffected.

For most pipelines this is a silent fix.

If yours was compensating for the old raw-value behaviour, it will now double-correct.

Restore with:

SET input_format_read_datetime_number_as_raw_value = 1;

`Date32` range extended

The Date32 range is extended from:

[1900-01-01, 2299-12-31]

to:

[0000-01-01, 9999-12-31]

matching DateTime64.

Parsing and conversions accept the extended range instead of silently clamping.

The compatibility note matters: in toDate32(N), values in:

[120530, 2932896]

are now interpreted as day numbers — dates from 2300-01-01 to 9999-12-31 — rather than Unix timestamps in early 1970.

Numbers below the day number of 0000-01-01, and timestamps after 9999-12-31, saturate to the new boundaries.

`arrayIntersect` and `arraySymmetricDifference` deduplication fixed

A value repeated inside a single argument is no longer treated as though it appeared in several arguments.

A value now counts for an argument only when it was present in every argument before it, so the result contains exactly the values present in all arguments.

Examples:

SELECT arrayIntersect([1], [2], [1, 1]);
-- []

SELECT arrayIntersect([1, 2], [2], [1, 1, 2]);
-- [2]

SELECT arraySymmetricDifference([1], [2], [1, 1]);
-- [2, 1]

SELECT arraySymmetricDifference([1], [2, 2]);
-- [2, 1]

arrayUnion is unaffected.

As part of the same change, arrayIntersect builds its hash table from the smallest argument rather than all of them — up to 1.85x faster and a third less memory when argument sizes differ significantly.

Window functions over `AggregateFunction` columns are rejected

A window PARTITION BY or ORDER BY over an AggregateFunction column now raises ILLEGAL_COLUMN, as top-level ORDER BY over such a column already did.

Previously at least one analyzer accepted it, and window PARTITION BY partitioned differently depending on max_threads.

The refusal covers states nested in Array, Tuple, Map, Variant or SimpleAggregateFunction.

A SimpleAggregateFunction over an ordinary type, QBit, and GROUP BY or DISTINCT over a state are all unaffected.

Timespan settings that overflow `Int64` microseconds are rejected

This applies to millisecond and second settings.

Query Planning and Output

Trivial views over `Distributed` tables are pushed to the shards

For a view whose body is a plain SELECT over a single Distributed table, the whole outer query now goes to the shards.

This is the new:

optimize_trivial_view_pushdown_to_distributed

setting, enabled by default.

Observable behaviour changes in two ways:

FINAL and SAMPLE written on the view reference are now propagated to the shard-local table instead of being ignored.
extremes is not reported on single-shard clusters.

If you had views where FINAL was silently a no-op, results will change.

Set the setting to 0 to restore the previous behaviour:

SET optimize_trivial_view_pushdown_to_distributed = 0;

`EXPLAIN SYNTAX` returns a single record

The reformatted query comes back as one String value with embedded newlines rather than one record per line.

So:

SELECT count() FROM (EXPLAIN SYNTAX ...);

returns:

To restore the old behaviour:

SET explain_syntax_single_record = 0;

Other EXPLAIN kinds — PLAN, PIPELINE, AST — keep their per-line tree output.

Security and Configuration

A clear theme this release: the server no longer opens filesystem paths supplied from SQL, because it opens them with its own privileges.

MySQL source TLS credentials

MySQL source TLS credentials:

ssl_ca
ssl_cert
ssl_key

can no longer be given as file paths from SQL — not in CREATE NAMED COLLECTION, query arguments, or CREATE DICTIONARY.

Paths remain supported in the server configuration file.

Elsewhere, pass the contents via the new:

ssl_ca_pem
ssl_cert_pem
ssl_key_pem

parameters, which are masked in logs and SHOW output the way passwords are.

NATS credentials move inline

The new nats_credentials setting takes the same payload as a .creds file.

nats_credential_file is no longer accepted from SQL.

It can only be set in:

a named collection defined in the server configuration, or
nats.credential_file in the server configuration itself.

A query may replace a configured path with inline nats_credentials unless the operator pinned it with:

<nats_credential_file overridable="false">

Tables created before the restriction keep working.

PostgreSQL database engines respect `remote_url_allow_hosts`

The PostgreSQL and MaterializedPostgreSQL database engines now honour remote_url_allow_hosts, as the table engine, table function and DDL-created dictionaries already did.

With it configured, CREATE DATABASE and user-issued ATTACH DATABASE pointing at disallowed hosts fail with:

UNACCEPTABLE_URL

Existing databases still load at startup.

`SYSTEM ... CACHE ON CLUSTER` privilege checks are granular

Each command now uses its own privilege rather than the SYSTEM DROP CACHE group.

This lets a holder of a single granular cache privilege run its matching command, and prevents a holder of the group from running:

SYSTEM SYNC FILESYSTEM CACHE ON CLUSTER

without:

SYSTEM SYNC FILESYSTEM CACHE

`include_from` no longer defaults to `/etc/metrika.xml`

That file was previously used for configuration substitutions whenever it existed, even with nothing in the ClickHouse® configuration referring to it.

If you relied on it, add the element explicitly:

<include_from>/etc/metrika.xml</include_from>

Separately loaded users.xml and XML dictionary configs each need their own include_from element.

Removals

The `library` dictionary source is gone

SOURCE(LIBRARY(...))

now fails with:

UNKNOWN_ELEMENT_IN_CONFIG

The dictionaries_lib_path server setting is obsolete with no effect.

Apache Arrow library-based reader and writer removed

The Apache Arrow library-based reader and writer for Arrow and ArrowStream are removed.

The native ClickHouse® implementation, default since 26.7, is now the only one.

The following settings are still accepted but have no effect:

input_format_arrow_use_native_reader
output_format_arrow_use_native_writer

So a query that set them to 0 to force the Apache Arrow path now silently uses the native one.

Experimental `ALP(STD)` codec changes

The experimental ALP(STD) codec now performs Float32 scaling arithmetic in Float64.

This improves compression ratios and eliminates exception-heavy compression of decimal data, but Float32 values written by earlier versions may decode 1 ULP differently.

Monitoring and Introspection

Asynchronous metrics can now be `Map`-typed

Asynchronous metrics can now be Map-typed, and the per-CPU-core and per-device metrics were consolidated.

For example:

OSUserTimeCPU0
OSUserTimeCPU1
...

became a single:

OSUserTimeCPU

metric holding a map from core number to value.

The same applies to the other:

OS*TimeCPU*
CPUFrequencyMHz_*
Temperature*
EDAC*
Block*_*
Network(Receive|Send)*_*
Disk*_*
*BlobsQueueEstimate
AsyncLogging*QueueSize

metrics.

Downstream effects:

system.asynchronous_metrics gains key_values Map(LowCardinality(String), Float64).
The value column is NaN for these metrics.
system.asynchronous_metric_log logs one row per key via a new key column.
The Prometheus endpoint exports them with a label, for example:

ClickHouse®AsyncMetrics_BlockReadBytes{device="sda"}

Graphite's MetricsTransmitter sends them as:

<prefix>.<Metric>.<key>

This is the change most likely to break existing dashboards.

Panels keyed on the old metric names will not error — they will simply return nothing, which is easy to miss.

`system.users.valid_until` changed type

system.users.valid_until changed type from:

Array(DateTime)

to:

Array(DateTime64(0))

so deadlines beyond the year 2106 are represented exactly.

Tooling reading this column needs to handle the new type.

This arrived alongside a new:

VALID FOR <interval>

clause on CREATE USER and ALTER USER.

It is a shorthand for VALID UNTIL, where the deadline is computed at query execution time and stored in VALID UNTIL form.

Default Settings That Changed in 26.8

Around 30 core settings and several MergeTree settings changed their defaults.

These do not appear in the breaking-change list but will change behaviour on upgrade.

Behaviour

Setting	Previous	New
`max_insert_threads`	`1` - single-threaded	`auto` - all available cores
`input_format_read_datetime_number_as_raw_value`	`true`	`false`
`optimize_trivial_view_pushdown_to_distributed`	-	`true`
`explain_syntax_single_record`	`false`	`true`
`filesystem_cache_wait_for_concurrent_download_timeout_milliseconds`	`60000`	`1000`

Security

Setting	Previous	New
`ai_function_allow_insecure_endpoint`	`true`	`false`
`ai_function_max_api_calls_per_query`	`0` (unbounded)	`1000`

Performance (Enabled by Default)

The following performance improvements are now enabled by default:

enable_adaptive_aggregator
enable_group_by_top_k_optimization
enable_packed_string_keys_in_aggregation
enable_parallel_single_level_merge
read_in_order_use_virtual_row
query_plan_push_down_volume_reducing_functions
query_plan_short_circuit_constant_false_join
use_query_condition_cache_for_top_k
allow_distinct_partitions_independently
allow_window_partitions_independently
allow_creating_set_partitions_independently
optimize_trivial_count_with_sparsity_filter
input_format_parquet_spatial_filter_push_down
query_plan_optimize_count_from_text_index
materialize_statistics_on_insert

The last setting has a 25 GiB table-size cap.

MergeTree - On-Disk Formats

Setting	Previous	New	Compatibility
`text_index_serialization_version`	`v1_with_codec`	`v2_with_positions`	Older servers cannot read the new format. Pin to v1 during a rolling upgrade
`packed_skip_index_max_bytes`	`0`	`1 MiB`	New parts only; still readable by older servers, though pre-26.6 ignores packed indices for pruning
`compute_exact_num_defaults_for_sparse_columns`	`false`	`true`	The flag in `serialization.json` is ignored by older versions, so parts survive downgrade
`text_index_posting_list_apply_mode`	`materialize`	`lazy`	Posting lists decoded on demand
`text_index_max_memory_usage_before_flush`	unlimited	`1 GiB`	Memory-based flush trigger for index builders

One protocol-level note: the native protocol changed how String columns are transmitted — a separate stream of cumulative byte offsets followed by concatenated data — once both peers are on revision 54489 or later.

This is around 4x faster for client-side reads.

It is negotiated by protocol revision, so old clients and servers are unaffected, but maintainers of custom native-protocol clients should handle the new revision.

What's New

SQL Surface

Pipe operators

GoogleSQL-style |> chaining is now supported.

Each pipe wraps the preceding query in a subquery, so the resulting AST matches the nested equivalent.

Example:

FROM events
|> WHERE status = 'active'
|> AGGREGATE count() AS total GROUP BY user_id
|> ORDER BY total DESC
|> LIMIT 10;

In a query starting with FROM, SELECT is now optional and defaults to:

SELECT *

`GROUPS` window frame mode

GROUPS is a SQL:2011 window frame mode.

Frame boundaries count whole peer groups rather than physical rows or value distances, completing the set alongside ROWS and RANGE.

Query AST as JSON

New functions:

parseQueryToJSON
formatQueryFromJSON

An experimental ClickHouse®_json dialect is also available behind:

enable_json_ast_dialect

Also new:

arr[indexes] for subscripting an array with an array of positions
notHas
gini
mergedJSONPatch

Server and Operations

SQL-defined HTTP handlers

CREATE HANDLER, ALTER HANDLER and DROP HANDLER define custom HTTP endpoints from SQL.

Handlers can be persisted locally or in Keeper.

Supporting additions include:

currentHandler()
currentRequestURL()
system.handlers
http_handler_name in system.query_log
http_request_url in system.query_log

For teams maintaining small API services that exist only to expose a query over HTTP, this is worth evaluating.

`run_query_in_background`

The server accepts the query, returns immediately, and runs it to completion regardless of what happens to the connection.

Track it by query_id in:

system.processes
system.query_log

This is aimed at long-running operations such as:

INSERT SELECT
CREATE TABLE AS SELECT
POPULATE

Atomic `POPULATE`

A plain:

CREATE MATERIALIZED VIEW ... POPULATE

is now locally atomic, controlled by:

materialized_views_populate_atomically

and enabled by default.

Rows inserted through the same server during population are no longer missed or duplicated.

This applies to the local insert path only and requires a snapshot-capable source — the MergeTree family or Memory.

CREATE OR REPLACE, REPLACE, and Replicated databases keep the legacy path.

POPULATE also works with TO now.

Introspection port

A native-protocol TCP listener starts before tables attach and stops after detach completes.

This makes:

SHOW PROCESSLIST

and:

system.stack_trace

available during startup and shutdown.

Alongside it, shutdown_wait_unfinished moved from 5 seconds to 120 seconds.

The old default was shorter than the connection poll interval.

Keeper on-disk storage

Keeper now supports on-disk storage via a custom LSM tree.

Enable with:

use_lsmt_storage = true
storage_memory_only = false

Configure through:

data_storage_path

or:

data_storage_disk

This can point at an s3_plain disk for S3.

The Keeper dashboard also gains a Cluster tab showing Raft membership as a topology graph.

HTTP URL-path access to tables

Tables can now be accessed through HTTP URL paths such as:

/database/table.format.gz?filter=a>0

This is behind a set of http_allow_* opt-ins.

Framing output formats

framing_output_format multiplexes:

data chunks
totals and extremes
progress
profile events
logs
exceptions

into a single HTTP stream.

Available formats include:

EventStream
JSONEachPacketBase64
JSONEachPacketString

Also new:

default_session_user as a server setting
Prometheus constant labels via a <labels> element inside <prometheus>
ALTER TABLE ... MODIFY PROJECTION to change projection settings without a rebuild

Projection changes are applied lazily via merges.

Observability

Several useful observability improvements landed in 26.8:

`system.user_query_log`

Every user sees their own query log rows without needing access to:

system.query_log

`create_union_system_log_tables`

This creates auto-maintained all_... tables such as:

system.all_query_log

These union:

a log table
its rotated versions
the same table across cluster replicas

remote, remoteSecure, cluster and clusterAllReplicas now accept a trailing SETTINGS clause.

`system.mutations.finish_time`

Provides mutation duration without having to infer it.

`system.tables.skipping_indices_types`

Provides a cheap summary of which index types a table uses.

Play UI

The Play UI now supports:

server-side sorting
filtering
paging

These settings are encoded in the page URL so a shared link reproduces the result.

Joins and Text Search

`IEJoin`

IEJoin is a sort-based algorithm for ON clauses containing two inequality comparisons.

Previously such joins ran as a CROSS JOIN with a filter, and only as INNER.

Enable by adding:

ie_join

to:

join_algorithm

`parallel_full_sorting_merge`

parallel_full_sorting_merge shards a full sorting merge join by join-key hash across threads.

Upstream benchmarks put it around:

2.4x faster
3.3x lighter on memory

than parallel_hash, while keeping streaming memory behaviour.

The result is unordered.

New text index tokenizers

New tokenizers include:

japanese — MeCab
chinese — jieba-style dictionary plus HMM
icu — locale-aware Unicode segmentation
splitByRegexp — keeps tokens such as C++ and C# intact

Data Lakes

A number of data-lake integrations have been expanded.

BigQuery

A bigquery table function and BigQuery table engine are now available.

Authentication supports:

OAuth token
service account JSON
refresh token

Snowflake Horizon

Snowflake Horizon catalog support is available for reading and writing Iceberg.

S3 Tables

The S3 Tables catalog now supports:

INSERT

Puffin

Puffin file format support has been added.

URL database engine

A URL database engine and s3_base setting complete the URL unification.

ClickHouse®-local's default database now uses it with a file:// base.

This means:

SELECT * FROM 'https://example.com/data.csv'

works directly.

AI Functions

The experimental:

aiSimilarity
aiFilter
aiRedact

functions were hardened in this release.

Insecure http endpoints to remote hosts are denied by default.

Outbound calls per query are bounded at:

Provider error responses are also sanitised before logging.

Performance

ClickHouse® 26.8 contains 127 performance entries, with the majority enabled by default.

The improvements with the broadest reach include:

Aggregation

A new adaptive parallel GROUP BY allows each thread to aggregate into its own cache-resident hash table until it hits a threshold, then freezes it.

Other aggregation improvements include:

bounded-heap pruning for GROUP BY ... ORDER BY ... LIMIT
smaller hash-table cells for single-String keys
parallelised final merge of single-level tables
aggregations without aggregate functions now use HashSet rather than HashMap

The latter can be up to 1.8x faster.

Reads

Lazy materialization for Parquet on object storage can significantly reduce I/O.

For example, on a 200 MB S3 file:

ORDER BY ... LIMIT 10

read:

3.3 MB

instead of:

171 MB

That represents:

51x less I/O
8x faster

read_in_order_use_virtual_row is enabled by default and reduces peak memory when reading in primary key order across many parts.

The Parquet V3 reader also gains dictionary-page-based row group skipping.

Per-Partition Processing

DISTINCT, window functions and IN (subquery) set building can now keep each partition's rows in a single stream when the partition expression is a deterministic function of the relevant columns.

This skips the hash scatter entirely.

The Important Trade-Off

The trade-off worth stating plainly:

Query plans and resource usage will change after upgrading, even for queries you did not touch.

Most workloads benefit, but plan for a period of observation rather than assuming parity.

Upgrade Checklist

Before Upgrading

Check for dictionaries using the removed library source:

SELECT name, source
FROM system.dictionaries
WHERE source ILIKE '%library%';

Check non-default settings you are carrying:

SELECT name, value, default
FROM system.settings
WHERE changed;

And:

SELECT name, value
FROM system.server_settings
WHERE changed;

Then check by hand:

Named collections and dictionaries using MySQL ssl_ca / ssl_cert / ssl_key paths
NATS tables or collections using nats_credential_file
Reliance on the implicit /etc/metrika.xml substitutions file
PostgreSQL / MaterializedPostgreSQL databases against hosts outside remote_url_allow_hosts
Ingestion clients writing unquoted epoch numbers into DateTime64 columns via JSONEachRow
Queries using toDate32 on epoch-second values
Anything parsing EXPLAIN SYNTAX output
Tooling reading system.users.valid_until
Dashboards keyed on individual per-CPU or per-device asynchronous metric names

During a Rolling Upgrade

Pin:

patch_parts_version = 'v1'

and:

text_index_serialization_version = 'v1_with_codec'

until every replica is on 26.8.

Then remove both pins.

After Upgrading

Watch part counts and merge queue depth in:

system.parts
system.merges

for the max_insert_threads effect.

Also monitor:

system.errors

for:

UNACCEPTABLE_URL
ILLEGAL_COLUMN
UNKNOWN_ELEMENT_IN_CONFIG
BAD_ARGUMENTS

These cover most of the new rejections.

If you want to stage the transition:

SET compatibility = '26.7';

This restores the majority of the default flips in one move, letting you upgrade the binaries first and enable the new behaviour deliberately afterwards.

It does not cover removals or type changes — those need code fixes, which the checks above should surface.

Summary

The three changes most likely to affect a running deployment are:

1. `max_insert_threads` defaulting to `auto`

This changes part creation patterns and insert row order on every:

INSERT SELECT

2. Asynchronous metric consolidation

This can break dashboards silently rather than loudly.

3. Patch parts v2 format

This requires a rolling-upgrade pin on replicated clusters.

Beyond that, 26.8 is a strong release.

The security tightening around credential handling is overdue and welcome.

The operational additions — SQL-defined handlers, background queries, atomic POPULATE, and the introspection port — address real gaps.

And the 127 performance improvements, mostly enabled by default, represent a meaningful return on the upgrade work.

The key lesson is simple:

Treat ClickHouse® 26.8 as a real upgrade project, not just a version bump.

Review the defaults, test workload behaviour, validate observability, and plan the rollout carefully.

Install CH-Ops in 10 Minutes: Docker, Binary, or Source

Kanishga Subramani — Sat, 15 Aug 2026 15:29:16 +0000

Getting started with a new database operations tool shouldn't take hours of setup.

CH-Ops gives you three installation options depending on how you prefer to work:

Docker — the fastest way to get started.
Standalone Binary — ideal for production deployments.
Build from Source — perfect if you want to contribute or customize the application.

In this guide, you'll learn how to install CH-Ops using all three approaches, configure it, run it as a Linux service, troubleshoot startup issues, and connect it to your ClickHouse® deployment.

What Is CH-Ops?

CH-Ops is a browser-based operations platform for ClickHouse®. Instead of relying entirely on the command line or HTTP API, it provides a visual interface for managing your ClickHouse deployment.

It provides functionality for:

Running SQL queries
Monitoring cluster health
Managing users
Managing backups
Configuring alerts
Exploring dashboards
Managing ClickHouse clusters

CH-Ops stores its own configuration in a local SQLite database. It does not modify your ClickHouse data unless you explicitly execute queries against your cluster.

Choose Your Installation Method

Method	Best For	Approximate Setup Time
Docker	Fastest setup, testing, local development	~5 minutes
Standalone Binary	Production deployments	~5 minutes
Build from Source	Development and customization	~10 minutes

Let's look at each option.

Option 1: Install with Docker

If you already have Docker installed, this is the quickest way to run CH-Ops.

Step 1: Clone the Repository

Clone the CH-Ops repository:

git clone https://github.com/Quantrail-Data/CH-Ops.git
cd CH-Ops

Step 2: Create Your Configuration

Copy the example environment file:

cp .env.example .env

Update the required values in .env:

SUPER_ADMIN_1=admin
SUPER_ADMIN_1_PASSWORD=your_secure_password_here
SUPER_ADMIN_1_EMAIL=you@example.com
SESSION_SECRET=paste_a_random_string_here

Generate a secure session secret with:

openssl rand -hex 32

Make sure the required environment variables are configured before starting the application.

Step 3: Start CH-Ops

Start CH-Ops using Docker Compose:

docker compose up -d --build

Once the container has started, open:

http://localhost:3000

You should now see the CH-Ops login page.

That's it.

Why Docker?

Docker is a convenient option because it provides:

Minimal setup
No need to install Bun separately
Persistent SQLite storage
Easy upgrades
A consistent runtime environment
A quick way to test CH-Ops

Remember to configure all required environment variables before starting the container.

Option 2: Install Using the Standalone Binary

A standalone binary is a good choice when you want to deploy CH-Ops directly on a server without running it through a development environment.

CH-Ops can be packaged as a single executable with the application and its dependencies included.

Prebuilt binaries can be provided for platforms such as:

Linux
macOS
Windows

If you need to build the binary yourself, follow the steps below.

Step 1: Install Bun

CH-Ops is built using Bun.

Install the required Bun version:

curl -fsSL https://bun.com/install | bash -s "bun-v1.3.13"

Verify the installation:

bun --version

Step 2: Clone the Repository

git clone https://github.com/Quantrail-Data/CH-Ops.git
cd CH-Ops

Step 3: Install Dependencies

Install the project dependencies:

bun install

Step 4: Build a Standalone Binary

Build a binary for your current platform:

bun run build:binary

You can also cross-compile for specific platforms:

bun run build:binary:linux

bun run build:binary:mac

bun run build:binary:windows

Depending on the target, the generated files can look like:

chops-linux-x64
chops-darwin-arm64
chops-windows-x64.exe

During the build process, the Vite build compiles the React frontend into static assets under dist/. Bun then packages the backend, dependencies, and frontend assets into the standalone executable.

Run the Binary

For Linux, make the binary executable:

chmod +x chops-linux-x64

Then configure the required environment variables:

export SUPER_ADMIN_1=admin
export SUPER_ADMIN_1_PASSWORD=your_secure_password
export SUPER_ADMIN_1_EMAIL=you@example.com
export SESSION_SECRET=your_secure_session_secret

Run CH-Ops:

./chops-linux-x64

Generate a secure session secret with:

openssl rand -hex 32

CH-Ops automatically creates its internal SQLite database when it starts.

Open the application at:

http://localhost:3000

You can now sign in using your configured administrator credentials.

Why Choose the Binary?

A standalone binary is useful when you want:

A single executable
Minimal runtime dependencies
Easy server deployment
Simple distribution
A production-oriented installation

If you need to customize the application or contribute code, building directly from source is the better option.

Running CH-Ops as a Linux systemd Service

When deploying CH-Ops on a Linux server, manually starting the application after every reboot isn't ideal.

Instead, you can run CH-Ops as a systemd service.

systemd is the default service manager on many modern Linux distributions. It can:

Start CH-Ops automatically during boot
Restart the application if it crashes
Run it under a dedicated user
Manage permissions
Centralize application logs through journald

Step 1: Create a Dedicated Service User

For security, avoid running CH-Ops as root.

Create a dedicated system user:

sudo useradd --system --no-create-home --shell /usr/sbin/nologin chops

Then give the service user ownership of the CH-Ops installation:

sudo chown -R chops:chops /opt/chops

The chops account exists only to run the application and cannot be used for interactive login.

Step 2: Create the systemd Service File

Create a service definition:

sudo nano /etc/systemd/system/chops.service

Add:

[Unit]
Description=CH-Ops - ClickHouse Administration Dashboard
Documentation=https://github.com/Quantrail-Data/CH-Ops
After=network.target

[Service]
Type=simple
User=chops
Group=chops
WorkingDirectory=/opt/chops

ExecStart=/opt/chops/chops

Restart=on-failure
RestartSec=5

EnvironmentFile=/opt/chops/.env

NoNewPrivileges=true
ProtectSystem=strict
ProtectHome=true
ReadWritePaths=/opt/chops/data
PrivateTmp=true

StandardOutput=journal
StandardError=journal
SyslogIdentifier=chops

[Install]
WantedBy=multi-user.target

Understanding the Service Configuration

Each setting has a specific purpose:

Setting	Purpose
`After=network.target`	Starts CH-Ops after networking is available
`User=chops`	Runs the application as a dedicated non-root user
`Restart=on-failure`	Automatically restarts CH-Ops if it crashes
`RestartSec=5`	Waits five seconds before restarting
`EnvironmentFile`	Loads configuration from `.env`
`NoNewPrivileges=true`	Prevents the process from gaining additional privileges
`ProtectSystem=strict`	Makes most of the filesystem read-only
`ReadWritePaths=/opt/chops/data`	Allows CH-Ops to write to its data directory
`PrivateTmp=true`	Provides an isolated temporary directory

These settings help reduce the potential impact of accidental changes or application vulnerabilities while keeping the service manageable.

Step 3: Enable and Start the Service

Reload systemd:

sudo systemctl daemon-reload

Enable CH-Ops at boot:

sudo systemctl enable chops

Start the service:

sudo systemctl start chops

Check its status:

sudo systemctl status chops

If everything is configured correctly, you should see:

Active: active (running)

Troubleshooting Startup Issues

If CH-Ops fails to start, the system journal is usually the first place to look.

Follow the service logs in real time:

sudo journalctl -u chops -f

This lets you see startup errors and configuration problems as they happen.

You can also inspect recent logs:

sudo journalctl -u chops -n 50

Common systemd Commands

Once CH-Ops is running as a systemd service, these are the commands you'll use most often.

Start:

sudo systemctl start chops

Stop:

sudo systemctl stop chops

Restart:

sudo systemctl restart chops

Check status:

sudo systemctl status chops

View recent logs:

sudo journalctl -u chops -n 50

Follow logs in real time:

sudo journalctl -u chops -f

Logging in CH-Ops

CH-Ops writes structured JSON logs to standard output.

When running under systemd, these logs are automatically captured by journald.

A log entry can contain information such as:

Timestamp
Log level
HTTP method
Request path
Response status
Request duration
User
Execution context

For example:

{
  "ts": "2026-05-18T10:30:00.000Z",
  "level": "info",
  "msg": "GET /api/alerts/rules 200 12ms",
  "ctx": {
    "method": "GET",
    "path": "/api/alerts/rules",
    "status": 200,
    "duration": 12,
    "user": "admin",
    "ip": "::1"
  }
}

View Human-Readable Logs

sudo journalctl -u chops -f

View JSON Logs

sudo journalctl -u chops -o json | jq '.MESSAGE | fromjson'

View Only Errors

sudo journalctl -u chops -f | grep '"level":"error"'

View Logs from the Last Hour

sudo journalctl -u chops --since "1 hour ago"

Enable Debug Logging

For more detailed diagnostic information during development, add the following to .env:

LOG_LEVEL=debug

Supported log levels include:

debug
info — default
warn
error

CH-Ops can log API requests, scheduler activity, server startup events, and application errors while avoiding sensitive information such as passwords, authentication tokens, and request bodies.

Option 3: Build CH-Ops from Source

If you're developing new functionality, customizing CH-Ops, or contributing to the project, building from source gives you complete control.

Step 1: Install Bun

Install the required Bun version:

curl -fsSL https://bun.com/install | bash -s "bun-v1.3.13"

Verify it:

bun --version

Step 2: Clone the Repository

git clone https://github.com/Quantrail-Data/CH-Ops.git
cd CH-Ops

Step 3: Install Dependencies

bun install

Step 4: Create Your Configuration

Copy the example configuration:

cp .env.example .env

Configure the required administrator and session variables:

SUPER_ADMIN_1=admin
SUPER_ADMIN_1_PASSWORD=your_secure_password
SUPER_ADMIN_1_EMAIL=you@example.com
SESSION_SECRET=your_secure_session_secret

Generate a secure session secret:

openssl rand -hex 32

Step 5: Run Database Migrations

Initialize the application's database schema:

bun run db:migrate

Step 6: Start CH-Ops

For development:

bun run dev

The development server will be available at:

http://localhost:5173/

For a production build:

bun run build

Then start the backend:

bun src/backend/server.js

First Login

Once CH-Ops is running, open the application in your browser.

After logging in:

Navigate to Administration → Cluster Management.
Add your ClickHouse® server.
Test the connection.
Save the configuration.
Start exploring the available management and monitoring features.

Once your ClickHouse cluster is connected, you can use CH-Ops for tasks such as:

Querying your database
Monitoring cluster activity
Managing users
Managing backups
Configuring alerts
Exploring dashboards

Which Installation Method Should You Choose?

The right installation method depends on what you're trying to accomplish.

Choose Docker

Use Docker when you want:

The fastest setup
Local testing
Development environments
A consistent deployment environment

Choose the Standalone Binary

Use the standalone binary when you want:

A production-oriented deployment
A single executable
Minimal runtime dependencies
Simple server installation

Choose Build from Source

Build from source when you want:

To contribute to CH-Ops
To customize the application
To develop new features
Full control over the build process

All three approaches ultimately provide the same CH-Ops experience. The main difference is how you install and operate the application.

Conclusion

Installing a ClickHouse® operations platform doesn't have to be complicated.

With CH-Ops, you can choose the installation approach that matches your workflow:

Docker for a quick start, standalone binaries for production-oriented deployments, or source builds for development and customization.

Once CH-Ops is running, connect your ClickHouse® cluster, sign in with your administrator account, and start managing your database through a browser-based interface.

For the fastest way to get started, Docker is a good first choice. For a Linux production server, a standalone binary combined with systemd provides a straightforward deployment model.

References

Day 100 of #100DaysOfClickHouse: Optimizing Data Lake Queries with ClickHouse® 26.3 LTS

Kanishga Subramani — Sat, 25 Jul 2026 10:14:07 +0000

Introduction

Data lakes have become the backbone of modern analytics, allowing organizations to store petabytes of structured and unstructured data in cloud object storage services such as Amazon S3, Azure Blob Storage, and Google Cloud Storage. Open table formats like Apache Iceberg, Delta Lake, Apache Hudi, and Parquet have further accelerated this trend by enabling multiple analytics engines to access the same datasets without vendor lock-in or unnecessary data duplication.

While querying data directly from cloud object storage offers tremendous flexibility, it also introduces performance challenges. Network latency, metadata lookups, and large Parquet file scans can significantly slow query execution compared to locally stored data.

ClickHouse® 26.3 LTS addresses these challenges with several enhancements designed specifically for data lake workloads. These include improved parallel object storage reads, a built-in Parquet metadata cache, and asynchronous Iceberg metadata prefetching. Together, these optimizations reduce query latency and make interactive analytics over cloud-hosted datasets significantly faster.

In this article, we'll explore how ClickHouse® queries data lakes, the challenges involved, the new optimizations introduced in version 26.3 LTS, and practical examples of how these improvements benefit real-world analytical workloads.

Why Data Lake Query Optimization Matters

Modern analytics architectures increasingly separate storage from compute.

Instead of copying data into multiple databases, organizations store analytical datasets in cloud object storage and allow various query engines to access the same files directly.

This architecture provides several benefits:

Lower storage costs
Vendor-neutral open formats
Simplified data management
Multiple analytics engines sharing the same datasets

However, it also introduces several performance challenges:

Remote storage latency
Metadata lookups
Large file scans
Network overhead

ClickHouse® 26.3 introduces several optimizations that specifically target these bottlenecks.

Understanding Data Lake Queries

A data lake is a centralized repository that stores both raw and processed data using open formats, allowing multiple analytics engines to query the same datasets without duplication.

Unlike traditional data warehouses, where data is imported into proprietary storage, data lakes keep information inside cloud object storage.

Popular storage platforms include:

Amazon S3
Azure Blob Storage
Google Cloud Storage

Common open table formats include:

Apache Iceberg
Delta Lake
Apache Hudi
Parquet

Instead of importing data into MergeTree tables, ClickHouse® can query these datasets directly.

During query execution, ClickHouse:

Reads metadata
Identifies the required files
Reads only the required columns
Applies predicate pushdown where possible
Returns results without copying data into native storage

Cloud Object Storage
        │
        ▼
Parquet / Iceberg / Delta / Hudi
        │
        ▼
     ClickHouse
        │
        ▼
   SQL Analytics

This architecture eliminates duplicate storage while allowing ClickHouse® to serve as a high-performance analytical engine over existing data lakes.

Challenges of Querying Data Lakes

Although cloud object storage is highly scalable and cost-effective, querying remote datasets introduces several challenges.

Remote Storage Latency

Unlike local disks, every query must retrieve data across the network, increasing response times.

Metadata Overhead

Open table formats maintain metadata describing snapshots, manifests, partitions, and data files.

Before reading any actual data, ClickHouse® must first retrieve and process this metadata.

Large File Scans

Poor partitioning or inefficient pruning may require scanning significantly more data than necessary.

Repeated Metadata Reads

Interactive dashboards often execute the same queries repeatedly.

Without caching, ClickHouse® must repeatedly download identical metadata from remote storage.

How ClickHouse® Queries Data Lakes

ClickHouse® supports querying data directly from cloud object storage without requiring ingestion into MergeTree tables.

Supported technologies include:

Apache Iceberg
Delta Lake
Apache Hudi
Parquet files
Amazon S3 and compatible object storage

During query execution, ClickHouse® reads only the required files and columns, minimizing unnecessary I/O and enabling efficient analytics over remote datasets.

Data Lake Enhancements in ClickHouse® 26.3 LTS

Version 26.3 introduces several improvements that significantly reduce latency when querying remote object storage.

1. Faster Parallel Reading

One of the biggest improvements is enhanced parallel reading of remote data.

When a query accesses a relatively small number of large files, ClickHouse® now distributes work more efficiently across available CPU cores.

This optimization applies to:

Apache Iceberg
Delta Lake
Apache Hudi
Object storage reads

Benefits include:

Better CPU utilization
Faster remote file processing
Lower overall query execution time

For many workloads, queries become several times faster on multi-core systems.

2. Built-in Parquet Metadata Cache

Reading Parquet files requires accessing the file footer to obtain schema and metadata information.

Before ClickHouse® 26.3, repeated queries frequently reread this metadata from remote storage.

The new Parquet Metadata Cache stores footer information in memory.

Benefits include:

Reduced metadata reads
Lower remote I/O
Faster repeated queries
Improved dashboard responsiveness

The cache is enabled by default and automatically tracks object changes using file ETags to maintain consistency.

3. Asynchronous Iceberg Metadata Prefetching

Apache Iceberg maintains metadata describing snapshots, manifests, partitions, and data files.

Earlier versions often fetched this metadata during query execution, increasing planning time.

ClickHouse® 26.3 introduces asynchronous metadata prefetching.

Instead of waiting during query execution, ClickHouse® refreshes Iceberg metadata in the background and serves queries from the cache whenever possible.

Benefits include:

Reduced planning latency
Faster repeated queries
Improved dashboard responsiveness

Architecture Overview

              Data Lake
                  │
                  ▼
        Cloud Object Storage
 (Amazon S3 • Azure Blob • GCS)
                  │
                  ▼
 Apache Iceberg • Delta Lake
 Apache Hudi • Parquet
                  │
                  ▼
      ClickHouse® 26.3 LTS

   • Parallel File Reads
   • Parquet Metadata Cache
   • Iceberg Metadata Prefetch

                  │
                  ▼
 Fast SQL Analytics & Dashboards

Example 1: Querying a Parquet Dataset

SELECT
    country,
    count() AS total_events
FROM s3(
    'https://my-bucket.s3.amazonaws.com/events/*.parquet'
)
WHERE event_date >= today() - 7
GROUP BY country;

In this query, ClickHouse® reads Parquet files directly from cloud storage.

Because only the required columns are read and filtering is applied early, significantly less data must be processed.

Example 2: Querying an Iceberg Table

SELECT
    product_category,
    SUM(revenue) AS total_sales
FROM iceberg('analytics.sales')
WHERE order_date >= today() - 30
GROUP BY product_category
ORDER BY total_sales DESC;

Here, ClickHouse® uses Iceberg metadata to identify the necessary files.

The metadata cache and asynchronous prefetching reduce planning overhead while enabling efficient execution.

Real-World Example

Imagine an e-commerce company storing several years of clickstream data in Amazon S3 using Apache Iceberg.

Instead of copying terabytes of historical data into ClickHouse®, analysts query the Iceberg tables directly.

With ClickHouse® 26.3:

Enhanced parallel reading speeds up remote file processing.
The Parquet metadata cache avoids repeatedly reading footer metadata.
Iceberg metadata prefetching reduces planning latency.

As a result, dashboards become significantly faster without requiring data duplication or complex ETL pipelines.

Performance Benefits

Feature	Benefit	Why It Matters
Enhanced Parallel Reading	Faster query execution	Better CPU utilization when reading remote files
Parquet Metadata Cache	Lower query latency	Eliminates repeated metadata reads
Iceberg Metadata Prefetching	Faster query planning	Metadata is available before execution

Together, these optimizations improve:

Interactive dashboards
BI workloads
Exploratory analytics
Recurring analytical queries

Best Practices

To maximize performance when querying data lakes with ClickHouse®:

Store analytical datasets in Parquet format.
Partition datasets using commonly filtered columns.
Select only the columns required by your queries.
Apply filters as early as possible.
Take advantage of the built-in Parquet metadata cache.
Enable asynchronous Iceberg metadata prefetching for frequently queried datasets.
Keep ClickHouse® updated to benefit from ongoing data lake optimizations.

When Should You Use These Features?

ClickHouse® 26.3 is particularly valuable for organizations that:

Store analytical datasets in cloud object storage
Query Apache Iceberg tables
Query Delta Lake datasets
Analyze Apache Hudi data
Perform ad hoc SQL analysis over Parquet files
Build interactive BI dashboards
Operate large-scale cloud-native analytics platforms

Key Takeaways

Query cloud-hosted datasets without duplicating data.
Parallel reading accelerates remote file processing.
The Parquet metadata cache reduces repeated metadata access.
Iceberg metadata prefetching lowers query planning latency.
ClickHouse® 26.3 makes interactive analytics over modern data lakes significantly faster and more efficient.

Conclusion

As organizations continue adopting cloud-native architectures and open table formats, efficiently querying data lakes has become essential for modern analytics. ClickHouse® already enables users to query Apache Iceberg, Delta Lake, Apache Hudi, and Parquet datasets directly from cloud object storage without duplicating data or building complex ETL pipelines.

ClickHouse® 26.3 LTS further strengthens these capabilities with enhanced parallel object storage reads, a built-in Parquet metadata cache, and asynchronous Iceberg metadata prefetching. Together, these improvements reduce metadata overhead, minimize remote I/O, improve CPU utilization, and significantly accelerate query execution.

Whether you're building business intelligence dashboards, exploring petabyte-scale datasets, or developing cloud-native analytics platforms, these enhancements make ClickHouse® an even more compelling engine for high-performance analytics directly on modern data lakes.

Day 99 - Efficient Random Sampling with system.numbers_mt: Parallel Number Generation in ClickHouse® 26.3

Kanishga Subramani — Sat, 25 Jul 2026 08:16:28 +0000

Introduction

Every major ClickHouse® release introduces new features and performance improvements, but occasionally older experimental features are removed to simplify maintenance and improve long-term stability.

One such change in ClickHouse® 26.3 is the removal of the experimental Hypothesis Skip Index (TYPE hypothesis).

If you experimented with this index type in earlier versions, you'll need to update your schema before upgrading to ClickHouse® 26.3. Otherwise, table creation or schema restoration involving this index type will fail.

In this article, we'll explore what Hypothesis Skip Indexes were, why they were removed, how to identify affected tables, and the recommended migration path.

Understanding Data Skipping Indexes

Before discussing the deprecation, it's useful to understand how skip indexes work.

Unlike traditional relational databases that rely on B-tree secondary indexes, ClickHouse® is a column-oriented database optimized for analytical workloads. Instead of locating individual rows, ClickHouse® stores data in granules (blocks of rows).

A data skipping index stores metadata about each granule, allowing the query engine to determine whether an entire granule can be skipped during query execution.

When a query contains filtering conditions, ClickHouse® evaluates the skip index before reading data. If a granule cannot possibly satisfy the filter, it is skipped entirely, reducing disk I/O and improving query performance.

Depending on the workload, skip indexes can significantly reduce the amount of data scanned.

What Was the Hypothesis Skip Index?

The Hypothesis Skip Index (TYPE hypothesis) was an experimental skip index designed to precompute whether a particular boolean expression could evaluate to true within each granule.

Instead of storing values themselves, it stored one of three states for every granule:

Stored Value	Meaning
0	Expression is definitely false for all rows (granule can be skipped)
1	Expression may be true (granule must be scanned)
Unknown	Insufficient information

For queries using the same expression, ClickHouse® could immediately eliminate granules where the condition was guaranteed to be false.

Example

CREATE TABLE default.orders
(
    order_id UInt32,
    amount Float64,
    is_large UInt8 MATERIALIZED (amount > 1000),
    order_date Date,

    INDEX idx_large is_large TYPE hypothesis GRANULARITY 4
)
ENGINE = MergeTree()
ORDER BY order_id;

In earlier releases, ClickHouse® would precompute whether is_large could ever be true within each granule.

During execution of:

SELECT *
FROM orders
WHERE is_large = 1;

granules known to contain only is_large = 0 could be skipped.

Why Was It Removed?

Although technically interesting, the feature never matured beyond experimental status.

Some of its limitations included:

Limited production adoption
Known issues with certain data types such as FixedString
Experimental behavior without long-term compatibility guarantees
Similar optimization could be achieved using supported skip indexes together with materialized columns
Additional maintenance burden for the ClickHouse® developers

Because of these reasons, the feature has been removed in ClickHouse® 26.3.

What Changed in ClickHouse® 26.3?

Starting with ClickHouse® 26.3:

INDEX ... TYPE hypothesis is no longer recognized.
Creating new tables using this index type fails.
Schemas containing this index must be updated before upgrading.
Existing metadata referencing the deprecated index should be cleaned up.

What Happens After Upgrading?

Attempting to create a table with the removed index now results in an error similar to:

Unknown skip index type: hypothesis

Similarly, restoring backups or executing old DDL statements containing TYPE hypothesis will fail.

Finding Affected Tables

Before upgrading, review your table definitions.

SHOW CREATE TABLE default.orders;

If the output contains:

TYPE hypothesis

that table requires modification before upgrading.

For larger environments, searching exported DDL files or schema repositories for TYPE hypothesis is also recommended.

Removing the Deprecated Index

If the index is no longer required:

ALTER TABLE default.orders
DROP INDEX idx_large;

This removes the deprecated index definition without affecting the table's data.

Recommended Replacement Indexes

Depending on your workload, ClickHouse® offers several supported skip indexes.

Skip Index	Best Use Case
`minmax`	Numeric and date range filtering
`set`	Low-cardinality equality filters
`bloom_filter`	String equality and `IN` predicates
`ngrambf_v1`	Substring search
`tokenbf_v1`	Token-based full-text search

The replacement should be selected based on actual query patterns rather than simply replacing TYPE hypothesis with another index.

Example Migration

Old definition:

INDEX idx_large is_large TYPE hypothesis GRANULARITY 4

Possible replacement:

INDEX idx_large is_large TYPE minmax GRANULARITY 4

INDEX idx_status status TYPE set(100) GRANULARITY 4

For existing data, materialize the new index:

ALTER TABLE default.orders
MATERIALIZE INDEX idx_large;

ALTER TABLE default.orders
MATERIALIZE INDEX idx_status;

Materialization builds the new skip index for all previously stored parts.

Upgrade Checklist

Before moving to ClickHouse® 26.3:

Step	Action
1	Search schemas for `TYPE hypothesis`
2	Remove deprecated indexes
3	Replace with supported skip indexes where appropriate
4	Materialize new indexes
5	Validate changes in a staging environment
6	Proceed with the production upgrade

Best Practices

To avoid similar upgrade surprises in the future:

Avoid using experimental features in production systems.
Review release notes before every major upgrade.
Choose skip indexes based on observed query workloads.
Benchmark performance after index changes.
Validate schema migrations in a staging environment before production deployment.

Important Clarification

One common point of confusion is the similarity between two different features.

The deprecated feature discussed in this article is:

TYPE hypothesis

This is not the same as the newer Hypothetical Indexes feature introduced through:

CREATE HYPOTHETICAL INDEX

These are entirely different features with different purposes.

This article focuses only on the removal of the experimental TYPE hypothesis skip index in ClickHouse® 26.3.

Conclusion

The removal of the experimental Hypothesis Skip Index is a relatively small but important breaking change in ClickHouse® 26.3. Organizations upgrading from earlier releases should review their schemas for any remaining TYPE hypothesis definitions before upgrading.

Fortunately, modern skip indexes such as minmax, set, and Bloom filter variants provide reliable, production-ready alternatives for most workloads. By auditing existing tables, replacing deprecated indexes where necessary, and validating the changes in a staging environment, you can ensure a smooth upgrade with no unexpected schema failures.

As ClickHouse® continues to evolve, keeping schemas aligned with supported features is one of the simplest ways to maintain long-term performance, stability, and compatibility.

Day 98 of #100DaysOfClickHouse: ClickHouse® 26.3 JOIN Optimization – A Practical Guide to JOIN Types

Kanishga Subramani — Sat, 25 Jul 2026 05:33:13 +0000

Introduction

Joins are among the most performance-sensitive operations in any analytical database. Whether you're combining fact and dimension tables, filtering records based on related datasets, or performing data quality checks, the efficiency of your JOIN operations directly impacts query execution time and memory consumption.

ClickHouse® has continuously improved its JOIN execution engine over the years, making complex analytical queries faster and more resource-efficient. One of the notable enhancements in ClickHouse® 26.3 is the expansion of automatic JOIN reordering to additional JOIN types, including SEMI, ANTI, and FULL OUTER JOIN.

Prior to version 26.3, the query optimizer could automatically reorder only INNER and LEFT/RIGHT JOINs. With this release, the optimizer can now evaluate table statistics and automatically choose a more efficient join order for a wider range of JOIN types, reducing memory usage and improving query performance without requiring manual query rewrites.

In this article, we'll review the major JOIN types available in ClickHouse®, understand how automatic JOIN reordering works, explore what's new in ClickHouse® 26.3, and discuss practical best practices for writing efficient JOIN queries.

Understanding JOIN Types

Before exploring the optimizer improvements, it's important to understand what each JOIN type actually returns.

JOIN Type	Returns
INNER JOIN	Only matching rows from both tables
LEFT OUTER JOIN	All rows from the left table plus matching rows from the right
RIGHT OUTER JOIN	All rows from the right table plus matching rows from the left
FULL OUTER JOIN	All rows from both tables
LEFT SEMI JOIN	Left rows that have a matching row (without returning right-side columns)
LEFT ANTI JOIN	Left rows that have no matching row

Each JOIN serves a different purpose, and selecting the appropriate one can improve both correctness and performance.

Sample Tables

Throughout this article, we'll use two simple tables.

CREATE TABLE default.customers
(
    customer_id UInt32,
    name String
)
ENGINE = MergeTree
ORDER BY customer_id;

INSERT INTO default.customers VALUES
(1,'Alice'),
(2,'Bob'),
(3,'Charlie');

CREATE TABLE default.orders
(
    order_id UInt32,
    customer_id UInt32,
    amount Float64
)
ENGINE = MergeTree
ORDER BY order_id;

INSERT INTO default.orders VALUES
(101,1,1200),
(102,2,450);

Notice that Charlie has not placed any orders, making it easy to see how different JOIN types behave.

INNER JOIN

An INNER JOIN returns only rows that exist in both tables.

SELECT
    c.customer_id,
    c.name,
    o.order_id,
    o.amount
FROM customers AS c
INNER JOIN orders AS o
ON c.customer_id = o.customer_id;

Result:

customer_id	name	order_id	amount
1	Alice	101	1200
2	Bob	102	450

Charlie is excluded because no matching order exists.

Use when: You only need records that exist in both tables.

LEFT OUTER JOIN

A LEFT JOIN returns every row from the left table, regardless of whether a matching row exists on the right.

SELECT
    c.customer_id,
    c.name,
    o.order_id,
    o.amount
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id;

Result:

customer_id	name	order_id	amount
1	Alice	101	1200
2	Bob	102	450
3	Charlie	NULL	NULL

Charlie appears because every customer is preserved.

Use when: You need all rows from the left table.

RIGHT OUTER JOIN

A RIGHT JOIN returns every row from the right table.

SELECT
    c.customer_id,
    c.name,
    o.order_id,
    o.amount
FROM customers c
RIGHT JOIN orders o
ON c.customer_id = o.customer_id;

Since every order belongs to a customer, the result contains only Alice and Bob.

Use when: You need all rows from the right table.

FULL OUTER JOIN

A FULL OUTER JOIN returns every row from both tables.

SELECT
    c.customer_id,
    c.name,
    o.order_id,
    o.amount
FROM customers c
FULL OUTER JOIN orders o
ON c.customer_id = o.customer_id;

Result:

customer_id	name	order_id	amount
1	Alice	101	1200
2	Bob	102	450
3	Charlie	NULL	NULL

Rows without matches are filled with NULL values.

Use when: You need a complete combined view of both datasets.

LEFT SEMI JOIN

A SEMI JOIN checks whether a match exists but returns only columns from the left table.

SELECT
    c.customer_id,
    c.name
FROM customers c
LEFT SEMI JOIN orders o
ON c.customer_id = o.customer_id;

Result:

customer_id	name
1	Alice
2	Bob

Notice that no columns from the orders table are returned.

This is generally more efficient than using an INNER JOIN when you only need to verify that a related row exists.

Use when: Checking existence without retrieving data from the right table.

LEFT ANTI JOIN

ANTI JOIN is the opposite of SEMI JOIN.

It returns rows that do not have a matching record.

SELECT
    c.customer_id,
    c.name
FROM customers c
LEFT ANTI JOIN orders o
ON c.customer_id = o.customer_id;

Result:

customer_id	name
3	Charlie

Only Charlie is returned because he has never placed an order.

Use when:

Finding orphaned records
Data validation
Missing relationships
Customers who have never purchased

JOIN Comparison

JOIN Type	Alice	Bob	Charlie
INNER JOIN	✅	✅	❌
LEFT JOIN	✅	✅	✅ (NULL)
RIGHT JOIN	✅	✅	❌
FULL OUTER JOIN	✅	✅	✅ (NULL)
LEFT SEMI JOIN	✅	✅	❌
LEFT ANTI JOIN	❌	❌	✅

What's New in ClickHouse® 26.3?

ClickHouse executes hash joins by building an in-memory hash table from one side of the JOIN.

If the larger table becomes the hash table, memory consumption increases significantly.

Before ClickHouse® 26.3, the optimizer could automatically swap JOIN order only for:

INNER JOIN
LEFT JOIN
RIGHT JOIN

For SEMI, ANTI, and FULL OUTER JOIN, developers often needed to manually arrange tables in the most efficient order.

ClickHouse® 26.3 removes much of this manual work.

The optimizer now evaluates table statistics and can automatically reorder:

SEMI JOIN
ANTI JOIN
FULL OUTER JOIN

to build smaller hash tables whenever possible.

For example:

SELECT
    c.customer_id,
    c.name
FROM customers c
LEFT ANTI JOIN orders o
ON c.customer_id = o.customer_id;

Even if the query isn't written in the optimal order, ClickHouse® 26.3 can internally rearrange the join plan to improve efficiency.

Collecting Statistics for Better Optimization

Automatic JOIN reordering relies on accurate table statistics.

It is recommended to collect statistics on:

JOIN key columns
Frequently filtered columns

Useful statistics include:

TDigest

Provides data distribution and quantile estimates.

Useful for estimating filter selectivity.

ALTER TABLE orders
ADD STATISTICS customer_id TYPE tdigest;

ALTER TABLE orders
MATERIALIZE STATISTICS customer_id;

Uniq

Estimates column cardinality.

Useful for predicting JOIN selectivity.

CountMinSketch

Useful when filtering frequently on exact values.

Provides approximate frequency estimates with minimal memory.

Performance Best Practices

1. Prefer SEMI JOIN over INNER JOIN

Instead of:

SELECT customer_id
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;

Use:

SELECT customer_id
FROM customers
LEFT SEMI JOIN orders
ON customers.customer_id = orders.customer_id;

This avoids reading unnecessary columns.

2. Prefer ANTI JOIN over NOT IN

Instead of:

SELECT customer_id
FROM customers
WHERE customer_id NOT IN
(
SELECT customer_id
FROM orders
);

Use:

SELECT customer_id
FROM customers
LEFT ANTI JOIN orders
USING(customer_id);

ANTI JOIN is typically faster and more memory-efficient on large datasets.

3. Let the Optimizer Help

Older ClickHouse versions often required manually placing the smaller table on the right.

With ClickHouse® 26.3, automatic JOIN reordering reduces the need for manual optimization across many JOIN types.

4. Verify Query Plans

Always inspect execution plans.

EXPLAIN

SELECT
    c.customer_id,
    c.name
FROM customers c
LEFT ANTI JOIN orders o
ON c.customer_id = o.customer_id;

EXPLAIN helps verify that the optimizer is selecting the expected execution strategy.

When Will This Improvement Matter?

You'll benefit the most if your workloads include:

Large analytical datasets
Multi-table joins
Frequent SEMI or ANTI JOIN queries
FULL OUTER JOIN operations
Memory-sensitive workloads
Data warehouse environments with complex reporting

For smaller datasets, the improvement may not be immediately noticeable, but at scale it helps reduce memory usage and improves overall query execution.

Conclusion

Choosing the correct JOIN type is one of the simplest ways to improve query performance in ClickHouse®.

While INNER, LEFT, and FULL OUTER JOIN cover most common scenarios, SEMI JOIN and ANTI JOIN are powerful alternatives that are often overlooked. They can reduce unnecessary data processing and improve memory efficiency when you only need to check whether matching rows exist.

ClickHouse® 26.3 builds on these capabilities by extending automatic JOIN reordering to SEMI, ANTI, and FULL OUTER JOINs. By leveraging table statistics, the optimizer can automatically choose a more efficient execution plan, reducing memory consumption and eliminating much of the manual tuning previously required.

It's another step toward making ClickHouse not only one of the fastest analytical databases available, but also one that increasingly optimizes itself behind the scenes—allowing developers to focus more on writing queries and less on fine-tuning execution strategies.

Day 97 of #100DaysOfClickHouse: Analyzing Memory Efficiency with Vertical Merges in ClickHouse® 26.3

Kanishga Subramani — Sat, 25 Jul 2026 05:15:13 +0000

Background merge operations are one of the most important components of ClickHouse®. Every insert creates immutable data parts, and the storage engine continuously merges these parts to keep query performance high and storage efficient. These merges also perform critical maintenance tasks such as applying TTL rules, removing expired records, recompressing data, and consolidating files.

For most workloads, this process happens quietly in the background. However, for organizations storing massive analytical datasets with hundreds of columns, background merges can become one of the largest consumers of memory.

ClickHouse® already introduced Vertical Merge, an optimization designed to reduce memory usage during merge operations by processing columns independently instead of entire rows. In ClickHouse 26.3, this optimization has been extended to TTL DELETE merges, significantly lowering memory consumption when expired rows are removed automatically.

This article explains why this enhancement matters, how Vertical Merge works internally, and why it improves the efficiency of large production deployments.

Why Merge Memory Matters

Unlike traditional databases that update data in place, ClickHouse stores data as immutable parts.

Every INSERT creates a new part, and background merge threads continuously combine smaller parts into larger ones.

During these merges, ClickHouse performs several maintenance operations simultaneously:

Merging multiple parts into one
Recompressing column files
Applying TTL expressions
Removing expired rows
Rewriting data into optimized storage layouts

For small datasets, merge memory consumption is usually insignificant.

Production analytical systems, however, often store:

Hundreds of columns
Large String columns
Nested structures
Arrays
Maps
JSON data

During a merge, ClickHouse may need to read, decompress, merge, filter, and rewrite large amounts of column data.

As tables become wider, the peak memory required during merges grows substantially.

While query optimization often receives the most attention, reducing merge memory is equally important because merges execute continuously in the background.

Horizontal Merge vs Vertical Merge

Historically, ClickHouse performed merges using a horizontal merge algorithm.

In a horizontal merge:

Entire rows are processed together.
All required columns remain active throughout much of the merge.
Memory usage increases as the number of columns grows.

This approach works well for narrow schemas but becomes increasingly expensive for wide analytical tables.

Vertical Merge takes a different approach.

Instead of processing complete rows, ClickHouse separates the merge into stages.

First, it determines the correct row ordering using the primary key. After that, each remaining column is processed independently.

The workflow looks like this:

Merge primary key columns.
Build the merged row mapping.
Process one non-key column at a time.
Write the merged output.
Release memory before processing the next column.

Since only a small subset of columns is active at any moment, peak memory usage becomes dramatically lower.

Diagram 1: Horizontal Merge vs Vertical Merge

Horizontal Merge

Part A          Part B
 |                |
 | Read ALL Columns
 |________________|
         |
   Merge Entire Rows
         |
   Write New Part


Vertical Merge

Part A          Part B
 |                |
 | Merge Primary Keys
 |_________________|
         |
   Build Row Mapping
         |
Column 1 -> Write
Column 2 -> Write
Column 3 -> Write
...
Column N -> Write

TTL DELETE Before ClickHouse 26.3

One of the most common maintenance tasks in ClickHouse is automatic data retention using TTL.

For example:

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

Rows older than 30 days are automatically removed during background merges.

Before ClickHouse 26.3, TTL DELETE operations primarily relied on the horizontal merge algorithm.

Although this approach correctly removed expired records, it also meant that all participating columns needed to be processed together.

For tables with hundreds of columns, peak memory usage during TTL cleanup could become quite large.

This was rarely noticeable for small tables but became increasingly important for enterprise deployments storing billions of rows.

What's New in ClickHouse 26.3?

ClickHouse 26.3 extends Vertical Merge support to TTL DELETE merges.

Instead of evaluating TTL rules while processing every column simultaneously, ClickHouse now performs TTL cleanup using the same memory-efficient vertical workflow already used for standard merges.

The process now works like this:

Read and merge primary key columns.
Evaluate the TTL expression.
Identify rows that should be deleted.
Build the row mapping.
Process each remaining column independently.
Write only the surviving rows.

Because non-key columns are handled individually, ClickHouse no longer needs to keep every column in memory simultaneously.

The result is significantly lower peak memory usage during automatic data cleanup.

Before vs After ClickHouse 26.3

Feature	Before 26.3	ClickHouse 26.3
TTL DELETE Merge	Horizontal Merge	Vertical Merge
Peak Memory Usage	Higher	Lower
Wide Table Performance	Memory intensive	More memory efficient
Background Cleanup	Higher resource consumption	Reduced memory pressure
Scalability	Limited by merge memory	Better scalability

Why Vertical Merge Uses Less Memory

The biggest improvement is that memory usage becomes far less dependent on the number of columns.

Imagine a table containing 400 columns.

With a horizontal merge, many of those columns may be loaded and processed together.

With Vertical Merge, ClickHouse processes:

one column,
writes the filtered data,
releases memory,
moves to the next column.

Only a tiny portion of the table is active at any given time.

The total amount of data processed remains identical.

What changes is the maximum memory required at any instant, which can be significantly smaller.

For servers running multiple concurrent merges, this reduction can substantially improve overall system stability.

Practical Example

Suppose an event logging platform stores billions of events and automatically deletes records older than 90 days.

CREATE TABLE events
(
    id UInt64,
    timestamp DateTime,
    user_id UInt64,
    event_type String,
    payload String
)
ENGINE = MergeTree
ORDER BY id
TTL timestamp + INTERVAL 90 DAY DELETE;

Every day, background merges remove expired records.

On previous versions, cleaning up a very wide table could temporarily consume a large amount of memory because every column participated in the merge simultaneously.

With ClickHouse 26.3, the same cleanup benefits from Vertical Merge processing.

The result is smoother background maintenance with lower peak memory usage and less pressure on the operating system.

Where You'll Notice the Biggest Improvements

This optimization is especially valuable when:

Tables contain hundreds of columns.
Large MergeTree tables are merged frequently.
TTL DELETE rules remove old data continuously.
Memory resources are limited.
Multiple background merges execute concurrently.
Analytical workloads generate large numbers of data parts.

Smaller tables may not show dramatic improvements because merge memory requirements are already relatively low.

Benefits of Vertical Merge for TTL DELETE

Extending Vertical Merge to TTL DELETE operations provides several practical advantages:

Lower peak memory consumption during background merges
Better scalability for very wide schemas
More stable merge execution
Reduced memory pressure across the server
Improved efficiency of automatic data retention
Better utilization of available system resources
More predictable performance under heavy workloads

Although users may never interact with this feature directly, its impact can be significant for production environments processing terabytes or petabytes of data.

Conclusion

Background merges are essential to how ClickHouse maintains fast analytical performance. As datasets become larger and schemas become wider, however, merge operations can consume substantial amounts of memory.

ClickHouse 26.3 addresses this challenge by extending the Vertical Merge algorithm to TTL DELETE operations. Instead of processing every column simultaneously, the storage engine now handles non-key columns independently, dramatically reducing peak memory requirements while preserving the correctness of TTL processing.

This enhancement operates entirely behind the scenes, requiring no application changes or configuration updates. Yet for production workloads that rely on automated data retention and large MergeTree tables, it can lead to more stable background merges, improved resource utilization, and better overall scalability.

It is another example of how ClickHouse continues refining its storage engine—not only to execute queries faster, but also to make the underlying maintenance operations increasingly efficient for modern analytical workloads.

Day 96/100 – Standard SQL Time Functions in ClickHouse® 26.3: Better ANSI SQL Compatibility

Kanishga Subramani — Thu, 23 Jul 2026 14:01:58 +0000

Introduction

One of the key goals of recent ClickHouse® releases has been improving compatibility with the ANSI SQL standard. While ClickHouse has always offered a rich set of date and time functions, some syntax differences compared to traditional relational databases required developers to modify existing SQL queries during migrations.

A common example was retrieving the current date or timestamp. Earlier versions of ClickHouse relied on function calls such as today() and now(), whereas many popular databases use SQL-standard keywords like CURRENT_DATE and CURRENT_TIMESTAMP.

ClickHouse® 26.3 bridges this gap by introducing support for SQL-standard time functions without parentheses. Although this may seem like a small enhancement, it significantly improves portability for applications, BI tools, reporting platforms, and ORMs that generate ANSI SQL.

In this article, we'll explore what's new, how these functions work, and why this seemingly simple feature makes migrating workloads to ClickHouse much easier.

Why SQL Compatibility Matters

Organizations rarely build analytics platforms from scratch.

Many migrate workloads from databases such as:

PostgreSQL
MySQL
SQL Server
Oracle
Snowflake

These systems generally follow ANSI SQL syntax for obtaining the current date and timestamp.

For example:

SELECT CURRENT_DATE;

SELECT CURRENT_TIMESTAMP;

Prior to ClickHouse® 26.3, these queries required modification before they could run successfully.

Even small syntax differences become significant when migrating:

Thousands of SQL queries
BI dashboards
Stored reports
ORM-generated SQL
Data transformation pipelines

Reducing these incompatibilities simplifies migrations and improves developer productivity.

Before ClickHouse® 26.3

Traditionally, ClickHouse used dedicated functions.

SELECT today();

SELECT now();

These functions remain fully supported and continue to be the recommended native ClickHouse approach.

However, applications written for ANSI SQL databases often expected keyword-based syntax instead.

What's New in ClickHouse® 26.3?

ClickHouse® 26.3 introduces support for several SQL-standard temporal keywords without requiring parentheses.

Supported syntax includes:

SELECT CURRENT_DATE;

SELECT CURRENT_TIMESTAMP;

SELECT NOW;

These expressions behave exactly like their ClickHouse counterparts while following the SQL standard used by many other database systems.

Function Comparison

SQL Standard	Traditional ClickHouse®	Supported in 26.3
`CURRENT_DATE`	`today()`	✅ Yes
`CURRENT_TIMESTAMP`	`now()`	✅ Yes
`NOW`	`now()`	✅ Yes (without parentheses)

These additions provide alternative syntax—they do not replace existing ClickHouse functions.

Examples

Current Date

SELECT CURRENT_DATE;

Equivalent native ClickHouse syntax:

SELECT today();

Example output:

2026-03-18

Current Timestamp

SELECT CURRENT_TIMESTAMP;

Equivalent ClickHouse function:

SELECT now();

Example output:

2026-03-18 14:35:12

NOW Without Parentheses

Before ClickHouse® 26.3:

SELECT now();

Now you can simply write:

SELECT NOW;

Both statements return the same current timestamp.

Why This Matters for Migrations

Many migration projects involve moving hundreds or thousands of SQL statements from existing analytical databases into ClickHouse.

Consider a PostgreSQL query:

SELECT
    CURRENT_DATE,
    CURRENT_TIMESTAMP;

Earlier versions required rewriting this as:

SELECT
    today(),
    now();

With ClickHouse® 26.3, the original SQL can often run unchanged.

This reduces migration effort while improving compatibility with third-party SQL generators.

Better Support for BI Tools

Many reporting platforms automatically generate ANSI SQL.

Examples include:

Apache Superset
Metabase
Tableau
Power BI
Looker
JDBC-based reporting tools

Because these tools frequently generate CURRENT_DATE or CURRENT_TIMESTAMP, ClickHouse now accepts these expressions without requiring query modifications.

This helps reduce compatibility issues during deployment.

Existing Date and Time Functions Remain Available

It's important to note that ClickHouse® 26.3 does not introduce a new date-time engine.

All existing functions remain available, including:

today()
now()
toDate()
toDateTime()
dateDiff()
dateAdd()
toStartOfMonth()
toStartOfDay()
toStartOfWeek()
toStartOfHour()

The new feature simply adds SQL-standard alternatives for retrieving the current date and timestamp.

Benefits

Feature	Benefit
`CURRENT_DATE`	ANSI SQL compatibility
`CURRENT_TIMESTAMP`	Easier query portability
`NOW` without parentheses	Familiar syntax for SQL users
Reduced query rewrites	Faster database migrations
Better ORM compatibility	Less application code modification
Improved BI tool support	Greater interoperability

Best Practices

To get the most from this enhancement:

Use SQL-standard syntax when writing portable SQL that may run across multiple database systems.
Continue using native ClickHouse functions if your environment is already optimized around them.
When migrating applications, test existing SQL before rewriting—it may now work without changes.
Validate ORM-generated SQL after upgrading to ClickHouse® 26.3, as many generated queries may become compatible automatically.
Keep using ClickHouse's rich date and time functions for advanced analytical workloads, as these remain the primary tools for date manipulation.

What Didn't Change

Although the syntax is new, the underlying behavior is not.

These additions:

Do not change how ClickHouse calculates dates or timestamps.
Do not replace existing functions such as today() or now().
Do not introduce new time zones or formatting options.
Do not affect performance compared to their native equivalents.

The primary goal is better SQL compatibility, making ClickHouse easier to adopt for teams migrating from other database platforms.

Final Thoughts

Not every new feature needs to be a major performance optimization or a groundbreaking capability. Sometimes, small improvements can have a significant impact on developer experience.

The addition of CURRENT_DATE, CURRENT_TIMESTAMP, and NOW without parentheses in ClickHouse® 26.3 is one such enhancement. By embracing ANSI SQL syntax, ClickHouse reduces friction for organizations migrating from traditional relational databases and improves compatibility with BI tools, ORMs, and SQL-generating applications.

Existing ClickHouse functions like today() and now() remain fully supported, giving developers the flexibility to choose the syntax that best fits their workflows. For teams building portable SQL or modernizing existing analytics platforms, this update makes the transition to ClickHouse just a little smoother—and that's a meaningful improvement in itself.

References

ClickHouse® 26.3 Release Notes
ClickHouse® Documentation – Date and Time Functions
ANSI SQL Standard – Date and Time Expressions

Day 95/100 – ClickHouse® 26.3 S3 Object Storage Read Enhancements: Faster Data Lake Queries

Kanishga Subramani — Thu, 23 Jul 2026 13:50:08 +0000

Introduction

Cloud object storage has become the foundation of modern data lake architectures. Services such as Amazon S3 and S3-compatible object stores provide virtually unlimited, cost-effective storage for massive analytical datasets, making them the preferred choice for storing historical logs, clickstream data, IoT events, machine learning datasets, and business intelligence workloads.

Rather than copying every dataset into local storage, many organizations now query data directly from object storage using formats such as Parquet, Iceberg, Delta Lake, and Apache Hudi. This approach reduces storage costs, simplifies data sharing, and enables multiple analytics engines to work on the same data lake.

ClickHouse® has long supported querying data directly from S3 using table functions and native storage engines. However, network latency and object storage access overhead have traditionally made remote reads slower than querying local MergeTree tables.

ClickHouse® 26.3 significantly improves this experience with major enhancements to the S3 object storage read path. The release introduces faster parallel reads, smarter metadata caching, asynchronous Iceberg metadata prefetching, more efficient S3Queue ingestion, and reduced memory usage for semi-structured data.

The result is dramatically faster queries against object storage without requiring schema changes, query rewrites, or application modifications.

In this article, we'll explore these improvements and understand how they benefit modern lakehouse architectures.

Why Object Storage Matters

Today's analytical workloads increasingly separate storage from compute.

Instead of keeping all datasets on local disks, organizations store data in cloud object storage while scaling compute independently.

Common use cases include:

Data lakes
Data lakehouses
Historical event storage
Log analytics
Clickstream analysis
Machine learning datasets
Business intelligence platforms

Popular table formats include:

Apache Parquet
Apache Iceberg
Delta Lake
Apache Hudi

ClickHouse can query these formats directly without first importing data into MergeTree tables.

The Challenge Before ClickHouse® 26.3

Although object storage offers excellent scalability and lower storage costs, remote reads naturally introduce additional overhead.

Each query may require:

Opening remote objects
Reading metadata
Fetching Parquet footers
Downloading row groups
Waiting on network latency

For workloads scanning only a handful of large files, CPUs frequently became underutilized while waiting for remote I/O.

As a result, applications experienced higher query latency compared to local storage.

Faster Parallel Reads

The most significant improvement in ClickHouse® 26.3 is a redesigned object storage read path.

Instead of waiting for individual remote read operations to complete sequentially, ClickHouse now parallelizes object storage reads across multiple CPU cores.

This improvement benefits:

Amazon S3
S3-compatible storage
Apache Iceberg
Delta Lake
Apache Hudi
Parquet files
CSV files queried through the s3() table function

The biggest performance gains occur when:

Queries read a relatively small number of files
Files are large
Multiple CPU cores are available

Instead of leaving CPU cores idle while waiting on network operations, ClickHouse keeps multiple cores busy simultaneously.

According to the ClickHouse® 26.3 release notes, these optimizations can make object storage reads tens of times faster on multi-core systems for suitable workloads.

Smarter Parquet Metadata Caching

Every Parquet file contains metadata stored in its footer.

Before processing data, ClickHouse reads this footer to understand:

Schema
Row groups
Column statistics
File layout

Repeatedly downloading this metadata for frequently queried files adds unnecessary latency.

ClickHouse® 26.3 introduces a new SLRU (Segmented Least Recently Used) cache for Parquet metadata.

Benefits include:

Enabled by default
Up to 2× fewer metadata reads
Faster repeated queries
Reduced network requests

To ensure correctness, cached metadata is validated using each file's ETag, preventing stale metadata from being used.

Faster Iceberg Metadata Access

Iceberg users receive another major optimization.

Traditionally, each query needed to communicate with the Iceberg catalog before execution.

Although necessary for consistency, repeated catalog lookups increase query latency.

ClickHouse® 26.3 introduces asynchronous metadata prefetching.

Instead of retrieving metadata during every query, ClickHouse periodically refreshes metadata in the background.

Example:

CREATE TABLE my_iceberg (...)
ENGINE = IcebergS3(...)
SETTINGS
iceberg_metadata_async_prefetch_period_ms = 60000;

Queries can specify acceptable metadata freshness.

SELECT *
FROM my_iceberg
SETTINGS
iceberg_metadata_staleness_ms = 30000;

If cached metadata is sufficiently recent, ClickHouse avoids contacting the Iceberg catalog entirely during query execution.

This removes catalog communication from the critical query path.

Better S3Queue Performance

S3Queue continuously monitors object storage for newly uploaded files.

Before ClickHouse® 26.3, queues frequently scanned the complete object prefix history to identify new files.

For buckets containing millions of historical objects, repeated listing operations became increasingly expensive.

ClickHouse® 26.3 improves ordered-mode S3Queue by using the StartAfter parameter.

Benefits include:

Avoids scanning entire bucket history
Reduces ListObjects API requests
Faster detection of new files
Lower cloud API costs

This is particularly valuable for long-running ingestion pipelines.

Lower Memory Usage for JSON Data

Many organizations store event data as JSON inside object storage.

Queries often read only a small subset of JSON attributes.

Earlier versions sometimes overestimated memory requirements for these partial reads.

ClickHouse® 26.3 introduces more accurate memory estimation for JSON subcolumns.

Benefits include:

Up to 8× lower memory usage
Better resource utilization
Improved query stability
More efficient semi-structured analytics

This enhancement is especially useful for:

Event logs
Clickstream data
Application telemetry
Observability platforms

What This Means for Your Architecture

One of the best aspects of these improvements is that they require virtually no application changes.

If you're already using:

s3() table functions
IcebergS3
DeltaLakeS3
Hudi
S3Queue

most improvements become available simply by upgrading to ClickHouse® 26.3.

Organizations benefit from:

Lower query latency
Reduced network overhead
Fewer API calls
Better CPU utilization
Improved scalability

without changing existing SQL queries.

Getting Started

Most improvements are enabled automatically.

For the best results, consider the following recommendations.

Verify Parquet Metadata Caching

Ensure metadata caching remains enabled for frequently queried datasets.

use_parquet_metadata_cache = 1

Configure Iceberg Metadata Prefetch

For busy Iceberg tables:

iceberg_metadata_async_prefetch_period_ms = 60000

Enable Ordered S3Queue

Long-running ingestion pipelines benefit from ordered mode together with the StartAfter optimization.

Best Practices

To maximize performance:

Store analytical data using Parquet whenever possible.
Partition data appropriately to minimize unnecessary scans.
Enable metadata caching for repeated queries.
Configure asynchronous Iceberg metadata refresh on heavily queried tables.
Use ordered-mode S3Queue for continuous ingestion.
Monitor object storage latency alongside ClickHouse performance metrics.
Keep ClickHouse updated to benefit from ongoing object storage optimizations.

Final Thoughts

Object storage has become the backbone of modern analytics, offering scalable and cost-effective storage for massive datasets. However, remote reads have traditionally introduced a performance gap compared to querying locally stored data.

ClickHouse® 26.3 significantly narrows that gap by redesigning the object storage read path, improving parallelism, introducing intelligent Parquet metadata caching, optimizing Iceberg metadata access, enhancing S3Queue ingestion, and reducing memory consumption for JSON workloads.

For organizations building lakehouse architectures on Amazon S3 or compatible object storage, these enhancements translate into faster queries, lower API costs, better resource utilization, and improved scalability—all without changing schemas, rewriting SQL, or redesigning ingestion pipelines.

As more organizations adopt cloud-native analytics, these S3 improvements make ClickHouse an even stronger choice for high-performance querying directly against data lakes.

References

ClickHouse® 26.3 Release Notes
ClickHouse® 26.3 Announcement
ClickHouse® Documentation – S3 Table Function
ClickHouse® Documentation – Iceberg Table Engine
ClickHouse® Documentation – S3Queue

Day 94/100 – Automated Insert Batching in ClickHouse® 26.3: Higher Throughput with Asynchronous Inserts

Kanishga Subramani — Wed, 22 Jul 2026 16:13:58 +0000

Introduction

Modern data platforms are expected to ingest massive volumes of data in real time. Whether it's application logs, IoT sensor readings, monitoring metrics, clickstream events, or messages from streaming platforms like Kafka, many workloads generate thousands—or even millions—of small INSERT operations every second.

ClickHouse® is designed for high-performance analytical workloads and can ingest data at remarkable speeds. However, one common performance bottleneck remains: a large number of very small inserts.

Each individual INSERT requires ClickHouse to parse the query, validate the data, compress blocks, update metadata, and create new data parts. While these operations are efficient individually, repeating them thousands of times per second creates unnecessary overhead and increases the work performed by background merge processes.

ClickHouse® 26.3 improves this scenario by enhancing the batching behavior of asynchronous inserts. Instead of writing every small insert immediately, ClickHouse temporarily buffers incoming asynchronous insert requests and combines multiple small inserts into larger writes before flushing them to storage.

The result is:

Higher ingestion throughput
Fewer data parts
Reduced merge overhead
Better compression efficiency
Lower CPU and disk utilization

In this article, we'll explore how automated insert batching works, why it improves performance, and when you should consider enabling asynchronous inserts in your ClickHouse deployments.

Note: "Automated Insert Batching" is not an official ClickHouse feature name. Throughout this article, the term refers to the enhanced batching behavior of asynchronous inserts introduced in ClickHouse® 26.3, where multiple small insert requests are automatically grouped into larger writes before being persisted.

Why Small INSERT Statements Hurt Performance

Imagine an application receiving telemetry events every second.

Instead of accumulating events into larger batches, it continuously executes tiny insert operations.

INSERT (10 rows)
INSERT (15 rows)
INSERT (8 rows)
INSERT (12 rows)
INSERT (20 rows)
...

Although each insert contains only a handful of rows, ClickHouse still performs the complete insert workflow for every request.

Each insert requires:

SQL parsing
Query validation
Block creation
Data compression
Metadata updates
New data part creation
Scheduling future merge operations

When applications generate thousands of tiny inserts, these fixed costs are repeated continuously, reducing overall ingestion efficiency.

Traditional INSERT Workflow

Without batching, every insert is processed independently.

Application

│
├── INSERT
├── INSERT
├── INSERT
├── INSERT
│
▼

ClickHouse

│
├── Part 1
├── Part 2
├── Part 3
├── Part 4
│
▼

MergeTree Storage

Every insert creates its own data part.

Although MergeTree is optimized for immutable data parts, creating thousands of tiny parts introduces unnecessary overhead throughout the storage engine.

Why Too Many Small Parts Are a Problem

A large number of tiny data parts negatively impacts multiple areas of ClickHouse performance.

Some common consequences include:

More background merge operations
Increased CPU utilization
Higher disk I/O
Larger metadata structures
Slower query planning
Increased memory consumption
Additional storage fragmentation

Background merges become especially busy trying to combine many small parts into larger ones.

This is why ClickHouse documentation consistently recommends:

Avoid sending many tiny INSERT statements whenever possible.

Automated Insert Batching

ClickHouse® 26.3 improves this workflow by batching asynchronous inserts automatically.

Instead of writing every request immediately, ClickHouse buffers incoming asynchronous insert requests for a short period.

Multiple insert requests are then combined into one larger write.

Application

INSERT
INSERT
INSERT
INSERT
INSERT

        │
        ▼

 Asynchronous Buffer

        │

Combine Requests

        ▼

Large INSERT

        ▼

ClickHouse Storage

From the application's perspective, nothing changes.

The application continues sending small inserts.

Internally, however, ClickHouse performs fewer, larger writes.

Why This Improves Throughput

Every insert has a fixed processing cost regardless of whether it contains:

5 rows
20 rows
100 rows

When ClickHouse combines many small inserts into a single larger write, those fixed costs occur only once.

Instead of repeating expensive operations thousands of times, ClickHouse performs them for the combined batch.

Benefits include:

Fewer metadata updates
Fewer compression operations
Fewer data parts
Reduced write amplification
Lower CPU overhead
Less merge activity

The overall result is significantly higher ingestion throughput.

Example

Suppose a monitoring platform generates:

2,000 INSERT statements
20 rows per INSERT

Without batching:

2,000 INSERTS

↓

2,000 Data Parts

With automated batching:

2,000 INSERTS

↓

40 Large Batches

↓

40 Data Parts

The total number of inserted rows remains exactly the same.

However, ClickHouse creates only a small fraction of the data parts.

This dramatically reduces merge operations and improves overall system efficiency.

How Asynchronous Inserts Work

Automatic batching is powered by asynchronous inserts.

Instead of immediately writing each insert to disk, ClickHouse temporarily stores incoming insert requests in memory.

The buffered data is flushed when configurable thresholds are reached.

Typical controls include:

Maximum buffer size
Flush timeout
Queue size limits

These settings allow administrators to balance:

Insert latency
Memory usage
Throughput

Depending on the workload, ClickHouse can automatically optimize write efficiency without requiring application changes.

Enabling Asynchronous Inserts

Asynchronous inserts can be enabled at the session level.

SET async_insert = 1;

SET wait_for_async_insert = 1;

Or configured for an individual insert statement.

INSERT INTO events
SETTINGS
    async_insert = 1,
    wait_for_async_insert = 1
VALUES (...);

What do these settings mean?

async_insert = 1

Enables asynchronous inserts, allowing ClickHouse to buffer incoming requests before writing them to storage.

wait_for_async_insert = 1

Waits until the buffered data has been successfully written before acknowledging the insert to the client.

These settings can also be configured globally using server configuration files, making them suitable for production deployments with consistent ingestion patterns.

When Does Automatic Batching Help?

Automatic batching is particularly valuable for workloads that continuously generate many small inserts.

Common examples include:

IoT Platforms

Thousands of sensors continuously publish measurements.

Instead of immediately writing every reading, ClickHouse batches them into larger writes.

Application Logging

Modern applications generate logs every few milliseconds.

Batching dramatically reduces write overhead.

Monitoring Systems

Monitoring agents continuously send metrics.

Automatic batching helps reduce part creation while maintaining near real-time visibility.

Event Streaming

Applications consuming events from Kafka, RabbitMQ, or Pulsar often insert relatively small batches.

ClickHouse combines them automatically for higher throughput.

Clickstream Analytics

User interactions arrive continuously throughout the day.

Batching improves scalability without requiring changes to event producers.

Benefits

Feature	Benefit
Larger insert batches	Higher throughput
Fewer data parts	Lower merge overhead
Better compression	Reduced storage usage
Lower CPU utilization	More efficient ingestion
Lower disk I/O	Faster writes
Better scalability	Handles higher event rates

Before vs After

Without Automatic Batching	With Automatic Batching
Many tiny writes	Writes combined automatically
Large number of data parts	Significantly fewer parts
Frequent merges	Reduced merge activity
Higher CPU usage	Lower CPU usage
Lower throughput	Higher throughput

Monitoring Insert Performance

Several ClickHouse system tables help monitor insert behavior.

System Table	Purpose
`system.parts`	Active data parts
`system.part_log`	Part creation history
`system.merges`	Background merge activity
`system.metrics`	Insert-related metrics

Example:

SELECT
    table,
    count() AS active_parts
FROM system.parts
WHERE active = 1
GROUP BY table;

If a table contains an unusually large number of active parts, it may indicate that inserts are arriving in very small batches and causing excessive merge activity.

Monitoring these tables regularly can help identify ingestion bottlenecks before they affect query performance.

Best Practices

To maximize ingestion throughput:

Enable asynchronous inserts for high-frequency workloads.
Prefer larger batches whenever your application allows.
Avoid single-row inserts whenever possible.
Monitor active data parts using system.parts.
Watch background merges using system.merges.
Tune asynchronous insert thresholds based on workload characteristics.
Measure throughput before and after enabling batching to quantify improvements.

When Automatic Batching Provides Limited Benefit

Although batching improves many workloads, it isn't beneficial in every situation.

Performance gains may be limited when:

Your application already sends large batch inserts.
Insert operations occur infrequently.
Immediate data visibility is more important than throughput.
Your workload already produces relatively few data parts.

In these cases, batching provides little additional optimization because the application has already minimized insert overhead.

Things to Consider

Automatic batching introduces a small trade-off.

Because ClickHouse briefly buffers insert requests before writing them to disk, data may become visible slightly later than with synchronous inserts.

For most analytical workloads, this delay is negligible.

The improvements in throughput, storage efficiency, CPU utilization, and merge performance typically outweigh the small increase in insert latency.

The optimal configuration depends on your workload's balance between:

Freshness requirements
Write throughput
Resource utilization

Final Thoughts

ClickHouse® 26.3 continues to improve one of its greatest strengths—high-speed data ingestion.

By enhancing the batching behavior of asynchronous inserts, ClickHouse automatically combines multiple small insert requests into larger writes, reducing the overhead associated with part creation, compression, metadata updates, and background merges.

For workloads involving logs, metrics, IoT telemetry, event streaming, clickstream analytics, or real-time monitoring, enabling asynchronous inserts can significantly improve scalability while reducing CPU usage, disk I/O, and storage fragmentation.

If your applications generate thousands of small inserts every second, automated insert batching is a simple optimization that can deliver substantial performance improvements with minimal changes to your existing ingestion pipeline.

As ClickHouse continues to evolve, features like this demonstrate how thoughtful engineering can improve both performance and operational efficiency, making it even better suited for modern, data-intensive applications.

References

ClickHouse® 26.3 Release Notes
ClickHouse® Documentation – Asynchronous Inserts
ClickHouse® Documentation – Bulk Inserts Best Practices

Day 93/100 – New Unicode String Functions in ClickHouse® 26.3: Better Text Search and Normalization

Kanishga Subramani — Wed, 22 Jul 2026 16:03:13 +0000

Introduction

Modern data platforms process enormous volumes of text originating from users all over the world. Names, addresses, product descriptions, customer reviews, search queries, and application logs often contain accented characters, Unicode symbols, ligatures, emojis, and language-specific casing rules. While these characters make text accurate and meaningful for users, they also introduce challenges when performing searches, comparisons, joins, deduplication, or analytics.

Consider a few common examples:

résumé and resume
Straße and STRASSE
crème brûlée and creme brulee
Different Unicode representations of visually identical characters

Although these strings may look identical—or nearly identical—to people, they are stored differently internally. Traditional string comparison functions such as lower() or upper() often fail to recognize these differences, leading to missed search results, duplicate records, inconsistent grouping, and unexpected join failures.

To simplify Unicode-aware text processing, ClickHouse® 26.3 introduces three powerful new string functions:

caseFoldUTF8()
removeDiacriticsUTF8()
normalizeUTF8NFKCCasefold()

Together, these functions make it much easier to build multilingual applications that perform reliable searches, comparisons, and text normalization while following Unicode standards.

In this article, we'll explore each function in detail, understand when to use it, and walk through practical SQL examples.

Why Unicode Normalization Matters

Working with Unicode text is more complicated than it first appears.

Many characters have multiple valid Unicode representations. Two strings can look identical on screen while having completely different binary representations.

For example:

The letter é can exist as a single Unicode character.
It can also be represented as the character e followed by a combining accent.

Similarly:

Straße
STRASSE

represent the same German word, but traditional lowercase conversion treats them differently because the German letter ß is not equivalent to ss under simple lowercase rules.

Accent marks introduce similar problems:

résumé
resume

Both words may represent the same search intent, but standard string comparisons treat them as different values.

Without Unicode normalization:

Searches miss valid matches.
Duplicate detection becomes unreliable.
GROUP BY operations produce unexpected results.
JOIN conditions fail.
User-entered data becomes inconsistent across applications.

Unicode-aware normalization solves these problems by transforming text into standardized forms before comparison.

Unicode Support Before ClickHouse® 26.3

ClickHouse® already provided several Unicode normalization functions before version 26.3.

Function	Description
`normalizeUTF8NFC()`	NFC normalization
`normalizeUTF8NFD()`	NFD normalization
`normalizeUTF8NFKC()`	Compatibility composition
`normalizeUTF8NFKD()`	Compatibility decomposition
`upperUTF8()`	UTF-8 uppercase conversion
`lowerUTF8()`	UTF-8 lowercase conversion

These functions remain useful for Unicode normalization, but ClickHouse® 26.3 expands the toolkit with three new functions specifically designed for modern multilingual text processing.

New Functions Introduced in ClickHouse® 26.3

1. caseFoldUTF8()

What does it do?

caseFoldUTF8() performs Unicode case folding.

Unlike lowerUTF8(), case folding is specifically designed for case-insensitive comparison according to the Unicode standard.

Instead of simply converting uppercase letters into lowercase letters, case folding also handles special Unicode characters that ordinary lowercase conversion cannot.

Example

SELECT
    caseFoldUTF8('Straße') AS value1,
    caseFoldUTF8('STRASSE') AS value2;

Output

value1	value2
strasse	strasse

Now compare them:

SELECT
    caseFoldUTF8('Straße') =
    caseFoldUTF8('STRASSE') AS is_equal;

Output

is_equal
1

Notice the difference.

Using lowerUTF8():

Straße
↓

straße

while

STRASSE
↓

strasse

These values are still different.

Unicode case folding correctly transforms:

ß → ss

making both strings identical.

Practical Example – Case-Insensitive Name Search

SELECT
    user_id,
    name,
    country
FROM default.users
WHERE caseFoldUTF8(name) =
      caseFoldUTF8('müller');

This query successfully matches:

Müller
MÜLLER
MüLLeR
müller

regardless of how users entered the name.

More Examples

SELECT
    caseFoldUTF8('Hello World') AS english,
    caseFoldUTF8('HÉLLO WÖRLD') AS accented,
    caseFoldUTF8('Ω') AS greek;

Output

english	accented	greek
hello world	héllo wörld	ω

2. removeDiacriticsUTF8()

What does it do?

Many European languages use accent marks known as diacritics.

For example:

Although visually important, many search systems ignore these marks so users can find results without typing accents.

removeDiacriticsUTF8() removes these accent marks while preserving the base letters.

Example

SELECT
    removeDiacriticsUTF8('crème brûlée') AS result;

Output

result
creme brulee

More Examples

SELECT
    removeDiacriticsUTF8('résumé') AS example1,
    removeDiacriticsUTF8('naïve') AS example2,
    removeDiacriticsUTF8('São Paulo') AS example3,
    removeDiacriticsUTF8('Zürich') AS example4;

Output

example1	example2	example3	example4
resume	naive	Sao Paulo	Zurich

Practical Example – Accent-Insensitive Search

SELECT
    city,
    country,
    population
FROM default.cities
WHERE
removeDiacriticsUTF8(lowerUTF8(city))
=
removeDiacriticsUTF8(lowerUTF8('sao paulo'));

This matches:

São Paulo
Sao Paulo
SAO PAULO
são paulo

without requiring multiple conditions.

3. normalizeUTF8NFKCCasefold()

What does it do?

normalizeUTF8NFKCCasefold() combines two powerful Unicode operations into one function:

Unicode Compatibility Normalization (NFKC)
Unicode Case Folding

It is the most comprehensive Unicode normalization function introduced in ClickHouse® 26.3.

Unlike removeDiacriticsUTF8(), this function does not remove accents. Instead, it standardizes compatibility characters and applies Unicode-aware case folding.

What is NFKC Normalization?

Unicode includes many compatibility characters that look different but represent the same logical character.

Examples include:

Character	Normalized Result
ﬁ	fi
Ａ	A
²	2

NFKC converts these compatibility characters into their standard equivalents.

Example

SELECT
normalizeUTF8NFKCCasefold('ﬁle') AS normalized;

Output

normalized
file

The ligature ﬁ becomes the letters fi, and case folding is also applied.

More Examples

SELECT
normalizeUTF8NFKCCasefold('ﬁle') AS ligature,
normalizeUTF8NFKCCasefold('Ａ Ｂ Ｃ') AS fullwidth,
normalizeUTF8NFKCCasefold('²²') AS superscript,
normalizeUTF8NFKCCasefold('Straße') AS german;

Output

ligature	fullwidth	superscript	german
file	a b c	22	strasse

Practical Example – Normalizing User Search Queries

SELECT
    user_id,
    normalizeUTF8NFKCCasefold(search_query) AS normalized_query
FROM default.search_logs
WHERE normalizeUTF8NFKCCasefold(search_query)
      =
      normalizeUTF8NFKCCasefold('café')
LIMIT 10;

This query ensures that searches become consistent even when users type different Unicode compatibility characters or different letter cases.

If you also want accent-insensitive matching, combine it with removeDiacriticsUTF8().

Which Function Should You Use?

Function	Primary Purpose	Typical Use Case
`caseFoldUTF8()`	Unicode-aware case comparison	Case-insensitive matching
`removeDiacriticsUTF8()`	Removes accents	Accent-insensitive search
`normalizeUTF8NFKCCasefold()`	Compatibility normalization + case folding	Deduplication and equality checks
`removeDiacriticsUTF8(caseFoldUTF8())`	Case + accent normalization	Flexible multilingual search

Also New in ClickHouse® 26.3: naturalSortKey()

Although not a Unicode normalization function, ClickHouse® 26.3 also introduces naturalSortKey(), which provides human-friendly sorting for strings containing numbers.

Example:

SELECT name
FROM files
ORDER BY naturalSortKey(name);

Output

name
file1.txt
file2.txt
file10.txt
file20.txt
file100.txt

Without naturalSortKey(), standard lexicographical sorting would incorrectly place file10.txt before file2.txt.

Natural sorting recognizes numeric values inside strings and orders them the way people naturally expect.

Quick Reference

Function	Example Input	Output
`caseFoldUTF8()`	Straße	strasse
`caseFoldUTF8()`	MÜLLER	müller
`removeDiacriticsUTF8()`	crème brûlée	creme brulee
`removeDiacriticsUTF8()`	São Paulo	Sao Paulo
`normalizeUTF8NFKCCasefold()`	ﬁle	file
`normalizeUTF8NFKCCasefold()`	Straße	strasse
`naturalSortKey()`	file10.txt	Sorts after file2.txt

Best Practices

Use caseFoldUTF8() instead of lowerUTF8() whenever you need reliable Unicode-aware case-insensitive comparisons.
Use removeDiacriticsUTF8() for search functionality where accents should not affect matching, such as names, cities, or product catalogs.
Combine removeDiacriticsUTF8(caseFoldUTF8(column)) to support both case-insensitive and accent-insensitive searches.
Use normalizeUTF8NFKCCasefold() when importing data from external systems that may contain compatibility characters, ligatures, or full-width Unicode characters.
Store normalized values as MATERIALIZED columns whenever possible so normalization occurs only once during inserts rather than on every query.
Choose the least aggressive normalization function that satisfies your use case to avoid altering text more than necessary.

Final Thoughts

As applications become increasingly global, handling multilingual text correctly is no longer optional. Reliable search, accurate deduplication, and consistent data processing all depend on understanding Unicode beyond simple uppercase and lowercase conversions.

The new Unicode string functions introduced in ClickHouse® 26.3—caseFoldUTF8(), removeDiacriticsUTF8(), and normalizeUTF8NFKCCasefold()—provide a modern toolkit for working with international text. Whether you're processing customer names, addresses, search queries, product catalogs, or user-generated content, these functions help ensure your comparisons behave consistently across languages and writing systems.

Combined with existing Unicode normalization functions and new utilities like naturalSortKey(), ClickHouse® continues to strengthen its support for real-world text processing while maintaining the high performance expected from a modern analytical database.

As multilingual datasets continue to grow, incorporating these Unicode-aware functions into your data pipelines and queries will help improve search accuracy, reduce duplicate records, and deliver a better experience for users around the world.

DEV Community: Kanishga Subramani

Where Did the Time Go? Query Profiler in CHOps

What a flame graph actually is

First, understand what a "sample" is

The workflow

Trace types: pick the one that matches your symptom

Memory graphs get a second dropdown

Reading the shapes

One very wide bar

Many narrow towers

Two towers of similar width

Flat, no towers

What CHOps is doing under the hood

Query Profiler or Processors Profile?

Before you get a graph

When it doesn't work

Four habits worth forming

1. Raise the sampling rate before you profile

2. Profile something heavy

3. Always compare CPU against Real

4. Take a before and after

Try it

CHOps SQL Editor: A Complete Guide to Faster ClickHouse® Query Development and Optimization

What is CHOps?

Introducing the CHOps SQL Editor

The SQL Editor at a Glance

Connecting First

Built-in Database Explorer

Smart SQL Autocomplete

Example

Running Your Query

Understand Queries with EXPLAIN

AI-Powered SQL Generation

Example prompt:

The Debugging Tools Around the Query

Estimate Query Cost

Max Rows Control

Action Buttons After a Run

History

Bookmarks

Share Option

Compare Query Performance

Export Option

A Simple Workflow

Conclusion

Further Reading

ClickHouse® 26.8 LTS Release: What's New and Why It Matters

In This Article

At a Glance

Breaking Changes

Ingestion and the Write Path

max_insert_threads now defaults to auto

Lightweight UPDATE patch parts use a new v2 on-disk format

Object-storage disk transactions

disable_insertion_and_mutation changes

Type and Function Semantics

Unquoted JSON numbers are now Unix timestamps

Date32 range extended

arrayIntersect and arraySymmetricDifference deduplication fixed

Window functions over AggregateFunction columns are rejected

Timespan settings that overflow Int64 microseconds are rejected

Query Planning and Output

Trivial views over Distributed tables are pushed to the shards

EXPLAIN SYNTAX returns a single record

Security and Configuration

MySQL source TLS credentials

NATS credentials move inline

PostgreSQL database engines respect remote_url_allow_hosts

SYSTEM ... CACHE ON CLUSTER privilege checks are granular

include_from no longer defaults to /etc/metrika.xml

Removals

The library dictionary source is gone

Apache Arrow library-based reader and writer removed

Experimental ALP(STD) codec changes

Monitoring and Introspection

Asynchronous metrics can now be Map-typed

system.users.valid_until changed type

Default Settings That Changed in 26.8

Behaviour

Security

`max_insert_threads` now defaults to `auto`

Lightweight `UPDATE` patch parts use a new v2 on-disk format

`disable_insertion_and_mutation` changes

`Date32` range extended

`arrayIntersect` and `arraySymmetricDifference` deduplication fixed

Window functions over `AggregateFunction` columns are rejected

Timespan settings that overflow `Int64` microseconds are rejected

Trivial views over `Distributed` tables are pushed to the shards

`EXPLAIN SYNTAX` returns a single record

PostgreSQL database engines respect `remote_url_allow_hosts`

`SYSTEM ... CACHE ON CLUSTER` privilege checks are granular

`include_from` no longer defaults to `/etc/metrika.xml`

The `library` dictionary source is gone

Experimental `ALP(STD)` codec changes

Asynchronous metrics can now be `Map`-typed

`system.users.valid_until` changed type

`GROUPS` window frame mode

`run_query_in_background`

Atomic `POPULATE`

`system.user_query_log`

`create_union_system_log_tables`

`system.mutations.finish_time`

`system.tables.skipping_indices_types`

`IEJoin`

`parallel_full_sorting_merge`

1. `max_insert_threads` defaulting to `auto`