DEV Community: Mohamed Hussain S

Understanding the Replication Queue in ClickHouse

Mohamed Hussain S — Mon, 07 Sep 2026 18:28:48 +0000

I was testing out CH-Ops - an admin GUI for self-hosted ClickHouse - on a simple setup: 1 shard, 2 replicas. Stumbled onto the replication queue almost by accident.

Here's what I did: I stopped one of the nodes (let's call it Node B), then inserted some data through the other one (Node A). Just wanted to see what would happen.

Then, while Node B was still down, I checked it in CH-Ops. It had stuff sitting in its replication queue.

My first assumption was: okay, this must be showing what's left to replicate across the cluster - the total pending replication work.

So I switched over and checked Node A, the one that was actually up and had just received the insert.

Its queue was empty.

That didn't match what I expected at all. If the queue was a cluster-wide "here's what still needs to replicate" view, Node A should've shown something too - it was the one that had the fresh data now waiting to reach Node B. Instead it was Node B, the down one, sitting there with pending tasks.

That mismatch is what sent me digging. Turns out the queue isn't cluster-wide at all - it's specific to each ClickHouse instance. Once I brought Node B back up, its queue drained in seconds and the data showed up.

That whole experiment is basically the entire post in miniature. Here's the mental model I ended up with.

A Queue Belongs to a Replica, Not to the Table

This is the first thing to get straight.

With a ReplicatedMergeTree table, you can have multiple replicas holding copies of the same data. It's tempting to think of replication as one shared pipe between them.

It isn't.

Each replica keeps its own local replication queue.

So if you see:

Replica 1 → queue_size = 0
Replica 2 → queue_size = 25

that doesn't mean 25 operations are waiting somewhere in the middle for both replicas to pick up.

It means Replica 2, specifically, has 25 tasks it hasn't finished yet.

Once that clicked for me, the rest of the system made a lot more sense.

So Where Do These Tasks Come From?

Replication in ClickHouse isn't a direct copy from one replica to another. There's a coordination layer in between.

ClickHouse Keeper holds the replication log - the record of operations that happened on the table. When something replicated happens (a part gets inserted, a merge runs, a mutation is applied), that operation gets logged in Keeper.

Each replica watches that log and figures out: what do I need to do to catch up to this?

That "what do I need to do" becomes a task, and the task lands in that replica's queue.

Depending on what happened, a task might be:

fetch a part
merge parts
apply a mutation
drop a range
run another replicated operation

So the queue is really just a to-do list, generated from the shared log, but executed locally.

Where You Actually See This: `system.replicas`

CH-Ops was showing me a friendlier view of exactly this, but under the hood, this is the table doing the work. It's usually the first place people look, and for good reason - it gives you a quick health snapshot per replica.

SELECT
    database,
    table,
    replica_name,
    is_leader,
    is_readonly,
    queue_size,
    inserts_in_queue,
    merges_in_queue,
    part_mutations_in_queue,
    absolute_delay
FROM system.replicas;

Example output:

┌─replica_name─┬─queue_size─┬─inserts_in_queue─┬─merges_in_queue─┬─absolute_delay─┐
│ replica_1    │ 0          │ 0                │ 0               │ 0              │
│ replica_2    │ 12         │ 3                │ 9               │ 4              │
└──────────────┴────────────┴──────────────────┴─────────────────┴────────────────┘

queue_size gives you the total. inserts_in_queue, merges_in_queue, and part_mutations_in_queue break that total down by task type, which already tells you more than the single number does.

But it still doesn't tell you why those 12 tasks are sitting there. For that, you need the next table.

The Detail View: `system.replication_queue`

If system.replicas tells you how much work is waiting, system.replication_queue tells you what that work actually is.

SELECT
    database,
    table,
    replica_name,
    type,
    create_time,
    num_tries,
    last_exception
FROM system.replication_queue
ORDER BY create_time;

Instead of a single number, you now get the actual list:

GET_PART
MERGE_PARTS
GET_PART
MUTATE_PART
GET_PART
...

You can see the task type, how long it's been sitting there (create_time), how many times it's been retried (num_tries), and - this is the important one - last_exception.

That single column is often the difference between "the queue is 12" and "I know exactly why the queue is 12."

Two Tables, Two Different Questions

Once I had both queries in my toolkit, it became clear they answer different questions:

Table	Question it answers
`system.replicas`	How much work is a replica behind on, right now?
`system.replication_queue`	What is that work, specifically, and is it stuck?

Neither one alone gives you the full picture. system.replicas is a summary. system.replication_queue is the detail behind that summary.

Queue Size Is Not the Same as Replication Lag

This one tripped me up early on, so it's worth calling out explicitly.

A replication queue measures pending work. Replication lag (absolute_delay) measures how far behind a replica is.

They're related, but they're not interchangeable.

A replica can have:

queue_size = 50

and be processing those 50 tasks fast enough that lag barely moves. A few minutes later, the queue is empty and nothing was ever "behind" in any meaningful sense.

A replica sitting at:

queue_size = 5

for hours, on the other hand, is a very different story - even though 5 sounds small next to 50.

The number by itself doesn't tell you which situation you're in. That's the whole point of this post: the queue is a data source, not a verdict.

The Mental Model, Put Simply

Here's the picture:

The replica is the participant - it holds data and does work.

The replication queue is that participant's pending work, generated from what Keeper says has happened on the table.

The queue exists because the replica has catching up to do. That's it. It's not a fault indicator on its own - it's a reflection of activity and progress.

What This Sets You Up For

Understanding the queue as "a to-do list, not a fault code" is step one.

Step two - the part where you actually decide when to worry - comes down to trend, task type, and last_exception, and that's a big enough topic to deserve its own post.

For now, the takeaway is simpler:

A non-zero replication queue isn't a problem statement. It's a starting point for one.

Know what the queue is before you try to diagnose what it means.

ClickHouse 26.8 LTS: 57 Breaking Changes Since 26.3

Mohamed Hussain S — Fri, 28 Aug 2026 09:33:52 +0000

If you run ClickHouse in production, you're probably on 26.3 LTS. And now 26.8 LTS has been announced, which means the LTS-to-LTS upgrade conversation starts again.

Here's the thing most release posts skip: this is not a one-release hop.

Going from 26.3 LTS to 26.8 LTS means crossing 26.4, 26.5, 26.6 and 26.7 as well. Every breaking change in those four releases applies to you, and some of the ones most likely to ruin your day aren't in 26.8 at all.

So instead of writing another "here are the 26.8 features" post, I wanted to write the thing I'd actually want before scheduling this upgrade: what breaks, what silently changes, what order to do things in, and what you get for the trouble.

A note on release timing

As of writing (27 August 2026), 26.8 has been announced but is not fully released yet.

The release branch is cut and versioned (v26.8.1.1-lts), but the tag and Docker images have not been published yet, and the upstream changelog still marks the 26.8 section as in progress.

By the time you read this, the tag has probably landed. Check for yourself:

curl -s https://raw.githubusercontent.com/ClickHouse/ClickHouse/master/utils/list-versions/version_date.tsv \
  | awk -F'\t' '$1 ~ /^v26\.8\./ {print "26.8 is released - newest: " $1 " (" $2 ")"; f=1; exit}
                END {if (!f) print "26.8 not released yet"}'

version_date.tsv is the list ClickHouse maintains of every released version and its date, so this is the most direct answer available - no auth, no rate limit, nothing to download. As of writing it prints 26.8 not released yet.

Worth knowing: the Docker image will lag whatever that command tells you. The Docker Official Images repo trails the GitHub tags by a few patch versions - clickhouse:lts currently resolves to 26.3.20.7 even though 26.3.24.4 has already shipped. So don't treat a missing image as evidence the release hasn't happened.

Either way, the timing works in your favour. Historically ClickHouse LTS releases pick up several patch releases quickly - 26.7 had five within a month, and 26.3 is already at 26.3.24 - so waiting for roughly 26.8.3 before touching production is the normal conservative play. Use the gap to prepare.

The scale of what you're crossing

Release	Date	Breaking	New features	Perf improvements
26.4	2026-04-30	6	34	45
26.5	2026-05-21	10	31	48
26.6	2026-06-25	10	50	79
26.7	2026-07-22	11	35	117
26.8 LTS	2026-08-27	21	49	127
Total		58	199	416

A note on that 58. The upstream changelog lists 58 backward-incompatible entries across these five releases, but one is recorded twice - the http_max_fields reduction (PR #103285) appears in both the 26.4 and 26.5 sections, presumably from a backport. That leaves 57 unique breaking changes, which is the number in the title.

Two further caveats, since I'd rather you knew than found out: three of 26.8's 21 entries still carry unresolved TODO markers upstream, and one of those is flagged "wait for the revert-of-revert," so it may not survive to the final notes. The 26.8 section is also still marked in progress, so this count can move - more likely up than down. Everything here was counted on 27 August 2026.

On top of all that, roughly 70 settings changed their default value across the five releases. Those don't appear in any "backward incompatible" list, and they're where a lot of the surprises actually live.

I've grouped everything below by what it does to you, not by which release it landed in. That's the more useful ordering when you're planning an upgrade.

Part 1: Three things that can stop the upgrade dead

Start here. These aren't "test carefully" items - they're "the server won't start" or "you can't go back" items.

1. The x86 build now requires AVX2 (26.6)

The default x86 build moved from x86-64-v2 (SSE4.2) to x86-64-v3, which needs AVX2, BMI1, BMI2, F16C, FMA, LZCNT, MOVBE and XSAVE. In practice: Intel Haswell or newer, AMD Excavator or newer.

Virtually every x86 CPU after 2015 supports this. But if you have older hardware anywhere in the fleet, or you're running on a hypervisor that masks CPU flags, the binary simply won't run.

Check every node before you plan anything else:

grep -o 'avx2' /proc/cpuinfo | head -1

No output means you need the amd64compat build, which targets plain x86-64.

This one is easy to miss because it doesn't appear in the 26.8 notes at all.

2. `insert_deduplication_version` has a mandatory migration order (26.6 → 26.7)

If your config sets insert_deduplication_version to old_separate_hashes or compatible_double_hashes, a 26.7+ server refuses to start. Not a warning - a refusal.

The migration path has to happen before you upgrade:

Run a release that supports compatible_double_hashes (it writes both the legacy and unified hashes).
Keep it running for at least replicated_deduplication_window_seconds - one hour by default - for replicated tables. For non-replicated tables with non_replicated_deduplication_window > 0, the window is count-based, so run it for at least that many inserts.
Remove the setting, or set it to new_unified_hash.
Now upgrade.

If you never set this setting, you can ignore all of it. Worth a quick SELECT * FROM system.server_settings WHERE name = 'insert_deduplication_version' to be sure.

While we're here: 26.6 also changed what dedup means. It now works on the whole inserted block per insert rather than per part or partition. A retry of the same insert is still deduplicated, but two different inserts that happen to produce an identical part are no longer cross-deduplicated, and reordered inserts of the same rows are no longer deduplicated either. If you were relying on the old behaviour as a correctness guarantee, that's worth thinking about.

3. S3 stops using the server's own credentials for user queries (26.7)

This is the one I'd most expect to catch people out, because of how it fails.

S3 access originating from user SQL no longer resolves the server's own cloud credentials by default - not environment variables, not IMDS/IRSA, not instance profiles, not AWS config files, not role_arn-based STS, not GCP OAuth metadata. Such requests now need explicit credentials or NOSIGN. Named collections default use_environment_credentials to 0.

To restore the old behaviour you need both:

SET use_environment_credentials = 1;
SET s3_allow_server_credentials_in_user_queries = 1;

The nastier part is startup behaviour. A persistent S3 or S3Queue table, a dynamic S3 disk, or a DataLakeCatalog whose definition resolves such credentials now loads anonymously rather than aborting server startup. That's controlled by s3_load_table_anonymously_if_credentials_restricted, which is on by default.

So the server comes up clean, everything looks healthy, and then queries against those tables fail at read time. Test every S3 access path before this hop, not after.

Part 2: Changes that alter results without throwing an error

These are the ones that won't show up in your logs. Your queries keep running; the answers change.

Loose date strings now parse (26.5)

date_time_input_format and cast_string_to_date_time_mode both moved from basic to best_effort.

Strings like 2024 April 4 or Apr 15, 2020 10:30:00 used to be rejected. Now they parse. If you were relying on strict parsing to reject malformed input at the door, that door is now open - bad data lands and gets interpreted rather than bouncing.

`CAST` preserves the source time zone (26.5)

CAST to DateTime or DateTime64 without an explicit time zone now preserves the time zone of its source argument, matching what toDateTime and toDateTime64 already did.

More correct, but it changes displayed timestamps in existing queries. If you have dashboards where times shifted after an upgrade, this is a likely culprit.

`FINAL` on a JOIN stopped leaking to other tables (26.6)

This was a bug fix, but it changes results.

Previously, FINAL on the left-most table of a JOIN was also applied to the other joined tables. That was never intended. Now it isn't.

If you have queries that were quietly depending on the old behaviour, they'll start returning pre-merge duplicates from the right-hand side. There's a compatibility setting:

SET analyzer_compatibility_apply_final_to_all_joined_tables = 1;

It's registered in the settings history, so compatibility set below 26.6 restores it automatically.

Unquoted JSON numbers become timestamps (26.8)

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

Concretely, before this change:

1703363853.035 was rejected outright
A bare 1703363853 was read as the raw scaled tick value, producing a 1970-... timestamp

Now both do what you'd expect. Quoted strings and ClickHouse's own JSON output are unaffected.

For most people this silently fixes things. But if your pipeline was compensating for the old raw-ticks behaviour - pre-scaling values, or writing quoted strings specifically to avoid it - you'll now get a double correction.

SET input_format_read_datetime_number_as_raw_value = 1;

`toDate32` reinterprets a whole numeric range (26.8)

Date32 extended its range from [1900-01-01, 2299-12-31] to [0000-01-01, 9999-12-31], matching DateTime64.

The subtle consequence: 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. If you feed epoch seconds into toDate32, you get wildly different results.

Array function semantics fixed (26.8)

arrayIntersect and arraySymmetricDifference no longer count a value repeated within a single argument as if it appeared in several arguments:

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

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

There's a nice side effect: arrayIntersect now builds its hash table from the smallest argument rather than all of them - up to 1.85x faster and about a third less memory when argument sizes differ a lot.

Trivial views over `Distributed` tables push down (26.8)

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 optimize_trivial_view_pushdown_to_distributed, on by default.

Two observable changes: FINAL and SAMPLE written on the view reference now propagate to the shard-local table instead of being silently ignored, and extremes isn't reported on single-shard clusters.

If you had views where FINAL was quietly a no-op, your results change - more correct, probably slower.

Part 3: Operational behaviour changes

`max_insert_threads` now defaults to auto (26.8)

Probably the biggest operational change in 26.8. INSERT SELECT is now parallelised across available CPU cores by default, and max_insert_threads can also fan out the write side of a plain INSERT where the destination path can safely do so.

Faster ingestion, but with consequences:

More data parts from the same query, so more merge pressure and a real risk of too_many_parts on tables with tight parts_to_throw_insert
Different row order within an insert, which matters for anything depending on insertion order - a non-deterministic ORDER BY tie-break, _part / _block_number assumptions, a ReplacingMergeTree without a proper version column

One detail worth knowing if you diff configs: the declared default is literally 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 system.settings reads 0 before and after while the behaviour flips underneath you.

Upstream records the change as 1 → 0 in its compatibility mapping, which looks contradictory until you realise 1 is what you now set to get the old behaviour - not what the declaration used to say.

SET max_insert_threads = 1;  -- restores the old behaviour

Lightweight update patch parts get a v2 format (26.8)

Patch parts produced by lightweight UPDATE now use a v2 on-disk format, sorted by (sorting_key..., _block_number, _block_offset) with a new merge algorithm. Peak memory during apply is bounded by the largest equal-sort-key run rather than the whole patch, and updates crossing merge boundaries no longer fall back to an in-memory Join apply. Old-format patch parts stay readable.

For replicated clusters, this needs a rolling-upgrade pin. Note that patch_parts_version is a MergeTree setting, not a session setting, so SET patch_parts_version = 'v1' will throw. Use the config:

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

Or per table:

ALTER TABLE my_table MODIFY SETTING patch_parts_version = 'v1';

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

The same logic applies to text indexes: text_index_serialization_version defaults to v2_with_positions in 26.8, which persists token positions for phrase search. Older servers can't read that format, so pin it to v1_with_codec during the rollout.

Hash joins now spill by default (26.5)

max_bytes_ratio_before_external_join moved from 0 to 0.5. Hash joins now spill to disk at 50% of the memory limit.

Generally a good thing - fewer OOM kills. But if you had joins comfortably fitting in memory, some of them will now start writing to disk and get slower. Watch join-heavy query latency after the upgrade.

`REPLACE PARTITION` from an empty source is now rejected (26.6)

Previously, ALTER TABLE ... REPLACE PARTITION ... FROM ... where the source table had no parts in that partition would silently drop the destination partition and write nothing. A genuine data-loss footgun.

It's now rejected with BAD_ARGUMENTS. If you have automation that relied on the old behaviour as a clearing mechanism, it will start failing - which is the correct outcome, but it will fail. Use DROP PARTITION explicitly, or set allow_replace_partition_from_empty_source = 1.

`AggregatingMergeTree` validates schemas at creation (26.7)

AggregatingMergeTree now rejects, at table creation time, schemas where a column is neither part of the sorting key nor an aggregate-state measure (AggregateFunction / SimpleAggregateFunction).

Those columns were silently collapsed to an arbitrary value during background merges, producing wrong results for anything grouping or filtering on them. Now you find out up front.

Existing tables keep working, but any automation that recreates such tables will start failing. allow_dimensions_outside_sorting_key = 1 restores the old permissiveness.

HTTP request limits tightened (26.4)

http_max_fields dropped from 1,000,000 to 1,000, and http_max_field_name_size from 128 KB to 4 KB, to limit pre-authentication memory usage. New http_max_request_header_size and http_headers_read_timeout settings arrived alongside.

If any client sends a lot of headers or URL parameters - some role-based auth proxies do - it'll start getting rejected.

Part 4: The tooling around ClickHouse

This is the part upgrade checklists usually miss. ClickHouse itself starts fine; the dashboards, exporters and scripts built around it don't.

Asynchronous metrics became maps (26.8)

Per-CPU-core and per-device metrics were consolidated into single key-value metrics. OSUserTimeCPU0, OSUserTimeCPU1, OSUserTimeCPU2 became a single OSUserTimeCPU metric holding a map from core number to value.

The same applies to the other OS*TimeCPU* metrics, plus CPUFrequencyMHz_*, Temperature*, EDAC*, Block*_*, Network(Receive|Send)*_*, Disk*_*, *BlobsQueueEstimate and AsyncLogging*QueueSize.

Downstream:

system.asynchronous_metrics gains key_values Map(LowCardinality(String), Float64). The plain value column is NaN for those metrics
system.asynchronous_metric_log logs one row per key via a new key column
Prometheus exports them as labels: ClickHouseAsyncMetrics_BlockReadBytes{device="sda"}
Graphite MetricsTransmitter sends <prefix>.<Metric>.<key>

Any Grafana panel keyed on the old metric names goes blank. Not errors - just empty charts, which is worse because nobody notices for a week.

`EXPLAIN PLAN` output format changed (26.7)

explain_query_plan_default moved from legacy to pretty, so EXPLAIN PLAN now defaults to actions=1, compact=1, pretty=1.

If you have anything that parses EXPLAIN output - query validators, cost estimators, index advisors, any "explain this query" feature in an internal tool - it breaks. This one is in 26.7, so a 26.8-only reading of the changelog misses it entirely.

SET explain_query_plan_default = 'legacy';

`EXPLAIN SYNTAX` returns one row (26.8)

EXPLAIN SYNTAX now returns the reformatted query as a single String record with embedded newlines, instead of one record per line. So SELECT count() FROM (EXPLAIN SYNTAX ...) returns 1.

Two ways to restore the old shape - the per-statement option and the session setting:

EXPLAIN SYNTAX single_record = 0 SELECT 1;
-- or session-wide:
SET explain_syntax_single_record = 0;

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

System table changes

Change	Release	Impact
`system.users.valid_until` is now `Array(DateTime64(0))`, was `Array(DateTime)`	26.8	Deadlines past 2106 are exact. Tooling reading this column needs to handle the new type
`show_data_lake_catalogs_in_system_tables` renamed to `show_remote_databases_in_system_tables` and broadened	26.6	At the default `0`, MySQL and PostgreSQL databases are now hidden from `system.tables`, `system.columns` and `system.completions` - not just data lake catalogs. Schema browsers will show fewer databases
`system.instrumentation.parameters` renamed to `arguments`; `HANDLER PARAMETERS` syntax renamed to `HANDLER ARGUMENTS`	26.6	Old names are gone, not aliased
`system.histogram_metric_log` deprecated	26.5	Histograms are now a nested column on `system.metric_log`

`allow_feature_tier` numbers shifted (26.8)

A new private preview tier sits between experimental and beta, so the ordering is now experimental < private preview < beta < production.

The numeric levels moved with it: 0 allows all tiers, 1 excludes experimental, 2 additionally excludes private preview, 3 allows production only.

If you pinned allow_feature_tier = 2 to mean "production settings only", you now need 3. This is filed under New Feature in the changelog, but it's a silent behaviour change to an existing config value.

Part 5: Removals - the grep list

Things that no longer exist. Each of these is a cheap search through your codebase and configs.

Removed	Release	Replacement
`library` dictionary source (`SOURCE(LIBRARY(...))`)	26.8	Fails with `UNKNOWN_ELEMENT_IN_CONFIG`. `dictionaries_lib_path` is inert
`include_from` defaulting to `/etc/metrika.xml`	26.8	Add `<include_from>/etc/metrika.xml</include_from>` explicitly. Separately-loaded `users.xml` and XML dictionary configs each need their own
MySQL source `ssl_ca` / `ssl_cert` / `ssl_key` as file paths from SQL	26.8	Use `ssl_ca_pem` / `ssl_cert_pem` / `ssl_key_pem` with inline contents. Paths still work in the server config file
NATS `nats_credential_file` from SQL	26.8	Use inline `nats_credentials`, or a named collection in the server config
Apache Arrow-based `Arrow` / `ArrowStream` reader and writer	26.8	Native implementation only. The `*_use_native_reader` / `_writer` settings are accepted but ignored
Config-based workload scheduling (`resources`, `workload_classifiers` sections)	26.7	`CREATE RESOURCE` / `CREATE WORKLOAD`. Old sections ignored with a warning
`snowflakeToDateTime`, `snowflakeToDateTime64`, `dateTimeToSnowflake`, `dateTime64ToSnowflake`	26.7	The `snowflakeID*` variants. Deprecated since 24.6
`hasColumnInTable` remote form (hostname/username/password args)	26.7	Only `hasColumnInTable(database, table, column)`. The remote mode allowed arbitrary outbound connections and leaked credentials into query logs
`zip` / `zipx` backup archives on object storage	26.7	Use `tar.gz`. Zip needs seeking to read its central directory, which is very slow over object storage
Naive Bayes models as XML config + `.bin` files	26.7	Recreate as dictionaries with the `NAIVE_BAYES` layout. Upside: roughly 49x less memory and 11x faster on the cited model
`kql` table function	26.5	`SET dialect = 'kusto'`
Arrow-based Parquet reader and writer	26.5	Native implementation
KQL `array_sort_asc` / `array_sort_desc`	26.6	Now `UNKNOWN_FUNCTION`
`allow_experimental_query_deduplication`	26.6	Removed entirely
Arguments to `RANK` / `DENSE_RANK`	26.5	Per SQL standard these take zero arguments. `allow_rank_dense_rank_arguments = 1` restores leniency

Also worth checking: icebergHash and icebergBucket now reject 128/256-bit integers and Decimal256 (26.6) instead of silently truncating them into colliding hashes, and nested Dynamic/Variant are rejected in min/max aggregates and minmax indexes (26.6).

Part 6: What you actually get for it

It's worth saying: this is a lot of upgrade work, and the payoff is real. 416 performance improvements across the five releases, most of them enabled by default.

Aggregation and grouping. A new adaptive parallel GROUP BY algorithm where each thread aggregates into its own cache-resident hash table until it hits a threshold, then freezes it. 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 instead of HashMap, up to 1.8x faster.

Reads. Lazy materialization for Parquet on object storage - on a 200 MB S3 file, an ORDER BY ... LIMIT 10 read 3.3 MB instead of 171 MB. That's 51x less I/O and 8x faster. read_in_order_use_virtual_row on by default cuts peak memory when reading in primary key order over tables with many parts. Dictionary-page-based row group skipping in the Parquet V3 reader.

Joins. IEJoin, a sort-based algorithm for ON clauses with two inequality comparisons - previously those ran as a CROSS JOIN with a filter, INNER only. parallel_full_sorting_merge, which shards a merge join by join-key hash across threads, benchmarked around 2.4x faster and 3.3x less memory than parallel_hash. Constant-false JOIN conditions no longer read the non-contributing side.

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, skipping the hash scatter entirely.

On the feature side, the ones I'd actually reach for:

Pipe operators - FROM t |> WHERE ... |> AGGREGATE ... |> ORDER BY ..., GoogleSQL-style. In a query starting with FROM, SELECT is optional and defaults to SELECT *
run_query_in_background - the server accepts the query, returns immediately, and runs it to completion regardless of what happens to the connection. Built for long INSERT SELECT, CTAS and POPULATE
CREATE HANDLER / ALTER HANDLER / DROP HANDLER - custom HTTP handlers defined in SQL, persisted locally or in Keeper, with currentHandler(), currentRequestURL() and system.handlers. This could genuinely replace some small API services
Atomic POPULATE - CREATE MATERIALIZED VIEW ... POPULATE is now locally atomic, so rows inserted through the same server during population aren't missed or duplicated. Local insert path only, and it needs a snapshot-capable source. POPULATE also works with TO now
system.user_query_log - every user sees their own query log rows without needing access to system.query_log. Useful if you're building multi-tenant tooling
create_union_system_log_tables - auto-maintained all_... tables like system.all_query_log, unioning a log table, its rotated versions, and the same table across cluster replicas
Keeper on-disk storage via a custom LSM tree, with similar performance to the in-memory storage, and a Raft topology view in the Keeper dashboard
GROUPS window frames (SQL:2011), where boundaries count peer groups rather than physical rows or value distances
New text index tokenizers - Japanese (MeCab), Chinese (jieba-style), ICU locale-aware segmentation, and splitByRegexp, which keeps tokens like C++ and C# intact
Data lake expansion - BigQuery table function and engine, Snowflake Horizon (read and write Iceberg), S3 Tables catalog with working INSERT, and Puffin format support

The upgrade plan

Here's the order I'd actually do this in.

Step 0: Audit before you touch anything

-- Dictionaries using the removed library source
SELECT name, source FROM system.dictionaries WHERE source ILIKE '%library%';

-- AggregatingMergeTree schemas that 26.7 would now reject
SELECT database, table, name, type
FROM system.columns
WHERE (database, table) IN (
    SELECT database, name FROM system.tables
    WHERE engine LIKE '%AggregatingMergeTree%'
  )
  AND is_in_sorting_key = 0
  AND type NOT LIKE '%AggregateFunction%';

-- Non-default settings you're carrying
SELECT name, value, default FROM system.settings WHERE changed;
SELECT name, value FROM system.server_settings WHERE changed;

-- Removed functions still in use
SELECT DISTINCT query FROM system.query_log
WHERE event_date >= today() - 30
  AND type = 'QueryFinish'
  AND (query ILIKE '%snowflakeToDateTime%'
       OR query ILIKE '%dateTimeToSnowflake%'
       OR query ILIKE '%array_sort_asc%'
       OR query ILIKE '%array_sort_desc%'
       OR query ILIKE '%kql(%')
LIMIT 100;

Plus a manual pass over:

/proc/cpuinfo on every node for AVX2
insert_deduplication_version in your config
Every S3 access path - tables, disks, catalogs, named collections
/etc/metrika.xml reliance
MySQL ssl_* paths and NATS nats_credential_file in named collections and dictionaries
Backup scripts using zip to object storage
resources / workload_classifiers config sections
allow_feature_tier = 2 in config
Ingestion clients writing unquoted epoch numbers into DateTime64 via JSONEachRow

Step 1: Use `compatibility` as a shock absorber

SET compatibility = '26.3';

Set this globally, upgrade the binaries, confirm the cluster is healthy, then peel it back one release at a time - 26.4, 26.5, 26.6, 26.7 - with a validation pass between each.

This restores the great majority of the ~70 default flips in one move, which turns "57 breaking changes at once" into something you can bisect. It does not cover removals, renames or type changes. Those need actual code fixes, which is what Step 0 finds.

Step 2: Pin the on-disk formats during the rollout

While a mixed-version cluster exists:

patch_parts_version = 'v1' (MergeTree setting, config or ALTER TABLE)
text_index_serialization_version = 'v1_with_codec'

Remove both once every replica is on 26.8.

Two other on-disk changes are safe by design and don't need pinning: packed_skip_index_max_bytes (defaults to 1 MiB, affects only new parts, still readable by older servers) and compute_exact_num_defaults_for_sparse_columns (the flag in serialization.json is ignored by older versions, so parts survive a downgrade).

Step 3: Watch these after the upgrade

Part counts and merge queue depth in system.parts and system.merges - the max_insert_threads effect
system.errors for UNACCEPTABLE_URL, ILLEGAL_COLUMN, UNKNOWN_ELEMENT_IN_CONFIG and BAD_ARGUMENTS, which cover most of the new rejections
Monitoring gaps where per-CPU and per-device async metrics used to be
Join memory profiles - max_bytes_ratio_before_external_join = 0.5 means spilling starts where it previously didn't
Anything that parses EXPLAIN output

Final thoughts

The pattern I'd take away from this: the changes most likely to hurt you aren't in the release you're upgrading to. AVX2, the S3 credential change, the dedup migration ordering and the EXPLAIN output format all landed in 26.6 and 26.7. If you read only the 26.8 notes - which is what most people do for an LTS upgrade - you miss all four.

The second pattern: a meaningful share of the risk sits around ClickHouse rather than inside it. Dashboards keyed on metric names, scripts parsing EXPLAIN output, schema browsers reading system.tables, tooling reading system.users. The server will start fine and those will quietly break.

None of which is an argument against upgrading. 416 performance improvements, mostly on by default, is a genuinely good deal - and 26.3 won't be the current LTS forever.

Just budget for it as a five-release jump, because that's what it is.

Verified against the upstream CHANGELOG.md, SettingsChangesHistory.cpp and the 26.8 release branch source as of 27 August 2026. If you spot something that's drifted since, let me know in the comments.

Understanding the ORC File Format: Why Is It So Fast

Mohamed Hussain S — Fri, 21 Aug 2026 10:15:32 +0000

Every article about ORC says the same thing.

"Columnar storage, built-in indexes, predicate pushdown - so it's fast."

That's not an explanation. That's a slogan.

So I wrote a small ORC parser from scratch, generated 5 million rows, and opened the file with my hands.

The question I actually wanted answered:

What happens inside an ORC file when a query asks for 2 columns out of 100?

What a Columnar File Actually Does

Strip away the marketing, and it comes down to three things.

Store each column's values together, so you can read one column without touching the others.
Encode those values in ways that only work when neighbours are similar.
Keep a small map of what's inside, so a query can skip most of the file without opening it.

Everything else - compression codecs, bloom filters, ACID support - is built around that core job.

The Setup

An orders table. Deliberately boring.

order_id     int64    sequential
customer_id  int64    250k distinct
country      string   10 distinct
product      string   2,000 distinct
quantity     int32    1-19
price        double   40k distinct
order_date   date     730 days
status       string   5 distinct

5,000,000 rows, written with PyArrow:

orc.write_table(
    tbl, "orders.orc",
    compression="zlib",
    stripe_size=8 * 1024 * 1024,      # small on purpose
    row_index_stride=10_000,
    dictionary_key_size_threshold=0.8,
)

Production ORC uses 64-256 MB stripes. My whole file is 53 MB, so a default stripe would swallow the entire dataset and there'd be nothing to skip. Shrinking it is how you see multi-stripe behaviour on a laptop.

Same data also went out as a 310 MB CSV, for scale.

ORC Is Read Backwards

The last byte of the file is a single number: the length of the PostScript.

Here are the final 26 bytes of my file:

08 c0 03 10 01 18 80 80 04 22 02 00 0c 28 97 05 30 06 82 f4 03 03 4f 52 43 19

That's a Protocol Buffers message. Decoded:

field	value	meaning
1	448	footer length
2	1	codec = ZLIB
3	65536	compression block size
5	663	metadata length

So a query engine does three reads: last byte, PostScript, then footer + metadata. A few hundred bytes on a 53 MB file.

After that it knows the schema, the row count, every stripe's byte offset, and min/max stats for every column.

It hasn't touched a single row of data yet.

Note the ordering: the codec lives in the PostScript, and the PostScript is never compressed. It can't be - you'd need the codec to decompress the thing that tells you the codec.

Stripes

A stripe is a self-contained slab of rows. Its own index, its own data, its own footer.

 #       offset     index         data  footer       rows
 0            3     8,412    8,544,070     159    687,104
 1    8,552,644     8,487    8,556,939     158    688,128
 ...
 7    59,917,580     2,796    2,315,836    152    186,176

8,412 bytes of index for 8.5 MB of data. 0.1% overhead for the ability to skip.

That ratio is the entire trade.

Because each stripe carries its own footer, you can hand a Spark executor one byte range and it can decode that stripe with zero knowledge of the rest of the file. That's the basis of parallel scans.

Where a Column Physically Lives

Inside a stripe, a column isn't "a column." It's a set of streams.

col name       stream                   bytes
  3 country    DICTIONARY_DATA             23
  3 country    LENGTH                       5
  3 country    DATA                   310,404
  6 price      DATA                 2,374,052

All streams for one column sit contiguously. "Read country from stripe 3" is one range read, not 687,104 scattered seeks.

There's also a PRESENT stream for nulls - and ORC omits it entirely when a column has none. My columns are all non-null, so null tracking cost zero bytes. A row format pays for nullability on every row whether you use it or not.

Encodings Do More Than Compression Does

People credit ORC's size to compression. Mostly wrong.

Bytes per column, whole file:

column	bytes	bytes/row
order_id	11,541	0.002
price	17,022,257	3.404
customer_id	13,293,357	2.659

Five million 64-bit integers in 11 KB.

That's ORC's RLE v2 picking its DELTA sub-encoding: for a monotonic sequence it stores a base, a delta width, and a run length. zlib never saw 40 MB of integers, because the encoder never produced them.

Same effect on sorted dates:

order_date, 5M values:
  random order : 7,390,184 bytes
  sorted       :     4,898 bytes    <- 1,500x smaller

The PyArrow gotcha

dictionary_key_size_threshold defaults to 0.0, which disables dictionary encoding entirely.

	DIRECT (default)	DICTIONARY
status	796,196 B/stripe	271,047 B/stripe
whole file	62.24 MB	53.32 MB

14% of the file, from one keyword argument.

Compression Sits On Top

Only after encoding does the codec run - in independent 64 KB chunks, each with a 3-byte header. Bit 0 says "stored raw", because if compressing a chunk made it bigger, ORC just doesn't.

Chunking is what makes seeking possible. One big compressed blob would mean inflating 8 MB to read the last value.

codec	file MB	vs CSV	write s	full scan s
uncompressed	86.14	3.6x	2.15	1.73
snappy	69.49	4.5x	2.35	0.51
zlib	53.32	5.8x	4.45	1.02
zstd	51.72	6.0x	2.34	0.53

zstd wins on all three axes. zlib is only still the default because ORC is old.

Also worth noting: uncompressed ORC is still 3.6x smaller than CSV. That gap is pure layout and encoding.

I Actually Measured the Skipping

ORC keeps min/max statistics at three zoom levels: file (448 B), stripe (663 B), and row group of 10,000 rows (8.4 KB per stripe).

Query:

SELECT price FROM orders
WHERE order_date BETWEEN '2024-06-01' AND '2024-06-30'

One month out of 24. About 4% of rows. Same query, same data, two files - one in insertion order, one sorted by order_date.

Unsorted:

stripe 0   2023-01-01 .. 2024-12-30   READ
stripe 1   2023-01-01 .. 2024-12-30   READ
...
stripes to read: 8/8
bytes read: 24,430,256

Every stripe spans the whole date range, so every min/max says "maybe". Zero pruning.

Sorted by order_date:

stripe 0   2023-01-01 .. 2023-04-10   SKIP
stripe 4   2024-02-02 .. 2024-05-11   SKIP
stripe 5   2024-05-11 .. 2024-08-19   READ
stripe 6   2024-08-19 .. 2024-11-26   SKIP
...
stripes to read: 1/8
bytes read: 2,335,435          <- 13.7% of the unsorted read

And inside that one surviving stripe, the row-group index eliminated another 68%: 22 of 69 row groups matched. The engine ends up decoding roughly 220,000 of 5,000,000 rows.

The uncomfortable conclusion:

ORC's statistics are a summary, not an index. Their usefulness is entirely determined by how you wrote the data.

Sorting by your dominant filter column isn't a tuning detail. It's the difference between skipping 87% of the file and skipping nothing.

A predicate stats can't help with

country = 'IN'
stripe ranges: ('AU','US'), ('AU','US'), ('AU','US'), ...
stripes that could contain 'IN': 8/8

Scattered values mean every range straddles the target. That's the gap bloom filters fill - they cost 1.4% of file size on product, and are a waste on country.

Does Column Projection Actually Pay Off?

I wrapped the file handle in a counter so I could see every byte the reader actually pulled off disk.

8 columns, 53 MB:

query	bytes read	% of file
`SELECT *`	53,279,130	99.93%
`SELECT country, price`	19,265,356	36.13%
`SELECT order_id`	29,356	0.06%

100 columns, 1M rows, 153 MB - the original question:

query	bytes read	% of file	time
`SELECT *`	153,188,271	99.92%	3.03s
`SELECT order_id, price`	3,424,697	2.23%	0.04s

2 columns out of 100 = 2.23% of the bytes. Not "less I/O" in a hand-wavy sense. 45x less, measured.

Reading 2 columns from the 310 MB CSV still requires pulling all 310 MB off disk and parsing every field to find the commas.

How Big Should a Stripe Be?

Same sorted data, same June predicate:

stripe target	stripes	rows scanned	% rows
2 MB	29	350,208	7.0%
8 MB	8	680,960	13.6%
32 MB	2	2,294,592	45.9%
64 MB	1	5,000,000	100.0%

Halve the stripe, halve the rows you're forced to scan - until pruning bottoms out at the real selectivity. Total file size moved 0.3% across that whole range, because more stripes cost more metadata, not more data.

So why not 2 MB stripes everywhere? Because a stripe is also the unit of parallelism, and on object storage each one is a separate GET. The row-group index already gives you 10,000-row granularity inside a big stripe - that's the layer meant to do fine pruning. Keep stripes big.

What I Couldn't Test

A real cluster. Everything here is one machine, local NVMe. On S3, request count matters more than byte count, and my numbers say nothing about that.
lz4 in this build. It produced 86.02 MB - essentially identical to uncompressed. Either the PyArrow build isn't wiring it up or something else is wrong. I'm reporting it rather than quietly dropping the row, but don't trust that number.
Bloom filter effectiveness. I measured what they cost (1.4% of file size), not what they save, because PyArrow's reader doesn't expose filter pushdown for ORC. You'd need Hive or Trino for that.

Where This Doesn't Help

Columnar layout is a bet, and it loses on some workloads.

Not for OLTP. Inserting one row means rewriting a stripe. Fetching one full record means touching every column's streams separately.
Small files kill it. All this metadata machinery amortizes over large files. A thousand 2 MB ORC files is the classic way to make a fast format slow.

One Thing a Different Columnar Format Does Differently

ORC isn't the only format built on these ideas - Parquet uses the same stripe/row-group/statistics playbook. The clearest place they diverge is encoding scope.

Same data, same codec, same row grouping:

order_id (sequential)    ORC 7.8 KB    vs   Parquet 7.2 MB     (912x)
price (40k distinct)     ORC 16.3 MB   vs   Parquet 10.2 MB    (0.62x)

ORC's dictionary encoding applies only to string columns - a DOUBLE always goes out DIRECT, full width, and only the compressor gets a shot at it. Parquet dictionary-encodes any physical type, so a double with modest cardinality compresses to narrow indices where ORC can't.

That's a real, measured trade-off, not a verdict. It's also its own rabbit hole - I measured it properly in a follow-up post.

So, Why Is It Fast?

Not one reason. A chain, where each link multiplies the next.

columnar layout      -> read 2 columns, not 100          (45x fewer bytes)
  x encodings        -> delta/dictionary before codec    (up to 3,467x on one column)
  x compression      -> chunked, seekable                (~2x on top)
  x statistics       -> skip stripes that can't match    (8x when sorted)
  x row-group index  -> skip 10k-row blocks inside those (3x more)
  ---------------------------------------------------------------
  = read 2.2% of the file, decode 4% of the rows you touched

Every layer is doing the same thing from a different angle: making it possible to not read something.

ORC isn't fast because it's clever with the bytes it reads. It's fast because it spends 0.1% of the file on a map detailed enough to avoid reading almost all of them.

And the part that's actually in your control: sorting by your filter column moved my query from 24 MB to 2.3 MB. No format setting in this entire post came close to that.

Why My ClickHouse Server Ran Out of Disk (and It Wasn't the Data I Expected)

Mohamed Hussain S — Wed, 22 Jul 2026 10:57:03 +0000

A deployment failed with an error that had nothing to do with ClickHouse on the surface.

usermod: /etc/passwd... No space left on device

At first, it looked like a Linux problem. Maybe a Docker problem.

It wasn't either.

The root filesystem was completely full.

Checking Disk Usage

The first step was the obvious one.

df -h

/dev/sda1   96G   100% Used

100% used. No surprise there, given the error. But that's a symptom, not a cause.

Ruling Out Inode Exhaustion

Before assuming it was actual data filling the disk, I checked whether it was inode exhaustion instead - a different problem that looks similar from the outside.

df -i

3% inode usage

Not an inode problem. Something was genuinely consuming space.

Finding What Was Actually Consuming Disk

Instead of guessing, I started at the root and walked down.

sudo du -sh /* | sort -hr

/var was consuming almost everything.

sudo du -sh /var/* | sort -hr

/var/lib.

sudo du -sh /var/lib/* | sort -hr

/var/lib/clickhouse.

sudo du -sh /var/lib/clickhouse/* | sort -hr

store.

Five commands, one directory deeper each time. No assumptions, just narrowing the search space until there was nowhere left to hide.

Finding Which Table Actually Owned the Space

store is where ClickHouse keeps its actual data parts, so this didn't tell me what was large, only where. I connected to ClickHouse to ask it directly.

SELECT
    database,
    table,
    formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts
GROUP BY database, table
ORDER BY sum(bytes_on_disk) DESC;

system.text_log   76 GiB

Not application data. Not the tables I actually cared about.

system.text_log - ClickHouse's own internal server log, stored as a table - had quietly grown to 76 gigabytes.

Nobody had configured a retention policy on it. It had just been logging, forever, since the server started.

Deciding Not to Preserve It

This was a development server, so I didn't need to preserve anything. Stop the service, clear the data, move on.

sudo systemctl stop clickhouse-server

sudo rm -rf /var/lib/clickhouse/*

Checked the disk.

df -h

Still full.

sudo du -sh /var/lib/clickhouse

87G

The wildcard delete hadn't actually cleared everything. I listed the directory directly instead of trusting the glob.

sudo ls -lah /var/lib/clickhouse
sudo bash -c 'du -sh /var/lib/clickhouse/* | sort -hr'

87G   store

store was still there. I never fully pinned down why the first wildcard delete skipped it - possibly the server hadn't finished releasing file handles the moment systemctl stop returned. Either way, trusting the first command's exit code without re-checking the actual directory size cost me a step.

Removed it directly.

sudo rm -rf /var/lib/clickhouse/store

df -h

84G free

Problem solved, for now. But "for now" wasn't good enough, because the same 96GB disk was still the only place ClickHouse could write to.

The Real Fix: Get ClickHouse Off the Root Disk Entirely

The server had a second, 256GB disk. Unmounted. Unused. The whole time.

lsblk -f

sda   root filesystem
sdb   ext4   UUID=b16098f7-bd6a-496d-a2f5-86ad19913c7c

Mounted it temporarily to check what was on it.

sudo mkdir -p /mnt/chdisk
sudo mount /dev/sdb /mnt/chdisk
sudo ls -la /mnt/chdisk

lost+found

Empty. Unmounted it again and made the arrangement permanent instead of temporary.

sudo umount /mnt/chdisk

Configuring the Permanent Mount

Edited /etc/fstab:

UUID=b16098f7-bd6a-496d-a2f5-86ad19913c7c  /var/lib/clickhouse  ext4  defaults  0  2

Moved the old (now-empty) directory out of the way, created a fresh mount point, and mounted it.

sudo mv /var/lib/clickhouse /var/lib/clickhouse.old
sudo mkdir /var/lib/clickhouse
sudo mount -a
sudo systemctl daemon-reload

Verified the mount actually took:

findmnt /var/lib/clickhouse

TARGET                SOURCE
/var/lib/clickhouse   /dev/sdb

Every read and write to /var/lib/clickhouse now transparently goes to /dev/sdb instead of the root disk. ClickHouse doesn't need to know or care - as far as its own config is concerned, it's still just writing to /var/lib/clickhouse.

Fixed ownership and permissions, since a fresh mount point doesn't inherit them:

sudo chown -R clickhouse:clickhouse /var/lib/clickhouse
sudo chmod 750 /var/lib/clickhouse

Restarted and confirmed:

df -h

/dev/sda1   84G free
/dev/sdb    mounted on /var/lib/clickhouse

What I'm Changing Going Forward

Clearing text_log fixed the immediate problem. It didn't fix the actual cause - nothing stops it from growing back to 76GB again.

The real fix is giving ClickHouse's system logs a retention policy, the same way you'd expect any log to expire eventually.

<clickhouse>
    <text_log>
        <ttl>event_date + INTERVAL 7 DAY DELETE</ttl>
    </text_log>

    <trace_log>
        <ttl>event_date + INTERVAL 7 DAY DELETE</ttl>
    </trace_log>

    <metric_log>
        <ttl>event_date + INTERVAL 7 DAY DELETE</ttl>
    </metric_log>

    <part_log>
        <ttl>event_date + INTERVAL 7 DAY DELETE</ttl>
    </part_log>

    <query_log>
        <ttl>event_date + INTERVAL 7 DAY DELETE</ttl>
    </query_log>

    <query_thread_log>
        <ttl>event_date + INTERVAL 7 DAY DELETE</ttl>
    </query_thread_log>

    <processors_profile_log>
        <ttl>event_date + INTERVAL 7 DAY DELETE</ttl>
    </processors_profile_log>

    <asynchronous_metric_log>
        <ttl>event_date + INTERVAL 7 DAY DELETE</ttl>
    </asynchronous_metric_log>
</clickhouse>

Dropped in as /etc/clickhouse-server/config.d/system_log_retention.xml, this keeps every system log table capped at 7 days of history, deleted automatically, no manual cleanup required again.

Before and After

The Bigger Lesson

I went in assuming a full disk meant the actual data had grown too fast.

It hadn't. The application data was fine. What had grown out of control was ClickHouse's own internal logging - a system table nobody had put a limit on, quietly consuming more space than the data it was supposed to be observing.

And underneath that, a second problem had been sitting there the whole time: a 256GB disk that was never mounted, on a server that only ever had 96GB to work with.

Neither problem was visible from df -h alone. Getting to the real cause meant walking the filesystem one directory at a time, then asking ClickHouse directly which table actually owned the space.

Isolating database storage onto its own disk, separate from the OS, isn't just a performance habit. It's what stops a logging table from being able to take your entire root filesystem down with it.

Can ClickHouse Replace a Vector Database?

Mohamed Hussain S — Tue, 21 Jul 2026 12:07:17 +0000

Vector databases like Pinecone, Weaviate, and Milvus exist for one job: store embeddings and find the nearest ones to a query vector, fast.

ClickHouse wasn't built for that.

It was built for analytics - scanning huge tables of structured data quickly.

But ClickHouse can also store vectors and search them, using plain SQL.

So the real question isn't "does ClickHouse support vector search."

It's "is that support good enough to skip a dedicated vector database."

What a Vector Database Actually Does

Strip away the marketing, and it comes down to three things.

Store embeddings (arrays of floats).
Index them so similarity search doesn't require scanning everything.
Return the nearest neighbors to a query vector.

Everything else - hybrid search, metadata filtering, embedding generation - is built around that core job.

Storing Vectors in ClickHouse

Vectors are just arrays.

CREATE TABLE articles (
    id UInt32,
    title String,
    embedding Array(Float32)
) ENGINE = MergeTree ORDER BY id

No extension needed. No plugin. This is a normal column type.

Brute Force Search

The simplest way to find similar vectors is a distance function, ordered and limited.

SELECT title, L2Distance(embedding, {target_vector}) AS score
FROM articles
ORDER BY score ASC
LIMIT 5

This works correctly out of the box, and gives exact results.

The catch: every query scans every row's vector. Fine at a few thousand rows, painfully slow at a few million.

This is exactly the problem specialized vector databases were built to avoid.

I Actually Benchmarked This

Instead of guessing, I ran the brute-force query above against real ClickHouse (via chdb, the embeddable version of the same engine), on synthetic normalized embeddings.

How search time scales with row count (128-dim vectors):

Rows	Avg query time
50,000	0.025s
100,000	0.046s
250,000	0.155s
500,000	0.220s
1,000,000	0.413s

It's close to linear, which is exactly what you'd expect from brute force: no shortcuts, every row gets a distance calculation.

How search time scales with vector dimension (200,000 rows):

Dimension	Avg query time
128	0.086s
384	0.251s
768	0.449s

Dimension hurts roughly as much as row count. A 768-dim embedding (common for larger sentence-transformer models) costs about 5x what a 128-dim one does, at the same row count. If you're using something like OpenAI's 1536-dim embeddings, expect that cost to roughly double again - I couldn't get a clean number at 1536 dims in my test environment (it ran out of memory before finishing), but the trend makes the direction obvious.

Does SQL-native filtering actually help? I tested WHERE category = 'engineering' against an unfiltered query, on the same 500k-row table, filtering out about 75% of rows:

Query	Avg time
Unfiltered	0.245s
Filtered (~25% of rows match)	0.225s

Honestly, smaller improvement than I expected - about 8%, not a proportional 75% drop. The vector column still gets read before the filter meaningfully prunes work, because the table isn't ordered by category. If you want filtering to actually pay off, you'd need to design your ORDER BY / partitioning around your common filter columns, the same way you'd tune any ClickHouse table - vectors don't get a free pass on data modeling.

What I couldn't test: ClickHouse's HNSW-based vector_similarity index. The chdb build I used doesn't ship with it compiled in, so I can't give you a real indexed-vs-brute-force number here - only the brute-force baseline above. If you want that comparison, you'll need to run it against a real ClickHouse server (Docker or ClickHouse Cloud) with allow_experimental_vector_similarity_index enabled. Better to tell you that than make up a "10x faster" number I didn't actually measure.

Indexed (Approximate) Search

ClickHouse supports an HNSW-based vector similarity index - the same graph-based approach most dedicated vector databases use internally.

The idea: once an index is built on the vector column, queries should run faster than brute force, at the cost of exact accuracy - trading a bit of recall for a lot of speed. That tradeoff is the entire premise behind nearest-neighbor search in every vector database, not just ClickHouse. I just can't hand you a verified number for it from this test run - see above.

What ClickHouse Adds That Vector Databases Usually Don't

The interesting part isn't the distance calculation. It's what you can do around it in the same query.

SELECT title, category, L2Distance(embedding, {target_vector}) AS score
FROM articles
WHERE category = 'engineering'
  AND published_at > now() - INTERVAL 30 DAY
ORDER BY score ASC
LIMIT 5

Filtering by metadata, joining against other tables, aggregating - all native SQL, all in the same pass as the similarity search.

In many dedicated vector databases, metadata filtering is a secondary feature bolted onto the vector index. In ClickHouse, it's the same engine that's been handling filters, joins, and aggregations for years - the advantage is in query flexibility and not having a second system to keep in sync, not necessarily raw speed (my benchmark below shows filtering alone isn't a big speed win unless your table is actually modeled around it).

Where ClickHouse Falls Short

It isn't a drop-in replacement for every vector database use case.

It's not built for high-frequency, single-row upserts the way an OLTP-oriented vector store is.
It doesn't generate embeddings for you - you bring your own vectors, computed elsewhere.
Its ANN indexing is newer than systems that have done nothing but similarity search for years, so tooling and edge-case handling are less mature.

If the workload is millions of small writes per second with constant re-indexing, a purpose-built vector database is still the safer choice.

The Actual Answer

ClickHouse is an analytical database that added vector search.

A vector database is a system built only around vector search.

If vectors are one part of a larger analytical dataset - filtered, joined, aggregated alongside structured columns - ClickHouse can genuinely replace a dedicated vector database and remove a piece of infrastructure.

If vectors are the whole workload, and writes are constant and high-frequency, a dedicated vector database still earns its place.

There isn't a single right answer. There's a right answer for a given workload.

Why Localhost Worked but the Application Couldn't Connect

Mohamed Hussain S — Wed, 08 Jul 2026 14:48:10 +0000

One of the most frustrating debugging sessions I've had wasn't caused by a broken application.

It wasn't caused by a firewall.

It wasn't even caused by the network.

The application worked perfectly.

At least, that's what I thought.

From the server itself, everything looked healthy.

curl http://127.0.0.1:8080

The application responded immediately.

The API worked.

Health checks passed.

Logs looked normal.

Yet every request coming from outside the server failed.

At first, it felt like the application was refusing connections.

The real problem was much simpler.

I misunderstood what localhost actually meant.

The Initial Assumption

When an application starts successfully, it's easy to assume it's ready for everyone to access.

That was my assumption too.

The service was running.

The process was alive.

The port existed.

So naturally, I expected clients on the network to connect without any issues.

Instead, every external request timed out.

Everything Looked Healthy

The first thing I checked was whether the application was actually running.

ps aux | grep sidecar

The process was there.

Next, I checked whether it was listening.

ss -tulpn

Again, everything seemed fine.

The application was listening on port 8080.

So why couldn't another machine connect?

The Important Detail I Missed

The answer was hidden in a single column of the output.

Instead of listening on:

0.0.0.0:8080

the application was listening on:

127.0.0.1:8080

At first glance, they both look like valid addresses.

Operationally, they mean completely different things.

What Localhost Really Means

127.0.0.1 is the loopback interface.

Traffic sent to this address never leaves the machine.

It doesn't travel through the network.

It never reaches another computer.

Only processes running on the same host can connect to it.

So this works:

curl http://127.0.0.1:8080

But from another machine:

curl http://server-ip:8080

the connection simply fails.

Nothing was wrong with the application.

It was doing exactly what it had been told to do.

Why 0.0.0.0 Is Different

When an application binds to:

0.0.0.0

it listens on all available network interfaces.

That means the service becomes reachable through:

the server's IP address
internal network interfaces
external interfaces (assuming routing and firewall rules allow it)

The application hasn't changed.

Only the interface it listens on has.

That tiny configuration difference completely changes who can communicate with it.

My Wrong Mental Model

This debugging session taught me an important lesson.

I had been thinking:

If the application starts successfully, networking must also be working.

Those are two completely different things.

An application can:

start correctly
bind successfully
respond to localhost

and still be completely unreachable from anywhere else.

Application health and network accessibility are separate problems.

Debugging It Systematically

Instead of assuming the application was broken, I started validating each layer independently.

First, verify the process exists.

ps aux | grep sidecar

Next, verify what interface it's actually listening on.

ss -tulpn

Then test locally.

curl http://127.0.0.1:8080

Finally, test remotely.

curl http://server-ip:8080

That simple progression immediately tells you where communication stops.

Instead of debugging everything at once, you're narrowing the search space one layer at a time.

Why This Happens So Often

This isn't unique to one application.

Many frameworks default to binding only to localhost during development.

That's perfectly reasonable.

It prevents accidentally exposing services to an entire network.

The problem appears when that same configuration moves into production.

The application still starts.

Health checks still succeed.

Logs still look clean.

Only remote clients fail.

Without checking the listening interface, it's easy to spend hours investigating the wrong thing.

The Bigger Lesson

Looking back, nothing in the deployment was actually broken.

The operating system behaved exactly as expected.

The application behaved exactly as expected.

The network behaved exactly as expected.

The only thing that was wrong was my assumption.

I treated "the application is running" as proof that "the application is reachable."

Those aren't the same statement.

One describes a running process.

The other describes network accessibility.

Confusing the two can send you down completely the wrong debugging path.

Final Thoughts

Production debugging often isn't about finding a broken component.

It's about understanding how different layers interact.

An application can be healthy while remaining inaccessible.

A network can be perfectly functional while a service listens on the wrong interface.

The more I debug distributed systems, the more I realize that successful deployments depend less on memorizing commands and more on building accurate mental models of how systems communicate.

This experience reinforced one lesson I'll carry into every future deployment:

Just because localhost works doesn't mean your application is actually reachable.

Understanding ACME: What I Learned While Debugging HTTPS Certificate Failures

Mohamed Hussain S — Tue, 07 Jul 2026 14:26:48 +0000

Deploying an application with HTTPS feels straightforward.

Point your domain to the server, configure a reverse proxy like Caddy, and let Let's Encrypt automatically issue a certificate.

That was exactly what I expected.

Instead, certificate issuance kept failing, even though everything looked correct.

At first, I assumed something was wrong with Caddy.

It wasn't.

The real lesson wasn't about Caddy at all-it was about understanding how ACME actually validates a domain.

The Problem

I had a server running behind Caddy, with DNS records pointing to the correct IP address.

The application was reachable, the firewall allowed HTTP and HTTPS traffic, and Caddy was listening on the expected ports.

Yet every attempt to obtain a certificate failed.

Initially, I started checking the obvious things:

Was DNS configured correctly?
Were ports 80 and 443 open?
Was Caddy configured correctly?
Was the firewall blocking traffic?

Everything appeared to be fine.

So why wasn't Let's Encrypt issuing a certificate?

My Wrong Assumption

At first, I thought HTTPS certificate issuance was simply a conversation between my server and Let's Encrypt.

If my server could reach Let's Encrypt, surely the certificate should be issued.

That assumption was completely wrong.

The important connection isn't just your server reaching Let's Encrypt.

It's Let's Encrypt successfully reaching your server.

That single realization changed how I approached the entire problem.

Understanding ACME

Let's Encrypt uses the ACME (Automatic Certificate Management Environment) protocol to verify that you actually own the domain you're requesting a certificate for.

The process is roughly:

Your ACME client requests a certificate.
Let's Encrypt sends a validation challenge.
Your server proves ownership by responding correctly.
Only after successful validation is the certificate issued.

In other words, certificate issuance isn't automatic trust.

It's a verification process.

Looking at the Logs

Once I stopped guessing and started reading the logs, things became much clearer.

Instead of generic "certificate failed" messages, the ACME logs explained which validation step was failing.

That immediately narrowed the investigation.

Rather than assuming TLS was broken, I started asking more useful questions:

Can Let's Encrypt reach my server?
Is the expected challenge actually being served?
Is DNS resolving to the correct address?
Is traffic arriving where I think it is?

Those questions were far more valuable than repeatedly changing configuration files.

The Bigger Lesson

One thing I learned during this debugging session is that HTTPS failures are often not TLS failures at all.

They can be:

DNS issues
Routing problems
Network interface problems
Reverse proxy configuration mistakes
Port forwarding issues

The certificate request simply exposes those underlying networking problems.

In my case, the root cause wasn't ACME itself-it was a networking issue preventing successful validation.

Understanding how ACME worked helped me stop treating the symptoms and start investigating the actual cause.

A Better Way to Debug ACME Failures

Instead of randomly changing configuration files, I found it much more effective to work through a simple checklist.

First, verify that DNS resolves to the expected IP address.

Next, confirm that ports 80 and 443 are reachable from outside the network.

Then inspect the ACME client logs to determine which validation step is failing.

Finally, validate that your server is actually serving the expected challenge response.

Each step eliminates an entire class of possible problems.

What Changed My Thinking

Before this experience, I viewed HTTPS certificate issuance as a feature provided by the reverse proxy.

Now I see it differently.

ACME is fundamentally a validation protocol.

TLS certificates are simply the end result of successfully proving domain ownership.

Once I understood that, the debugging process became much more systematic.

Instead of asking:

"Why won't Let's Encrypt issue my certificate?"

I started asking:

"What is the ACME server trying to verify, and why is that verification failing?"

That shift in thinking made all the difference.

Final Thoughts

Debugging production systems often comes down to replacing assumptions with understanding.

At first, I thought the problem was Caddy.

Then I suspected DNS.

Then I questioned my firewall.

In reality, none of those were the underlying issue.

The real lesson was understanding how ACME validates a deployment before a certificate is ever issued.

Once that mental model clicked, the logs became easier to interpret, the investigation became more focused, and the actual root cause was much easier to identify.

Sometimes the hardest part of debugging isn't fixing the system.

It's understanding how the system is supposed to work in the first place.

When Logs Aren't Enough: Using tcpdump to Debug Real Network Problems

Mohamed Hussain S — Mon, 22 Jun 2026 16:13:56 +0000

In my previous post, I wrote about a Linux routing issue that broke a deployment and caused repeated validation failures.

What ultimately led me to the root cause wasn't a configuration change or a log entry.

It was a packet capture.

This article isn't about a routing issue. It's about the tool that helped uncover it and the lesson I took away from the entire investigation.

Everything Looked Healthy

The first step was verifying the basics.

I checked whether the service was listening on the expected ports:

sudo ss -tulpn | grep -E ':80|:443'

Everything looked normal.

Next, I verified the application itself:

curl http://localhost

The application responded immediately.

I also verified DNS resolution and confirmed the domain was pointing to the correct public IP.

At this point, nothing looked obviously wrong. The application was healthy, the reverse proxy was healthy, and the network configuration appeared healthy. Yet validation attempts continued to fail.

The Logs Weren't Helping

The logs consistently showed variations of:

authorization failed
timeout during connect
likely firewall problem

The problem was that the logs only described the symptom.

They didn't explain why it was happening.

So I started investigating the usual suspects: DNS, firewall rules, reverse proxy configuration, and listening ports.

Everything continued to look fine.

The more I investigated, the less sense the issue made.

Looking Beyond The Logs

At some point I realized I was only looking at what the software was reporting.

I wasn't looking at what the network was actually doing.

So I decided to capture the traffic directly:

sudo tcpdump -ni ens3 tcp port 80

I triggered another validation attempt and watched the packets arrive.

Almost immediately, I saw something interesting:

IP 124.x.x.x > 51.x.x.x.80: Flags [S]
IP 124.x.x.x > 51.x.x.x.80: Flags [S]
IP 124.x.x.x > 51.x.x.x.80: Flags [S]

The requests were reaching the server.

That single observation completely changed the direction of the investigation.

The First Real Clue

Up until that moment, I had been operating under the assumption that external systems couldn't reach the server.

The packet capture proved otherwise.

Traffic was arriving.

The server was receiving connection attempts.

The problem wasn't inbound connectivity.

The problem was somewhere after that.

Instead of asking:

Why can't external systems reach my server?

I started asking:

If traffic is reaching the server, why isn't the connection completing?

That shift in thinking changed the entire investigation.

What tcpdump Revealed

To understand why the packet capture was so important, it helps to understand what a normal TCP connection looks like.

A healthy connection follows a three-way handshake:

Client  -> SYN      -> Server
Client <- SYN-ACK   <- Server
Client  -> ACK      -> Server

What I was actually seeing looked more like this:

Client  -> SYN      -> Server
Client  -> SYN      -> Server (Retry)
Client  -> SYN      -> Server (Retry)

The incoming connection attempts were reaching the server, but the connection was never being established successfully.

That immediately ruled out several possibilities.

DNS wasn't the problem because requests were arriving.

The reverse proxy wasn't the problem because it was listening correctly.

The application wasn't the problem because it responded locally.

Within a few minutes, the packet capture had eliminated entire categories of potential root causes.

Following The Evidence

Once I knew inbound traffic was reaching the server, I shifted my attention toward the network path itself.

I started examining interfaces, routes, and outbound traffic behaviour using commands like:

ip route

and

ip route get 8.8.8.8

Those commands eventually exposed the real issue.

The server had multiple network interfaces, and outbound traffic was being routed through an unexpected path. That routing behaviour was causing validation attempts to fail even though the service itself was perfectly healthy.

The actual root cause turned out to be a Linux routing issue.

But I might never have found it if I hadn't first verified what was happening on the wire.

Why tcpdump Was The Turning Point

Before running tcpdump, I was relying entirely on logs and assumptions.

The logs suggested a firewall issue.

The packet capture showed requests reaching the server.

Those two observations pointed in completely different directions.

Without the packet capture, I probably would have continued tweaking firewall rules, reverse proxy settings, and application configuration.

Instead, the investigation moved toward routing almost immediately.

That's what made tcpdump so valuable.

It wasn't the tool that solved the problem.

It was the tool that revealed reality.

Lessons Learned

1. Logs Don't Tell The Entire Story

Logs are useful, but they're generated by software. They only describe what the application believes is happening.

Sometimes that's not enough.

2. Verify Assumptions Early

I spent time investigating DNS, firewall rules, and reverse proxy configuration because they seemed like the most likely causes.

The packet capture disproved those assumptions within minutes.

3. Packet Captures Can Change Everything

You don't need advanced networking knowledge to get value from tcpdump.

Even a simple capture can tell you whether traffic is arriving, leaving, or disappearing somewhere in between.

4. Eliminate Entire Categories Of Problems

One of the biggest advantages of packet captures is that they quickly rule things out.

Sometimes that's more valuable than finding the answer immediately.

Final Thoughts

This incident reminded me that assumptions can be surprisingly expensive.

The logs pointed toward a firewall issue. The services looked healthy. Everything seemed to suggest a particular problem.

But the moment I looked at the packets, the entire investigation changed direction.

Since then, whenever a network issue doesn't make sense, I try to reach for tcpdump much earlier.

Because logs tell you what software thinks happened.

Packets show you what actually happened.

The Hidden Linux Routing Issue That Broke My Deployment

Mohamed Hussain S — Wed, 17 Jun 2026 03:41:37 +0000

The deployment should have taken a few minutes.

The application was running, DNS was configured correctly, and the domain was already pointing to the server's public IP. Caddy was configured as a reverse proxy and was listening on ports 80 and 443. Every item on my deployment checklist appeared healthy.

Yet every Let's Encrypt validation attempt kept failing.

The error looked simple enough:

authorization failed
timeout during connect
likely firewall problem

At first, I believed it.

I checked DNS resolution, verified firewall rules, confirmed that Caddy was listening on the expected ports, and made sure the application itself was reachable. Every check came back clean.

That was the first clue that the problem might not be where the logs were pointing.

The Obvious Things

The first assumption was DNS.

I verified that the domain resolved to the correct public IP.

dig +short my-domain.com

Everything looked correct.

Next came the firewall.

sudo ufw status

Ports 80 and 443 were open. There were no unexpected deny rules, and nothing suggested inbound traffic was being blocked.

Then I checked whether Caddy was actually listening.

sudo ss -tulpn | grep -E ':80|:443'

Again, everything looked normal.

The application itself was healthy too.

curl http://localhost:3001

returned a valid response.

At this point I had checked most of the things engineers typically check when certificate validation fails. DNS looked good, the firewall looked good, the reverse proxy was healthy, and the application was running.

Yet the validation errors continued.

The Part That Sent Me In The Wrong Direction

The error messages kept mentioning connectivity problems and possible firewall issues.

That wording influenced my thinking more than it should have.

I spent time investigating firewall rules, reverse proxy configuration, TLS settings, and domain configuration. Every new hypothesis felt reasonable, but none of them explained why local tests consistently succeeded while external validation continued to fail.

The contradiction kept bothering me.

If the service was truly unreachable, why did everything work from inside the server?

Then I Hit The Rate Limit

This was the point where I realized I was no longer troubleshooting.

I was guessing.

After several failed validation attempts, Let's Encrypt stopped accepting new authorization requests and returned a rate-limit error.

too many failed authorizations

I had burned through multiple validation attempts without actually understanding the root cause.

Looking back, this was probably the most useful lesson from the entire incident.

Repeatedly retrying a failing system is not the same thing as debugging it.

Looking At The Network Instead Of The Logs

At this point I stopped changing configurations and started gathering evidence.

The first useful clue came from tcpdump.

sudo tcpdump -ni ens3 tcp port 80

While monitoring traffic, I triggered requests from outside the server.

The packet capture immediately showed incoming connection attempts reaching the machine.

That was important.

It meant DNS was working.

It meant external traffic was reaching the public interface.

It meant the firewall was not silently dropping inbound requests.

The requests were arriving exactly where they were supposed to.

So why was validation timing out?

The Routing Table Finally Revealed The Problem

The next step was checking the routing table.

ip route

The output looked roughly like this:

default via 10.2.0.1 dev ens4 metric 100
default via 51.x.x.x dev ens3 metric 100

The server had two network interfaces.

ens3 connected to the public network
ens4 connected to a private network

Initially, I didn't think much of it. Multi-interface servers are fairly common.

Then I started checking where outbound traffic was actually leaving.

ip route get 8.8.8.8

The result surprised me.

8.8.8.8 via 10.2.0.1 dev ens4

I tested several additional destinations.

ip route get 1.1.1.1
ip route get 8.8.4.4
ip route get <validator-ip>

Every single lookup showed outbound traffic leaving through the private interface.

That was the breakthrough.

Understanding What Was Actually Happening

A Quick Note About Asymmetric Routing

The issue I was dealing with has a name: asymmetric routing.

Traffic was entering the server through the public interface (ens3), but Linux was attempting to send replies through the private interface (ens4).

From the application's perspective everything looked healthy.

From Let's Encrypt's perspective the connection never completed successfully.

Why This Can Cause Timeouts

While investigating the issue, I came across Linux's Reverse Path Filtering (rp_filter).

When a packet arrives on one interface but Linux believes the reply should leave through another, the kernel may treat the traffic as suspicious and drop it.

Whether the packet was being dropped by rp_filter, upstream networking, or another layer wasn't something I conclusively proved.

But understanding this interaction finally explained why inbound requests were visible while validation attempts still timed out.

Let's Encrypt validators were connecting to my public IP.

Those packets arrived through the public interface.

Let's Encrypt
      |
      v
Public Interface (ens3)
      |
      v
    Server

So far, everything was fine.

The problem appeared when Linux generated a response.

Instead of sending the response back through the same public interface, the routing table was selecting the private interface as the preferred outbound path.

Let's Encrypt
      |
      v
Public Interface (ens3)
      |
      v
    Server
      |
      v
Private Interface (ens4)

This is a classic networking issue known as asymmetric routing.

Traffic enters through one interface and attempts to leave through another.

From the application's perspective, everything appears healthy.

From the remote system's perspective, the connection never completes correctly.

The result is timeouts.

Exactly what Let's Encrypt was reporting.

Why This Was So Difficult To Find

The issue hid behind several misleading signals.

The application was healthy.

The reverse proxy was healthy.

DNS was correct.

Ports were open.

The firewall was configured properly.

Every layer looked healthy when viewed independently.

The actual failure existed underneath all of them.

Most deployment troubleshooting guides focus on application configuration, reverse proxies, certificates, and firewall rules. Very few immediately point you toward route selection.

Especially when the server appears to be functioning normally.

The Fix

Once the routing issue was identified, the fix itself was straightforward.

The server needed to use the public interface for internet-bound traffic instead of attempting to route those responses through the private network.

After correcting the routing configuration, I verified the result.

ip route get 8.8.8.8

The output now showed traffic leaving through the public interface.

Exactly what I wanted.

I restarted Caddy and triggered another validation attempt.

This time the validators connected successfully, the challenge completed, and the certificate was issued within seconds.

Hours of troubleshooting ultimately came down to a routing decision that Linux was making automatically.

Lessons Learned

A few takeaways from this incident stood out.

Error messages often describe symptoms, not causes

The logs repeatedly suggested firewall issues.

The firewall was never the problem.

Stop retrying and start investigating

I hit Let's Encrypt's authorization limits because I kept retrying before understanding the failure.

That was entirely avoidable.

Packet captures reveal reality

When logs become confusing, tcpdump often provides a much clearer picture of what is actually happening on the network.

Multi-interface servers deserve extra scrutiny

If a server has both public and private interfaces, route selection should be one of the first things you verify.

Two commands can save hours

If you're debugging unexplained connectivity issues, run these early:

ip route

ip route get 8.8.8.8

Those two commands exposed the real problem faster than everything else I tried.

Final Thoughts

I started this investigation convinced I had a TLS problem.

Then I thought it was DNS.

Then I suspected the firewall.

Then I questioned my reverse proxy configuration.

In the end, none of those were responsible.

The real issue was a routing decision happening at the operating system level long before the request ever reached my application.

And like most memorable debugging sessions, the hardest part wasn't fixing the problem.

It was figuring out where the problem actually lived.

ClickHouse Duplicates: Clean Your Results vs. Clean Your Storage

Mohamed Hussain S — Sat, 13 Jun 2026 12:36:42 +0000

The word FINAL appears in multiple places in ClickHouse.

Two of the most commonly confused examples are:

SELECT *
FROM events FINAL;

and:

OPTIMIZE TABLE events FINAL;

At first glance, they sound like they should do roughly the same thing.

After all, both contain the word FINAL.

But they actually solve two completely different problems.

One affects query results.

The other affects how data is physically stored.

Understanding this distinction can save a lot of confusion when working with MergeTree tables.

Why This Confusion Happens

Most people encounter FINAL while working with engines like:

ReplacingMergeTree
SummingMergeTree
AggregatingMergeTree

Sooner or later they notice something like:

SELECT *
FROM users;

returns duplicate versions of rows.

Then they discover:

SELECT *
FROM users FINAL;

and suddenly the results look correct.

Naturally, many people assume:

FINAL merges the table.

But that's not exactly what is happening.

What SELECT FINAL Actually Does

When you run:

SELECT *
FROM users FINAL;

ClickHouse applies merge logic during query execution.

Think of it as:

"Show me what the table would look like if all relevant merges had already happened."

The important part:

It only affects the query result.

After the query finishes:

parts remain unchanged
storage remains unchanged
nothing is rewritten on disk

The merge logic happens temporarily while the query is running.

Once the query completes, the table is exactly as it was before.

What OPTIMIZE FINAL Actually Does

Now let's look at:

OPTIMIZE TABLE users FINAL;

This is a completely different operation.

Instead of modifying query results, ClickHouse physically merges parts on disk.

The operation:

rewrites data
merges eligible parts
removes obsolete versions
creates larger merged parts

Unlike SELECT FINAL, the effects remain after the command completes.

This is a storage operation, not a query operation.

The Simplest Way to Remember It

Whenever I think about these commands, I use a very simple mental model:

Command	Purpose
SELECT FINAL	Clean the result
OPTIMIZE FINAL	Clean the storage

That's really the core difference.

One affects what you see.

The other affects how the data is stored.

Does OPTIMIZE FINAL Create One Giant Part?

This is another common misconception.

Suppose your table is partitioned like this:

PARTITION BY toYYYYMM(event_date)

and contains:

2025-01
2025-02
2025-03

Many people assume:

OPTIMIZE TABLE events FINAL;

will merge the entire table into one huge part.

It won't.

Merge operations do not cross partition boundaries.

What you are more likely to end up with is:

2025-01 -> one large part
2025-02 -> one large part
2025-03 -> one large part

Each partition is optimized independently.

This distinction becomes important when working with large datasets.

Should You Use SELECT FINAL Everywhere?

Not really.

FINAL is incredibly useful when correctness matters.

For example:

SELECT *
FROM users FINAL;

may be exactly what you need when querying a ReplacingMergeTree table and you want the latest state immediately.

But it still introduces additional work during query execution.

So while modern ClickHouse versions have significantly improved FINAL performance, it shouldn't automatically become your default query pattern.

Use it when you need the merge logic.

Not because it's available.

Should You Run OPTIMIZE FINAL Regularly?

Also no.

This is another mistake people sometimes make.

OPTIMIZE FINAL is a heavy operation.

It forces merges that ClickHouse would normally schedule on its own.

In many cases, background merges already do a good job of maintaining healthy storage.

Running:

OPTIMIZE TABLE events FINAL;

every time you insert data is usually unnecessary.

Think of it as an operational tool.

Not a routine query optimization technique.

When Would You Use Each?

SELECT FINAL

Useful when:

querying ReplacingMergeTree tables
validating latest state
merge results are needed immediately

OPTIMIZE FINAL

Useful when:

forcing merges intentionally
maintenance operations
testing storage behavior
special operational situations

Both have valid use cases.

They simply solve different problems.

Final Thoughts

The word FINAL appears in both commands, which makes them easy to confuse.

But once you understand the difference, many ClickHouse behaviors start making a lot more sense.

SELECT FINAL does not physically merge your table.

It only applies merge logic while reading data.

OPTIMIZE FINAL actually rewrites and merges parts on disk.

Or put another way:

SELECT FINAL cleans what you see.

OPTIMIZE FINAL cleans how the data is stored.

And that's a distinction every ClickHouse engineer should understand.

Why ClickHouse Loves Append-Heavy Workloads

Mohamed Hussain S — Wed, 27 May 2026 09:24:45 +0000

One thing that makes ClickHouse feel very different from traditional OLTP databases is how much it prefers append-heavy workloads.

And once you understand why, many ClickHouse behaviors suddenly start making sense:

immutable parts
background merges
ingestion batching
merge pressure
even why FINAL exists

At first, this can feel strange if you are coming from databases like PostgreSQL or MySQL where updates and row modifications are extremely normal.

But analytical databases think very differently internally.

Traditional OLTP Systems Think in Terms of Updates

In most transactional databases, modifying rows constantly is completely normal.

For example:

UPDATE inventory
SET stock = stock - 1
WHERE product_id = 101;

or:

UPDATE users
SET last_login = now()
WHERE user_id = 42;

These systems are heavily optimized for:

transactional correctness
row-level updates
operational consistency
frequent modifications

Because that is exactly what OLTP workloads need.

And honestly, PostgreSQL is incredibly good at this.

ClickHouse Thinks Very Differently

ClickHouse is not primarily designed around transactional row updates.

It is designed around analytical workloads:

metrics
logs
observability
event streams
historical analytics
large aggregations

And these workloads are naturally append-heavy.

For example:

INSERT INTO events VALUES (...);

or:

INSERT INTO metrics VALUES (...);

New events continuously arrive.

Old data is rarely modified frequently.

That changes the entire storage philosophy underneath.

ClickHouse Stores Data as Immutable Parts

This is one of the most important concepts to understand.

In MergeTree engines, ClickHouse stores inserts as immutable parts on disk.

Meaning:

inserts create new parts instead of constantly rewriting existing rows directly.

And honestly, this is one of the biggest reasons ClickHouse scales analytical ingestion so well.

Because append-heavy writes are operationally much cheaper than constantly rewriting data in place.

Why Immutable Storage Works So Well

Immutable storage gives ClickHouse several advantages:

efficient sequential writes
better compression
reduced locking pressure
faster analytical scans
simpler background merging

Instead of constantly modifying rows directly, ClickHouse can:

append data quickly
merge parts later
optimize storage asynchronously

This model fits analytical systems extremely well.

Especially when ingesting:

logs
metrics
telemetry
clickstream data
observability events

at very large scale.

Columnar Storage Makes This Even More Powerful

Another reason append-heavy storage works so well in ClickHouse is because data is stored by column instead of by row.

This matters a lot for analytical workloads.

Because queries often need only a few columns from massive datasets.

For example:

SELECT avg(response_time_ms)
FROM metrics;

does not need to read:

user_agent
request_headers
payload columns

at all.

And because parts are immutable, ClickHouse can compress these columns extremely efficiently using specialized compression algorithms.

This is one of the reasons analytical scans in ClickHouse can remain surprisingly fast even at very large scale.

This Is Why Background Merges Exist

One thing that confused me initially was:

why ClickHouse relies so heavily on merges.

But once you understand immutable parts, merges make perfect sense.

Because inserts continuously create smaller parts.

And background merges later:

combine them
reduce fragmentation
improve compression
optimize query performance

This is also why:

tiny inserts become dangerous
too many parts create pressure
unhealthy fragmentation slows systems down

Many ClickHouse operational behaviors trace back to this append-heavy storage philosophy underneath.

Why Updates Feel Different in ClickHouse

This does not mean ClickHouse cannot handle updates.

It absolutely can.

But updates behave differently because the storage engine is optimized differently.

In many cases, updates are internally handled through:

mutations
part rewrites
asynchronous merge operations

instead of lightweight in-place row modifications like traditional OLTP systems.

And this is why large-scale frequent updates can feel operationally heavier in ClickHouse.

Because the system is optimizing for analytical scale first.

Not transactional mutation-heavy workloads.

Many Systems Handle Updates as New Inserts Instead

One thing I found interesting is that many ClickHouse workloads avoid frequent in-place updates entirely.

Instead, systems often:

insert newer versions of rows
append updated events
track timestamps or versions

and later use things like:

ReplacingMergeTree
argMax()
merge logic

to retrieve the latest state.

For example:

SELECT
    user_id,
    argMax(status, updated_at)
FROM user_status
GROUP BY user_id;

This fits naturally into ClickHouse’s append-heavy design philosophy.

Instead of constantly rewriting rows directly, systems continuously append newer versions while merges and analytical queries reconcile state later.

Why Event Streams Fit ClickHouse So Naturally

This is honestly where ClickHouse feels extremely powerful.

Modern systems continuously generate:

logs
metrics
traces
user events
telemetry streams

And these workloads naturally behave like:

append-heavy event streams.

New records continuously arrive.

Historical records mostly remain unchanged.

That is exactly the kind of workload ClickHouse loves.

Which is why architectures like:

Applications
      ↓
Kafka / Streaming
      ↓
ClickHouse

feel so natural operationally.

The storage model aligns perfectly with the workload behavior.

This Also Explains Why FINAL Exists

A lot of ClickHouse behavior becomes easier to understand once you think in terms of append-heavy storage.

For example:
ReplacingMergeTree may temporarily contain multiple versions of rows until merges eventually reconcile them.

That is why queries sometimes use:

SELECT * FROM events FINAL;

to apply merge logic during query execution.

Again:

immutable parts
append-heavy ingestion
asynchronous merging

all connect back together underneath.

The Important Lesson

One thing I’ve started realizing with ClickHouse is that many operational behaviors make much more sense once you stop thinking in terms of:

"traditional transactional databases."

ClickHouse is optimizing for:

analytical ingestion
historical querying
append-heavy workloads
large-scale scans

And once you understand that design philosophy, many of its storage behaviors stop feeling strange.

Final Thought

ClickHouse is not trying to behave like a traditional OLTP database.

It is optimizing for analytical scale.

And append-heavy design is one of the biggest reasons it performs so well for:

observability
metrics
event streams
analytical systems
real-time analytics workloads

Why Too Many Parts Hurt ClickHouse Performance

Mohamed Hussain S — Mon, 25 May 2026 14:00:25 +0000

A lot of people initially think ClickHouse performance problems come from:

large queries
bad joins
massive datasets
missing indexes

And honestly, those things can matter.

But one of the most common operational problems in ClickHouse often starts much earlier:

too many tiny parts.

This is one of those issues that usually stays invisible at first.

Then suddenly:

merges fall behind
queries slow down
memory usage increases
inserts become unstable

And the cluster starts behaving strangely.

Every Insert Creates Parts

This is the first thing that’s important to understand.

In MergeTree-based engines, ClickHouse stores data as immutable parts.

Something as simple as:

INSERT INTO events VALUES (...);

creates new parts on disk.

And this is completely normal.

ClickHouse is designed around this storage model.

So:

parts themselves are not the problem.

The real issue starts when parts begin accumulating faster than merges can stabilize them.

Why Tiny Inserts Become Dangerous

At smaller scale, tiny inserts may seem harmless.

For example:

inserting row-by-row
extremely frequent micro-batches
tiny streaming flush intervals

Initially:

everything still works.

But over time, the number of parts starts growing aggressively.

Now ClickHouse has to manage:

more metadata
more merges
more scheduling
more file operations

This creates operational overhead.

Meaning:

the system starts spending increasing resources managing fragmentation itself.

Why Merges Matter So Much

ClickHouse relies heavily on background merges.

These merges:

combine smaller parts
reduce fragmentation
improve compression
optimize query performance

Under healthy ingestion patterns, merges naturally keep the system stable over time.

That is the ideal state.

But problems start when:

parts created per second
        >
parts merged per second

Now fragmented parts begin accumulating faster than ClickHouse can compact them.

And this is usually where instability slowly starts building.

The Dangerous Part Is That It Builds Slowly

This is what makes the issue tricky operationally.

You usually do not notice the problem immediately.

The cluster may look perfectly healthy initially.

Then gradually:

insert latency increases
merges lag behind
CPU usage becomes unstable
queries become heavier
replication slows down

And eventually ClickHouse may start throwing errors like:

Too many parts

At that point, the merge system is already under serious pressure.

Queries Also Become More Expensive

A lot of people think parts only affect inserts.

But queries suffer too.

Because queries now need to:

open more parts
scan more metadata
coordinate more files

Even when the actual dataset itself is not massive.

So sometimes:

performance degradation comes more from fragmentation than raw data volume.

That is a very important operational insight.

FINAL Does Not Really Solve This

One thing that’s important to understand:

FINAL is not really a solution for too many parts.

For example:

SELECT *
FROM events FINAL;

FINAL applies merge logic during query execution.

But the fragmented parts still physically exist underneath.

So if the system already has excessive fragmentation:

queries still scan many parts
merge pressure still exists
query execution can become heavier

Which means:

FINAL can actually become more expensive when fragmentation becomes unhealthy.

The real fix is usually improving ingestion and merge behavior itself.

Over-Partitioning Can Quietly Make This Worse

Another thing that often accelerates part explosion is overly granular partitioning.

For example:

PARTITION BY toYYYYMMDDhh(timestamp)

instead of something broader like:

PARTITION BY toYYYYMM(timestamp)

Now even small inserts may create parts across many partitions simultaneously.

Which means:

a single insert can end up creating multiple fragmented parts underneath.

And over time, merge pressure increases much faster than expected.

ClickHouse Also Has Ways to Help

Modern ClickHouse versions also support features like async inserts to help reduce excessive tiny-part creation.

Instead of immediately flushing every small insert into separate parts, ClickHouse can buffer inserts internally before writing larger parts to disk.

This helps reduce fragmentation and merge pressure in workloads that naturally produce smaller inserts.

But async inserts are not a replacement for healthy ingestion patterns themselves.

Stable batching still matters a lot.

Why Batch Size Matters So Much

ClickHouse generally performs much better with:

larger batches
fewer inserts
healthier merge behavior

Because fewer parts means:

fewer merges
lower metadata overhead
better compression
more efficient scans

This is one of the reasons ClickHouse ingestion patterns often look very different from traditional OLTP systems.

Too Many Parts Also Affects Startup and Recovery

Another thing people often discover late:

Large numbers of parts also affect:

startup time
replication recovery
metadata loading
server restarts

Because ClickHouse now has to:

scan part metadata
validate parts
rebuild internal state

before the server becomes fully operational again.

So the issue is not just query performance.

It becomes an overall operational stability problem.

The Important Lesson

One thing I’ve noticed with ClickHouse is that many performance problems are actually merge-management problems underneath.

And too many parts is one of the clearest examples of that.

Because the issue usually is not:

“ClickHouse cannot handle large data.”

The issue is more often:

fragmentation and merge pressure slowly became unhealthy.

That is a very different operational problem.

Final Thought

ClickHouse is extremely good at handling massive analytical workloads.

But it performs best when the storage engine is allowed to merge parts efficiently.

And sometimes the biggest performance problem is not the query itself.

It is the thousands of tiny fragmented parts quietly building underneath the system over time.

DEV Community: Mohamed Hussain S

Understanding the Replication Queue in ClickHouse

A Queue Belongs to a Replica, Not to the Table

So Where Do These Tasks Come From?

Where You Actually See This: system.replicas

The Detail View: system.replication_queue

Two Tables, Two Different Questions

Queue Size Is Not the Same as Replication Lag

The Mental Model, Put Simply

What This Sets You Up For

ClickHouse 26.8 LTS: 57 Breaking Changes Since 26.3

A note on release timing

The scale of what you're crossing

Part 1: Three things that can stop the upgrade dead

1. The x86 build now requires AVX2 (26.6)

2. insert_deduplication_version has a mandatory migration order (26.6 → 26.7)

3. S3 stops using the server's own credentials for user queries (26.7)

Part 2: Changes that alter results without throwing an error

Loose date strings now parse (26.5)

CAST preserves the source time zone (26.5)

FINAL on a JOIN stopped leaking to other tables (26.6)

Unquoted JSON numbers become timestamps (26.8)

toDate32 reinterprets a whole numeric range (26.8)

Array function semantics fixed (26.8)

Trivial views over Distributed tables push down (26.8)

Part 3: Operational behaviour changes

max_insert_threads now defaults to auto (26.8)

Lightweight update patch parts get a v2 format (26.8)

Hash joins now spill by default (26.5)

REPLACE PARTITION from an empty source is now rejected (26.6)

AggregatingMergeTree validates schemas at creation (26.7)

HTTP request limits tightened (26.4)

Part 4: The tooling around ClickHouse

Asynchronous metrics became maps (26.8)

EXPLAIN PLAN output format changed (26.7)

EXPLAIN SYNTAX returns one row (26.8)

System table changes

allow_feature_tier numbers shifted (26.8)

Part 5: Removals - the grep list

Part 6: What you actually get for it

The upgrade plan

Step 0: Audit before you touch anything

Step 1: Use compatibility as a shock absorber

Step 2: Pin the on-disk formats during the rollout

Step 3: Watch these after the upgrade

Final thoughts

Understanding the ORC File Format: Why Is It So Fast

What a Columnar File Actually Does

The Setup

ORC Is Read Backwards

Stripes

Where a Column Physically Lives

Encodings Do More Than Compression Does

The PyArrow gotcha

Compression Sits On Top

I Actually Measured the Skipping

A predicate stats can't help with

Does Column Projection Actually Pay Off?

How Big Should a Stripe Be?

What I Couldn't Test

Where This Doesn't Help

One Thing a Different Columnar Format Does Differently

So, Why Is It Fast?

Why My ClickHouse Server Ran Out of Disk (and It Wasn't the Data I Expected)

Checking Disk Usage

Ruling Out Inode Exhaustion

Finding What Was Actually Consuming Disk

Finding Which Table Actually Owned the Space

Deciding Not to Preserve It

The Real Fix: Get ClickHouse Off the Root Disk Entirely

Configuring the Permanent Mount

What I'm Changing Going Forward

Before and After

The Bigger Lesson

Can ClickHouse Replace a Vector Database?

What a Vector Database Actually Does

Storing Vectors in ClickHouse

Brute Force Search

I Actually Benchmarked This

Indexed (Approximate) Search

Where You Actually See This: `system.replicas`

The Detail View: `system.replication_queue`

2. `insert_deduplication_version` has a mandatory migration order (26.6 → 26.7)

`CAST` preserves the source time zone (26.5)

`FINAL` on a JOIN stopped leaking to other tables (26.6)

`toDate32` reinterprets a whole numeric range (26.8)

Trivial views over `Distributed` tables push down (26.8)

`max_insert_threads` now defaults to auto (26.8)

`REPLACE PARTITION` from an empty source is now rejected (26.6)

`AggregatingMergeTree` validates schemas at creation (26.7)

`EXPLAIN PLAN` output format changed (26.7)

`EXPLAIN SYNTAX` returns one row (26.8)

`allow_feature_tier` numbers shifted (26.8)

Step 1: Use `compatibility` as a shock absorber