DEV Community: Data Engineering Guide

When to denormalize, when to join: A ClickHouse guide (2026)

Manveer Chawla — Mon, 29 Jun 2026 06:17:28 +0000

Denormalization has been the standard approach to analytical data modeling for good reason. Moving joins, lookups, and business rules out of query time and into ingestion gives you the fastest possible reads for a known access pattern. For most of the past decade, it was often the practical default for latency-sensitive analytics. Earlier columnar engines and distributed query processors could execute joins, but many workloads paid for them through higher latency, higher compute cost, spill-to-disk, or distributed coordination overhead.

That constraint has loosened. Modern columnar databases with advanced join algorithms have reduced the cost of runtime joins enough that normalization is now a genuinely viable option for many analytical workloads. Denormalization still delivers faster reads, but normalization can bring operational benefits: simpler pipelines, flexible schemas, and cleaner governance. Engineers can now make the decision based on their actual workload characteristics, rather than being forced into one approach by engine limitations.

This guide is a decision framework for making that choice in ClickHouse. It starts with why denormalization became the default, explains what has changed in join performance, then compares the tradeoffs on both sides so you can decide where to denormalize, where to join, and where to use ClickHouse primitives that bridge the gap.

For a broader evaluation framework covering latency, concurrency, ingest throughput, SQL flexibility, and cost across real-time OLAP options, see our guide to choosing a database for real-time analytics in 2026. For a deeper comparison of how ClickHouse executes star schema joins against Druid, Pinot, and cloud DWHs, see our star schema and fast joins guide.

TL;DR

Denormalization and normalization are both valid modeling strategies. The right choice depends on your workload.
Denormalization's tradeoffs are primarily operational: pipeline complexity, write-path overhead, data freshness lag, backfill burden, and semantic drift.
Modern real-time OLAP engines (ClickHouse most prominently) have made normalized joins performant enough for many analytical workloads, using parallel/grace hash joins, merge joins, join reordering, runtime bloom filters, and dictionary-based direct joins.
Denormalization still wins on raw read performance for a known access pattern. Scanning one pre-joined table with efficient filters is almost always faster than scanning multiple tables and joining at runtime.
The tradeoff: denormalization optimizes read cost at the expense of write-path complexity, schema flexibility, and governance. Normalization preserves those operational qualities but adds join overhead at query time, including higher per-query CPU and memory use, especially under concurrency.
Use the decision framework below to evaluate which approach fits each part of your workload.

Why denormalization became the default, and what changed in join performance

Data engineering practice has long followed a strict split: normalize for transactional writes, denormalize for analytical reads. Engineers adopted denormalization because it made analytical read latency more predictable, especially when joins required large distributed shuffles, disk spill, or careful query tuning.

The constraints were real and came from multiple directions.

Memory limitations. Early columnar engines executed hash joins purely in memory. When the right-hand side of a join exceeded available RAM, the options were bad: out-of-memory errors, or spilling to disk with severe performance penalties that made queries unpredictably slow.

Distributed coordination overhead. The MPP and MapReduce architectures that dominated the 2010s could work around memory limits by going wide, distributing join work across many nodes. But this introduced network shuffles, coordination overhead, and multi-step job execution that made joins slow and expensive. Today, many traditional cloud data warehouses still follow that design and will complete a massive join, but they may spend significant time and credits doing it.

Primitive optimizers. Legacy query planners couldn't dynamically reorder join graphs based on cardinality estimates, and they couldn't push predicates down efficiently. Engineers couldn't trust the optimizer to find a good plan, so they did the optimization themselves at ingestion time.

Given these constraints, denormalization was the rational engineering choice: pay the compute cost once at ingestion to guarantee predictable read performance. That calculus made sense, and for many workloads it still does.

What's changed is the engine side. Modern real-time OLAP engines have substantially closed the join performance gap, with ClickHouse investing heavily in join execution. Standard hash joins remain the default for fast, memory-resident operations. When intermediate state exceeds memory, grace hash joins spill intermediate state to disk without requiring pre-sorted data, allowing the query to continue instead of failing purely because the hash table no longer fits in RAM. Parallel hash joins use multiple CPU cores to accelerate execution. If tables are already sorted, full and partial merge joins can reduce or avoid the hashing phase, requiring less memory. For ultra-low-latency dimension lookups, ClickHouse's direct dictionary joins function as key-value lookups, delivering up to 25x speedup over hash joins in published benchmarks. All standard SQL join types are supported (INNER, LEFT, RIGHT, FULL, CROSS), plus SEMI, ANTI, and ASOF joins for analytical patterns spanning time windows or selectivity-driven filtering.

Enhanced global join reordering allows cost-based optimizers to restructure complex join graphs using cardinality estimates. On a six-table TPC-H query (scale factor 100), naive ordering without statistics took 3,903 seconds and ~100 GiB of peak memory. Enabling global join reordering with column statistics brought the same query to 2.7 seconds with under 4 GiB of memory: a 1,450x speedup and 25x memory reduction on the same hardware, data, and SQL. Runtime bloom filters, where the build side of a join passes filter conditions to the probe side before the join executes, delivered an additional 2.1x speedup and 7x memory reduction in ClickHouse's published TPC-H example.

Append-only event stores like Druid and Pinot often favor wide event tables because their architectures are optimized around immutable segments, ingestion-time indexing, and lookup or broadcast-style joins. Cloud data warehouses like Snowflake and BigQuery can execute complex joins, but the latency and cost profile is different from a purpose-built real-time OLAP engine, especially for high-concurrency serving workloads.

The bottom line: joins are no longer a constraint that automatically forces your modeling decisions. They are a cost you can now evaluate against the tradeoffs of denormalization for your specific workload.

Why denormalization is still the right choice for many workloads

Before talking about costs, it's worth stating the positive case clearly: denormalization works. If a workload has a dominant query path, a stable schema, and tight latency requirements, flattening the data is often the most direct way to make reads fast and predictable.

A denormalized table eliminates join overhead from the serving path. The engine can filter, aggregate, and return results from one physical table without building hash tables, probing dictionaries, or managing intermediate join state. Under high concurrency, that simplicity matters. Hundreds or thousands of simultaneous queries against a well-designed wide table are easier to reason about than the same traffic pattern repeatedly executing joins.

Denormalization also improves ergonomics for consumers. BI tools, embedded analytics, and application queries often work better against a table where the relevant attributes are already present. Fewer joins means fewer opportunities for analysts to pick the wrong key, apply the wrong join type, or accidentally change metric semantics.

This is why the right framing is not "normalize instead of denormalize." It is: denormalize when the read path is stable, latency-sensitive, and valuable enough to justify the extra work on the write path. Use joins when flexibility, freshness, and semantic clarity matter more than shaving every millisecond from a known query pattern.

Tradeoffs of denormalization

Denormalization optimizes read performance for known access patterns. That optimization has real tradeoffs on the write side and operational side. These tradeoffs don't make denormalization wrong, but they should be weighed explicitly against the read-time benefits.

Pipeline complexity and write-path overhead

Denormalization pushes join logic into the ingestion path. That extra work can live outside the database or inside it. Outside the database, joining streams before ingestion means managing stateful stream processors like Flink, with their checkpoint state management, recovery delays, and late-arriving data handling. This operational surface area grows with the complexity of your denormalization logic.

Inside the database, materialized views that maintain precomputed results, including rollups or denormalized target tables, create write amplification. An incremental materialized view acts like an insert trigger on the source table. Each insert generates additional work for the target view, and high-frequency inserts can outpace the engine's background merge capacity, leading to throttled writes once partitions hit active-part thresholds. For denormalized joins, incremental materialized views only react to inserts on the source table and need additional handling when joined dimension tables change. ClickHouse Cloud can mitigate this with compute-compute separation: read-write services handle inserts and background merges while read-only services run user-facing queries against the same underlying data.

Dimension updates surface the tradeoff clearly. Updating a customer's country in a normalized model touches one row in the customer table. In ClickHouse, lightweight updates (Patch Parts), when appropriate for the update size and table design, write a compact patch containing only the changed columns and rows, with roughly 40 bytes of uncompressed overhead per updated row. The patch part is created immediately when the UPDATE returns; the physical merge into the underlying data happens asynchronously in background merges. Benchmarks show this running up to 1,000x faster than classic ClickHouse mutations and up to 4,000x faster than PostgreSQL on bulk cold updates.

The same update against a denormalized flat table involves more work. If the predicate column isn't part of the table's ordering key, the engine must scan parts to identify where affected rows are located, then write potentially many sparse patch parts, followed by additional merge work to consolidate them. This is manageable for infrequent updates, but becomes a consideration when dimension updates are frequent or contend with the same compute serving user-facing queries.

Data freshness lag

Pre-joining bounds your analytical freshness to your slowest updating dimension. If a transaction stream arrives in real-time but the customer enrichment batch job runs hourly, your flattened table is artificially delayed. Late-arriving events can land, but derived wide-table columns remain stale until the pipeline resolves the discrepancy and rewrites the affected records.

In a normalized model, the dimension table updates independently, and queries against the current state reflect the latest values at join time.

Storage and scan considerations

Columnar storage achieves strong compression by grouping values of the same type together, letting codecs like LZ4 and ZSTD exploit patterns in the data. On typical fact tables, ClickHouse delivers 10x to 20x compression using dictionary encoding, run-length encoding, and general-purpose codecs.

Denormalization's impact on storage depends on the cardinality of the dimensions being flattened. Dimensions are typically low-cardinality: a country column might have 200 distinct values, a subscription tier might have 5. Flattening these into a billion-row fact table duplicates those values, but ClickHouse's LowCardinality column type mitigates this by storing the unique values once in a dictionary and using small integer pointers for each row. The pointers still take space, and you need to remember to declare the type, but the storage overhead is manageable for genuinely low-cardinality dimensions.

Where storage can suffer is when dimension columns aren't part of the table's sort order. Columnar compression works best when adjacent values are similar. Dimension values that are randomly distributed relative to the sort key compress less effectively regardless of their cardinality.

Schema rigidity and backfill burden

Schema changes cascade differently in normalized and denormalized models.

A concrete case: security asks to hash or redact customer names under a new privacy policy. In a normalized model, that's one column transformation on a 100k-row customer table. Future writes only need to hash when new customer rows are created.

In a denormalized model, the same request requires backfilling the hash across billions of historical fact rows, and reconfiguring the denormalization pipeline to apply the hash on every future fact row (whether it's a new customer or not). In any schema, downstream consumers (dashboards, alerts, reverse-ETL jobs) need verification that the change didn't break filters or joins. But the backfill scope is larger in the denormalized case, which translates to more compute, longer execution windows, and more risk to ongoing ingestion.

Consistency and semantic drift

Duplicating data duplicates business meaning. Flattened tables force implicit decisions about slowly changing dimensions.

SCD Type 1 attributes (overwrite the current value) and Type 2 (preserve versioned history) need different handling. Denormalizing them forces a decision about whether historical fact rows reflect the "as-was" state (what was true when the event happened) or the "as-is" state (what is currently true).

If a user upgrades their subscription tier, separate the two reporting questions explicitly. For "as-is" reporting, keep the current tier in a dimension table and join to it at query time. For "as-was" reporting, either model the dimension as SCD Type 2 and join by the event timestamp and effective date range, or intentionally record the tier at the point of the transaction in the fact table. The important part is deciding which meaning each column represents before downstream teams build metrics on top of it.

In a denormalized model, maintaining both views requires either rewriting historical rows when the dimension changes or accepting that the flat table reflects only one perspective. Teams that skip the rewrite can end up with divergence between the flat table and the dimension table, where each reports different values for the same logical attribute.

Tradeoffs of normalization

Normalization has its own tradeoffs. These are often underweighted in discussions that focus on denormalization's downsides, so they're worth stating explicitly.

Query-time overhead and concurrency cost

Every query that joins tables at runtime does more work than scanning a single pre-joined table. Depending on the join algorithm, the engine may build hash tables, probe lookup structures, spill intermediate state, or merge sorted streams. Under high concurrency, this overhead compounds: each concurrent query executing joins consumes more CPU and memory than the equivalent scan against a wide table. For latency-critical serving workloads with hundreds or thousands of concurrent queries, this overhead can be the deciding factor.

Query complexity for consumers

Normalized models push join logic to query time, which means analysts and application developers need to understand the schema relationships and write (or generate) correct joins. A denormalized table with clear column names is easier to query correctly, especially for less technical consumers or BI tools that generate SQL automatically.

Optimizer dependency

Normalized models rely on the query optimizer to find efficient join plans. A bad plan, whether from stale statistics, a complex join graph, or an optimizer limitation, can cause large performance regressions. Denormalized models sidestep this risk for the access patterns they serve.

Aggregate query performance

For aggregation-heavy workloads, denormalized tables let the engine apply filters and group-bys in a single pass without join overhead. Normalized models may require joining before aggregating, which increases intermediate data volumes and processing time.

When to join vs. denormalize in an analytical database

The choice isn't binary, and it shouldn't be made as a blanket architectural decision. Different parts of your workload may warrant different approaches. A common layered pattern keeps raw events in a bronze tier, cleaned and conformed data in a silver tier, dimensional and semantic models for reusable definitions, and denormalized serving tables for specific hot dashboards. In that setup, denormalized tables serve known access patterns while dimensional and semantic models remain available for workloads that need flexibility.

dbt is a common orchestration tool for this layered model. The ClickHouse dbt adapter supports incremental materializations for append-only facts and full-refresh for dimensions, with all models version-controlled in git.

Evaluate the tradeoff for your workload

Before flattening a schema, run your workload through these questions:

Is the path strictly latency-critical? Sub-second SLA requirements, like ad-tech routing or fraud detection, favor flattening because eliminating join overhead provides the most predictable latency.
How volatile are the dimensions? Frequently updated dimensions increase the write-path cost of keeping a denormalized table current. Stable, append-only dimensions are cheap to flatten.
How many access patterns does the data serve? A single dominant query pattern is the sweet spot for denormalization. Multiple diverse patterns mean the flat table is optimized for one path and suboptimal for the rest, while a normalized model can support more patterns without duplicating the same attributes into multiple serving tables.
Is the table well-filtered by partition and ordering keys? Strong pruning makes runtime joins efficient by reducing the data volumes involved.
Can schema changes be backfilled safely? If backfills are slow enough to interfere with ingestion, require careful operational windows, or risk consistency issues, the schema rigidity cost of denormalization is high.
Is it a hierarchical relationship? Deeply nested JSON often warrants selective extraction or, in ClickHouse, using the native JSON type, which shreds JSON into dynamic sub-columns with column-level compression and no upfront schema.

Quick reference: when each approach fits

Factor	Denormalization fits when...	Normalization fits when...
Query pattern	Single dominant access pattern with tight latency SLA	Multiple diverse query patterns
Dimension volatility	Dimensions are stable, rarely updated	Dimensions change frequently
Read performance	Lowest possible latency is non-negotiable	Interactive latency is acceptable
Write-path complexity	Ingestion pipeline complexity is manageable	Simpler ingestion pipelines are a priority
Schema evolution	Schema is stable, changes are rare	Schema evolves frequently, backfills must be cheap
Governance	Single team owns the data, meaning is unambiguous	Multiple teams consume the data, semantic consistency matters

ClickHouse primitives that bridge the gap

ClickHouse provides several primitives that let you get closer to denormalized read performance while maintaining normalized source data. These aren't all forms of denormalization themselves; they're different mechanisms that reduce the need to choose.

Dictionary-based lookups (direct joins) for fast dimension enrichment

Dictionaries load dimensional data into an optimized key-value structure. The flat layout provides array-offset lookups, delivering access speeds up to 25x faster than hash joins and 15x faster than parallel hash joins in published benchmarks. You keep your dimensions in a separate table and get near-denormalized lookup speed at query time without physically duplicating dimension columns in your fact table. Dictionaries work best for one-to-one or many-to-one lookups where a key maps to a single authoritative value; they are not appropriate for one-to-many or many-to-many relationships that require preserving multiple matches.

CREATE DICTIONARY customer_tiers (
  customer_id UInt64,
  tier String
)
PRIMARY KEY customer_id
SOURCE(ClickHouse(TABLE 'customers'))
LAYOUT(FLAT())
LIFETIME(MIN 300 MAX 3600);

Materialized views for pre-aggregation

Materialized views let the database maintain pre-computed aggregations as data arrives, without requiring external pipeline infrastructure. They process incoming data blocks automatically and store the results in a target table. This is aggregation, not denormalization: you're pre-computing rollups, not flattening relationships.

Materialized views aren't free. They create write amplification (each insert generates parts for both the source and target tables). But that cost is usually smaller than running a parallel Flink or Kafka Streams pipeline externally, both in compute and in operational surface area.

CREATE MATERIALIZED VIEW hourly_sales_mv
  ENGINE = SummingMergeTree
  ORDER BY (shop_id, hour)
  AS SELECT
    shop_id,
    toStartOfHour(created_at) AS hour,
    sum(amount) AS total_revenue
  FROM raw_sales
  GROUP BY shop_id, hour;

Projections for alternate access patterns

Projections maintain alternate physical sort orders of your base table's data. They're not a form of denormalization; they're a way to optimize multiple query patterns against the same underlying data. The optimizer automatically routes queries to a more efficient projection.

Since ClickHouse 25.6, lightweight projections can store only their sorting key plus a _part_offset pointer back into the base table, rather than duplicating full rows. In the benchmark discussed in ClickHouse's projection post, this used roughly half the storage of traditional projections and reduced query time by 90%. That makes lightweight projections a practical middle ground when you need better query performance on non-primary access patterns without duplicating every projected column.

When you do denormalize: guardrails

For workloads where explicit denormalization is the right choice, apply these guardrails to keep costs contained.

Separate point-in-time facts from current-state dimensions

When flattening data, capture the dimension value at transaction time in the fact table for "as-was" reporting. For "as-is" reporting, keep the current state in a dimension table and join at query time. In ClickHouse, dictionaries can make this lookup fast when the current-state mapping is one-to-one or many-to-one:

SELECT
  s.order_id,
  s.historical_tier,
  dictGet('customer_tiers', 'tier', s.customer_id) AS current_tier
FROM sales s
WHERE s.historical_tier != dictGet('customer_tiers', 'tier', s.customer_id);

Backfill incrementally

Avoid one-shot population-style backfills when creating a materialized view on a live production table with active writes. Backfill by partition or time range to bound memory and merge pressure. This reduces contention with incoming real-time streams and helps the database engine manage part merges without throttling.

Conclusion

Denormalization and normalization are both valid engineering choices. Neither option is universally better. The choice must fit the specific requirements of each part of your workload.

Denormalization gives you the fastest possible reads for a known access pattern. Normalization preserves schema flexibility, simplifies writes, and keeps business meaning in one place.

The best analytical systems let you make the choice per workload. Use normalized or partially normalized models where operational flexibility and governance matter. Denormalize the specific serving paths where read latency is the binding constraint. Review the ClickHouse join documentation to see how the optimizer selects between algorithms in production.

The fastest test uses your own data and your own access patterns. Spin up a free ClickHouse Cloud trial, load a representative slice of your fact and dimension tables, and run the joins that matter to you. For a reproducible join benchmark you can run yourself, explore the coffeeshop benchmark. The only latency number that matters for your build-or-flatten decision is the one your queries produce on your data.

Frequently asked questions about denormalization in analytical databases

Is denormalization a bad practice in modern analytical databases?

No. Denormalization is a specialized optimization that excels for latency-critical, read-heavy serving layers with known access patterns. It's a valid choice when the read-time benefits outweigh the pipeline complexity, schema rigidity, and governance overhead it introduces.

Does columnar storage eliminate the need for denormalization?

Not entirely. Columnar compression, block pruning, and vectorized execution make normalized star schemas much faster than legacy row-stores, which raises the bar for when denormalization is actually required. But scanning a single pre-filtered wide table is still generally faster than joining multiple tables at runtime. Columnar storage shifts the breakeven point; it doesn't eliminate the tradeoff.

Are joins slow in modern columnar databases?

Not necessarily. Modern engines, such as ClickHouse, use join reordering, parallel/grace hash joins, merge joins, and runtime bloom filters to make normalized star-schema joins fast and predictable at scale. Joins still have overhead compared to scanning a single table, but that overhead has decreased enough to be acceptable for many analytical workloads.

When should I denormalize in an analytical database?

Denormalize when you have a single dominant query pattern with tight latency SLAs (ad-tech bidding, real-time personalization, fraud detection), the dimensions are stable, and the schema is unlikely to change frequently. The operational tradeoffs of denormalization are lowest in that scenario.

What are the biggest operational tradeoffs of denormalization?

Pipeline complexity (stateful stream processors, materialized view write or refresh overhead), data freshness lag (bounded by your slowest dimension update), backfill burden when schemas change, and semantic drift when duplicated business logic diverges from the dimension tables.

What's the best alternative to denormalizing for fast dimension lookups?

Dictionary-based lookups (direct joins) in ClickHouse. They load dimension data into an optimized key-value structure, delivering up to 25x the speed of hash joins in published benchmarks. You keep your dimensions normalized and get near-denormalized lookup performance at query time for one-to-one or many-to-one relationships.

Should I use materialized views instead of denormalizing upstream in ETL?

Materialized views can replace external pipeline work for pre-aggregation use cases, and refreshable materialized views can support some denormalized serving-table patterns. They reduce operational surface area by keeping transformation logic inside the database. They add write or refresh overhead, but that may still be simpler than running a separate streaming pipeline.

How do I handle slowly changing dimensions (SCD) if I denormalize?

Store point-in-time attribute values in the fact table only when you intentionally want that denormalized "as-was" view. Another valid option is an SCD Type 2 dimension joined by event time and effective range. For "as-is" values, keep the current state in a dimension table and join at query time. In ClickHouse, dictionaries can make this fast for one-to-one or many-to-one lookups.

How can I backfill safely after adding a new column to a wide table?

Backfill incrementally by partition or time range to bound memory and merge pressure. Avoid one-shot population-style backfills on live write-heavy tables to reduce consistency and throttling risks.

Build vs Buy a Managed Streaming Platform for Real-Time RAG in 2026

Manveer Chawla — Mon, 15 Jun 2026 22:31:39 +0000

Moving a retrieval-augmented generation (RAG) prototype from a Python notebook into production isn't an API orchestration challenge. It's a distributed systems problem. For engineering managers and data platform leads, the build-versus-buy decision on streaming infrastructure will dictate your artificial intelligence (AI) feature velocity for the next three to five years.

This guide assumes you've already prototyped a RAG pipeline. The question we tackle here is what changes when you put it in front of customers, where the real cost lives, and how to choose a streaming foundation that won't trap your team in maintenance work for the next decade.

Executive Summary

The problem. Production real-time RAG is a streaming-systems problem, not an API-orchestration problem. DIY pipelines accumulate an integration tax that compounds over time, slowing AI feature velocity to a crawl.

The recommendation. For most enterprises, buying an unified managed streaming platform that delivers stream, connect, process, and govern under a single service-level agreement (SLA) is the correct choice. It should ship with AI-native primitives built in: in-flight embedding generation, Streaming Agents, and context served via the Model Context Protocol (MCP).

The evidence.

A single production change data capture (CDC) connector typically takes three to six engineering months to build and stabilize
DIY paths break against the serverless ceiling (e.g., AWS Lambda's 15-minute execution limit) and bleed cross-availability zone (AZ) egress at $0.01 per GB
Confluent customers like Henry Schein One, Notion, and Palmerston North City Council credit the platform for moving high-quality data fast enough to power production AI

The build. A production-grade platform powered by the Kora engine (GBps+ throughput, 99.99% SLA, fully compatible with Apache Kafka® APIs), more than 120 connectors with more than 80 fully managed (PostgreSQL Debezium, Oracle CDC and XStream, Snowflake, S3), Confluent Cloud for Apache Flink® with ML_PREDICT and AI_COMPLETE for in-flight embeddings, Stream Governance (Schema Registry, Data Contracts, Stream Catalog, Stream Lineage), and Confluent Intelligence (Streaming Agents, Real-Time Context Engine, and built-in ML functions) for agentic AI.

Scope. This guide is for engineering managers and data platform leads weighing build versus buy for a real-time RAG initiative. Build is still the right answer if you're air-gapped, have extreme customization needs, or have a large platform team to staff ongoing operations.

What Real-Time RAG Looks Like in Production

Production RAG is never just a stateless app calling a vector database. When you shift from static file uploads to enterprise real-time context, the architecture becomes a persistent, stateful streaming data problem.

Real-time RAG data flow architecture:

The invisible components in this diagram demand continuous synchronization. CDC ingestion from operational databases translates complex, high-throughput row-level updates into event streams. Those change events need to be normalized, chunked, and routed to embedding APIs (OpenAI, Cohere, Amazon Bedrock, Voyage AI, or self-hosted models). The generated vectors must then be securely upserted into your vector database (Pinecone, Weaviate, Milvus, or PostgreSQL using pgvector) while you continuously monitor end-to-end freshness.

Operating this pipeline exposes teams to demanding day two distributed system operations. You need to handle late-arriving data via precise stream watermarking without corrupting the vector index. You need to gracefully process upstream schema changes, like a suddenly dropped column, without breaking downstream chunking logic. And when your AI team upgrades their foundation model, you face the challenge of dual-writing to new indexes and re-embedding millions of historical records without triggering application downtime.

These aren't problems you can solve with simple Python scripts or basic batch cron jobs. They require handling continuous database updates, maintaining strict idempotency to prevent duplicate embeddings, and executing high-throughput writes. If you don't treat RAG synchronization as a hardened data layer reality, you'll end up with index bloat, stale context, and degraded AI output quality.

Faced with these realities, teams pick one of two paths. Build is the natural starting point. Here's why it usually doesn't end there.

Building Real-Time RAG Pipelines: Hidden TCO and the Integration Tax

Engineering teams initially lean toward building their own streaming infrastructure for valid reasons. Extreme customizability, specialized networking protocols, strict air-gapped GovCloud compliance, and a mandate to avoid perceived vendor lock-in often drive the decision to assemble raw open source components.

But these architectures rapidly hit the "serverless ceiling."

Initial RAG pipelines built on serverless functions or batch jobs buckle under continuous CDC ingestion. Standard serverless limits, such as AWS Lambda's strict 15-minute execution limit, break long-running streaming state. Lambda's Kafka Event Source Mapping (ESM) handles polling for free, but you still pay $0.0000166667 per GB-second plus request fees on every invocation, and the stateless invocation model leaves no room for the stateful joins, watermarks, or exactly-once guarantees that production CDC pipelines need.

The architectural breaking point arrives when your team stops shipping differentiated AI features and starts maintaining fragile infrastructure. Highly paid engineers spend their sprints tuning Kafka partitions, managing distributed dead letter queues (DLQs), rewriting broken connector scripts, and orchestrating complex re-embedding workflows when a large language model (LLM) is upgraded.

This operational drag is the "integration tax."

Stitching together best-of-breed raw cloud components comes with an ever-growing maintenance burden that stalls feature velocity. Building and stabilizing a single production-grade CDC connector typically consumes three to six engineering months of labor. That's because building a connector involves navigating single-threaded snapshot bottlenecks, handling complex state management, and overcoming performance barriers. For example, the Debezium PostgreSQL connector is architecturally limited to one streaming task, meaning a single thread captures all changes in order. Under high write volumes, this causes lag and requires multiple connectors to scale, adding to the complexity of partitioning and reassembly.

The total cost of ownership (TCO) formula has three components: infrastructure (compute, storage, network), operations (labor), and hidden costs (downtime, opportunity cost, cross-AZ traffic). Self-managed deployments also incur a "state tax." Managing Flink requires tuning RocksDB block caches and remote durable storage for checkpoints. Multi-AZ open source Kafka deployments silently rack up massive AWS cross-AZ data transfer fees at $0.01 per GB.

The table below maps each of those three buckets to where DIY teams pay versus what a unified managed platform absorbs.

TCO Comparison by Cost Component: Custom Build vs Unified Managed Platform

Cost component	Self-managed (open source Kafka, Flink, and connectors)	Unified managed platform (e.g., Confluent)
Broker infrastructure	Self-managed VMs, 24/7 on-call, multi-AZ egress at $0.01 per GB	Fully managed, 99.99% SLA, optimized cross-AZ paths
Connectors	Three to six engineering months per source for the first version, plus ongoing schema-drift fixes	More than 80 fully managed connectors out of the box, no source-side maintenance
Stream processing	Self-managed Flink: RocksDB tuning, checkpoint storage, JVM upgrades	Serverless Flink, billed per Confluent Unit for Flink (CFU) consumed, hard spending caps available
Embedding tier	Separate fleet of Python embedding workers, plus queue and retry logic	`ML_PREDICT` and `AI_COMPLETE` inside the stream processor, no separate worker tier
Governance and lineage	Build your own schema registry, lineage tracker, and role-based access control (RBAC) layer	Schema Registry, Data Contracts, Stream Catalog, Stream Lineage included
Operational labor	0.5 to 2 dedicated platform FTEs at small or medium scale, multiple teams at enterprise	Capacity reclaimed for AI feature work

Specific dollar values vary widely by workload, region, and data volume. Anyone who hands you a single annual figure without your topology in hand is selling you a number. Forrester's Total Economic Impact study of Confluent Cloud is a defensible starting point for benchmarking your own scenario against a self-managed open source build, and Confluent's public cost estimator lets you size a workload directly.

Generating embeddings natively inside the stream processor eliminates the need to provision, scale, and monitor a separate fleet of Python embedding workers, reducing both your cloud bill and operational headcount.

How to Evaluate Managed Streaming Platforms for Real-Time RAG in 2026

With the cost of building mapped, the next question is what a managed alternative actually needs to deliver to absorb that complexity. Evaluating managed streaming platforms for RAG workloads requires moving beyond basic throughput benchmarks. In 2026, production-grade data streaming infrastructure must natively execute four foundational capabilities: stream, connect, process, and govern. On top of those four, it needs dedicated AI-native primitives (in-flight embedding, MCP-served context, agent runtime) under a single SLA.

The four subsections below cover the foundational capabilities. The fifth covers the AI-native layer that sits on top of them.

Stream: Throughput, Latency, and Uptime Requirements

Your foundational messaging layer must support GBps+ throughput, ultra-low tail latency, and a 99.99% uptime SLA, without manual partition rebalancing.

Modern cloud-native engines, like the Kora engine, which powers Confluent cloud, decouple compute from storage to deliver 10x faster autoscaling and 10x lower tail latencies than self-managed Kafka while staying fully compatible with Apache Kafka® at the protocol level. Your existing producers and consumers keep working as they are. Cluster Linking creates real-time replicas of existing Kafka data and metadata for zero-downtime migration when you move away from open-source Kafka. The decoupled architecture means a cluster absorbs sudden ingestion spikes (common during a backfill or re-embedding window) without you having to lift a finger.

Connect: Fully Managed CDC and Connector Coverage

Evaluate platforms strictly on the breadth and depth of their fully managed connector ecosystem. You need out-of-the-box support for complex CDC workloads, software-as-a-service (SaaS) applications, and object storage.

A platform offering more than 120 connectors, where more than 80 are fully managed (including complex integrations like Postgres Debezium, Oracle CDC, and Snowflake), lets your engineers provision reliable data pipelines in minutes rather than dedicating months to custom development.

Process: Stateful Stream Processing and In-Flight Embeddings

Stream processing must be serverless, support stateful joins, and execute in-flight machine learning (ML) inference. Transforming a text column into a vector embedding directly inside the stream processor simplifies your architecture.

Engines like Confluent Cloud for Apache Flink ship SQL functions like ML_PREDICT and AI_COMPLETE that replace a separate embedding worker tier. Your data engineer writes one ANSI SQL statement to turn a text column in a Kafka topic into a continuous stream of vector embeddings, and the platform handles batching, retries, and rate limits against the embedding API. The same engine supports Python and Java for cases where SQL isn't expressive enough, useful for custom chunking strategies or hybrid retrieval logic.

What's distinctive about Confluent Cloud for Apache Flink is the combination of three languages, native AI functions, and a managed runtime sharing one SLA with the broker. The closest AWS path pairs Amazon Managed Streaming for Apache Kafka (MSK) with Amazon Managed Service for Apache Flink (MSF), which delivers a real Flink runtime supporting SQL, Python, and Java but ships no ML_PREDICT or AI_COMPLETE equivalent and sits on a separate SLA from MSK. MSK paired with Lambda is simpler for short enrichment, but Lambda's 15-minute execution wall breaks long-running streaming state. Open source Flink demands deep Java fluency and a self-managed cluster, and Redpanda has no native Flink at all (its in-broker WebAssembly transforms are sandboxed and limited, by Redpanda's own admission, to "trivial and stateless" cases).

The processing engine must guarantee exactly-once semantics. Without advanced two-phase commit protocols, retry loops will push duplicate embeddings or miss delete commands, permanently corrupting your RAG context.

The processor must also offer robust failure handling (configurable backpressure, buffer debloating, exponential retries, and dead letter queues) to safely navigate strict API rate limits from LLM embedding providers.

Govern: Data Contracts, Catalog, Lineage, and Access Control for RAG

AI outputs are only as trustworthy as their inputs. You need enterprise-grade governance to keep RAG indexes secure, traceable, and accurate.

Start with a Schema Registry that enforces strict Data Contracts, preventing an upstream database change from silently breaking your downstream embedding pipeline. Pair it with a Stream Catalog that organizes Kafka topics as discoverable data products with metadata tagging, search, and self-service access requests, so AI teams can find and adopt trusted streams without bottlenecking on a central data engineering team.

Stream Lineage gives you the audit trail every AI agent's context source needs, answering "where did this RAG document come from, and what schema version produced its embedding?" RBAC, client-side field-level encryption (CSFLE), and masking ensure personally identifiable information (PII) is masked before it ever reaches the vector database.

AI-Native: Streaming Agents, MCP Context, and Built-In ML

A modern streaming platform must speak the language of agentic AI. The four foundational capabilities above keep your data plane reliable. The AI-native layer on top is what turns it into a substrate for production agents.

Confluent Intelligence is the dedicated AI layer of the data streaming platform and ships three components on top of Kafka and Flink:

Streaming Agents. Agents that run as Flink jobs inside the stream processing pipeline, with always-on state, tool calling via MCP and Agent2Agent (A2A), and replayable, governed event flows. Because they are Flink jobs, the same exactly-once and lineage guarantees apply to agent decisions.
Real-Time Context Engine. A fully managed service that serves structured context to AI apps and agents over the Model Context Protocol, with built-in authentication, RBAC, and audit logging. MCP integrations include LangChain, Amazon Bedrock, Salesforce Agentforce, and Anthropic Claude.
Built-in ML functions. Native Flink SQL functions for embedding, anomaly detection, fraud prevention, forecasting, and sentiment analysis, with hooks to invoke remote AI/ML models or custom ones.

Tableflow extends these same Kafka topics into open table formats (Apache Iceberg™ and Delta Lake), so the streams that feed your real-time RAG pipeline form the bronze and silver layers of an analytics medallion stack. Tableflow eliminates separate ETL pipelines and shifts processing and governance left, an approach Confluent reports cuts analytical compute costs by up to 30% and reduces data quality issues by up to 60%, while giving AI agents readily queryable historical context alongside their real-time streams.

Streaming Platform Comparison: Custom Build, MSK, Redpanda, Confluent

Apply those evaluation criteria to the market, and the practical streaming choices for a real-time RAG initiative are narrowed to four. You can roll your own with open source components, lean on a hyperscaler-managed broker like MSK, pick a Kafka-compatible alternative like Redpanda, or buy a complete data streaming platform like Confluent. Each has a defensible use case. Only one was designed end-to-end for production agentic AI.

At a Glance: How Each Option Covers the Four Capabilities Plus AI-Native Primitives

Option	Stream	Connect	Process	Govern	AI-native
Custom build (self-managed Kafka, Flink, and connectors)	Self-managed	Self-managed	Self-managed	Self-managed	DIY
AWS MSK + Glue + MSF/Lambda	✓ Managed broker, 99.9% SLA (infrastructure only)	Bring your own connectors, limited managed CDC	Bolt-on via MSF (separate SLA from MSK, no `ML_PREDICT`/`AI_COMPLETE`) or Lambda (15-min cap)	Piecemeal (Glue Schema Registry is primarily Java-focused, no unified catalog or lineage)	Bring your own
Redpanda	✓ C++ Kafka-compatible broker, 99.99% multi-zone / 99.5% single-zone, bring your own cloud (BYOC) option	More than 10 fully managed connectors	No native Flink (in-broker WebAssembly only)	Basic schema registry, no Stream Catalog or Stream Lineage	Bring your own
Confluent	✓ Kora engine, 99.99% SLA covering infrastructure and Kafka software	✓ More than 120 connectors, more than 80 fully managed	✓ Serverless Flink with `ML_PREDICT` and `AI_COMPLETE`	✓ Schema Registry, Data Contracts, Stream Catalog, Stream Lineage, CSFLE, bring your own key (BYOK)	✓ Confluent Intelligence (Streaming Agents, Real-Time Context Engine, built-in ML functions)

The subsections below give a profile of the best-fit and trade-offs for each option. The decision matrix later in the article maps these options to specific organizational profiles.

Custom Build: Self-managed Kafka, Flink, andConnectors

The traditional self-managed approach involves provisioning open source Kafka, managing KRaft (or legacy ZooKeeper) quorums, deploying Flink clusters, and writing custom Python workers for chunking and vector embeddings.

Best for: massive enterprises with dedicated, heavily staffed infrastructure teams, extensive legacy on-premises deployments, unique networking constraints, and extreme customization requirements.

Trade-offs: you assume the maximum possible operational burden and get zero vendor SLAs on integrations, which means your team handles all edge cases, schema evolutions, and scaling events. This path incurs the highest hidden labor costs and delays time-to-market for AI features.

AWS MSK: AWS-Native Broker With Bolt-On Processing

MSK provides a managed broker experience. Teams often pair MSK with MSF or Lambda for processing and AWS Glue for schema management.

Best for: organizations under strict mandates to use only native AWS services for billing consolidation, or teams already deeply entrenched in the AWS ecosystem and willing to absorb significant day 2 operational burden.

Trade-offs: for production real-time RAG, the gaps add up fast.

First, the ZooKeeper-to-KRaft migration. Apache Kafka removed ZooKeeper entirely in Kafka 4.0. For any MSK customer still running on a ZooKeeper-based cluster (which covers most clusters spun up before AWS added KRaft support to MSK), this is a forced cluster rebuild: MSK has no in-place upgrade path from ZooKeeper to KRaft, so those customers must spin up a new cluster and migrate their data and applications. The technical effort to migrate from ZooKeeper-based MSK to KRaft-based MSK is roughly the same as migrating to Confluent Cloud.

Second, the SLA gap is structural. MSK provides 99.9% uptime covering infrastructure only, with Kafka and ZooKeeper software failures explicitly excluded. That works out to 7.9 additional hours (or more due to exclusions) of potential downtime per year compared to Confluent Cloud's 99.99%, which covers both infrastructure and Kafka software. For a real-time RAG pipeline feeding production AI, the gap of nearly eight hours is the difference between a minor incident and a stale-context outage.

Third, the hidden costs compound. MSK's apparent low price expands once you account for monitoring beyond CloudWatch's basic tier (topic-level metrics cost extra), a Kafka UI (MSK ships none), Cruise Control for partition rebalancing on Standard clusters, schema registry self-management (Glue Schema Registry primarily supports Java clients), proxy infrastructure, and a Private Certificate Authority for mTLS. Layer on a processing tier you assemble yourself: MSF runs on its own SLA separate from MSK and ships no ML_PREDICT or AI_COMPLETE equivalents, and Lambda is bound by a 15-minute execution wall that breaks long-running streaming state. Add a piecemeal governance story across Glue, Identity and Access Management (IAM), and CloudWatch with no unified Stream Catalog or Stream Lineage equivalent, and you're stitching multiple disparate services together with no single SLA, no Kafka-specific support, and AWS-only deployment with no multi-cloud or hybrid path.

Companies like Square, Instacart, iFood, SmartThings, and SecurityScorecard switched from MSK to Confluent because the operational burden and feature gaps became intolerable at scale. SecurityScorecard alone reports more than $1 million in savings after switching from MSK to Confluent.

Redpanda: Kafka-Compatible Broker Without a Full RAG Platform

Redpanda is a C++ Kafka clone with high (but not 100%) Kafka API compatibility, packaged across community on-premises, BYOC, dedicated, and serverless tiers.

Best for: small teams running simple event logging or edge workloads where C++ thread-per-core architecture and broker-level p99 latency are the primary constraints.

Trade-offs: Redpanda is a broker, not a data streaming platform, and the platform gap matters most for production RAG.

First, it isn’t fully compatible with Kafka API. Partial compatibility means edge cases break with tools that the open-source Kafka community treats as standard. Redpanda's "225 connectors" headline counts processors, which are equivalent to Kafka's single-message transforms (SMTs). The genuine production-ready connector count is a fraction of that figure, none of which are offered as a managed service, compared with Confluent's more than 120 connectors, with more than 80 fully managed.

Second, performance claims deserve scrutiny. Redpanda's "10x faster than Kafka" headline holds in synthetic, single-producer benchmarks. It degrades in real production workloads with larger producer groups, record keys, and long-running tests. Confluent's Kora engine, on production-shaped workloads, has been measured up to 10x faster than self-managed Kafka and delivers GBps+ throughput with elastic scaling rather than tier-based manual sizing.

Third, compliance and reliability are uneven. Redpanda lists two production-grade certifications (SOC 2 and GDPR readiness, plus a recent HIPAA self-attestation) against Confluent's 10 (SOC 1/2/3, ISO 27001/27701, PCI DSS, CSA Star, TISAX, HITRUST, HIPAA). The single-zone Redpanda BYOC and Dedicated SLA is 99.5%, equivalent to approximately 43 more hours of potential downtime per year than Confluent Cloud. Redpanda BYOC additionally requires installing an agent inside your virtual private cloud (VPC) with break-glass support access for Redpanda engineers, a model that enterprise security teams with strict data sovereignty requirements may find concerning.

Stream processing is bolt-on. Redpanda's in-broker WebAssembly transforms are sandboxed and, by Redpanda's own admission, limited to "trivial and stateless" cases. There is no native Flink, no ML_PREDICT or AI_COMPLETE equivalent, no Stream Lineage, no Stream Catalog, no client-side field level encryption, and no BYOK. Customers building real-time RAG end up assembling external processing and governance, which puts them back at the integration tax we already mapped.

Real customer migrations underscore the gap. Elemental Cognition, an AI digital native, switched from Redpanda to Confluent Cloud for mission-critical real-time workloads.

Confluent: Unified Streaming Platform for Real-Time RAG

Confluent delivers a complete data streaming platform that encompasses the Kora engine, Confluent Cloud for Apache Flink, more than 120 managed connectors, Stream Governance, Tableflow, and Confluent Intelligence under one SLA.

Best for: enterprises that need to stream, connect, process, and govern data under a single 99.99% SLA covering both infrastructure and Kafka software, and especially for teams building production-grade agentic AI applications who want first-class AI primitives natively integrated into the data plane.

Trade-offs: Confluent's list price can feel premium for basic, low-volume logging use cases. For complex, multi-source RAG architectures, the consolidated ecosystem typically yields the lowest TCO once connector development time, embedding worker tier consolidation, and avoided governance build-out are included. Forrester's Total Economic Impact study reports 257% ROI and $2.58M in savings over self-managed Apache Kafka, and Confluent's migration cost analysis shows up to 60% TCO reduction.

The Confluent advantage stack is concrete. Kora delivers GBps+ throughput with full Kafka protocol compatibility, so your existing producers and consumers don't change. Cluster Linking gives you a zero-downtime migration path from MSK or self-managed Kafka. Stream Governance bundles Schema Registry, Data Contracts, Stream Catalog, and Stream Lineage into a single suite, and CSFLE and BYOK lock down PII before it reaches the vector index.

The people and the AI layer round it out. Confluent was founded by the original co-creators of Apache Kafka. It’s one of the largest contributors to the Apache Kafka open source project, and offers committer-led support with a 60-minute contractual P1 response. On top of that foundation, Confluent Intelligence ships Streaming Agents, the Real-Time Context Engine, and built-in ML functions as native primitives, which is exactly the surface area a production RAG pipeline needs.

Customer evidence backs the position. Henry Schein One frames it directly: "Everyone wants AI, but the hard part is getting high-quality data moving in real time. The Confluent data streaming platform makes that possible for us." Notion attributes its ability to keep AI tools fed with up-to-the-second context to Confluent's managed connector and streaming layer. The Palmerston North City Council team summarizes the AI-data dependency clearly: "Good AI needs good data. Confluent is our trusted source of truth. The data streaming platform provides context and orchestration for our AI agents to automate workflows and accelerate our smart city transformation." SecurityScorecard reports more than $1 million in savings after switching from MSK to Confluent. The pattern is consistent: when teams move from a piecemeal stack to a unified platform, the AI roadmap unlocks.

Decision Matrix: Which Streaming Approach Fits Your Real-Time RAG Needs?

Choosing the right streaming infrastructure requires an assessment of your organizational constraints, existing engineering headcount, and strategic AI goals.

Organizational constraints and engineering profile	Recommended approach
If you have: Strict air-gapped environments, unique networking protocols, a dedicated team of more than 20 infrastructure engineers, and a mandate to avoid commercial software.	Choose: Custom build. The heavy integration tax and high labor costs are justified by absolute architectural control.
If you have: Predominantly simple event logging needs, low data volume, edge or single-zone deployments where the 99.5% single-zone SLA is acceptable, and a preference for a C++ broker.	Choose: Redpanda. Redpanda provides a low-footprint Kafka-compatible broker for targeted workloads, though you sacrifice platform completeness, governance, and a managed connector ecosystem.
If you have: A strict mandate to consolidate cloud billing within AWS, existing expertise in AWS Glue, AWS-only deployment with no multi-cloud or hybrid plans, and a willingness to absorb a forced ZooKeeper-to-KRaft migration.	Choose: AWS MSK. MSK offers native billing integration, provided you accept the 99.9% infrastructure-only SLA, several categories of hidden costs, and heavier orchestration overhead.
If you have: Multiple complex data sources, strict enterprise data governance requirements, the need to inject real-time context into AI agents, and a strategic mandate to ship fast.	Choose: Confluent. Confluent eliminates the integration tax, delivers stream, connect, process, govern, and AI-native primitives under one 99.99% SLA, and supports zero-downtime migration from MSK or self-managed Kafka via Cluster Linking.

Build vs Buy: Making the Call

Real-time RAG is a streaming systems problem before it is an AI problem. That single reframe is what separates teams who ship production AI from teams who stall in pilot purgatory.

The case for building is narrow and well-defined. If you operate in an air-gapped or sovereign environment, have unique networking constraints, or already staff a team of more than 20 engineers dedicated to Kafka and Flink operations, the upfront flexibility of open source components can justify the integration tax.

For most enterprises, that case doesn't apply. The cost math in this article is not subtle: three to six engineering months per CDC connector, a serverless ceiling that breaks long-running streaming state, and cross-AZ egress fees that compound silently. None of those costs show up in a vendor proposal. They show up two years in, when your AI roadmap is being held hostage by day two operations on infrastructure your team didn't set out to own.

A unified managed streaming platform shifts that math. Stream, connect, process, and govern collapse into one SLA. The embedding worker tier disappears into Confluent Cloud for Apache Flink. Schema Registry, Data Contracts, and Stream Lineage replace governance you would otherwise build yourself. And on top of those four foundational capabilities, AI-native primitives (Streaming Agents, Real-Time Context Engine, and built-in ML functions) give your agent teams a substrate they can actually ship against.

If your organization is building agentic AI and needs continuous, trusted context, Confluent is the streaming foundation that absorbs the integration tax instead of charging you for it. To go deeper, explore Confluent's ML_PREDICT and AI_COMPLETE model-inference functions inside Confluent Cloud for Apache Flink, or model your own infrastructure savings with Confluent's cost estimator.

Frequently Asked Questions

What is "real-time RAG" and why does it require streaming infrastructure?

Real-time RAG continuously syncs changes from operational systems into a vector index so LLM responses use fresh context. That requires CDC ingestion, stateful processing, and reliable delivery, not periodic batch jobs.

How do you keep a vector database in sync with Postgres or Oracle changes?

Use CDC connectors to capture inserts, updates, and deletes, process events to chunk text and generate embeddings, then apply upserts and deletes to the vectors database to prevent drift.

What is the "integration tax" in a DIY RAG pipeline?

The integration tax is the ongoing engineering cost of stitching together and operating connectors, stream processing, retries and dead letter queues (DLQs), schema evolution handling, and re-embedding workflows. It often dwarfs the initial build effort.

Where do real-time analytics databases fit in a real-time RAG architecture?

Real-time analytics databases serve a different role from streaming platforms. The streaming platform handles ingestion, processing, governance, and delivery. A real-time analytics database sits downstream as a query engine, powering sub-second dashboards, operational monitoring, and ad-hoc investigation over the same governed event streams. In architectures that use Tableflow, the analytics engine can query Kafka topics directly as Iceberg tables without a separate ETL pipeline.

How long does it take to build a production-grade CDC connector?

Commonly, three to six engineering months per connector, once you include snapshots, backfills, failure handling, schema changes, and operational runbooks.

Why do exactly-once semantics matter for embeddings and vector upserts?

Without exactly-once semantics, retries can create duplicate embeddings or miss deletes, corrupting the vector index and leading to stale or incorrect retrieval results.

What happens when the source schema changes (schema evolution)?

Pipelines can break or silently produce wrong embeddings unless schemas are governed with contracts and a registry, and downstream processors are compatible with additive and breaking changes.

How do you handle re-embedding when you change models or chunking logic?

You typically dual-write to a new index, backfill historical records, and cut over once parity is verified. This requires orchestration, lineage, and careful rollback planning.

When is "build" the right choice for real-time RAG streaming?

When you must run in air-gapped or sovereign environments, need extreme customization, or already have a large platform team to own Kafka, Flink, connectors, and 24/7 operations.

Is AWS MSK enough for production real-time RAG?

MSK can cover the broker layer, but teams often still need to assemble connectors, processing, governance, and reliability patterns across multiple services. That raises operational complexity.

What should I look for in a managed streaming platform for RAG in 2026?

Native support for stream, connect, process, and govern, plus AI-ready capabilities like in-flight embedding generation, strong SLAs, schema governance, lineage, and secure PII handling.

How does a unified platform reduce cost compared to separate embedding workers?

If embeddings are generated within the stream processor, you can eliminate the need for a separate fleet of Python workers and the associated scaling, monitoring, retries, and queue management overhead.

How do you prevent PII from entering the vector database?

Apply governance controls (RBAC, masking, data minimization) and enforce policies in-stream before embedding or upserting, so sensitive fields never reach the index.

How to Build Compliant AI Agents With Stateful Stream Processing (EU AI Act-Ready Architecture Guide)

Manveer Chawla — Mon, 15 Jun 2026 22:15:11 +0000

The EU AI Act's general provisions are already in force, and high-risk AI system obligations apply from August 2026. The National Institute of Standards and Technology (NIST) AI Risk Management Framework and its Generative AI Profile set the baseline for what auditors expect, framing governance around four functions: identify, measure, manage, and monitor. Deploying artificial intelligence (AI) agents in regulated environments isn't a sandbox experiment anymore. It's a strict governance challenge.

Modern regulatory frameworks mandate automatic, lifetime event logging for high-risk AI systems, and stateless, chat-style agent frameworks typically can't satisfy that requirement. Replaying their decisions verbatim for auditors is rarely straightforward. Side effects like financial transactions can fire more than once during application retries. Audit trails get painstakingly reconstructed from fragmented application logs days after the fact. And sensitive personally identifiable information (PII) can scatter across vector stores, prompt caches, and external model providers with no centralized lineage and no client-side encryption.

Regulators don't just want to block bad answers. They expect you to reconstruct exactly why an agent made a decision months later, using the exact data, model weights, and logic available at that precise microsecond.

This guide gives Compliance Tech Leads and Enterprise Architects the architectural blueprint to evaluate agent runtimes and design legally defensible AI systems.

Executive Summary

Regulated AI agents can't typically be built as stateless chat apps. Auditors require lifetime, tamper-evident logging, exact traceability, and replayable decisions.
Model agents as event-driven, stateful workflows on a streaming-native runtime where Apache Kafka® and Apache Flink® form the deterministic system of control, and the large language model (LLM) is the probabilistic reasoning engine.
Maintain seven distinct states (case, regulatory obligation, evidence, model version, consent, risk, audit log) so every decision is grounded in a durable, auditable context.
Apply four streaming patterns: event sourcing for an immutable Agent Decision Record, stateful policy gates to block unsafe actions, windowed monitoring for drift and bias, and state-based replay for verifiable audits.
Add client-side field level encryption (CSFLE), schema-level data contracts, and end-to-end lineage so sensitive data stays governed from source system to model output.
Streaming-native runtimes (Apache Kafka and Apache Flink on Confluent Cloud) are the architectural category that puts deterministic control and probabilistic reasoning under a single governed backbone.

Seven Types of State Compliant AI Agents Must Maintain

For regulatory compliance, stateful processing goes well beyond maintaining chat memory or a rolling window of conversation history. It captures the durable, multi-dimensional context required to make a legally binding or financially impactful decision.

To build a defensible system, architects must capture and manage seven distinct states. The taxonomy below synthesizes the logging, traceability, and governance obligations of frameworks like the NIST AI Risk Management Framework, EU AI Act Article 12, and the IETF Agent Audit Trail draft into a unified state model for agent runtimes.

Case State

Case state tracks exactly where a review, application, or claim stands within its lifecycle: which step of the workflow is active, what's been completed, and what remains pending. It's the agent's working understanding of "where are we" on a specific business process.

Regulatory Obligation State

Obligation state binds each case to applicable regulatory rules, statutory deadlines, and required escalation paths. If a suspicious transaction is flagged, the obligation state tracks the strict 30-day window required to file a Suspicious Activity Report (SAR). The agent prioritizes tasks based on compliance deadlines, not arbitrary queue ordering.

Evidence State

Evidence state captures immutable snapshots of the documents, user inputs, and exact vector database retrieval corpus used to ground the prompt at execution time. Without the precise state of the retrieval corpus at the millisecond the decision was made, a verifiable reconstruction of the decision context becomes impossible.

Model Version State

Model state locks in the exact model versions, prompt template versions, and generation parameters deployed during the inference step. Combined with the evidence state, it gives auditors a complete snapshot of the conditions present when the agent acted.

Consent State

Consent state enforces attribute-based and role-based access controls, tracking user permissions and data processing expirations. It prevents the agent from using data or invoking tools beyond the scope that a user (or a specific regulatory basis) has authorized.

Risk State

Risk state maintains rolling anomaly windows and dynamically calculated risk scores, allowing the system to monitor for model drift or emergent bias and trigger escalations the moment thresholds are crossed.

Audit Log State

Audit state forms the immutable event log itself. It's the foundational ledger that guarantees non-repudiation and supports full replayability of the entire state machine.

Four Streaming Patterns for Compliant, Auditable AI Agents

To transform these state definitions into a defensible, auditable system, architects must apply specific distributed streaming patterns. These patterns dictate how data moves, how rules are enforced, and how history is preserved.

Pattern 1: Event Sourcing to Create an Immutable Agent Decision Record

Event sourcing means every input, vector retrieval, policy check, tool call, human override, and final action becomes a distinct, immutable event stored in a highly available Kafka topic. This forms the foundation for the audit, evidence, and model states.

The tangible output is the Agent Decision Record: a structured event stream that logs every step of the agent workflow with reason codes, evidence references, and rule citations attached. The schema draws from emerging proposals like the Internet Engineering Task Force (IETF) Agent Audit Trail draft, which specifies a tamper-evident cryptographic chain using a previous-hash field encoded in SHA-256 alongside digitally signed records to guarantee non-repudiation.

By capturing the exact prompt, retrieval citations, tool execution results, and policy gate evaluations in a tamper-evident ledger, organizations directly satisfy EU AI Act requirements for automatic logging and lifetime traceability.

Pattern 2: Stateful Policy Gates to Enforce Compliance Before Actions

In a compliant architecture, an agent typically can't directly execute a real-world action. Deterministic business rules get evaluated against the agent's accumulated state immediately before a proposed action can create a real-world side effect.

The language model only suggests. The stateful policy gate decides.

This acts primarily on the case, obligation, consent, and risk states. For instance, a policy gate queries the case state to determine whether an insurance claim remains within its legally mandated 30-day review period. It queries the risk state to check if a customer's rolling anomaly score exceeds the threshold for autonomous approval.

If the probabilistic output violates the deterministic policy, the gate blocks the transaction and safely routes the event to a human-in-the-loop dead letter queue. Policy gates also enforce segregation of duties (preventing the same agent identity from both proposing and approving a high-value action) and provide the system-wide kill switch that disables autonomous actuation while preserving intake, routing, and audit logging.

Pattern 3: Windowed Monitoring to Detect Drift, Bias, and Emerging Risk

Regulators require continuous monitoring for bias and performance degradation. Windowed monitoring computes real-time analytics over event-time windows to detect drift, bias, or runaway agent loops instantly. You don't wait for an end-of-month batch report.

This pattern continuously queries the risk state, applying statistical change detection algorithms like Kullback-Leibler divergence or the Page-Hinkley test over sliding time windows. The system instantly recalculates rolling risk scores and fraud probabilities.

It also monitors the case and obligation states to track service level agreements (SLAs), detect processing bottlenecks, and alert compliance teams if a queue of automated decisions approaches a statutory deadline.

Pattern 4: State-Based Replay to Reproduce Decisions for Auditors

Auditors demand proof, not promises.

By combining the immutable Agent Decision Record with versioned state backends, you can create reproducible decision traces. Supply the same input events alongside the exact same evidence, model, and case state snapshots, and the system reconstructs exactly what the agent knew, what context it operated on, and what decision was logged, giving auditors a complete, verifiable record.

Achieving this requires the model state to include a retrieval snapshot identifier that points to a specific backup or versioned instance of the vector database. This identifier ensures the exact retrieval corpus can be reloaded into the context window.

Verifiable reconstruction proves to an auditor precisely what the agent did, what it knew, and why it acted. That's the highest standard of regulatory verifiability.

Reference Architecture for Compliant AI Agents Using Confluent

To achieve these patterns in production, enterprise architects need a streaming-native infrastructure stack. The following reference architecture positions Confluent as the deterministic system of control, wrapping the probabilistic interactions of the language model.

Compliant AI agent reference architecture:

A compliant implementation relies on a clear, unidirectional flow of events. External event sources feed into the system via managed connectors. Events land in an immutable Kafka topic that acts as the central nervous system of the architecture. A stream processor ingests these events, maintaining the seven states in local durable storage.

When an agent action is proposed, the stream processor routes the context to a stateful policy gate. If approved, the agent interacts with the language model layer. The model's response is validated, logged to the Agent Decision Record topic, and finally routed to a downstream audited sink for execution.

Ingest and Connect Event Sources

The architecture begins by capturing events via the Kafka protocol. One of the easiest ways to run a Kafka cluster is Confluent Cloud, powered by the the Kora engine, which delivers a 99.99% uptime SLA and holds SOC 2, ISO 27001, PCI DSS, and HIPAA compliance attestation.

Data flows in through more than 120 fully managed connectors for critical systems of record, including PostgreSQL via Debezium, and Oracle via change data capture (CDC) and XStream for transactional events, plus Snowflake for analytical context and Amazon S3 for document evidence. In regulated environments, those upstream systems include claims platforms, Know Your Customer (KYC) providers, electronic health records, and human resources information systems (HRIS).

Crucially, this layer supports client-side field level encryption (CSFLE). By defining encryption rules at the schema level, sensitive PII is encrypted before it ever leaves the source system. The data remains encrypted in motion and at rest within the broker, so sensitive information never travels in clear text to the agent or the model provider.

Process Events With Stateful Stream Processing (Apache Flink)

Confluent Cloud for Apache Flink serves as the brain of the control flow, holding the seven critical states across multi-step agent workflows using highly scalable RocksDB state backends. Teams can express logic in ANSI SQL, Python, or Java, matching the existing skill mix of data, platform, and compliance engineering.

Flink provides exactly-once processing semantics through its two-phase commit sink functions. A real-world side effect, like an approved financial transfer or a sent email, fires exactly one time even if the application crashes or the network forces a retry, though this guarantee applies to the stream-processing layer only. LLM API calls are non-transactional HTTP side effects and require separate idempotency handling.

This eliminates the duplicate execution risks inherent in stateless agent frameworks.

Govern Schemas, Data Contracts, and Lineage

Governance is enforced at the broker level using Schema Registry and Data Contracts. Malformed inputs, hallucinated schema structures, or missing required fields are rejected before they can corrupt the state machine.

Stream Catalog lets compliance teams discover and request access to trusted agent-input streams without depending on tribal knowledge. Stream Lineage provides an interactive, visual topology of the data flow, so architects can trace which specific schema version, input topic, and model pipeline produced a given automated approval.

AI Agent Reasoning Layer

The reasoning layer is managed through Confluent Intelligence, which runs Streaming Agents directly as Flink jobs. Tool calling is coordinated through the Model Context Protocol (MCP), and agent-to-agent coordination uses the emerging A2A protocol to safely expose external APIs and other agents to the reasoning engine.

Confluent’s Real-Time Context Engine serves as the bridge, providing privacy-aware context to the language model over MCP. Built-in machine learning functions handle embeddings, anomaly detection, and forecasting directly from the stream, so feature pipelines and model calls live in the same governed runtime as the agent itself.

Regulated AI Agent Use Cases by Industry

The separation of probabilistic reasoning and deterministic stream processing isn't theoretical. Leading organizations across highly regulated sectors currently use this blueprint to deploy agentic workflows safely. The patterns also extend cleanly to insurance underwriting and HR/workforce decisioning, where similar evidence, consent, and replay obligations apply.

Financial Services Use Case: AML and KYC Agents

In the financial sector, autonomous agents review transaction alerts and orchestrate Anti-Money Laundering (AML) and KYC data gathering. These agents maintain a continuously rolling customer risk state.

As new transactions stream in, Flink updates the risk profile in real time. Stateful policy gates enforce hard regulatory boundaries. Any customer whose risk score exceeds the acceptable threshold is blocked from autonomous approval. The agent must route the Agent Decision Record to a human compliance officer.

This architecture mirrors the real-time risk platforms used by institutions like Capital One, where high-throughput stream processing supports real-time banking for more than 100 million customers, including risk scoring and fraud detection without sacrificing operational latency.

Healthcare Use Case: Prior Authorization and Claims Agents

Healthcare claims and clinical decision-support agents operate under the strict privacy constraints of HIPAA.

In this blueprint, the case state tracks active medical reviews, managing the complex routing required for human-in-the-loop approvals from medical directors. CSFLE ensures that protected health information (PHI) is cryptographically protected within the event stream.

Organizations like Henry Schein One use the Confluent data streaming platform to modernize legacy healthcare workflows, proving that streaming platforms can handle the integration and governance requirements of highly sensitive clinical data.

Public Sector Use Case: Benefits Eligibility Orchestration Agents

Government benefit orchestration agents must enforce strict data sovereignty rules and calculate exact-time eligibility windows.

If a citizen applies for municipal assistance, the agent must evaluate their eligibility based on a precise snapshot of their financial data and the legal statutes active on that specific day.

Public sector entities, such as the Palmerston North City Council, use real-time streaming architectures to orchestrate complex citizen services. Automated determinations stay transparent, legally sound, and immune to processing delays.

Privacy Operations Use Case: DSAR Handling (GDPR and CCPA)

Managing General Data Protection Regulation (GDPR) and California Consumer Privacy Act (CCPA) operations requires careful precision.

Agents deployed to handle Data Subject Access Requests (DSARs) track the state of identity verification and manage the strict 30-day regulatory deadline for compliance. This is distinct from the financial services 30-day SAR window above, but it's enforced through the same windowed-deadline pattern. Flink timers monitor these deadlines, automatically escalating cases at risk of a breach.

For erasure requests, the immutable event log uses tombstone records and cryptographic shredding. The user's data is irretrievably destroyed while preserving the integrity of the tamper-evident audit chain. You can prove to regulators that the deletion was executed correctly and on time.

How to Evaluate AI Agent Architectures for Compliance

When designing systems for highly regulated environments, architects need a clear rubric. The following four-dimensional scorecard separates architectures that can carry a high-risk workload from those that can't.

Agent-runtime properties: Always-on durable state versus reactive stateless invocation. Exactly-once execution of side effects. Replay capability. Version pinning across model, prompt, policy, and retrieval corpus.

Governance properties: Data contracts at the broker. Lineage from the source system to the model output. Role-based access control (RBAC) and CSFLE. Retention and deletion alignment with privacy obligations.

Connector and identity coverage: CDC against systems of record. KYC and identity feeds. HRIS integration. Coverage of the actual systems that hold regulated data.

AI primitives: MCP-served context. A2A coordination. Stateful policy gates. Kill-switch support that disables autonomous action while preserving intake, routing, and audit logging.

Applied to today's market, four categories emerge.

Platform Comparison Across the Four Dimensions

Dimension	Closed agent platforms (Agentforce, Copilot Studio, )	Open source frameworks (LangChain, LangGraph, LlamaIndex)	Workflow orchestrators (Temporal, AWS Step Functions)	Streaming-native runtimes (Apache Kafka and Apache Flink on Confluent Cloud)
Agent-runtime properties	Black-box state; replay and version pinning are typically not exposed	No native durable state; replay depends on bolted-on storage	Durable execution assumes deterministic code; LLM side effects break replay	Always-on durable state, exactly-once side effects, replayable with full version pinning
Governance properties	Vendor-managed; limited lineage, no broker-level data contracts	Application-level only; audit trails fragmented across logs and external databases	Workflow-level audit; no schema enforcement at the data plane	Broker-level data contracts, end-to-end lineage, RBAC, CSFLE
Connector and identity coverage	Tied to vendor ecosystem	DIY connectors; no managed CDC	Bring-your-own integrations	More than 120 managed connectors including CDC for Postgres, Oracle, Snowflake, and S3
AI primitives	Proprietary tool catalog; limited extensibility	Strong prototyping primitives; no stateful policy gates or kill switch	No native AI primitives; LLM is just another step	MCP-served context, A2A coordination, stateful policy gates, kill switch

Closed Agent Platforms

Proprietary platforms like Salesforce Agentforce, and Microsoft Copilot Studio offer rapid time-to-value for low-regulation, horizontal use cases such as basic customer support or internal knowledge retrieval.

For regulated workloads, however, they don't expose the deep, customizable event lineage, cryptographic audit trailing, and raw data control needed when an auditor demands a byte-for-byte reconstruction of a custom financial or clinical workflow.

Open Source Agent Frameworks

Open source libraries such as LangChain, LangGraph, and LlamaIndex have transformed developer productivity and excel as tools for prototyping language model interactions. LangGraph adds native checkpointing, but these frameworks remain application-level abstractions that lack exactly-once execution guarantees, and the enterprise-grade governance required to prevent data loss during catastrophic system failures.

These frameworks rely heavily on external databases and application logs, which produces fragmented audit trails that struggle to demonstrate non-repudiation.

Workflow Orchestrators

Standard workflow orchestrators like Temporal and AWS Step Functions excel for long-running, human-driven processes. They provide durable execution by replaying deterministic code against an event history.

The non-deterministic nature of language models is harder for them. If an LLM side effect isn't perfectly isolated and idempotent, orchestrators risk duplicate executions or non-determinism errors on replay. They're also not designed to handle massive, continuous event-time windowing or the high-throughput streaming integration required to calculate rolling risk metrics in real time.

Streaming-Native Runtimes

A streaming-native runtime built on Apache Kafka and Apache Flink, delivered through Confluent Cloud, unifies the system of control and the system of reasoning under a single governed backbone.

Kafka's immutable log provides the durable event backbone. Flink's checkpointing and Kafka-transaction integration close the loop with exactly-once semantics within the pipeline. For external side-effects, the architecture pairs at-least-once delivery with idempotent sinks to achieve effectively-once end-to-end behavior. Compliance teams get authority over data lineage, policy enforcement, and cryptographic auditing. The agent stays tethered to deterministic enterprise rules.

For low-regulation horizontal use cases, the closed and open-source options remain valid. For workloads where auditability and replay are non-negotiable, streaming-native runtimes are a stronger fit.

Phased Rollout Plan for Compliant AI Agents

Transitioning from stateless prototypes to compliant, event-driven agent programs requires a disciplined, iterative approach. Enterprise architects should adopt a three-phase rollout strategy to mitigate risk and establish foundational governance.

Phase 1: Pilot One Regulated Workflow With a Stateful Agent

Start by selecting a single, well-defined regulated use case, like initial claims triage or document classification.

Implement the core streaming architecture on Confluent Cloud's managed Kafka and Flink, focusing entirely on establishing the Agent Decision Record schema and enforcing CSFLE.

During this phase, disable autonomous actuation. Rely heavily on human-in-the-loop thresholds. Use the agent strictly as a decision-support tool while auditors validate the integrity and completeness of the tamper-evident event log.

Phase 2: Scale to Cross-Workflow Orchestration With Shared Governance

Once auditors verify the audit trail, expand the architecture to orchestrate multiple cooperative agents. Implement a centralized Schema Registry to enforce data contracts between different agent domains.

Abstract the stateful policy gates into versioned, manageable rule sets.

This phase introduces automated side effects for low-risk decisions, using Flink's exactly-once sinks to guarantee transactional integrity while routing medium and high-risk cases to human operators.

Phase 3: Run Fully Automated, Continuously Monitored Regulated Agents

In the final maturity phase, organizations achieve continuous, real-time oversight.

Implement complex windowed monitoring for instant drift detection and rolling risk scoring. Wire the kill switch into the operations console so compliance leaders can suspend autonomous actuation across the agent fleet without disrupting intake or audit logging. The architecture now supports fully automated, replayable backtesting.

Data science teams can simulate new prompt templates or model versions against historical, versioned state snapshots to demonstrate compliance before deploying updates to production.

Conclusion and Next Steps

For highly regulated enterprise workloads, robust auditability and verifiable reconstruction are not optional. They are mandates. You cannot bolt compliance onto a stateless prototype after the fact. It must be engineered into the foundational fabric of the system from day one.

Modern AI legislation requires a paradigm shift in how we architect autonomous systems. You need a clear boundary where deterministic policy and immutable state, driven by stream processing, tightly wrap and constrain the probabilistic reasoning of large language models.

If you are building AI agents under strict regulatory, financial, or clinical compliance requirements, the path forward is concrete:

Audit your current agent stack against the four-dimension rubric (agent runtime, governance, connectors, AI primitives). Identify which properties are missing today and document the regulatory exposure each gap creates.
Pick one regulated workflow for a Phase 1 pilot. KYC review, claims triage, or DSAR handling are good candidates: narrow enough to ship, regulated enough to validate the audit chain.
Stand up the Agent Decision Record schema first. Even when the agent runs as decision-support only, the tamper-evident event log is the artifact auditors will examine. Get the schema, signing, and lineage right before adding autonomy.
Run a reconstruction drill before Phase 2. Reconstruct a past decision from event history and versioned snapshots. If you can't, the architecture isn't ready for autonomous actuation.

Confluent provides the streaming-native runtime to make these systems verifiably defensible, scalable, and secure. Explore the Kora engine, Confluent Cloud for Apache Flink, and Confluent Intelligence when you're ready to design Phase 1.

Frequently Asked Questions

What makes an AI agent "compliant" in regulated environments like the EU AI Act?

A compliant agent produces a complete, tamper-evident audit trail of inputs, context, model configuration, decisions, and actions, plus the ability to reconstruct decisions later using the same evidence and versions. It must also enforce access controls, data minimization, and continuous risk monitoring.

Why are stateless, chat-based agent frameworks hard to audit?

They don't persist a deterministic decision history, so outputs can't be reconstructed exactly months later. They also rely on fragmented application logs and can trigger duplicate real-world side effects during retries.

What is an Agent Decision Record?

It's the structured, immutable event stream defined earlier in this guide. Every input, retrieval, prompt, tool call, policy check, human override, and final action is captured with reason codes and evidence references attached.

What does "stateful stream processing" mean for AI agents?

The agent's workflow context (case status, obligations, evidence snapshots, consent, risk signals, and audit history) is stored durably and updated continuously as events arrive. Decisions are made against the accumulated state, not just the current prompt.

How do you prevent an AI agent from executing an unsafe or non-compliant action?

Put a deterministic stateful policy gate in front of side effects. The LLM can propose an action, but the gate approves or blocks it based on current case, consent, obligation, and risk state. The system-wide kill switch can disable autonomous actuation entirely while keeping intake and audit flowing.

What is "exactly-once" execution, and why does it matter for agents?

Exactly-once guarantees that a side effect (e.g., payment, email, account change) happens one time, even if the system retries or crashes. This prevents duplicate transactions, which is an audit and financial risk common in stateless agent designs. Note that this guarantee applies to the stream-processing layer. Any external side effect, such as LLM API calls, requires separate idempotency handling.

How can an organization replay an agent decision for an auditor?

Store the full event history plus versioned snapshots of evidence and model configuration (including retrieval snapshot identifiers). Reloading the same input events and state snapshots reconstructs what the agent knew and what decision was logged, giving auditors a complete, verifiable record without running the LLM.

How do you handle PII and PHI safely when using LLMs in agent workflows?

Encrypt sensitive fields before they leave source systems with CSFLE, enforce schema-based contracts, and restrict what context can be sent to the model. Maintain lineage so you can prove where sensitive data flowed.

What's the difference between the "system of control" and the "system of reasoning"?

The system of control is a deterministic infrastructure (stream processing, policy, and state) that governs what can happen. The system of reasoning is the LLM, which generates probabilistic suggestions that must be validated and logged.

Do I need Apache Kafka and Apache Flink to build compliant AI agents?

You need an immutable event log, durable state, deterministic policy enforcement, and verifiable reconstruction at scale. Kafka and Flink commonly implement those requirements, but the key is meeting the compliance properties, not using specific products.

What is the best real-time analytics database in 2026? An engineering buyer's guide

Manveer Chawla — Sun, 14 Jun 2026 03:26:07 +0000

Traditional databases just can't keep up with high concurrency and low latency at the same time.

The term "real-time" has become kind of meaningless. Everyone claims it, from batch-oriented cloud data warehouses to transactional database extensions. This makes picking the right architecture really hard without expensive trial and error.

The best real-time analytics database in 2026 depends entirely on your workload shape.

Key takeaways

Real-time analytics (in this guide) = sub-second p95/p99 analytical queries on billions of rows, high concurrency, and milliseconds-to-seconds freshness.
Best overall in 2026 for most workloads: ClickHouse (ingest throughput, query speed at scale, compression/TCO).
Best for strictly predefined query paths via star-tree indexes: Apache Pinot.
Best for time-series operational dashboards and observability: ClickHouse. ClickStack is its full observability offering for logs, metrics, and traces.
Best for rigid ingestion-time roll-up aggregations: Apache Druid.
Best for unified OLTP + real-time analytics: ClickHouse paired with its managed Postgres offering and native sync to ClickHouse, giving you a purpose-built OLTP engine and a purpose-built OLAP engine without rolling your own CDC pipeline. SingleStore is an alternative if you prefer a single HTAP engine for both.
Traditional Data Warehouses: Snowflake and BigQuery are fine for batch BI if you already have one, but face latency, concurrency, and cost challenges under sub-second, high-concurrency workloads.
Evaluate using 4 axes: ingest/freshness, latency under concurrency, TCO, operational complexity.

What 'real-time analytics' means (and why warehouses and OLTP databases fail)

Strict engineering thresholds define true real-time OLAP: sub-second query latency on complex aggregations, the ability to serve tens to thousands of concurrent queries per second (QPS), and data freshness measured in milliseconds to seconds.

Traditional cloud data warehouses like Snowflake and BigQuery are fine for batch BI if you already have one, where minute-to-second latency is acceptable. They were not architected for sub-second, high-concurrency workloads, which is why many teams add a purpose-built real-time OLAP engine as a speed layer alongside their existing warehouse, or use it as a complete replacement and consolidation option.

Snowflake's virtual warehouse model can introduce compute startup overhead and queueing that adds latency variability, which can challenge sub-second SLAs for always-on interactive workloads.

BigQuery's shared slot model can introduce slot queueing under high concurrency, adding latency variability that conflicts with sub-second SLA requirements.

Exposing these warehouses to public-facing applications or frequent polling dashboards can drive costs up significantly due to compute-uptime pricing models that charge for always-on resources. At petabyte scale, purpose-built real-time engines can deliver significantly better cost-performance than cloud warehouses due to superior compression, vectorized execution, and compute-storage separation.

On the other end, PostgreSQL is an excellent OLTP database that works well for analytics at small scale. But extensions can't rewrite its core tuple-at-a-time execution engine, so scanning billions of rows with sub-second latency is beyond its architectural reach.

Columnar storage and CPU vectorization, foundational to purpose-built OLAP engines, are not present in PostgreSQL's core. At scale, row-oriented storage and B-tree indexes create increasing overhead under analytical ingestion workloads. For teams outgrowing PostgreSQL's analytical capabilities, ClickHouse's PostgreSQL integrations provide an upgrade path.

Real-time OLAP evaluation criteria: the four axes that matter

Ingest throughput and data freshness (Kafka/CDC)

A real-time database must ingest high-volume event streams from Kafka, Redpanda, or change data capture (CDC) pipelines without degrading read performance.

Focus your evaluation on exactly-once semantics, non-blocking inserts, and whether the system makes data queryable within milliseconds or seconds of arrival. Engines using Log-Structured Merge (LSM) style architectures allow heavy ingestion to proceed without blocking read operations.

Query latency under concurrency (p95/p99, QPS)

Horizontal scaling alone can't maintain sub-second p95 and p99 latency when over a thousand external users simultaneously query a dashboard.

The system needs architectural advantages like SIMD vectorized execution, pre-aggregation mechanisms, and intelligent data pruning to minimize query fanout and CPU cycles per row. Vectorized execution using SIMD instructions maximizes CPU throughput per query by processing data in batches of column values.

Total cost of ownership at scale (compression, compute-storage separation)

As data volume grows from terabytes to petabytes, infrastructure costs scale dynamically based on storage layout.

True columnar compression deeply impacts TCO. Systems offering configurable compression codecs and compute-storage separation let teams scale compute independently of storage. Storing a petabyte of raw data in a highly compressed columnar format often reduces the footprint to a fraction of its original size, but the primary cost saving comes from improved performance. Scanning significantly less data translates faster I/O directly into cheaper compute, dramatically lowering overall costs for high-cardinality data compared to uncompressed row stores.

Operational complexity and reference architecture

The modern real-time reference architecture has shifted away from batch loading. The standard pipeline now flows from a streaming source into a real-time OLAP engine, through materialized views, and out to a serving API or dashboard.

You'll need to evaluate the burden of cluster management, metadata handling, node types, and schema evolution. Systems requiring external coordination services, independent metadata databases, and multiple dedicated node types carry operational overhead. ClickHouse Keeper replaces ZooKeeper for self-managed ClickHouse deployments, while ClickHouse Cloud and other managed serverless runtimes abstract away cluster coordination and infrastructure maintenance entirely.

Top real-time analytics databases in 2026 (ClickHouse, Pinot, Druid, SingleStore)

ClickHouse for real-time analytics

ClickHouse strengths for real-time OLAP

ClickHouse provides the broadest workload coverage, highest raw ingest throughput, and the strongest price-performance because of unmatched columnar compression.

Recent engine advancements have addressed historical criticisms. ClickHouse now provides robust JOIN support for standard analytical patterns and star schemas. Recent investments in the query planner, including automatic global join reordering and memory-optimized execution strategies, drastically reduce memory usage and execution time without requiring explicit algorithm tuning.

A native JSON type enables sub-100ms queries on semi-structured data by splitting JSON objects into independently compressed sub-columns. Going beyond the fundamentals, it features automatic type inference and seamlessly handles arbitrarily deep, unlimited dynamic fields without schema changes. Query performance on the native JSON type is comparable to explicitly typed columns and significantly faster than string-based JSON parsing approaches.

Lightweight updates and deletes use a patch-parts mechanism: changes are applied immediately at query time via small delta parts and materialized asynchronously during the standard background merge process, establishing them as the primary, standard method for typical use cases that outperform standard ALTER TABLE mutations. Standard mutations are reserved for specific, large-scale, partition-aligned operations. Separately, ReplacingMergeTree provides current-state deduplication by key, well suited for CDC and upsert workloads.

ClickHouse trade-offs and limitations

Maximizing performance requires a solid understanding of specific table engines, materialized view mechanics, and sorting keys. ClickHouse favors explicit architectural control over magic black-box optimizations.

ClickHouse architecture and deployment options

ClickHouse runs as an efficient single binary, which means it is easier for Ops to run in production and easier for Devs to spin up for local development and testing. It also provides unmatched deployment versatility, running seamlessly in-memory, via CLI, on a single-server, or fully distributed. Self-managed deployments use MergeTree with workload scheduling and resource management for workload isolation. ClickHouse Cloud uses SharedMergeTree with separated storage and compute, plus dedicated read-write and read-only compute services for auto-scaling without replicated write overhead. For observability use cases, ClickStack is ClickHouse's full observability stack covering logs, metrics, and traces.

Apache Pinot for ultra-low-latency user-facing analytics

Pinot strengths (Kafka ingestion, star-tree indexes)

Apache Pinot delivers elite optimization for ultra-low-latency query performance and heavy Kafka-first event ingestion. Its native pull-based Kafka consumer reads micro-batches to make events queryable within milliseconds, offering exactly-once semantics.

Pinot's defining feature is the star-tree index. It's an intelligent, tunable materialized view that pre-aggregates user-defined dimensions while leaving raw data queryable, driving query times down by orders of magnitude.

The multi-stage V2 query engine supports robust distributed joins, including broadcast, lookup, and shuffle distributed hash joins, scaling complex join throughput to hundreds of queries per second.

Pinot trade-offs (operational complexity, upserts)

Pinot introduces significant operational complexity. You're managing controllers, brokers, servers, minions, ZooKeeper, and a deep store.

Full-row upserts require a heavy in-memory primary key map, adding substantial memory overhead in self-managed open-source deployments.

Pinot architecture (brokers, servers, controllers, deep store)

A complex, heavily distributed architecture optimized for multi-tenancy and predictable low latency. StarTree provides the primary managed cloud offering and notably offloads the in-memory upsert requirement to disk.

Apache Druid for time-series dashboards and rollups

Druid strengths (streaming ingestion, rollups)

Apache Druid is heavily optimized for time-series aggregation, high-ingest log data, and operational dashboards.

Native Kafka and Kinesis streaming ingestion is a core strength. Data processes through supervisor specifications and becomes visible within seconds. Druid achieves guaranteed sub-second query latency by relying heavily on ingestion-time rollups, drastically reducing the data volume scanned during routine, predictable dashboard queries.

Druid trade-offs (ad-hoc queries, high-cardinality data)

Druid struggles with ad-hoc queries over raw, non-aggregated, high-cardinality data because its engine heavily depends on its pre-aggregated segment format.

Druid also demands a large operational footprint. You're looking at overlord, coordinator, broker, historical, and MiddleManager nodes, alongside a separate relational metadata database and ZooKeeper.

Druid architecture (segments, coordinators, historical nodes)

A segment-based distributed architecture requiring strict data roll-up modeling. Imply Polaris serves as the primary managed cloud option.

SingleStore for HTAP (OLTP + real-time analytics)

SingleStore strengths for HTAP workloads

SingleStore excels at hybrid HTAP (Hybrid Transactional/Analytical Processing) capabilities. It allows simultaneous transactional writes and analytical reads within a single unified engine.

The architecture uses a memory-optimized rowstore for active operational data and a disk-based columnstore for historical analytical data, managed by a powerful query optimizer with mature automatic join reordering capabilities.

SingleStore trade-offs (memory footprint, cost for pure OLAP)

Supporting true OLTP-grade latency requires maintaining active data in memory. This significantly increases the infrastructure footprint and compute cost for pure analytical workloads compared to purpose-built, disk-optimized OLAP engines.

SingleStore architecture (rowstore vs columnstore)

A distributed SQL database blending row and columnar storage formats. SingleStore Helios provides the managed cloud database-as-a-service option.

Real-time OLAP comparison: database features, performance, and cost

Dimension	ClickHouse	Apache Pinot	Apache Druid	SingleStore
Core architecture	Columnar (MergeTree family)	Columnar (Segment-based)	Columnar (Immutable segments)	Hybrid HTAP (Row + Columnar)
Ingest freshness SLA	Seconds to near-real-time	Milliseconds (Kafka-native pull)	Seconds (Streaming supervisor)	Real-time (Transactional inserts)
Concurrency limit	Hundreds to 1,000s+ QPS	1,000s+ QPS (via Star-Tree)	Hundreds of QPS (via Rollups)	Hundreds to 1,000s QPS
Join performance	Grace Hash, Parallel Hash, Auto-reorder	Broadcast, Lookup, Shuffle Dist. Hash	Limited; pre-joined models preferred	Full SQL joins, mature auto-reordering
Mutable data handling	Lightweight Updates, ReplacingMergeTree	Full/partial upserts; Primary Key map	Append-mostly; no native upserts	Full ACID transactions (UPDATE/DELETE)
Managed cloud options	ClickHouse Cloud	StarTree Cloud	Imply Polaris	SingleStore Helios

All four engines support analytical workloads, but compression ratios and execution speed heavily influence total cost of ownership.

ClickHouse consistently achieves 10-20x compression over row stores because its fundamental columnar architecture groups similar data together. This layout makes configurable compression codecs, like LZ4 for hot query paths and ZSTD for cold storage, highly effective. This extreme compression, paired with hardware-optimized SIMD vectorized execution, allows ClickHouse to scan billions of rows with minimal compute resources.

Pinot and Druid achieve low latency primarily through aggressive data pruning, segment indexing, and ingestion-time pre-aggregation rather than raw vectorized scan speed.

SingleStore requires splitting memory between its rowstore and columnstore, meaning its pure analytical compression ratios can't match dedicated OLAP engines.

When evaluating these engines, it is a mistake to be overly reliant on vendor benchmarks, which are often closed and lack methodology. Instead, prioritize benchmarks that are open-source, reproducible, and industry-recognized. Open suites like ClickBench (maintained by ClickHouse and independently reproducible) and TPC-H provide verifiable data points for comparing sub-second latency and hardware efficiency across engines.

Which real-time analytics database should you choose? A workload-based decision tree

If you're processing massive log, event, or telemetry ingestion at petabyte scale and need versatile, general-purpose ad-hoc analytics with the lowest infrastructure cost, choose ClickHouse.
If you're building public-facing, ultra-low-latency applications with high concurrency, choose ClickHouse, which handles both ad-hoc queries and predefined paths efficiently. Apache Pinot is a specialized alternative if you strictly need to serve pre-defined query paths via star-tree indexes.
If your primary focus is operational time-series monitoring, network telemetry, or dashboards, choose ClickHouse, which handles massive telemetry ingestion while supporting both raw ad-hoc queries and aggregations. Apache Druid is an alternative if your workload perfectly aligns with rigid ingestion-time roll-up aggregations.
If you must unify high-throughput operational transactions (writes) and real-time analytics (reads) without building a custom CDC pipeline, choose ClickHouse with its managed Postgres offering and native sync to ClickHouse, which pairs a purpose-built OLTP engine (Postgres) with a purpose-built OLAP engine (ClickHouse). SingleStore is an alternative if you prefer a single HTAP engine for both.
If you want to expose fast data APIs directly to frontend developers without managing database infrastructure, query optimization, or cluster scaling, choose a managed runtime like ClickHouse Cloud or StarTree (on Pinot).

Conclusion: choosing the best real-time analytics database for your workload

Pinpoint your absolute primary constraint, whether that's ingest throughput, concurrency limits, or total cost of ownership, before committing to an architecture.

For the vast majority of real-time analytical workloads, ClickHouse offers the most versatile, high-performance foundation. Widely evaluated as the fastest analytics database for raw throughput and query execution at scale, it delivers unmatched query speed and storage compression.

If you're evaluating real-time OLAP and want to eliminate the operational overhead of cluster management, spin up a ClickHouse Cloud free trial, load as much of your own data as possible, run an evaluation at a realistic scale, and compare against your existing system. StarTree (on Pinot) is another managed runtime option for teams that do not want to operate clusters.

Real-time analytics database FAQs

What does "real-time analytics" mean in this guide?

Sub-second p95/p99 query latency under high concurrency, with data freshness measured in milliseconds to seconds. Not minutes.

Which real-time analytics database should I choose in 2026?

Choose based on workload: ClickHouse for general-purpose real-time OLAP, best price-performance, user-facing apps, and time-series operational dashboards/observability. For unified OLTP + real-time analytics, pair ClickHouse with its managed Postgres offering and native sync to ClickHouse, which gives you both engines without a custom CDC pipeline. Apache Pinot is a specialized alternative if you strictly need predefined query paths via star-tree indexes. Apache Druid suits workloads aligned to rigid ingestion-time roll-up aggregations. SingleStore is an alternative for HTAP teams preferring a single engine.

Can Snowflake or BigQuery support real-time dashboards?

They can support near-real-time BI, but they're typically a poor fit for sub-second, high-concurrency user-facing analytics because of latency variability and cost under frequent polling.

Do I need a streaming system (Kafka/Redpanda) to do real-time analytics?

Often yes for event ingestion and freshness. But the database still needs to serve fast ad-hoc queries. Streaming systems and real-time OLAP engines are complementary.

How should I benchmark real-time analytics databases?

Use reproducible, open benchmarks (e.g., ClickBench and TPC-H where applicable) and measure p95/p99 latency under concurrency, ingest freshness, and cost at your target data volume. Ensure you test beyond your own expected volume to account for bursts and future growth.

What's the biggest operational difference between ClickHouse, Pinot, and Druid?

ClickHouse can run as a simpler single-binary cluster (or managed cloud), while Pinot and Druid typically require more moving parts (multiple node roles plus ZooKeeper and external metadata/deep storage), increasing operational overhead.

How do these databases handle updates, deletes, and CDC?

Support for mutable data varies widely. ClickHouse natively supports standard SQL UPDATE and DELETE operations via lightweight patch parts and background deduplication for high-volume CDC, whereas systems like Druid remain primarily append-only. HTAP systems like SingleStore support full transactional UPDATE/DELETE semantics.

Can ClickHouse DELETE Data? A 2026 PR-by-PR Analysis

Manveer Chawla — Tue, 19 May 2026 21:33:41 +0000

TL;DR

ClickHouse has supported DELETE operations since 2018. As of 2026, it ships four production-grade deletion paths: heavyweight ALTER TABLE DELETE, lightweight DELETE FROM (default since v23.3), patch-part DELETEs (v25.7), and ALTER TABLE DROP PARTITION for bulk operations. The "ClickHouse is immutable / append-only" narrative is outdated by eight years and 80+ merged PRs spanning five architectural eras, and the evidence is in the commit history.

We analyzed 80+ GitHub pull requests, official ClickHouse changelogs, and release blogs to trace the full evolution of DELETE support from 2018 through early 2026.
In 2018, ClickHouse shipped ALTER TABLE … DELETE as a heavyweight asynchronous mutation that rewrote affected data parts. The criticism that "deletes require heavy mutations" was fair — for that era. It was also the only delete path for four years.
By early 2026, ClickHouse ships standard SQL DELETE FROM (lightweight by default since v23.3), ALTER TABLE DELETE for guaranteed physical removal, ALTER TABLE DROP PARTITION for bulk deletion, patch-part-based lightweight updates and deletes, on-the-fly mutation visibility at SELECT time, and engine-level deletion patterns through ReplacingMergeTree(version, is_deleted) with optimized FINAL. None of these require experimental flags.
The single highest-impact change is the lightweight DELETE introduction (PR #37893), which redefined DELETE on MergeTree from "rewrite all affected parts" to "rewrite only _row_exists, hardlink the rest, filter on read." Benchmarks in the PR show 15 single-row deletes on a 100M-row, 12-column table dropping from ~8 seconds to ~200 ms — roughly 40× faster on the initial mask write.
Patch parts (PR #82004), shipped in v25.7, eliminated the part rewrite entirely. A DELETE becomes a tiny insert that sets _row_exists = 0, applied on read until a background merge consolidates it. ClickHouse's own benchmark blog series claims up to ~1,000× speedup for small/selective changes versus classic mutations (vendor benchmark — treat as an upper bound, not a guarantee).
On-the-fly mutations (PR #74877) and on-the-fly LWD (PR #79281) eliminated the "DELETE was issued but rows still appear" surprise. Queued deletes that haven't materialized are now applied at SELECT time.
The allow_experimental_lightweight_delete setting hasn't been needed since 23.3. It was aliased to enable_lightweight_delete in PR #50044 (commit 7189481, June 2023) for backward compatibility, then promoted to default-enabled.
Verdict: the "ClickHouse is immutable" advice made sense in 2017. Repeating it in 2026 is misinformation. ClickHouse offers four production-grade deletion paths covering compliance, bulk, selective, and high-frequency operational workloads, each with explicit trade-offs and observability.

Why People Still Say "ClickHouse Can't Delete"

If you've evaluated ClickHouse in the last few years, you've heard the warnings:

"ClickHouse can't delete data"
"ClickHouse is immutable / append-only"
"Deletes require heavy mutations"
"ReplacingMergeTree can't handle deletes"
"You need allow_experimental_lightweight_delete"
"FINAL is too slow for production queries"

Some of these started as legitimate ClickHouse guidance in the 2018–2020 era. The original ALTER TABLE … DELETE was deliberately syntactically heavyweight: ClickHouse was founded on the principle that "logs are immutable," and the ALTER syntax (rather than DELETE FROM) was an explicit signal that this was an administrative operation, not OLTP-style row modification. The cost model was honest: for a 100-column table, deleting a single row required reading and rewriting all 100 column files for the affected part.

In 2018, the criticism was largely fair. There was one delete path (ALTER TABLE DELETE), it was a full part rewrite, it was asynchronous, and you tracked it through system.mutations. If you needed selective row-level deletion at scale, you were going to feel it.

Then ClickHouse's engineering team spent eight years dismantling every one of those limitations. Over 80 significant pull requests merged. They added DELETE FROM syntax with a hidden-mask implementation (PR #37893), promoted it to GA (v23.3), made it synchronous by default (PR #44718), added IN PARTITION scoping (PR #67805), made it observable (apply_deleted_mask, has_lightweight_delete, parts_postpone_reasons), made it correct in the presence of projections and skip indexes (PRs #52517, #52530, #62364, #65594), and finally re-implemented it as a tiny patch-part write (PR #82004) so the part rewrite is gone entirely.

This article traces that evolution with PR-level evidence. No marketing claims. No benchmarks on toy datasets. Just the commit history.

Methodology: How We Analyzed ClickHouse's DELETE Commit History

We went through ClickHouse's GitHub commit history, pull requests, changelogs, and release blogs from 2018 through early 2026. The scope covered every PR that touched the DELETE subsystem: mutation engine changes, the lightweight-delete read path, patch parts, projection and skip-index correctness, replication and ON CLUSTER propagation, observability, and default configuration changes.

Each PR was classified by category (mutation engine, read-path filtering, storage interaction, correctness, settings, observability), impact severity, and whether it changed default behavior. We cross-referenced PR descriptions against changelog entries and release blog benchmarks to verify the claimed improvements. Where multiple PRs addressed the same subsystem (for example, the long tail of LWD-vs-projection bugs), we traced the dependency chain to understand how the incremental fixes compounded.

The result is a ranked set of PRs by impact, organized into five chronological eras, with full provenance. Every claim in this article maps to a specific merged PR or commit SHA that you can verify yourself on GitHub. Where two reputable sources cite different SHAs for the same PR (a known issue with squash-merges in older PR threads), we surface the conflict rather than picking one.

This isn't a benchmarking exercise. Benchmarks measure peak performance on controlled workloads. This analysis measures the engineering trajectory: what was built, why, and what it means for teams deciding whether ClickHouse can handle their delete patterns today.

ClickHouse DELETE Features in 2026: What Ships by Default

The current state, as of early 2026:

Standard SQL DELETE FROM: Lightweight by default since v23.3. Hidden _row_exists mask column, PREWHERE-injected at read time, hardlinks unaffected column files in wide parts. Synchronous by default with explicit lightweight_deletes_sync control.
ALTER TABLE … DELETE (heavyweight mutation): Retained for use cases that require guaranteed physical removal at completion (compliance, GDPR right-to-erasure, audit-bound workloads). Tracked in system.mutations, cancellable via KILL MUTATION.
ALTER TABLE DELETE … IN PARTITION: Both for heavyweight mutations (PR #13403, 2020) and lightweight DELETE (PR #67805, 2024). Prunes partitions before the delete plan even starts.
ALTER TABLE DROP PARTITION: Bulk deletion via durable empty parts (PR #41145), atomic and non-blocking for concurrent reads. The most efficient path for time-bounded data lifecycle operations.
Patch-part lightweight updates and DELETEs: Available since v25.7 via lightweight_delete_mode = 'lightweight_update'. A DELETE becomes a tiny insert of a patch part instead of a part rewrite.
On-the-fly mutation visibility: apply_mutations_on_fly = 1 makes queued deletes visible at SELECT time before background materialization completes.
ReplacingMergeTree(version, is_deleted): Engine-native upsert and tombstone semantics with OPTIMIZE TABLE … FINAL CLEANUP for forced physical removal. Combined with the optimized FINAL keyword for immediate consistency at query time.
Explicit physical-removal control: ALTER TABLE … APPLY DELETED MASK (PR #57433) forces materialization without waiting for background merges. min_age_to_force_merge_seconds and exclude_deleted_rows_for_part_size_in_merge give the merge selector the right inputs.
Observability: system.parts.has_lightweight_delete, removal_state, last_removal_attempt_time, and system.mutations.parts_postpone_reasons give operators machine-readable state for every delete in flight.

These aren't experimental features hidden behind flags. They're defaults that ship with every ClickHouse installation.

ClickHouse DELETE Myths vs. Reality: A 2026 Checklist

#	The FUD	Score	Evidence Volume	Reality (2026)
1	"ClickHouse can't delete data"	🟢 False since 2018	80+ PRs across 5 eras	Four production-grade delete paths: heavyweight mutation, lightweight DELETE, patch-part DELETE, partition-scoped DELETE.
2	"ClickHouse is immutable / append-only"	🟢 Outdated	Mutations since 2018 (release `1.1.54388`); LWD since 22.8	Standard SQL DELETE FROM has been default-enabled since v23.3 (April 2023).
3	"Deletes require heavy mutations"	🟢 False since 22.8	PR #37893, #82004	LWD is ~40× faster than heavy mutations on the initial mask write. Patch-part DELETEs target up to ~1,000× speedup for small/selective changes per ClickHouse benchmarks.
4	"ReplacingMergeTree can't handle deletes"	🟢 False	`is_deleted` column parameter	`ReplacingMergeTree(version, is_deleted)` natively supports tombstones. `OPTIMIZE TABLE … FINAL CLEANUP` forces physical removal.
5	"You need `allow_experimental_lightweight_delete`"	🟢 Obsolete	PR #50044 (commit `7189481`, June 2023)	The setting was renamed to `enable_lightweight_delete` and default-enabled in v23.3. The old name remains as a backward-compatibility alias.
6	"`FINAL` is too slow for production queries"	🟡 Outdated	Multiple optimization PRs through v25.x	`FINAL` was significantly optimized for production. It's the recommended path for immediate consistency on `ReplacingMergeTree` regardless of background merge state.
7	"DELETE crashes the mutation queue"	🟢 False since 2023	PR #48522, #44718	Memory usage reduced for large mutation queues. LWD synchronous by default to bound queue growth.
8	"Deleted rows linger forever in storage"	🟢 False	PR #58223, #57433	Merge selector counts existing rows, not physical rows. `APPLY DELETED MASK` forces immediate physical cleanup on demand.
9	"Can't delete in a specific partition without scanning everything"	🟢 False since 2024	PR #67805, #13403	`DELETE FROM … IN PARTITION` and `ALTER … DELETE … IN PARTITION` prune partitions at plan time.
10	"DELETEs are invisible until merges finish"	🟢 False since 2025	PR #74877, #79281	`apply_mutations_on_fly` makes queued deletes visible at SELECT time. LWDs apply on the fly via the same mechanism.
11	"DELETE breaks projections and skip indexes"	🟢 False since 2024	PR #52517, #52530, #62364, #65594	Skip indexes and projections recalculate correctly during delete-driven merges. `lightweight_mutation_projection_mode` gives explicit policy options.
12	"No way to observe in-flight deletes"	🟢 False	`system.mutations`, `system.parts.has_lightweight_delete`, `parts_postpone_reasons`	Full lifecycle observability: queue state, postpone reasons, masked-part flagging, removal attempt timing.

Phase 1 (2018): The Original Mutation-Based DELETE

The FUD: "ClickHouse can't delete data"

This one was true for the first few years of ClickHouse's life. ClickHouse was designed around the principle that analytical data, once written, should not be modified. Compression and read throughput took priority over update flexibility. The first delete capability landed in mid-2018 as a deliberate compromise: support deletion, but signal architecturally that this was a heavyweight administrative operation.

`ALTER TABLE … DELETE` Lands as a Mutation (June–July 2018)

ClickHouse release 1.1.54388 (2018-06-28) added replicated ALTER TABLE t DELETE WHERE support together with the system.mutations table. Release 18.1.0 (2018-07-23) extended this to non-replicated MergeTree via PR #2634.

The mechanism was straightforward and expensive. When ALTER TABLE DELETE was issued, the server recorded the mutation with a unique ID (mutation_1.txt), returned immediately, and a background process scanned for parts containing rows matching the filter. For each affected part, a new version was created by reading the original data, applying the filter in memory, and writing only the surviving rows into a new part directory. The old part remained active until the new part was fully written and verified.

The write amplification was severe. For a 100-column table, deleting a single row required reading and rewriting all 100 column files for the affected part. The cost scaled with column count, not with the number of deleted rows. This is the math behind the "deletes are expensive" guidance from this era — and it was true.

Skip Unaffected Parts in DELETE Mutations (PR #2694, Late 2018)

PR #2694 added the first explicit rewrite-amplification optimization for DELETE: ALTER TABLE t DELETE WHERE no longer rewrote data parts that the predicate didn't touch. Before this PR, the mutation engine was conservative; after it, parts with no matching rows were skipped entirely. This established the pattern that would define the next eight years of DELETE evolution: do less work, lazily, and only on parts that actually need it.

DELETE Correctness Fixes (2020–2021)

The mutation-based DELETE generated a steady stream of correctness fixes:

PR #9048 (alesapin, 2020) — fixed primary.idx corruption after a delete mutation, the most severe class of DELETE bug.
PR #12153 (alexey-milovidov, 2020) — fixed over-deletion when the predicate evaluated to NULL on a row.
PR #21477 (alesapin, 2021) — fixed a deadlock for non-replicated MergeTree when ALTER DELETE WHERE referenced the same table.
PR #13403 (Vladimir Chebotarev, 2020) — added ALTER TABLE … DELETE … IN PARTITION for partition pruning, addressing metadata and ZooKeeper bloat in tables with thousands of partitions.

By the end of this era, ClickHouse had a working DELETE. It was honest about the cost. The "ClickHouse is immutable" critique was already inaccurate by 2018, but it was understandable.

Phase 2 (2022): How Lightweight DELETE Reframed Everything

The FUD: "Deletes require heavy mutations"

PR #37893, authored by Jianmei Zhang (zhangjmruc) and reviewed by davenger and alesapin, is the single highest-impact change in the entire DELETE history. Merged into v22.8 in mid-2022, it introduced standard SQL DELETE FROM <table> WHERE … and re-implemented it as a special mutation: ALTER TABLE <table> UPDATE _row_exists = 0 WHERE ….

The architectural shift was complete. DELETE went from "rewrite all affected parts" to "rewrite only the _row_exists mask, hardlink the rest, filter on read."

The `_row_exists` Mask Column

Each MergeTree part gained a virtual system column called _row_exists. When a row was deleted, its bit in this column was flipped from 1 to 0. The data itself remained on disk — only the mask was updated.

For wide-format MergeTree parts (the default for parts above a threshold size), where each column is stored in its own .bin and .mrk files, the optimization is dramatic. ClickHouse only writes a new _row_exists.bin; all other column files are hardlinked from the old part to the new one. For compact-format parts, where all columns are interleaved in one file, the gain is smaller because the single file still has to be rewritten.

PREWHERE Injection on Read

Reading from a table with deleted rows is where the design pays off. A query like:

SELECT count() FROM users WHERE age > 25

is internally transformed into:

SELECT count() FROM users PREWHERE _row_exists WHERE age > 25

PREWHERE runs before the main column reads. If _row_exists indicates an entire granule is deleted, ClickHouse skips reading any other column data for that granule. The mask is tiny (one bit per row, highly compressible), so the read overhead is negligible compared to the savings on filtered-out granules.

Hardlink Optimization for Wide Parts

The PR description includes a benchmark on a 100-million-row, 12-column table. Fifteen single-row lightweight DELETEs took roughly 200 ms. The same operation as a heavyweight mutation took roughly 8 seconds. That's a ~40× speedup on the initial mask write — and the gap widens as column count increases.

`IStorage::supportsDelete()`: Architectural Formalization (December 2022)

Commit 938aac9 (2022-12-29, davenger) added IStorage::supportsDelete(), formalizing the architectural separation between engines that support DELETE FROM and those that don't. This wasn't a feature in itself, but it was the contract that the rest of the system would build on.

Lightweight DELETE Correctness Fixes (Late 2022)

Lightweight DELETE introduced a new class of correctness bugs that had to be hunted down:

PR #40559 (davenger, August 2022) — fixed vertical merge of parts with lightweight-deleted rows. First post-introduction LWD bugfix; backported to 22.8.
PR #42126 (davenger, October 2022) — fixed "Invalid number of rows in Chunk" errors. Required a substantive PREWHERE refactor to support multiple PREWHERE steps so the _row_exists filter could be the first step in the chain.

By the end of 2022, the criticism had shifted. "Deletes require heavy mutations" had a documented expiration date in the changelog.

Phase 3 (2023): Lightweight DELETE Goes GA

The FUD: "You need allow_experimental_lightweight_delete"

Lightweight DELETE was promoted to GA / on-by-default in v23.3, announced in the v23.3 release blog and the April 2023 newsletter. The GA work is credited to Jianmei Zhang and Alexander Gololobov.

Synchronous by Default (PR #44718, December 2022 / January 2023)

PR #44718 made DELETE FROM synchronous by default so the command would not return until the rows were masked and invisible to subsequent queries. This bounded the mutation queue and prevented accumulating piles of pending LWD mutations. The original async behavior was later partially restored as a setting (lightweight_deletes_sync) for users on remote storage where the per-LWD coordination cost is high.

Memory and Concurrency Hardening (PR #48522, April 2023)

PR #48522 (KochetovNicolai) directly targeted "Reduce memory usage for multiple ALTER DELETE mutations" — the canonical reference for fixing OOM scenarios on large mutation queues. Related issue #57411 documented servers being killed by OOM while loading large numbers of mutation_*.txt files on startup. This PR shrunk the per-mutation memory footprint enough that the queue stopped being an operational hazard.

Lightweight DELETE on JSON and Object Columns (PR #49737, May 2023)

PR #49737 (davenger) stopped the MutatePlainMergeTreeTask log loop "There is no physical column _row_exists in table" when the table had an Object or JSON column. Fixed issues #49509 and #55076.

From `allow_experimental_lightweight_delete` to `enable_lightweight_delete` (PR #50044, June 2023)

PR #50044 (Azat Khuzhin, commit 7189481, 2023-06-04) aliased allow_experimental_lightweight_delete to enable_lightweight_delete. This was the bridge for users coming from older releases: their existing settings continued to work, but the canonical name reflected the feature's promotion out of experimental status.

This is the precise moment where "you need allow_experimental_lightweight_delete" became misinformation. The setting wasn't removed (backward compatibility matters), but it stopped being required, and the canonical documentation moved to the new name.

Projection Compatibility (PRs #52517 and #52530, July–August 2023)

PR #52517 (Anton Popov / CurtizJ, July 2023) — fixed lightweight DELETE failing after a projection was dropped. Even after the projection was gone, stale metadata could poison later LWD execution. Backported to 22.8 and 23.3.

PR #52530 (CurtizJ, August 2023, commit b6ce725) — fixed recalculation of skip indexes (bloom_filter, minmax, ngrambf, etc.) and projections in ALTER DELETE queries. Both needed to be recalculated, not stale-copied. Backported to 22.8, 23.3, 23.5, 23.7.

`apply_deleted_mask` and `APPLY DELETED MASK`: Operator Levers (PRs #55952 and #57433, late 2023)

PR #55952 (davenger, October 2023, commit 40062ca) added the apply_deleted_mask setting. With apply_deleted_mask = 0, SELECTs return rows that LWD has masked, which is essential for forensics, audits, and compliance verification — confirming that a delete actually happened, that the right rows were marked, and that the data is still recoverable until physical cleanup.

PR #57433 (CurtizJ, December 2023, commit 87d0cec) added ALTER TABLE … APPLY DELETED MASK [IN PARTITION …]. This is the explicit "stop waiting for merges, physically remove these rows now" lever. Implemented as an ordinary mutation command, it's the clean answer to compliance workloads that need guaranteed cleanup on demand.

By the end of 2023, lightweight DELETE was production-ready. The umbrella tracking issue (#56728) for production-readiness work was being closed. Operators had observability, force-cleanup levers, and synchronous semantics by default.

Phase 4 (2023–2024): Storage-Aware DELETE Optimizations

The FUD: "Deleted rows linger forever in storage"

The early lightweight DELETE design had a known operational gap. Because LWD only writes the mask, the underlying part still contains the deleted rows. They're filtered out at read time, but they consume disk space until the next merge. And the merge selector — which decides which parts to merge next — was counting physical rows, not existing rows. That meant a large part dominated by lightweight-deleted rows could sit at near max_bytes_to_merge_at_max_space_in_pool indefinitely, never picked up for merging, never cleaned.

Phase 4 fixed this and added the merge-selector and physical-cleanup machinery that makes LWD usable at scale.

`exclude_deleted_rows_for_part_size_in_merge`: Merge Selection That Counts Existing Rows (PR #58223, early 2024)

PR #58223 (jewelzqiu) added existing_rows_count to data parts and taught the merge selector to use it. The MergeTree settings exclude_deleted_rows_for_part_size_in_merge and load_existing_rows_count_for_old_parts give operators control over the trade-off.

The operational result: a 50 GB part where 90% of the rows are lightweight-deleted is now treated as a 5 GB part for merge selection. It gets picked up, merged, and the deleted rows physically disappear.

`lightweight_deletes_sync` (PR #62195, April 2024)

PR #62195 (CurtizJ, 2024-04-03) introduced the dedicated lightweight_deletes_sync setting (default value 2: "wait all replicas synchronously"), separating LWD synchronicity from the generic mutations_sync. This gave users on S3-backed deployments — where the per-LWD coordination cost is high — a way to lower the wait without weakening async semantics for heavy mutations. References to commit SHAs vary across sources (534905f and ed448ea both appear in PR #62195's commit set).

Projection Rebuild on Row-Reducing Merges (PR #62364, Q2 2024)

PR #62364 (cangyin / ardenwick) added projection rebuild for merges that reduce row count. Some merging modes (Replacing, Collapsing, deletes) genuinely reduce rows, and projections need to be rebuilt to avoid silently retaining "deleted" rows in projection data — a subtle correctness bug that could cause queries hitting the projection to return different results than queries hitting the base table.

`lightweight_mutation_projection_mode`: Lightweight DELETE on Tables with Projections (PR #65594, July 2024)

PR #65594 (jsc0218 / ShiChao Jin, 2024-07-04, commit 556c7de) added the lightweight_mutation_projection_mode table-level setting with three values: throw (default), drop, and rebuild. Without this setting, lightweight DELETE on a table with a projection unconditionally errored. Now you have an explicit policy: throw safely, drop the projection, or rebuild it as part of the merge.

`DELETE FROM … IN PARTITION` for Lightweight DELETE (PR #67805, August 2024)

PR #67805 (sunny19930321, 2024-08-30, commit 950ca28) added DELETE FROM … IN PARTITION 'xy' WHERE …. This means the planner doesn't have to scan all partitions when you know the delete is partition-bounded. Resolves issues #59409 and #60218. The ON CLUSTER form is also supported: DELETE FROM [db.]table [ON CLUSTER cluster] [IN PARTITION partition_expr] WHERE expr;.

`system.parts.has_lightweight_delete` and `system.mutations` Schema Additions (2024)

system.parts gained columns to flag and track lightweight-deleted parts:

has_lightweight_delete — true if the part has any rows masked by LWD
removal_state — current state of the part's removal lifecycle
last_removal_attempt_time — timestamp of the most recent attempt to remove the part

Combined with system.mutations.parts_postpone_reasons (commit 8903fd1) and parts_in_progress_names, operators have machine-readable answers for "why is this delete stuck" without grepping logs.

By the end of 2024, the storage layer understood lightweight DELETE end-to-end. Deleted rows didn't linger; the merge selector picked the right parts; projections and skip indexes were consistent; and operators had the levers and observability to manage it all.

Phase 5 (2025–2026): Patch Parts and On-the-Fly Mutations

The FUD: "DELETEs are invisible until merges finish"

The lightweight DELETE design from PR #37893 still required something to be written to disk for each delete — at minimum, a new version of _row_exists.bin for the affected part. For workloads with many small, frequent deletes, that per-DELETE write was the dominant cost. Phase 5 attacked it.

`apply_mutations_on_fly`: On-the-Fly Mutations (PR #74877, Q1 2025)

PR #74877 (CurtizJ) introduced apply_mutations_on_fly. Queued ALTER UPDATE and ALTER DELETE mutations that have not yet materialized are now applied at SELECT time, so users immediately see updated and deleted state. This closed the long-standing "DELETE was issued but rows still appear when mutations_sync = 0" surprise. Heavy mutations still materialize asynchronously in the background, but they're no longer invisible to queries in the meantime.

The mechanism has limits: only scalar subqueries up to mutations_max_literal_size_to_replace, only constant non-deterministic functions (controlled by mutations_execute_nondeterministic_on_initiator and mutations_execute_subqueries_on_initiator). Within those limits, the read path applies the mutation transform on the fly.

On-the-Fly Lightweight DELETE (PR #79281, April 2025)

PR #79281 (CurtizJ, commit dc9f636) extended on-the-fly mutations to lightweight DELETE specifically. Now DELETE FROM … SETTINGS lightweight_deletes_sync = 0 becomes visible immediately when apply_mutations_on_fly = 1. Resolves issue #75180.

Patch Parts: DELETEs Without Part Rewrites (PR #82004, July 2025)

PR #82004 (CurtizJ / Anton Popov), merged into v25.7, is the second-most-important PR in this entire history. It added standard SQL UPDATE syntax via patch parts and re-implemented lightweight DELETE on top of the same mechanism when lightweight_delete_mode = 'lightweight_update'.

The new shape: a DELETE creates a tiny patch part that sets _row_exists = 0 for affected rows. The patch is applied on read and physically merged in the next background merge. There's no rewrite of the source part. There's no hardlinking ceremony. The DELETE is, essentially, an insert.

Mechanically:

Patch parts are sorted by _part, _part_offset.
Partition ID is patch-<hash of column names>-<original_partition_id>.
Merging of patch parts uses a ReplacingMergeTree-style algorithm with _data_version as version.
Two read-time application modes: merge by sorted system columns when the source part is unchanged, join when the source part has been re-merged.
update_sequential_consistency and update_parallel_mode control behavior under concurrent updates.

The benchmarks in ClickHouse's own three-part series claim up to ~1,000× faster for small/selective changes versus classic mutations. Vendor benchmarks; treat as upper bounds, not guarantees, but the mechanism explains the size of the gap. For larger deletes (>~10% of a table), classic mutation is still preferred — the patch-on-read overhead grows with patch size.

2026 Correctness and Operability Hardening

The first half of 2026 has been a wave of LWD-related correctness and operability fixes:

PR #101212 (Anton Popov / CurtizJ, 2026-04-21, commit 509d35a) — "Fix several optimizations after lightweight deletes [2]." Critical fix: query optimizations like trivial COUNT(*) and minmax_count_projection were permanently disabled after a lightweight DELETE, even after all masked parts had been merged away. Replaced a sticky global flag with a per-snapshot computation: mutations_snapshot->hasLightweightDeletedMask(). This prevents permanent performance degradation on tables that ever ran an LWD.
PR #97589 (Alexey Milovidov, 2026-02-28, commit 53a75e8) — Fix KILL QUERY for ALTER DELETE with mutations_sync=1 on ReplicatedMergeTree. Synchronous replicated ALTER DELETE could become effectively unkillable. The fix is in mutation execution and control flow rather than DELETE semantics, but it materially improves operability under stalls.
PR #99281 (Yash, 2026) — Fix ALTER TABLE UPDATE/DELETE failing with "Missing columns" when a MATERIALIZED column depends on an EPHEMERAL column. Computed-column dependency analysis was wrong; the statement could fail before mutation execution.
Commit 9c4dda6 (2026-04-06) — Fix usage of text index with lightweight deletes. High-severity correctness fix for incorrect query results when both features were used together.
Commit 1acc6f3 — Fix for stuck mutations caused by phantom entries (race condition causing DELETE mutations to become stuck indefinitely).
PR #101792 — Broad LWD stateless test coverage. Not a feature landing, but a signal: LWD's hidden-row lifecycle and read-path semantics are now important enough to encode in dedicated stateless test suites.

This long correctness tail isn't a bad sign — it's how all mature deletion paths look once they're in production at scale. Compare to PostgreSQL's history of vacuum/freeze edge cases, or InnoDB's purge interactions. The volume of LWD fixes in 2026 reflects the volume of LWD usage.

Bulk Deletion: `DROP PARTITION` and Empty-Part Tombstones

The FUD: "Bulk deletion in ClickHouse is unsafe / non-atomic"

For bulk data lifecycle operations — removing a day's worth of data, dropping all rows for a deleted customer, reloading after a bad ETL run — the right answer is rarely DELETE FROM. It's ALTER TABLE … DROP PARTITION.

PR #41145 (Sema Checherinda, 2022) made these destructive partition operations durable. Before this PR, TRUNCATE TABLE, ALTER TABLE DROP PART, and ALTER TABLE DROP PARTITION worked by removing the part metadata and unlinking the files on disk. In a distributed system coordinated by ZooKeeper, a replica that was offline during the deletion or a crash between unlink and ZooKeeper update could leave the system in a state where the replica later attempted to "recover" the deleted part from another node. The result: "resurrected parts" — data that was supposedly deleted reappearing after a server restart or replica re-initialization.

The fix is elegant. Instead of immediate removal, these queries now create empty parts that explicitly cover the range of the old parts. Empty parts act as tombstones within the part set, so even if an old part is found on disk or on another replica, the system knows it has been superseded.

The achievements:

Durability: if the request succeeds, the empty part is committed to disk and ZooKeeper, preventing resurrection.
Atomicity: the substitution of old parts with empty ones is a single atomic operation within MergeTree's transaction scope.
Non-blocking reads: the operation no longer requires a follow-up exclusive lock to clean up filesystem entries, so concurrent reads aren't blocked.

For workloads where data has a clean partition boundary (date, customer, region), DROP PARTITION is the most efficient deletion path in ClickHouse, full stop. It's effectively constant-time in the data volume.

`ReplacingMergeTree`, `is_deleted`, and the Optimized `FINAL`

The FUD: "ReplacingMergeTree can't handle deletes" / "FINAL is too slow"

For workloads that look more like upserts — frequent updates to the same primary key, with occasional deletions — engine-level deletion through ReplacingMergeTree is often the right architecture. ClickHouse supports an is_deleted column parameter on ReplacingMergeTree, which is the canonical "tombstone" pattern.

The mechanics:

ReplacingMergeTree keeps only the most recent version of a row with a given primary key (using a version column).
Adding is_deleted as a parameter tells the engine to treat rows where is_deleted = 1 as tombstones during merges.
To delete a row, insert a new record with the same primary key, the latest version, and is_deleted = 1.
During a merge, ClickHouse keeps the record with the highest version; if that record has is_deleted = 1, the row is dropped entirely.

The allow_experimental_cleanup_merges setting allows OPTIMIZE TABLE … FINAL CLEANUP, which forces the engine to physically remove rows where the latest version is marked as deleted. This is a declarative, high-throughput way to manage deletions without going through the mutation engine at all.

For query-time consistency, SELECT … FINAL ensures deleted rows are excluded regardless of background merge state. The historical critique of FINAL — that it was prohibitively slow for production queries — has been largely addressed through extensive optimization work. FINAL now runs efficiently enough to be the recommended path for immediate consistency on ReplacingMergeTree tables, especially when paired with appropriate primary key design.

The pattern in production: write inserts and tombstones at full ingest speed, run analytical queries with FINAL for consistency, and let merges (or OPTIMIZE … FINAL CLEANUP on demand) handle physical cleanup in the background.

ClickHouse DELETE Internals: Low-Level Optimizations and Correctness Hardening

Beyond the headline features, the DELETE subsystem received systematic low-level optimization that compounds across every delete operation:

PREWHERE multi-step refactor (PR #42126): MergeTree reader now supports multiple PREWHERE steps so the _row_exists filter can be the first step in the chain, both for correctness and for performance — read the tiny mask first, then large columns only for surviving rows.
I/O pool and asynchronous reads (PR #43260): max_streams_for_merge_tree_reading and allow_asynchronous_read_from_io_pool_for_merge_tree allow a dedicated I/O pool for reading MergeTree parts during queries and mutations. Up to 100× speedup for mutation reads on high-latency storage like Amazon S3, per the 2022 changelog.
MemoryTracker for background tasks (PR #48787, novikd): Memory tracking and soft limits for background tasks, including DELETE mutations.
mutations_execute_subqueries_on_initiator / mutations_execute_nondeterministic_on_initiator (settings, 23.x): Address one of the historically thorniest classes of replicated-DELETE bugs — divergent results across replicas when the predicate contains now(), scalar subqueries, or IN (subquery) (issues #18118, #19315, #23734, #16532).
_row_exists user-collision fix (PR #41763): Early correctness proof point — handles the case where a user defines a column named _row_exists themselves, preventing segfaults and wrong results.
min_age_to_force_merge_seconds: MergeTree setting that lets operators force older parts to merge regardless of size, reclaiming space from lightweight-deleted rows on a predictable schedule.
Lock contention reduction in BackgroundSchedulePool: On high-core-count CPUs (240+ threads), internal mutex contention in the background schedule pool was a latency source. CPU cycles spent in native_queued_spin_lock_slowpath were reduced significantly through critical-section shrinking and thread-local timer_id storage. This improves the scalability of the mutation engine on massive servers, ensuring background deletions don't interfere with interactive query performance.

None of these individually make a press release. Together, they compound into a materially faster and more reliable DELETE engine at every level of the stack.

ClickHouse DELETE Limitations and Trade-offs in 2026

Fairness matters. A few things still require awareness:

MutationsInterpreter still routes through the old analyzer. Issue #61563 tracks the migration of MutationsInterpreter to the new query analyzer; PR #61528 is the partial work. Most user-facing DELETE behavior is unaffected, but some advanced predicate forms behave differently than equivalent SELECTs. This is the only feature area where the answer is "partial / not done."
Patch-on-read overhead grows with patch size. Patch-part DELETEs (PR #82004) are dramatically faster than classic mutations for small, selective changes. For deletes affecting more than ~10% of a table, classic mutation is still preferred. The optimizer doesn't auto-select between them; the user picks via lightweight_delete_mode.
Compact parts gain less from LWD's hardlink optimization. In compact parts (the format used for small parts), all columns are interleaved in a single file. The "rewrite only _row_exists, hardlink the rest" optimization can't apply, so LWD on compact parts still rewrites the file. The wider your parts and the more columns, the bigger the LWD win.
Lightweight DELETE on remote storage has surprising latency. Real-world reports (issues #58281, #59225, #67048) document that LWD on S3-backed deployments can be slow even when no rows match the predicate, due to the synchronous coordination cost. lightweight_deletes_sync and patch parts (#82004) are the architectural answers; users on older versions should expect older behavior.
DROP PARTITION is constant-time only if your partitioning key is right. If your data isn't partitioned along the dimension you want to delete by, DROP PARTITION doesn't help. Partition design is a one-shot decision that defines what bulk deletion looks like.
Correctness fixes are ongoing. The 2026 wave (PR #101212, #97589, #99281, 9c4dda6, 1acc6f3) shows that LWD's interactions with read-path optimizations, replication, and other indexes still produce edge cases. ClickHouse's engineering team has been rigorous about correctness, but running the latest stable release matters.

These are real engineering trade-offs, and understanding them is part of making an informed decision.

ClickHouse DELETE Improvements Timeline (2018–2026)

Year	What Changed	Key PRs	Impact
2018	`ALTER TABLE … DELETE` lands as mutation. Replicated and non-replicated MergeTree. Skip-unaffected-parts optimization.	Release `1.1.54388`; #2634; #2694	DELETE is supported. Heavyweight by design. `system.mutations`, `KILL MUTATION`, `mutations_sync` established.
2020–2021	Correctness hardening on the mutation path. `IN PARTITION` for mutations.	#9048, #12153, #21477, #13403	Primary index corruption fixed, NULL-predicate over-deletion fixed, deadlocks fixed. Partition pruning for heavy mutations.
2022	Lightweight DELETE introduction. `_row_exists` mask, PREWHERE injection, hardlink unaffected columns. Empty-part tombstones for `DROP PARTITION`.	#37893, #41145, #40559, #42126	Standard SQL `DELETE FROM`. ~40× faster than heavy mutation on initial mask write. Durable bulk-deletion semantics.
2023	LWD goes GA in v23.3. Synchronous by default. Memory hardening. Projection compatibility. `apply_deleted_mask`. `APPLY DELETED MASK`.	#44718, #48522, #52517, #52530, #55952, #57433, #50044	LWD production-ready. `allow_experimental_lightweight_delete` deprecated. Operators get visibility and force-cleanup levers.
2024	Storage-aware merge selection. `lightweight_deletes_sync`. Projection policy. `DELETE FROM … IN PARTITION`. Row-reducing-merge projection rebuild.	#58223, #62195, #65594, #67805, #62364	Deleted rows physically reclaimed by merges. Replicated LWD has independent sync control. Partition-scoped LWD.
2025	On-the-fly mutations and on-the-fly LWD. Patch parts: DELETE as a tiny insert.	#74877, #79281, #82004	Queued deletes visible at SELECT time. Patch-part path targets up to ~1,000× faster than classic mutations on small/selective changes.
2026	Read-path optimization fixes after LWD. Killable replicated synchronous DELETEs. Text-index correctness. Stuck-mutation race fixes.	#101212, #97589, #99281, `9c4dda6`, `1acc6f3`	`COUNT(*)` and projection optimizations no longer permanently disabled after LWD. `KILL QUERY` works for synchronous replicated `ALTER DELETE`.

When Should You Use Each DELETE Method in ClickHouse?

Workload	Verdict	Reasoning
Bulk historical cleanup along a partition boundary	✅ `ALTER TABLE … DROP PARTITION`	Constant-time, atomic, durable via empty-part tombstones (PR #41145). The most efficient bulk path.
Reloading data after a bad ETL run	✅ `DROP PARTITION` then re-insert	Same logic — partition-bounded operations are essentially free.
Selective row-level deletion (small set of rows)	✅ `DELETE FROM … WHERE …` (lightweight)	Default since v23.3. ~40× faster than heavy mutation.
High-frequency operational deletes (many small)	✅ Patch-part DELETE (`lightweight_delete_mode = 'lightweight_update'`)	Each DELETE is a tiny insert, no part rewrite. Up to ~1,000× faster on small/selective changes per ClickHouse benchmarks.
Compliance-grade deletion (GDPR right-to-erasure)	✅ `ALTER TABLE … DELETE` (heavyweight) or `APPLY DELETED MASK` after LWD	When you need "the bytes are physically gone" on completion, mutation is the path. `APPLY DELETED MASK` (PR #57433) forces materialization of LWD-marked rows.
Frequent updates with occasional deletions (upserts)	✅ `ReplacingMergeTree(version, is_deleted)` + `FINAL`	Engine-native pattern. Tombstones at ingest speed, query-time consistency via optimized `FINAL`.
Streaming data with explicit cancellation pairs	✅ `CollapsingMergeTree` with `Sign`	`+1` / `-1` pair "collapses" on merge. Highly efficient for streams that can produce cancellation events.
Deletes scoped to a known partition	✅ `DELETE FROM … IN PARTITION`	Planner skips unaffected partitions (PR #67805).
Need to verify a delete worked / forensic audit	✅ `apply_deleted_mask = 0`	Returns rows that LWD has masked but not physically removed (PR #55952).
Need queued deletes visible immediately to queries	✅ `apply_mutations_on_fly = 1`	On-the-fly mutation visibility (PR #74877, #79281).
Deleting more than ~10% of a table	🟡 `ALTER TABLE DELETE` (heavyweight) or `DROP PARTITION`	Patch-part overhead grows with patch size; classic mutation is more efficient at this scale.
Sub-10ms p99 latency on read-heavy workload with frequent deletes	🟡 Conditional	LWD is fast but adds a PREWHERE step. `ReplacingMergeTree` + `FINAL` may be faster depending on read-pattern. Benchmark on your workload.

How to Respond to "ClickHouse Can't Delete"

Run the PR numbers.

When someone tells you ClickHouse can't delete data in 2026, ask them what release they're benchmarking against. Specifically:

If they're citing "experimental lightweight delete," they're on something pre-v23.3. The setting was renamed and default-enabled in April 2023 (PR #50044).
If they're citing "heavyweight mutations only," they're on something pre-v22.8. Standard SQL DELETE FROM has been available for nearly four years.
If they're citing "deleted rows linger forever," they're on something pre-v24.x. The merge selector has counted existing rows since PR #58223.
If they're citing "DELETEs are invisible until merges finish," they're on something pre-v25.x. On-the-fly mutations (PR #74877) and on-the-fly LWD (PR #79281) closed that gap in early 2025.
If they're citing "DELETE breaks projections," they're on something pre-v24.7. lightweight_mutation_projection_mode (PR #65594) gives explicit policy options.
If they're citing "patch parts don't exist," they're on something pre-v25.7. PR #82004 shipped them in July 2025.

If they're benchmarking against ClickHouse 21.x or earlier, or repeating 2018-era documentation, they aren't evaluating ClickHouse. They're evaluating a system that no longer exists.

The commit history doesn't lie. 80+ pull requests. Five architectural eras. Four production-grade delete paths. Engine-level deletion patterns through ReplacingMergeTree. Storage-aware merge selection. Patch parts. On-the-fly visibility. Full observability through system.mutations and system.parts.has_lightweight_delete.

ClickHouse's DELETE subsystem in 2026 bears no resemblance to the one that earned the early "immutable" warnings. The engineers built a modern deletion engine, and the evidence is in the PRs.

Test it on your workload. That's the only benchmark that matters.

ClickHouse DELETE FAQ

Can ClickHouse delete data?

Yes. ClickHouse has supported ALTER TABLE … DELETE since 2018 (release 1.1.54388) and standard SQL DELETE FROM since v22.8 (lightweight, default-enabled since v23.3). It also supports bulk deletion via ALTER TABLE … DROP PARTITION, engine-level deletion patterns via ReplacingMergeTree(version, is_deleted), and patch-part-based DELETEs since v25.7. The "ClickHouse is append-only" claim is outdated by eight years.

What's the fastest way to delete in ClickHouse?

The fastest delete in ClickHouse is ALTER TABLE … DROP PARTITION for bulk deletion along a partition boundary — it's essentially constant-time in data volume. For selective row-level deletion, lightweight DELETE FROM (default since v23.3) is roughly 40× faster than heavyweight mutations on the initial mask write (PR #37893 benchmark). For high-frequency small deletes, patch-part DELETEs (v25.7, PR #82004) eliminate the part rewrite entirely.

Do I still need `allow_experimental_lightweight_delete`?

No. The allow_experimental_lightweight_delete setting was renamed to enable_lightweight_delete and default-enabled in ClickHouse v23.3 (PR #50044, commit 7189481, June 2023). The old name is preserved as a backward-compatibility alias but is no longer required. Anyone telling you to set this flag is benchmarking a release older than three years.

How does lightweight DELETE work?

Lightweight DELETE in ClickHouse implements standard SQL DELETE FROM <table> WHERE … as a hidden ALTER TABLE <table> UPDATE _row_exists = 0 WHERE … mutation. Each MergeTree part has a virtual column _row_exists; setting bits to 0 marks rows as deleted. Reads automatically inject PREWHERE _row_exists so deleted rows are filtered out before the main column scan. For wide-format parts, only the mask file is rewritten — all other column files are hardlinked from the old part. Physical removal happens during the next background merge, or on demand via ALTER TABLE … APPLY DELETED MASK.

What's the difference between lightweight DELETE and heavyweight ALTER DELETE?

In ClickHouse, lightweight DELETE writes a hidden mask and filters deleted rows out at read time; physical rows survive until the next background merge. It returns fast but defers physical cleanup. Heavyweight ALTER TABLE DELETE rewrites all affected parts to physically remove rows; it's slower but guarantees the bytes are gone when the mutation completes. For compliance-grade workloads (GDPR right-to-erasure), heavyweight ALTER DELETE or APPLY DELETED MASK after a lightweight DELETE is the right path.

Is `FINAL` slow in ClickHouse?

Not anymore. FINAL in ClickHouse was historically slow on ReplacingMergeTree and similar engines, which fueled the critique. It has been significantly optimized in recent versions and is now the recommended path for immediate consistency at query time, regardless of background merge state. For workloads using ReplacingMergeTree(version, is_deleted), SELECT … FINAL is the canonical pattern for ensuring deleted rows are excluded.

Can `ReplacingMergeTree` handle deletes?

Yes. ClickHouse's ReplacingMergeTree engine supports an is_deleted column parameter for tombstone-style deletion. To delete a row, insert a new record with the same primary key, the latest version, and is_deleted = 1. During a merge, rows where the latest version has is_deleted = 1 are dropped entirely. OPTIMIZE TABLE … FINAL CLEANUP (gated by allow_experimental_cleanup_merges) forces physical removal on demand.

How do I bulk-delete a lot of data efficiently?

In ClickHouse, the most efficient bulk-delete is ALTER TABLE … DROP PARTITION. Since PR #41145, partition drops use durable empty-part tombstones, making them atomic, non-blocking for concurrent reads, and resilient to replica crashes. The operation is essentially constant-time in the data volume, far cheaper than any row-level DELETE for the same scope. The trade-off: you have to design your partitioning key around the dimensions you'll bulk-delete by.

Are queued deletes visible to queries before they finish?

Yes, in ClickHouse v25.x and later. Set apply_mutations_on_fly = 1 (PR #74877, Q1 2025) and queued ALTER UPDATE / ALTER DELETE mutations are applied at SELECT time before background materialization. PR #79281 (April 2025) extended this to lightweight DELETE specifically. The "I deleted a row but a SELECT still returns it" surprise is solved.

What if my table has projections or skip indexes?

If a ClickHouse table with projections or skip indexes needs lightweight DELETE, use the lightweight_mutation_projection_mode table-level setting (PR #65594, v24.7): throw (default), drop, or rebuild. Projections and skip indexes are recalculated correctly during delete-driven merges as of PR #52530 (backported to 22.8 and later). Row-reducing merges trigger projection rebuild (PR #62364) so projections stay consistent with the base table.

How do I monitor in-flight deletes?

In ClickHouse, monitor in-flight deletes via the system.mutations table, which shows queued and running mutations with parts_to_do, is_done, latest_fail_reason, parts_postpone_reasons, and parts_in_progress_names. system.parts includes has_lightweight_delete, removal_state, and last_removal_attempt_time for masked parts. KILL MUTATION cancels a stuck or unwanted mutation. apply_deleted_mask = 0 lets you query rows that LWD has masked but not physically removed, useful for forensics and audits.

What are patch parts?

Patch parts in ClickHouse (PR #82004, v25.7) are a new on-disk architecture for lightweight UPDATEs and DELETEs. Instead of rewriting _row_exists in the source part, a DELETE creates a tiny patch part that records the rows to mark deleted. The patch is applied on read (via merge by sorted system columns or a join, depending on whether the source has been re-merged) and physically merged into the source during the next background merge. For small/selective changes, this eliminates the per-DELETE write cost almost entirely. ClickHouse's benchmarks claim up to ~1,000× speedup on small changes, though that's a vendor benchmark on a favorable workload — treat it as an upper bound. Set lightweight_delete_mode = 'lightweight_update' to enable.

Analysis based on 80+ GitHub pull requests, official ClickHouse changelogs, and release blogs covering the period 2018–2026. Every claim maps to a specific merged PR or commit SHA. Verify the evidence yourself — the commit history is public.

Does ClickHouse Support UPDATEs? A 2026 Data Analysis

Manveer Chawla — Thu, 30 Apr 2026 20:30:00 +0000

TL;DR

Yes, ClickHouse fully supports UPDATEs. As of April 2026, ClickHouse ships standard SQL UPDATE ... SET ... WHERE syntax that runs in milliseconds, alongside four other update mechanisms: ALTER TABLE … UPDATE mutations for bulk operations, lightweight DELETE, on-the-fly mutation visibility, and ReplacingMergeTree for high-volume upserts and CDC. The "ClickHouse is append-only" claim is outdated by eight years and 100+ merged pull requests.

Key facts:

Standard SQL UPDATE shipped in ClickHouse 25.7 (July 2025) via PR #82004, backed by a new "patch part" architecture. It was promoted to Beta with default enablement in version 25.8 (PR #85952).
Lightweight UPDATE delivers up to 1,000× to 2,400× speedup for single-row updates compared to classical mutations, per ClickHouse's own benchmarks. Patch parts store only the changed columns plus five system columns, with no part rewrite.
ALTER TABLE … UPDATE has shipped since August 2018 (ClickHouse v18.12), authored by Alex Zatelepin (ztlpn). Updates have never been "unsupported" in any release from the last eight years.
Lightweight DELETE has been GA since 2022 (PR #37893). The allow_experimental_lightweight_delete flag is no longer required.
On-the-fly mutations (PR #74877) make queued UPDATEs immediately visible to SELECTs, eliminating the eventual-consistency gap when needed.
Operational controls are production-grade: max_uncompressed_bytes_in_patches (default 30 GiB, PR #85641), exponential backoff for failed mutations (PR #58036), workload classification (PR #64061), and bandwidth throttling (PR #57877).
Observability is first-class: parts_postpone_reasons, latest_fail_error_code_name, mutation_ids in system.part_log, and dynamic system.warnings for stalled mutations all ship by default.
Verdict: the "ClickHouse is append-only" claim made sense in 2017. Repeating it in 2026 is misinformation. ClickHouse's UPDATE subsystem now uses standard SQL, runs in milliseconds, and replicates correctly across distributed clusters.

Why People Still Say "ClickHouse Doesn't Support Updates"

If you have evaluated ClickHouse in the last few years, you have probably heard one of these:

"ClickHouse is append-only."
"ClickHouse doesn't support UPDATEs."
"Updates require ALTER TABLE mutations that rewrite entire parts."
"Mutations are the only way to update data."
"Updates are eventually consistent."
"You have to use allow_experimental_lightweight_delete."
"There is no standard SQL UPDATE syntax."

Some of this was accurate documentation circa 2018 to 2022. Some is now folklore that competitors keep repeating because it is a convenient story: ClickHouse is fast for scans, but you can't update.

In 2017, before mutations even existed, the criticism was structurally correct. ClickHouse's MergeTree engine was designed around immutability, and "updates" had to be modeled by inserting new rows into specialized engines like ReplacingMergeTree and resolving the conflict at merge time or via FINAL. There was no UPDATE statement.

Then, over eight years, ClickHouse's engineering team systematically dismantled that limitation. They added ALTER TABLE … UPDATE (2018), KILL MUTATION and system.mutations for diagnosability (2019), mutations_sync for synchronous waits (2019), IN PARTITION scoping (2020), the MutateTask refactor (2021), a long correctness wave for replicated mutations (2020 to 2022), lightweight DELETE (2022), on-the-fly mutations (2025), and finally lightweight UPDATE backed by patch parts (2025), accompanied by 50+ stabilization PRs that made it production-safe by 2026.

This article traces that evolution with PR-level evidence. No marketing claims, no benchmarks on toy datasets. Just the commit history.

Methodology: How This ClickHouse UPDATE Analysis Was Built

We went through ClickHouse's GitHub commit history, pull requests, changelogs, and release blogs from 2018 through April 2026. The scope covered every PR that touched the UPDATE subsystem: the original mutation engine, the replicated-coordination correctness wave, lightweight DELETE, on-the-fly mutations, the patch-part architecture (PR #82004 plus 35 follow-up commits inside the same PR), and the post-landing stabilization work.

Each PR was classified by category (engine, planner, replication, observability, performance, correctness), impact severity, and whether it changed default behavior or required an opt-in flag. We cross-referenced PR descriptions against changelog entries and the ClickHouse Updates blog series to verify the claimed improvements. Where multiple PRs addressed the same subsystem, we traced the dependency chain to understand how the incremental changes compounded.

The result is a chronological narrative across seven distinct eras, with full provenance. Every claim in this article maps to a specific merged PR or issue that you can verify yourself on GitHub.

This is not a benchmarking exercise. Benchmarks measure peak performance on controlled workloads. This analysis measures the engineering trajectory: what was built, why, and what it means for teams deciding whether ClickHouse can support their update workloads today.

What Update Features Does ClickHouse Ship by Default in 2026?

The current state, as of April 2026:

Standard SQL UPDATE statement. UPDATE table SET col = expr WHERE … works for MergeTree-family tables, backed by patch parts. No special syntax, no experimental flags by default in stable production paths.
Classical ALTER TABLE … UPDATE mutations. The original 2018 mechanism is still available and is the right tool for bulk backfills, schema-level corrections, and operations where rewriting affected parts is acceptable.
Lightweight DELETE. DELETE FROM … WHERE is implemented as a single-column rewrite of a _row_exists virtual mask. Deletes that used to take 8 seconds finish in 200 ms.
On-the-fly mutation visibility. SELECTs see queued UPDATEs and DELETEs immediately, before background materialization completes. The latency between issuing an UPDATE and seeing its effect goes from "depends on the merge schedule" to "insert-like."
ReplacingMergeTree for CDC and upsert workflows. Updates are ingested as new rows; deduplication happens asynchronously during background merges. The FINAL keyword guarantees deduplicated reads at query time, and FINAL has been heavily optimized for production use.
Operational safety nets. Exponential backoff for failed mutations, workload classification for resource isolation, server-level bandwidth throttling, per-replica concurrency caps, and max_uncompressed_bytes_in_patches reject runaway UPDATE storms before they can hurt the cluster.
First-class observability. system.mutations, system.part_log, system.warnings, and system.parts.is_patch give operators the data they need to diagnose stalled or failed mutations without grepping logs.

These are not experimental features hidden behind flags. They are defaults that ship with every modern ClickHouse installation.

ClickHouse UPDATE Myths vs. Reality: A 2026 Checklist

#	The FUD	Score	Evidence	Reality (2026)
1	"ClickHouse is append-only"	🟢 False since 2018	v18.12 release	`ALTER TABLE … UPDATE` shipped in 2018. Standard SQL `UPDATE` shipped in 2025 (PR #82004).
2	"ClickHouse doesn't support updates"	🟢 False	100+ PRs across 8 years	Multiple update mechanisms ship by default: standard `UPDATE`, `ALTER TABLE … UPDATE`, lightweight DELETE, on-the-fly mutations, ReplacingMergeTree.
3	"Updates require ALTER TABLE mutations that rewrite entire parts"	🟢 False since 2025	PR #82004	Lightweight UPDATE writes only changed columns into patch parts. No part rewrite. Insert-like latency.
4	"Mutations are the only way to update"	🟢 False since 2022	PR #37893, #74877, #82004	Lightweight DELETE, on-the-fly mutations, and lightweight UPDATE all bypass the classical part-rewrite path.
5	"Updates are eventually consistent"	🟡 Nuanced	PR #74877, #82004	Classical `ALTER TABLE … UPDATE` is async by default. On-the-fly mutations and lightweight UPDATE provide immediate read-after-write visibility. `mutations_sync` provides synchronous semantics on demand.
6	"`allow_experimental_lightweight_delete` is required"	🟢 False since v22.8	Lightweight DELETE is GA	The flag is no longer needed. Lightweight DELETE is the default `DELETE` implementation.
7	"No standard SQL UPDATE syntax"	🟢 False since 2025	PR #82004	`UPDATE table SET col = expr WHERE …` works as standard SQL on MergeTree-family tables.
8	"Updates cause unbounded part rewriting"	🟢 Solved since 2025	PR #85641, #58036	`max_uncompressed_bytes_in_patches` (default 30 GiB) caps patch accumulation. Exponential backoff prevents failure-loop CPU burn.
9	"You can't kill a stuck UPDATE"	🟢 False since 2019	PR #4287	`KILL MUTATION` works on both MergeTree and ReplicatedMergeTree. `system.mutations` exposes failure reasons.
10	"ClickHouse can't isolate update workload from queries"	🟢 False since 2024	PR #64061, #57877	`mutation_workload`, `merge_workload`, and `max_mutations_bandwidth_for_server` provide first-class resource isolation.

Phase 0 (2016 to 2017): How Did You Update Data in ClickHouse Before Mutations?

The FUD: "ClickHouse is append-only and was never designed for updates."

This part of the criticism is half-right historically. ClickHouse was designed as a columnar OLAP store optimized for ingest throughput and scan performance. Row-level mutability was deliberately out of scope. There was no UPDATE statement.

But "no UPDATE statement" never meant "no way to update data." From the earliest releases, ClickHouse shipped specialized MergeTree engines that modeled mutations as insertions:

ReplacingMergeTree: last-write-wins on the sorting key. Updates are ingested as new rows with the same primary key, and the most recent version wins after the next background merge. The FINAL keyword forces deduplication at query time for cases where you can't wait for the merge.
CollapsingMergeTree: uses a Sign column (+1 for the new row, −1 for the old one). Pairs of rows with the same key cancel each other out during merges.
VersionedCollapsingMergeTree: adds a version column for ingestion of updates that arrive out of order.

These were not workarounds. They were the design. For change-data-capture (CDC) workloads, high-volume upserts, and event-sourcing patterns, they remain the most efficient option in ClickHouse to this day. The trade-off is moving the cost from write time to read time (or to merge time).

The competitor FUD that frames ClickHouse as "append-only" is technically describing this era. What it leaves out is everything that happened after.

Phase 1 (2018): How Does ClickHouse's `ALTER TABLE … UPDATE` Work?

The FUD: "ClickHouse has no UPDATE statement."

In v18.12, Alex Zatelepin (ztlpn) shipped ALTER TABLE … UPDATE and ALTER TABLE … DELETE. The model was deliberately heavyweight: every UPDATE is a logged mutation that runs asynchronously in the background.

Mechanically, a mutation does this:

The command is persisted (to ZooKeeper for ReplicatedMergeTree, or to a local mutation_*.txt file for non-replicated tables).
Each affected part is rewritten to a temporary part by MergeTreeDataMergerMutator::mutatePartToTemporaryPart. Files for unaffected columns are hardlinked in Wide parts; only the changed columns get rewritten.
A max_block_number invariant ensures mutations only process parts that existed when the mutation was issued. Data inserted after the UPDATE is not retroactively touched.
Replicas pull the mutation entry from the ZooKeeper log and execute it locally on their copies of the affected parts.

This design enforces several semantic restrictions that persist today. They are not bugs; they are the contract:

You cannot UPDATE primary-key or partition-key columns. Enforced in MutationsInterpreter::validateUpdateColumns. Changing the sort order would require rebuilding the entire part's index, which defeats the point of MergeTree.
No transactional atomicity. Mutations are not bundled into transactions by default. If the server restarts mid-mutation, the operation resumes from where it left off, but you do not get cross-mutation atomicity.
No immediate read-after-write. A SELECT issued right after an ALTER TABLE … UPDATE may return pre-update values until the background materialization completes.
No non-deterministic functions in replicated mutations. (PR #7247.) rand() and now() are forbidden because each replica would compute different values, causing divergence.

The 2018 criticism was: ClickHouse just got UPDATE support, but it is slow and async. That was fair. What followed was eight years of work to make it fast, predictable, and immediately visible, without giving up the bulk-update use case the original design was good at.

Phase 2 (2019 to 2021): Can You Diagnose, Cancel, and Wait for ClickHouse Mutations?

The FUD: "You can't kill a stuck mutation. There's no way to know if your UPDATE landed."

Once mutations existed, the next two years were dominated by operational maturity. Six PRs in particular turned mutations from "fire and pray" into something you could reason about in production.

`KILL MUTATION` and Failure Diagnostics (PR #4287, 2019)

ztlpn's February 2019 PR added KILL MUTATION for both MergeTree and ReplicatedMergeTree. It also extended system.mutations with latest_failed_part, latest_fail_time, and latest_fail_reason, and added an is_mutation flag in system.merges. From this point on, "my UPDATE is stuck" became a diagnosable problem rather than an opaque one.

`mutations_sync`: Defining "Did My UPDATE Land?" (PR #8237, 2019)

alesapin's December 2019 PR introduced the mutations_sync setting. Set to 0, the default, mutations are fully async. Set to 1, the client waits until the mutation completes on the local replica. Set to 2, it waits until all replicas have completed. Every later wait-correctness fix in the replication wave (#22669, #28889, #24809, #10588) is a repair of this contract.

`IN PARTITION` Scoping (PR #13403, 2020)

Vladimir Chebotarev's PR added ALTER UPDATE/DELETE … IN PARTITION. This was the first SQL semantics extension since the original landing, enabling partition pruning. If you only need to update last week's data, you say so explicitly and the mutation skips every other partition.

The `MergeTask` / `MutateTask` Refactor (PR #25165, 2021)

nikitamikhaylov's September 2021 PR is the quietly load-bearing change of the entire UPDATE history. It split the monolithic merge/mutate logic into stage-based, suspendable MergeTask and MutateTask objects. The PR description: "Added an ability to suspend and resume a process of a merge." Every later mutation improvement, from compact-part stage collapse to patch parts to vertical-merge correctness, builds on this refactor.

The Replication Correctness Wave (2020 to 2022)

In parallel, a long series of PRs from alesapin, azat, and tavplubix turned replicated UPDATE from "best-effort" into "predictable but slow." Notable fixes:

#9022 fixes the parts_to_do=0 ∧ is_done=0 hang where a mutation appeared "almost done" forever.
#11681 fixes the inconsistency between system.mutations.is_done=1 and a MUTATE_PART entry still sitting in the replication queue.
#17499 fixes ALTER hang when the corresponding mutation is killed on a different replica.
#19702 fixes virtual_parts after part corruption so replicated mutations can recover.
#22669 fixes wait-on-multiple-replicas semantics for mutations_sync=2.
#28889 fixes a rbegin vs begin typo in the cross-replica wait logic. Tiny diff, large blast radius.
#34096 fixes the race between mergeSelectingTask and queue reinit after ZooKeeper reconnect.

Distributed UPDATE is uniquely hard. ZooKeeper coordination, virtual_parts after part corruption, queue reinit races, finalization ambiguity: every distributed system that supports UPDATE eventually relives this set of problems. ClickHouse's 2020 to 2022 commit history is what working through them looks like.

Phase 3 (2022): Why Is ClickHouse's Lightweight DELETE So Much Faster?

The FUD: "Every delete in ClickHouse rewrites entire parts."

PR #37893 by zhangjmruc in 2022 was the architectural wedge that made everything later possible. It implemented DELETE FROM … WHERE as ALTER UPDATE _row_exists = 0 WHERE … against a new virtual mask column.

Before this PR, deleting matching rows meant rewriting every part that contained any of them. After it, deletion is a single-column UPDATE of the virtual _row_exists mask, with the actual row filtering happening at SELECT time.

The PR body cites a benchmark: 200 ms vs 8 seconds on the same workload. Forty-fold improvement, with no part rewrite required.

This was not just a performance win. It was a proof of concept for a new pattern: instead of physically modifying data, write a small "diff" alongside it and reconcile at read time. That pattern would later become the foundation of patch parts.

The competitor FUD point about allow_experimental_lightweight_delete refers to the early enablement flag for this feature. The flag is no longer needed; lightweight DELETE has been the default DELETE implementation for years.

Phase 4 (Early 2025): How Do On-the-Fly Mutations in ClickHouse Work?

The FUD: "You always have to wait for the next merge to see your update."

The latency problem with classical mutations is structural: the UPDATE is logged immediately, but the data is not physically modified until a background merge gets around to it. In a busy cluster, that can mean seconds or minutes between issuing an UPDATE and being able to read its effect.

PR #74877 by CurtizJ (early 2025) introduced on-the-fly mutations via the apply_mutations_on_fly setting. With this enabled, SELECTs apply non-finished UPDATE/DELETE mutations immediately, before background materialization. The latency between "I issued an UPDATE" and "I can read the new value" goes from "depends on the merge schedule" to "insert-like."

Three companion settings landed alongside it:

mutations_max_literal_size_to_replace: caps how large a literal can be while still being inlined into the on-the-fly application path.
mutations_execute_nondeterministic_on_initiator: controls where non-deterministic mutation expressions execute, to keep results consistent across replicas.
mutations_execute_subqueries_on_initiator: same idea for subqueries inside mutation predicates.

On-the-fly mutations made it explicit: read-after-write consistency is something users can opt into when they need it, without giving up the asynchronous bulk-rewrite model when they do not.

This was the latency wedge. The next PR was the syntax wedge.

Phase 5 (Mid-2025): How Does ClickHouse's Lightweight UPDATE and Patch-Part Architecture Work?

The FUD: "There is no standard SQL UPDATE syntax in ClickHouse. Every UPDATE rewrites parts."

PR #82004 is the landmark commit of this entire eight-year history. Authored by Anton Popov (CurtizJ), the initial commit (a5327c6) landed June 16, 2025, and the PR merged to master around July 6, 2025. It shipped in ClickHouse 25.7.

What it does: introduces standard SQL UPDATE table SET col = expr WHERE … for MergeTree-family tables, backed by a new artifact called a patch part.

What Does a ClickHouse Patch Part Contain?

A patch part is a small, separate part on disk that stores only what changed:

The columns that were updated (with their new values).
Five system columns: _part, _part_offset, _block_number, _block_offset, _data_version.

That is it. No copy of unchanged columns. No index rebuild. No part rewrite. The size overhead is approximately 40 bytes per row plus the actual changed cell values.

The implementation lives in a new directory, src/Storages/MergeTree/PatchParts/, with new types like PatchPartInfo, PatchMode, MergeTreeSinkPatch, MergeTreePatchReader, and a brand-new InterpreterUpdateQuery. The landing also touched MutationCommands, MutateTask, MergeTreeData, MergeTreeDataMergerMutator, MergeTreeSink, ReplicatedMergeTreeQueue, ReplicatedMergeTreeLogEntry, and the reader-chain files. By any reasonable measure, this was a multi-subsystem rewrite, not a feature add.

How Does ClickHouse Apply Patch Parts at Query Time?

ClickHouse reconciles a patch part with a base part at SELECT time. There are two strategies:

PatchMode::Merge: sorted on (_part, _part_offset). Used when patches and base parts share row offsets directly.
PatchMode::Join: joined on (_block_number, _block_offset). Used when offsets do not line up directly and a logical join is needed.

The choice is automatic. Implicit minmax indexes on _block_number and _block_offset inside patch parts (PR #85040) make the join-mode path much faster by pruning patches that do not touch the rows being read.

Patches themselves get merged together in the background (a "replacing-merge by _data_version"), so the read-time overhead does not accumulate forever. Eventually, patches fold into base parts during normal merges, and the system returns to baseline read performance.

How Fast Is ClickHouse's Lightweight UPDATE?

ClickHouse's own benchmarks, published in the Updates in ClickHouse, Part 3 blog post, report up to 1,000× to 2,400× faster for single-row updates compared to classical ALTER TABLE … UPDATE mutations. The exact multiplier depends on the workload shape; the headline is that what used to be a heavyweight asynchronous operation now has insert-like latency.

The cost is read-time overhead. The umbrella issue #82033 cites approximately 7% to 18% on average for SELECTs that have to apply patches. That is the trade-off: patches are cheap to write and bounded in size, but they do add a small reconciliation cost at read time. When patches fold into base parts during background merges, the overhead disappears.

What Settings Control Lightweight UPDATE in ClickHouse?

allow_experimental_lightweight_update: gate during the experimental period.
apply_patches_to_read: read-side toggle.
update_parallel_mode: controls write-side parallelism for patch creation.
update_sequential_consistency: visibility model.
enable_block_number_column = 1 and enable_block_offset_column = 1: prerequisites; patch parts depend on the per-row block-number/offset columns introduced for this purpose.
lightweight_delete_mode = 'lightweight_update': opt-in path for routing DELETEs through patch parts as well.

PR #85952 (August 24, 2025) promoted lightweight UPDATE to Beta with default enablement, shipping in ClickHouse 25.8.

Phase 6 (Mid-2025 to 2026): How Was ClickHouse's Patch-Part Architecture Stabilized?

The FUD: "The new UPDATE features are experimental and unsafe in production."

A feature this cross-cutting needed weeks of immediate stabilization. Inside PR #82004 itself, 35 commits landed between the initial June 16, 2025 commit and the final merge. Ten of those follow-up commits are worth naming:

Date	SHA	What it fixed
2025-06-17	`7f5a42a`	Lightweight updates on `ReplicatedMergeTree`
2025-06-19	`284c239`	Better consistency for lightweight updates in RMT
2025-06-19	`c7ec4db`	Merges of patch parts in RMT
2025-06-20	`f18385c`	Disable partition detach in RMT with patch parts (operational safety)
2025-06-20	`9902d37`	Crash in prefetch of patch parts
2025-06-23	`e7d8624`	Filtering of `versions_block`
2025-06-25	`cc28005`	Better waiting for LWU before running classic mutation
2025-06-26	`5af26c2`	Better applying patches with PREWHERE
2025-07-02	`6409858`/`b23a074`	Disable lazy columns with lightweight updates (correctness over a read-path optimization)

That is just inside the original PR. Outside it, the post-landing stabilization involved another wave of fixes:

Read-Path and Query-Plan Correctness

PR #85040: implicit minmax indexes on _block_number/_block_offset inside patch parts; reworked PatchJoinCache. Big SELECT-side win.
PR #92838: primary-index use for lightweight updates with IN-subquery predicates.
PR #99023: patch parts without _part_offset query-plan fix.
PR #99164: patch-parts column-order mismatch causing LOGICAL_ERROR.

Memory and Resource Guardrails

PR #85641: max_uncompressed_bytes_in_patches (default 30 GiB). New lightweight updates are rejected with TOO_LARGE_LIGHTWEIGHT_UPDATES if patches accumulate beyond the threshold. This is the operational governor that prevents runaway patch growth from degrading reads forever.
PR #95231: fixes inaccurate memory accounting for large patch-part application that could trigger OOM-killer events.
PR #77922: parallel column flushes during vertical merges.

Correctness and Crash Fixes (Late 2025 to April 2026)

PR #82945: mutations snapshot built from parts visible in the query; consistency for on-fly + patch parts vs running mutations.
PR #97162 (alexey-milovidov, 2026-02-17): fixes phantom entries in mutations' parts_to_do that caused stuck mutations. Race condition where PartCheckThread re-enqueued already-mutated parts; the fix adjusts ReplicatedMergeTreeQueue to immediately remove obsolete parts.
PR #97347 (Kirill Kopnev, 2026-02-20): scalar subquery in ALTER UPDATE/DELETE could corrupt the mutation command and even make the table unloadable on restart. High-severity.
PR #98044 (Raul Marin / Algunenano, 2026-02-26): fixes mutation after lightweight update on tables with secondary indices. The cleanest example of how the legacy mutation framework and the new lightweight-update system needed to learn to coexist.
PR #101403 (2026-04-22): fixes UPDATE SET DateTime literal not being rewritten with session timezone, which was a silent data-corruption hazard.

Replicated-Side Concurrency

PR #95771 (2026-04-09): optimizes ReplicatedMergeTree queue locks; reduces lock contention for SELECTs on replicated tables with mutations.
PR #87265: fixes lightweight UPDATE with WHERE col IN (SELECT …) in replicated tables with partitions.

The volume of stabilization work tells you something honest: a feature that lets you write UPDATE against a columnar OLAP store and finishes in milliseconds and replicates correctly and coexists with the legacy mutation framework is genuinely hard. ClickHouse's engineering team did the work. Running ClickHouse 25.8 or later gets you a feature that has been hardened in the open, with every fix traceable to a public PR.

What Operational Controls Does ClickHouse Provide for UPDATE Workloads?

Beyond the headline features, the eight-year history added a set of operational levers that make UPDATE workloads predictable in production:

Exponential backoff for failed mutations (PR #58036, 2024). Default retry interval of 5 minutes for mutations that keep failing (e.g., a bad CAST). Prevents CPU and log-file blowup from hot-looping on a permanent error.
Workload classification (PR #64061, 2024). The mutation_workload and merge_workload settings integrate with the workload scheduler so UPDATE mutations can be classed and throttled separately from merges and queries.
Server-level bandwidth throttling (PR #57877, 2024). The max_mutations_bandwidth_for_server setting caps the I/O bandwidth mutations can consume cluster-wide.
Pre-submit query validation (PR #71300, 2024). The full mutation query, including subqueries, is validated before being queued. Prevents queue-blocking dead mutations.
Throttling caps. number_of_mutations_to_delay, number_of_mutations_to_throw, and max_number_of_mutations_for_replica cap queued and concurrent mutation counts.
Replication coalescing limit (PR #48731, 2023). replicated_max_mutations_in_one_entry (default 10000) bounds how many mutation commands are coalesced into one ZooKeeper entry, preventing OOM on startup.
Lightweight-DELETE-with-projections control (PR #66169). lightweight_mutation_projection_mode (throw / drop / rebuild) gives operators explicit control over how lightweight DELETE interacts with materialized projections.

None of these individually make a press release. Together, they are what "production-grade UPDATE support in a columnar database" actually requires.

How Do You Monitor ClickHouse UPDATE Performance and Health?

If you cannot see what your UPDATEs are doing, you cannot run them in production. The 2024 to 2026 observability additions are substantial:

latest_fail_error_code_name in system.mutations (PR #72398). Enables automated alerting on specific failure classes.
parts_postpone_reasons in system.mutations (PR #92206, 2025-12-16). Lets operators diagnose stalled mutations instantly. "Why is this mutation not progressing?" used to require log-grepping. Now it is a column.
mutation_ids in system.part_log (PR #93811). For MUTATE_PART and MUTATE_PART_START events. Materially improves traceability during incident investigations.
is_patch in system.parts. Distinguishes patch overlays from base parts, so operators can see directly how much patch material has accumulated.
Long-running mutation warnings (PR #78658). Adds a dynamic system.warnings entry when mutations exceed max_pending_mutations_execution_time_to_warn. Surfaces silently-stuck mutations without external monitoring.
On-fly mutation metrics in system.tables (PR #75738). Per-table visibility into the on-the-fly mutation backlog.
Independent background settings for mutate vs. merge (PR #93905). Previously the two shared the default profile, which made it impossible to isolate update resource usage from merges.

What Are the Limitations of ClickHouse UPDATEs in 2026?

Fairness matters. A few things still require awareness:

Primary-key and partition-key columns still cannot be updated. This is a structural property of MergeTree, not a missing feature. Changing the sort order would require rebuilding the part's primary index; if you genuinely need to change a key column, the right pattern is to insert into a new table with the desired key and swap.
Classical ALTER TABLE … UPDATE mutations are still asynchronous by default. They are the right tool for bulk backfills and schema-level corrections, but if you need read-after-write consistency, you need on-the-fly mutations, lightweight UPDATE, or mutations_sync.
Patch parts have a read-time cost. The umbrella issue cites approximately 7% to 18% read overhead while patches are unmerged. Background merges fold patches into base parts and the overhead disappears, but a workload that issues massive patch volume faster than merges can absorb will see sustained read regression.
max_uncompressed_bytes_in_patches is a hard ceiling. The 30 GiB default is a sensible starting point, but a workload generating patches faster than merges can consume them will eventually hit the cap and have new updates rejected with TOO_LARGE_LIGHTWEIGHT_UPDATES. Tune it, monitor it.
The new analyzer is intentionally not used by MutationsInterpreter. PR #61528 (2024) explicitly forces mutations to use the legacy analyzer, and issue #61563 tracking the migration remains open in early 2026. This is the largest outstanding planner gap on the UPDATE side.
Lightweight UPDATE and classical mutations can interact. Issues like #98898 (LOGICAL_ERROR: Found patch part intersects mutation) and PR #98044 show that the two systems are still being taught to coexist cleanly. Run a recent stable release.
ReplacingMergeTree with FINAL is still the right tool for very high-volume CDC and upsert workloads. Lightweight UPDATE is fast for low-to-medium volume row-level changes; for streams of millions of upserts per second, the engine-level deduplication model continues to win.

These are real engineering trade-offs. Understanding them is part of making an informed decision.

ClickHouse UPDATE Improvements Timeline (2018 to 2026)

Year	What Changed	Key PRs	Impact
2018	Original `ALTER TABLE … UPDATE/DELETE` lands	ztlpn 2018 series	First UPDATE statement. Heavyweight, async, replicated via ZooKeeper.
2019	`KILL MUTATION`, `system.mutations` failure columns, `mutations_sync`	#4287, #8237	UPDATE becomes diagnosable, cancellable, and waitable.
2020	`IN PARTITION` scoping, NULL semantics fix, `isAffectingAllColumns` gate	#13403, #12153, #12760	First SQL semantics extension. Partition pruning. Correct WHERE handling.
2021	`MergeTask`/`MutateTask` refactor; replicated correctness wave	#25165, #22669, #28889	Architectural foundation for everything later. Replicated UPDATE becomes predictable.
2022	Lightweight DELETE via `_row_exists` mask	#37893	200 ms vs 8 s. Wedge for the patch-part architecture.
2023	Skip-index recalc, vertical Compact-to-Wide merges, `replicated_max_mutations_in_one_entry`	#55202, #45681, #48731	Storage-feature integration. Mutation-storm safety.
2024	Backoff, workload classification, bandwidth throttling, `latest_fail_error_code_name`	#58036, #64061, #57877, #72398	Production-grade operational controls. UPDATE becomes a first-class workload class.
2025	On-the-fly mutations; lightweight UPDATE / patch parts	#74877, #82004, #85641, #85952	Standard SQL `UPDATE`. Insert-like latency. 1,000× to 2,400× faster for single-row updates. Promoted to Beta.
2026	Stabilization: phantom-queue fix, secondary-index reconciliation, queue-lock optimization, timezone correctness	#97162, #98044, #95771, #101403	Production hardening. New observability columns. Replicated concurrency improvements.

When Should You Use Each ClickHouse Update Mechanism?

Workload	Recommendation	Reasoning
Single-row UPDATEs from an application	✅ Lightweight UPDATE (`UPDATE ... SET ... WHERE`)	Insert-like latency, standard SQL syntax, immediate read-after-write visibility (PR #82004).
Scattered row-level updates from a service	✅ Lightweight UPDATE	Patch parts handle scattered writes far better than classical mutations.
Bulk backfill of a column across millions of rows	✅ `ALTER TABLE … UPDATE`	Classical mutation rewrites parts efficiently when the volume justifies the rewrite.
Schema-level correction (one-off fix for bad data)	✅ `ALTER TABLE … UPDATE`	Async, runs in the background, no read-time overhead afterwards.
Continuous high-volume CDC / upsert stream	✅ `ReplacingMergeTree` + `FINAL`	Engine-level deduplication remains the most efficient path for millions of upserts per second.
Soft delete / mark-as-deleted	✅ Lightweight DELETE	`_row_exists` mask is a single-column rewrite (PR #37893).
Hard delete with disk reclamation	🟡 `ALTER TABLE … DELETE` or `APPLY DELETED MASK`	Lightweight DELETE leaves data on disk until merge; force physical removal when compliance or reclamation requires it.
Read-after-write consistency on a queued mutation	✅ `apply_mutations_on_fly` or `mutations_sync`	On-the-fly application makes pending mutations visible to SELECTs immediately (PR #74877).
Update to a primary-key or partition-key column	❌ Not supported	Insert into a new table with the desired key and swap. This is structural, not a missing feature.
Updates with non-deterministic functions in replicated tables	❌ Not supported	`rand()` and `now()` would diverge across replicas (PR #7247).

How to Respond to "ClickHouse Doesn't Support Updates"

Run the numbers on your data.

When someone tells you ClickHouse cannot handle updates in 2026, ask them which version they tested against. If they are benchmarking ClickHouse 22.x or earlier, they are testing a system that does not include lightweight DELETE (2022), on-the-fly mutations (early 2025), lightweight UPDATE (mid-2025), patch parts (mid-2025), or the entire 2025 to 2026 stabilization wave.

If they cite "ClickHouse is append-only" without acknowledging that ALTER TABLE … UPDATE shipped in v18.12, they are working from 2017 documentation.

If they cite "no standard SQL UPDATE syntax," they have not read PR #82004 or the Updates in ClickHouse blog series.

If they cite "all updates rewrite entire parts," they are describing one of three update mechanisms (the classical heavyweight one) and ignoring the other two (lightweight DELETE and lightweight UPDATE) plus the engine-level upsert pattern (ReplacingMergeTree).

If they cite "you need allow_experimental_lightweight_delete," they have not run a stable ClickHouse release in years.

The commit history does not lie. 100+ pull requests across eight years. Standard SQL UPDATE syntax. Insert-like latency for single-row updates. Production-grade observability. Workload isolation. Bandwidth throttling. Patch-part guardrails. Phantom-queue race conditions fixed in February 2026 by Alexey Milovidov himself.

ClickHouse's UPDATE subsystem in 2026 bears no resemblance to the one that earned the "append-only" label. The engineers built a real update story, and the evidence is in the PRs.

Test it on your workload. That is the only benchmark that matters.

ClickHouse UPDATE FAQ

Does ClickHouse support standard SQL `UPDATE`?

Yes. ClickHouse 25.7 (July 2025) added standard SQL UPDATE table SET col = expr WHERE … for MergeTree-family tables via PR #82004. It uses a "patch part" architecture and was promoted to Beta with default enablement in version 25.8.

Is ClickHouse append-only?

No. ClickHouse stopped being append-only in August 2018, when v18.12 added ALTER TABLE … UPDATE. Standard SQL UPDATE arrived in v25.7 (July 2025). The "append-only" label is accurate only for the 2016 to 2017 era.

Do all ClickHouse UPDATEs rewrite entire parts?

No. ClickHouse offers three update paths. Lightweight UPDATE (PR #82004) writes a small patch part containing only changed columns, with no part rewrite. Lightweight DELETE (PR #37893) rewrites only the _row_exists virtual column. Classical ALTER TABLE … UPDATE rewrites affected parts and is the right mechanism for bulk backfills.

Are ClickHouse UPDATEs eventually consistent?

It depends on the mechanism. Classical ALTER TABLE … UPDATE is asynchronous by default. Lightweight UPDATE and on-the-fly mutations (PR #74877) provide immediate read-after-write visibility. The mutations_sync setting forces synchronous semantics on demand. You choose the consistency model per workload.

What is `ReplacingMergeTree` and when should you use it?

ReplacingMergeTree is a ClickHouse engine that resolves duplicates on the sorting key during background merges. Use it for high-volume CDC and upsert workflows: updates are ingested as new rows, and deduplication runs asynchronously. Add the FINAL keyword to SELECT queries for guaranteed deduplicated reads. FINAL has been heavily optimized for production use.

What is the read-time overhead of ClickHouse patch parts?

Approximately 7% to 18% on average while patches are unmerged, per umbrella issue #82033. Background merges fold patches into base parts, after which the overhead disappears. The max_uncompressed_bytes_in_patches setting (default 30 GiB, PR #85641) caps total patch accumulation.

Do you still need `allow_experimental_lightweight_delete` in ClickHouse?

No. Lightweight DELETE has been GA for years and is the default DELETE implementation in modern ClickHouse releases. The experimental flag is no longer required.

Can you cancel a stuck UPDATE in ClickHouse?

Yes. KILL MUTATION (PR #4287, 2019) works on both MergeTree and ReplicatedMergeTree. The system.mutations table exposes latest_fail_reason, latest_failed_part, and latest_fail_time. Since late 2025, parts_postpone_reasons (PR #92206) tells you exactly why a mutation is not progressing.

Can you UPDATE primary-key or partition-key columns in ClickHouse?

No. This is structural, not a missing feature. Changing a key column would require rebuilding the part's primary index. The recommended pattern is to insert into a new table with the desired key and swap.

How fast is ClickHouse lightweight UPDATE compared to classical mutations?

Up to 1,000× to 2,400× faster for single-row updates, per ClickHouse's Updates in ClickHouse, Part 3 benchmark blog post. Classical mutation latency is bounded by the merge schedule; lightweight UPDATE has insert-like latency because it writes a small patch part instead of rewriting affected parts.

Does ClickHouse use the new query analyzer for mutations?

Not yet. PR #61528 (2024) explicitly forces MutationsInterpreter to use the legacy analyzer. The migration is tracked in issue #61563, still open in early 2026. This is the largest outstanding planner gap on the UPDATE side.

Analysis based on 100+ GitHub pull requests, official ClickHouse changelogs, and release blog posts covering the period 2018 to April 2026. Every claim maps to a specific merged PR, issue, or blog post. Verify the evidence yourself; the commit history is public.

Reference reading: Updates in ClickHouse, Part 1: Purpose-Built Engines · Part 2: SQL-Style Updates · Part 3: Benchmarks · Handling Updates and Deletes in ClickHouse · SQL Reference: UPDATE · Updating Data Overview · ReplacingMergeTree Guide.

ClickHouse Native JSON Support in 2026: A PR-by-PR Analysis

Manveer Chawla — Mon, 20 Apr 2026 20:18:38 +0000

TL;DR

ClickHouse has full native JSON support, and has since v25.3. The JSON type stores each path as a separate columnar subcolumn with native type preservation, primary key indexing, and selective path reads. It is 2,500x faster than MongoDB for aggregations on the JSONBench 1-billion-document benchmark. The narrative that "ClickHouse can't do JSON" is outdated by two years and 80+ merged PRs.

We analyzed 80+ GitHub pull requests, official ClickHouse changelogs, release blogs, and third-party benchmarks to trace the full evolution of JSON support from string-based functions through the modern native JSON type.
In 2021, the criticism had some basis. JSON was stored as opaque String blobs, queried via JSONExtract* functions that required full column scans on every query. The experimental Object('json') type shipped in 2022 but suffered from eager type unification, unbounded column explosion, and race conditions.
By early 2026, ClickHouse ships a production-ready native JSON type built on three foundational types (Variant, Dynamic, JSON), with configurable path limits, type hints, primary key support for JSON subcolumns, three generations of storage serialization, and a query planner that reads only the specific JSON paths your query needs. None of this requires manual schema management.
The single highest-impact storage change is advanced shared data serialization (PR #83777), which delivered 58x faster reads and 3,300x less memory for selective path access by introducing per-granule metadata with path indexes.
The native JSON type stores each path as a separate Dynamic-typed subcolumn in columnar format. The result: 2,500x faster than MongoDB for aggregations, 10x faster than Elasticsearch, and 9,000x faster than DuckDB/PostgreSQL for analytics on the same dataset, according to the JSONBench benchmark on 1 billion Bluesky documents.
The JSON type reached GA in ClickHouse 25.3 (PR #77785), with experimental flags removed and the type backported to the LTS release. The legacy Object('json') type was fully removed in v25.11 (PR #85718).
Verdict: the "ClickHouse doesn't do JSON" advice referenced a system that no longer exists. The current JSON type is a ground-up columnar implementation that preserves native types, supports primary key indexing, and reads only the paths you query. Repeating the old criticism in 2026 is misinformation.

Why People Still Say "ClickHouse Has No Native JSON Support"

If you've evaluated ClickHouse for semi-structured data, you've heard the warnings:

"ClickHouse doesn't support JSON natively"
"Flatten JSON into columns manually"
"Use JSONExtract functions on String columns" (as the primary approach)
"Use Object('JSON')" (deprecated type)
"No native JSON support"

Some of these started as legitimate observations circa 2021-2022. ClickHouse did store JSON as Strings. The JSONExtract* functions did scan the full column. The first attempt at a native type (Object('json')) did have serious architectural flaws.

Others were amplified by competitors who found a convenient story: ClickHouse is fast for scans, but it can't handle semi-structured data.

Then ClickHouse's engineering team spent three years building one of the most sophisticated columnar JSON implementations in any database. Over 80 significant pull requests merged. They built three new foundational types (Variant, Dynamic, JSON), three generations of storage serialization, a query planner that reads only needed subcolumns, primary key and skip index support for JSON paths, and clear migration paths from every legacy representation.

This article traces that evolution with PR-level evidence. No marketing claims. No benchmarks on toy datasets. Just the commit history.

Methodology: How We Analyzed ClickHouse's JSON Type Commit History

We went through ClickHouse's GitHub commit history, pull requests, changelogs, and release blogs from 2019 through early 2026. The scope covered every PR that touched JSON handling: type implementations, storage formats, function changes, planner optimizations, memory improvements, correctness fixes, and migration paths.

Each PR was classified by category (type system, storage, functions, planner, correctness, migration), impact severity, and whether it changed default behavior. We cross-referenced PR descriptions against changelog entries and benchmark results to verify claimed improvements. Where multiple PRs addressed the same subsystem, we traced the dependency chain to understand how incremental changes compounded.

The result is a ranked analysis of 80+ pull requests organized into six phases, with full provenance. Every claim in this article maps to a specific merged PR that you can verify yourself on GitHub.

ClickHouse JSON Capabilities in 2026: What Ships by Default

The current state, as of early 2026:

Native JSON data type (GA since v25.3): Each JSON path is stored as a separate Dynamic-typed subcolumn in columnar format. Full SQL query, filter, and aggregation support on JSON fields, including nested structures and arrays (Array(JSON)). Configurable max_dynamic_paths (default 1024) and max_dynamic_types (default 32) control resource usage. Known paths can be materialized as physical columns with type hints (JSON(key1 UInt32, key2 String)), while unknown paths are automatically discovered with type inference. Path filtering via SKIP and SKIP REGEXP provides fine-grained schema control.
Three foundational types: Variant (PR #58047) provides discriminated unions. Dynamic (PR #63058) extends Variant with open-ended type storage. JSON (PR #66444) combines both to store semi-structured data with native type preservation.
Primary key and skip index support: JSON subcolumns can appear in ORDER BY and data-skipping index expressions (PR #72644), enabling the same data pruning that ClickHouse applies to regular typed columns.
Advanced shared data serialization: Per-granule path indexes for selective reads of specific paths without scanning the entire JSON column (PR #83777). Three serialization modes optimized for different access patterns.
Planner-level subcolumn optimization: The query planner reads only the JSON paths referenced in your query (PR #68053), pushes subcolumn requirements through CTEs and views (PR #94105), and rewrites JSONExtract calls into direct subcolumn reads (PR #96711).
Full JSONExtract interop: All JSONExtract* functions work with native JSON columns (PR #96711). Introspection functions (distinctJSONPaths, distinctJSONPathsAndTypes) provide schema discovery from metadata alone (PR #68463, PR #92196).
Migration from every legacy format: ALTER TABLE ... MODIFY COLUMN converts String, Object('json'), Map, and Tuple columns to the native JSON type (PR #70442, PR #71784, PR #71320).

These are not experimental features behind flags. They are defaults that ship with every ClickHouse installation since v25.3.

ClickHouse JSON Myths vs. Reality: A 2026 Checklist

#	The FUD	Score	Evidence Volume	Reality (2026)
1	"No native JSON support"	False since Aug 2024	PR #66444, #77785	Native JSON type stores each path as a separate columnar subcolumn. GA since v25.3.
2	"Flatten JSON into columns manually"	False since Aug 2024	PR #66444, #72644	Automatic path flattening into Dynamic-typed subcolumns. No manual schema management.
3	"Use JSONExtract on String columns"	Outdated	PR #96711, #66444	JSONExtract works on native JSON columns and gets rewritten to direct subcolumn reads. No full-column scan.
4	"Use Object('JSON')"	Removed in v25.11	PR #85718, #66444	Object('json') was replaced by a ground-up redesign. The old type was fully removed in v25.11.
5	"JSON queries require full column scans"	False since 2024	PR #68053, #83777, #94105	Planner reads only referenced subcolumns. Advanced serialization provides per-granule path indexes. 58x faster, 3,300x less memory.
6	"Can't index JSON fields"	False since Dec 2024	PR #72644, #98886	JSON subcolumns in ORDER BY, primary key, and skip indexes. Bloom/text indexes on JSONAllPaths.
7	"JSON types lose type information"	False	PR #58047, #63058	Variant/Dynamic preserve native types (UInt32, Float64, DateTime, etc.). No String collapse.
8	"ClickHouse JSON is slower than document DBs"	False	JSONBench (1B docs)	2,500x faster than MongoDB. 10x faster than Elasticsearch. 9,000x faster than DuckDB/PostgreSQL.
9	"No schema discovery for JSON"	False since Aug 2024	PR #68463, #92196	`distinctJSONPaths()` and `distinctJSONPathsAndTypes()` read metadata only. Instant schema views.
10	"Can't migrate existing JSON String columns"	False since Oct 2024	PR #70442, #71784, #71320	ALTER TABLE converts String, Object, Map, and Tuple to native JSON. Background merge conversion.

Phase 1: ClickHouse JSON Functions and String Storage (2019-2021)

The FUD: "Use JSONExtract functions on String columns"

In this era, the criticism was fair. ClickHouse stored JSON as opaque String blobs, and every JSON query required parsing the entire string value.

ClickHouse JSONExtract Functions: simdjson-Powered but CPU-Heavy (May 2019)

PR #5235 introduced the JSONExtract* function family, powered by simdjson with a RapidJSON fallback. This was a meaningful step: SIMD instructions allowed structural element identification at near-memory-bandwidth speeds.

But the fundamental limitation remained. Every query, no matter which field it accessed, required scanning and parsing the full JSON string column. There was no way to read just event.user_id without also reading event.metadata, event.payload, and every other field.

ClickHouse provided two function families with different trade-offs:

simpleJSON / visitParam: Minimalist heuristic parsing with low CPU overhead, but strict assumptions about canonical encoding and no nested object support.
JSONExtract*: Full simdjson-powered parsing with standards-compliant extraction, but high per-row CPU cost from full document parsing.

Neither approach could avoid the core problem: 100% column scan for every query.

SQL/JSON Standard Functions (Mid-2021)

PR #24148 added JSON_VALUE, JSON_QUERY, and JSON_EXISTS with JSONPath expression support, bringing ClickHouse closer to SQL/JSON standard compliance. This improved SQL compatibility but did not change the underlying storage model. JSON was still strings.

Map(String, String): A Partial Improvement

The Map(String, String) type offered some improvement by storing JSON key-value pairs natively, eliminating the need for string parsing on every access. But it still required reading all keys to find one entry, and it lost all type information by collapsing everything to strings.

By the end of 2021, ClickHouse had capable JSON parsing functions but no native JSON storage. The gap was real, and the engineering team knew it.

Phase 2: ClickHouse Object('json') Type -- What Went Wrong (2022)

The FUD: "Use Object('JSON')"

PR #23932, merged March 2022 by Anton Popov, was the first attempt at native columnar JSON storage. It shipped in ClickHouse 22.3 LTS under allow_experimental_object_type. The implementation spanned 101 commits and proved a critical concept: JSON could be stored with each path as a separate subcolumn.

But it had serious architectural flaws:

Challenge	Impact	Consequence
Eager Type Unification	Mixed types at a path collapsed to String	Lost native type optimizations
Metadata Explosion	High memory for many unique keys	System instability with high-cardinality JSON
Race Conditions	Inconsistent results during merges	Unreliable query analysis
Schema Rigidity	Inability to handle type changes	Required manual ALTER or table rewrites
No Primary Key Support	JSON paths excluded from ORDER BY	No data pruning on JSON fields

Despite these flaws, Object('json') validated the demand for native JSON storage and identified every architectural challenge the replacement would need to solve.

Alongside the type work, ClickHouse continued improving JSON ecosystem support. PR #40910 introduced the JSONObjectEachRow format for keyed JSON objects. PR #39186 added automatic type inference from JSON strings, detecting dates, datetimes, and integers by default. PR #54427 enabled schema inference of JSON objects as named Tuples.

These format and inference improvements meant ClickHouse was getting better at ingesting JSON data. What it still lacked was a sound way to store it.

Phase 3: ClickHouse Variant and Dynamic Types -- The JSON Foundation (2024)

The FUD: "JSON types lose type information"

Rather than patching Object('json'), ClickHouse built from first principles. The redesign started with two new foundational types that solved the type-preservation problem that had plagued the original implementation.

Variant: Discriminated Union Type (January 2024)

PR #58047, by Pavel Kruglov, introduced Variant(T1, T2, ..., TN), a discriminated union storing values of different types in a single column. It uses a UInt8 discriminator column plus dense subcolumns per type variant, supporting up to 255 variants. The PR included 47 commits and roughly 5,000 lines of tests.

This solved the type-unification problem that killed Object('json'). Instead of collapsing 42 and "hello" at the same path into String, Variant stores them in their native types with a discriminator indicating which type each row contains.

A follow-up optimization (PR #62774) introduced compact discriminator serialization: when all discriminators in a granule are the same type (the common case for JSON paths), it stores 3 values instead of 8,192. This is highly effective in practice since most JSON paths have homogeneous types within a granule.

Dynamic: Open-Ended Type Storage (May 2024)

PR #63058, also by Pavel Kruglov, extended Variant with an open, self-describing type set. Dynamic has a max_types parameter (default 32); the most frequent types get their own Variant slots, and overflow types are stored in a SharedVariant as binary-encoded strings. This provided the flexibility that JSON demands without the unbounded explosion that doomed Object('json').

The PR included 39 commits and introduced the dynamicType() introspection function. A dynamic_structure.bin metadata file per data part tracks the type composition.

These two types, Variant and Dynamic, were the architectural foundation. The JSON type would combine them both.

Phase 4: ClickHouse Native JSON Type Implementation (2024)

The FUD: "ClickHouse doesn't support JSON natively"

How ClickHouse Implemented the Native JSON Data Type: PR #66444 (August 2024)

PR #66444 is the single most important commit in ClickHouse's JSON evolution. Authored by Pavel Kruglov, it implements the entirely new JSON data type in 91 commits, closing RFC #54864 ("Semistructured Columns") authored by Alexey Milovidov.

The design works as follows. JSON paths are flattened into individual Dynamic-typed subcolumns, each stored in separate column files per data part. Paths exceeding max_dynamic_paths (default 1024) overflow into a shared data structure. The type supports:

Full SQL support: Query, filter, and aggregate on any JSON field using standard SQL. Nested structures and arrays are first-class citizens.
Configurable limits: max_dynamic_paths (default 1024) and max_dynamic_types (default 32) control resource usage
Materialized known paths: Type hints like JSON(key1 UInt32, key2 String) materialize known paths as physical typed columns for maximum performance, while unknown paths are automatically created with type inference as they are discovered
Path filtering: SKIP and SKIP REGEXP to exclude noisy paths from columnar storage
Dot-notation access: json.a.b for direct path reads
Sub-object access: json.^prefix for extracting JSON subtrees
Array(JSON) support: Nested structures and arrays of JSON documents
Efficient data skipping on dynamic paths: JSON subcolumns in primary keys and skip indexes enable granule-level pruning

First shipped in ClickHouse 24.8 LTS under allow_experimental_json_type. The official blog post "How we built a new powerful JSON data type for ClickHouse" (October 2024) detailed the architecture.

20x Memory Reduction for Inserts (September 2024)

PR #69272 addressed a critical production concern: memory consumption during JSON inserts. Before this PR, inserting JSON data consumed 6.99 GiB of memory. After, 354 MiB. A 20x reduction.

The fix was adaptive write buffer sizing. Buffers start at 16 KiB and grow exponentially to a maximum of 1 MiB, selectively enabled for dynamic substreams. S3 inserts improved from 23.13 GiB to 7.65 GiB. No throughput regression.

ALTER String to JSON + Serialization V2 (October 2024)

PR #70442 delivered two major changes. First, ALTER TABLE ... MODIFY COLUMN col JSON to convert existing String columns to the JSON type. Conversion happens during background merges, so there is no downtime. Second, Serialization V2 for JSON and Dynamic types with an improved binary layout.

This was the beginning of clear migration paths. Teams no longer had to reimport data to adopt native JSON.

JSONExtract Refactoring for Native JSON (July 2024)

PR #66046 refactored the JSONExtract function family to work with the new type, splitting the implementation into reusable JSONExtractTree.h/cpp components and adding Dynamic type support. This ensured that existing queries using JSONExtract would continue to work when columns migrated to native JSON.

Introspection Functions (August 2024)

PR #68463 added distinctDynamicTypes(), distinctJSONPaths(), and distinctJSONPathsAndTypes(). These are essential schema discovery tools for semi-structured data. They were later optimized in PR #92196 to read only metadata files instead of scanning actual data, making schema diversity views effectively instant.

Subcolumn Optimization Enabled by Default (August 2024)

PR #68053 enabled optimize_functions_to_subcolumns by default. This planner optimization rewrites function calls to read only the specific subcolumns needed, which is transformative for JSON queries. A query accessing json.user.id reads only that subcolumn's data, not the entire JSON column.

Beta Promotion (November 2024)

PR #72294 moved JSON, Dynamic, and Variant to beta status, backported to 24.11. This signaled production readiness for early adopters.

JSON Subcolumns in Primary Key and Skip Indexes (December 2024)

PR #72644 was a milestone for performance. It enabled JSON subcolumns (json.path.to.key) in ORDER BY expressions and data-skipping index definitions. This means ClickHouse applies the same data pruning to JSON fields that it applies to regular typed columns.

The JSONBench benchmark uses this capability for sub-second queries over 1 billion documents. Without it, JSON columns could not participate in ClickHouse's primary mechanism for reducing scan ranges.

Migration Paths from Every Legacy Format

By the end of 2024, clear migration routes existed for every semi-structured representation:

Source Type	Migration Method	PR	Notes
`String`	`ALTER TABLE ... MODIFY COLUMN ... JSON`	#70442	Background merge conversion; `ALTER UPDATE` for immediate
`Map(String, String)`	`CAST(col AS JSON)`	#71320	Serialize-then-parse roundtrip
`Tuple`	`CAST(col AS JSON)`	#71320	Serialize-then-parse roundtrip
`Object('json')`	`ALTER TABLE ... MODIFY COLUMN ... JSON`	#71784	Must complete before upgrading past v25.11
`JSON(params_A)` to `JSON(params_B)`	`CAST` or `ALTER`	#72303	Change max_dynamic_paths, SKIP rules, type hints

Phase 5: ClickHouse JSON Reaches GA -- Performance and Storage Optimizations (2025)

The FUD: "JSON queries require full column scans"

ClickHouse JSON Production-Ready: GA in v25.3 (March 2025)

PR #77785, authored by Alexey Milovidov and expanded by Pavel Kruglov, removed all experimental and beta gates for JSON, Dynamic, and Variant. The commit message references https://jsonbench.com/. Backported to ClickHouse 25.3 LTS via cherry-pick PRs #77974 and #77975.

The 25.3 release blog stated: "About 1.5 years ago, we weren't happy with our JSON implementation, so we returned to the drawing board."

63x Memory Reduction for Read Prefetches (March 2025)

PR #77640 addressed memory consumption during read-ahead prefetches of JSON columns in Wide parts. Before: SELECT * WHERE y=1 on 1 million rows with 1,000 JSON paths consumed 69.16 GiB peak memory. After: 1.11 GiB. A 63x reduction.

4-10x Faster S3 Reads (February 2025)

PR #74827 introduced prefetches for subcolumn prefix deserialization, a cache for deserialized prefixes, and parallel prefix deserialization for JSON columns on S3. The result: 4x faster full scans and roughly 10x faster LIMIT 10 queries on remote storage. This introduced MergeTreePrefixesDeserializationThreadPool and benefits any remote filesystem with similar latency characteristics.

58x Faster Selective Reads: Advanced Shared Data Serialization (August 2025)

PR #83777 is the most impactful storage optimization in the JSON type's history. It introduced three serialization modes for shared data (the overflow storage for paths beyond max_dynamic_paths):

Mode	Read Latency	Memory	Write Cost	Ideal Use Case
`map`	High for subcolumns	High	Low	Writing data, reading whole JSON
`map_with_buckets`	Medium	Medium	Medium	Balanced workloads
`advanced`	Low for subcolumns	Low	High	Reading specific paths

The advanced mode creates per-granule .structure, .data, and .paths_marks files with a path index that enables direct lookup of specific paths without scanning the entire shared data structure.

The benchmarks speak for themselves. Reading a single key from 200,000 rows with 10,000 unique paths improved from 3.63s / 12.53 GiB to 0.063s / 3.89 MiB. That is 58x faster and 3,300x less memory. For Compact parts, non-existing key reads improved from 3.4s to 0.3s (roughly 11x faster), memory from 517 MiB to 3.7 MiB (roughly 140x reduction).

This PR contained 47 commits and is documented in the official ClickHouse blog "Making complex JSON 58x faster, use 3,300x less memory."

Substream Marks in Compact Parts (March 2025)

PR #77940 added marks for individual substreams within compact parts, extending selective subcolumn read efficiency to the compact storage format. Previously, reading any subcolumn from a compact part required reading the entire part.

Experimental Settings Obsoleted (v25.8)

PR #85934 marked the experimental and beta JSON settings as obsolete. JSON was now unconditionally enabled.

Legacy Object('json') Fully Removed (November 2025)

PR #85718 removed the deprecated Object('json') implementation entirely. 270 files changed. ColumnObjectDeprecated, DataTypeObjectDeprecated, deprecated serialization files, the JSONDataParser, and all legacy tests were deleted. This was backward-incompatible: any tables or queries referencing Object('json') must be migrated before upgrading past v25.11.

Phase 6: ClickHouse JSON Query Planner and JSONExtract Interop (2026)

The FUD: "JSONExtract on String columns is the primary approach"

ClickHouse JSONExtract Now Works with Native JSON Columns (2026)

PR #96711, by Fisnik Kastrati, extended all JSONExtract*, JSONHas, JSONLength, and JSONType functions to accept native JSON columns directly. More importantly, it introduced a FunctionToSubcolumnsPass planner optimization that rewrites constant-path JSONExtract calls into direct subcolumn reads.

This means existing queries that use JSONExtractString(json_col, 'user', 'name') now bypass text parsing entirely when the column is a native JSON type. The planner rewrites the call to a direct subcolumn read of json_col.user.name. This closed issue #88370.

Optimized has(JSON, path) Function (2026)

PR #96927 added an optimized has(json_col, 'path') function for fast path-existence checks without text parsing. This is essential for queries that filter based on whether a JSON path exists.

SubcolumnPushdownPass in Query Planner (January 2026)

PR #94105 introduced SubcolumnPushdownPass, which pushes subcolumn requirements through CTEs and views. This means wrapping a JSON table in a view or CTE no longer defeats subcolumn optimizations.

Skip Indexes on JSONAllPaths (April 2026)

PR #98886 enabled bloom and text skip indexes on JSONAllPaths(), allowing efficient filtering on JSON key presence. This gives ClickHouse the ability to skip entire granules when querying for documents that contain (or don't contain) specific paths.

SIMD Tokenizer Refactoring (2026)

PR #97871, by Amos Bird, refactored the tokenizer to a SIMD-ready stateful API, replacing the older iterator API. This lays the groundwork for continued parsing performance improvements.

Combined Subcolumn Access (2026)

PR #98788 introduced a unified combined subcolumn that returns Dynamic for both scalar and object access at a path. This simplifies queries that need to handle paths where the value might be a scalar or a nested object.

ClickHouse JSON Performance Benchmarks: MongoDB, Elasticsearch, and DuckDB Compared

The JSON type's performance has been validated by both ClickHouse's internal benchmarks and independent third-party testing. The numbers come from specific, verifiable sources.

JSONBench: 1 Billion Bluesky Documents (January 2025)

The JSONBench benchmark (https://jsonbench.com/) tested the native JSON type against other databases on 1 billion Bluesky social media documents on a single m6i.8xlarge node:

Comparison	ClickHouse Advantage	Source
vs MongoDB aggregations	2,500x faster (405ms vs ~16 min)	JSONBench
vs Elasticsearch aggregations	10x faster	JSONBench
vs DuckDB/PostgreSQL analytics	9,000x faster	JSONBench
Storage vs compressed files	20% more compact (same algorithm)	JSONBench
Storage vs MongoDB	40% more efficient	JSONBench
Peak memory (1B document count)	< 3 MiB	JSONBench

A follow-up benchmark in March 2025 scaled to 4 billion+ documents (1.6 TiB), achieving 91.84 million docs/sec throughput with sub-100ms queries.

Storage and Memory Improvements

Optimization	PR	Before	After	Improvement
Insert memory	#69272	6.99 GiB	354 MiB	20x reduction
S3 insert memory	#69272	23.13 GiB	7.65 GiB	3x reduction
Read prefetch memory	#77640	69.16 GiB	1.11 GiB	63x reduction
Selective read latency	#83777	3.63s	0.063s	58x faster
Selective read memory	#83777	12.53 GiB	3.89 MiB	3,300x reduction
S3 full scan	#74827	Baseline	4x faster	4x improvement
S3 LIMIT 10	#74827	Baseline	~10x faster	~10x improvement

Third-Party Validation

SigNoz, a ClickHouse-based observability platform, reported 30% faster log queries with the native JSON type. ClickHouse's own observability stack (ClickStack) demonstrated 9x faster queries compared to the previous Map-based approach for OpenTelemetry log attributes.

ClickHouse JSON for OpenTelemetry and Log Analytics: A Real-World Use Case

The observability domain is where the JSON type's impact is most visible. Before native JSON, log management solutions built on ClickHouse flattened attributes into Map(String, String) columns, losing type information. Queries like SUM(LogAttributes.response_size) required explicit casts on every access.

With the native JSON type, OpenTelemetry log attributes preserve their native types. The performance difference:

Dimension	Legacy (Map)	Modern (JSON)	Impact
I/O Efficiency	Read entire column	Read specific path subcolumns	Reduced disk I/O
Memory Footprint	High (String parsing)	Low (Columnar access)	Lower peak memory
Schema Migration	Manual ALTER TABLE	Fully Automatic	Simplified operations
Aggregation Speed	Slow (Cast required)	Native (No cast)	Up to 10x faster queries

ClickHouse JSON Limitations and Trade-offs in 2026

Fairness matters. A few things still require awareness:

Path explosion requires attention. Without appropriate max_dynamic_paths settings and SKIP rules, high-cardinality JSON (thousands of unique paths per document) can create many subcolumns. Set limits that match your schema shape, and use SKIP REGEXP for noisy paths.
Subcolumn pruning through SELECT * in CTEs is not yet supported. Issue #92455 documents this gap. Explicitly name columns in CTEs over JSON tables for now.
Legacy Object('json') migration is mandatory. PR #85718 enforces a hard removal. Post-upgrade to v25.12+, any tables or queries referencing Object('json') will fail. Audit schemas and run ALTER before upgrading past v25.11.
Correctness fixes are ongoing. Edge cases in JSONExtract interop (issue #102018, #102079), default value handling (issue #101721), and specific format combinations (issue #101911) show that a system this complex requires staying on the latest stable release.
Advanced shared data mode trades write cost for read performance. The per-granule path indexes that enable 58x faster reads add write overhead. For write-heavy workloads with infrequent selective reads, the simpler map mode may be more appropriate.
Type hints and path configuration require understanding your data. The defaults work well for moderate schemas (up to 1,024 unique paths). Workloads with tens of thousands of unique paths need tuning.

These are real engineering trade-offs, and understanding them is part of making an informed decision.

ClickHouse JSON Evolution Timeline (2019-2026)

Year	What Changed	Key PRs	Impact
2019	JSONExtract function family with simdjson	#5235	SIMD-accelerated extraction from String columns. Full column scan required.
2021	SQL/JSON standard functions	#24148	JSONPath support. Still string-based storage.
2022	Object('json') first attempt. Format ecosystem.	#23932, #40910, #39186	Proved columnar JSON concept. Architectural flaws identified. Schema inference improved.
2024 H1	Variant and Dynamic types. Compact discriminators.	#58047, #63058, #62774	Type-preserving foundation built. Efficient storage for homogeneous granules.
2024 H2	Native JSON type. 20x insert memory. Serialization V2. Primary key support. Beta.	#66444, #69272, #70442, #72644, #72294	Complete native JSON type ships. Migration paths established. JSON paths in ORDER BY.
2025	GA. 63x read memory. 58x selective reads. S3 optimization. Legacy removal.	#77785, #77640, #83777, #74827, #85718	Production-ready. Advanced shared data. 2,500x vs MongoDB. Legacy Object removed.
2026	JSONExtract interop. Planner intelligence. Skip indexes on paths.	#96711, #94105, #98886, #96927	Full function compatibility. Subcolumn pushdown through CTEs. Bloom indexes on JSON paths.

When Should You Use the Native JSON Type in ClickHouse?

Workload	Verdict	Reasoning
Log and event analytics with semi-structured attributes	Yes	Native type preserves types, subcolumn reads minimize I/O, primary key support enables data pruning
OpenTelemetry / observability data	Yes	Purpose-built for this. ClickStack validates 9x faster queries vs Map approach
JSON documents with known high-value fields	Yes	Use type hints for critical paths, SKIP rules for noisy paths, ORDER BY on key fields
Schema-on-read analytics over heterogeneous JSON	Yes	Dynamic type handles mixed schemas. `distinctJSONPaths()` provides instant schema discovery
Migrating from MongoDB/Elasticsearch for analytics	Yes	2,500x faster aggregations (MongoDB), 10x faster (Elasticsearch). Clear migration via ALTER
JSON with 10,000+ unique paths per document	Depends	Set appropriate `max_dynamic_paths`. Use SKIP REGEXP for noisy paths. Advanced shared data helps but requires tuning
Write-heavy JSON ingestion with rare reads	Depends	Simpler serialization modes (`map`) may be more appropriate than `advanced`
Existing String/Map JSON columns	Yes, migrate	ALTER TABLE converts in background. No downtime. Immediate query performance improvement

How to Respond When Someone Says "ClickHouse Doesn't Support JSON"

Run the PR and benchmark numbers.

When someone tells you ClickHouse can't handle JSON in 2026, ask them if they've tested against a version that includes the native JSON type (GA since v25.3, PR #77785), primary key support for JSON subcolumns (v24.12, PR #72644), advanced shared data serialization (v25.8, PR #83777), or JSONExtract interop with native JSON columns (v26.2, PR #96711).

If they're referencing Object('json'), that type was removed in v25.11. If they're recommending JSONExtract on String columns as the primary approach, the native JSON type has made that unnecessary since v24.8. If they're telling you to flatten JSON into columns manually, the type does this automatically with configurable limits and type hints.

The commit history doesn't lie. 80+ pull requests. Three foundational types. Three generations of storage serialization. Primary key indexing. Planner-level subcolumn optimization. 2,500x faster than MongoDB on real-world data.

ClickHouse's JSON implementation in 2026 bears no resemblance to the string-based functions and experimental Object type that earned those early warnings. The engineers built a ground-up columnar JSON storage system, and the evidence is in the PRs.

Test it on your workload. That's the only benchmark that matters.

ClickHouse JSON FAQ

Does ClickHouse support JSON natively in 2026?

Yes. ClickHouse's native JSON type (PR #66444) stores each JSON path as a separate Dynamic-typed subcolumn in columnar format. It reached GA in v25.3 (PR #77785) with all experimental flags removed. The legacy Object('json') type was fully removed in v25.11 (PR #85718).

What is the most impactful ClickHouse JSON optimization?

Advanced shared data serialization (PR #83777), which delivers 58x faster reads and 3,300x less memory for selective path access. For insert workloads, adaptive write buffers (PR #69272) with 20x memory reduction are equally important.

ClickHouse vs MongoDB vs Elasticsearch for JSON: Which Is Faster?

On the JSONBench benchmark (1 billion Bluesky documents, single node), ClickHouse with the native JSON type is 2,500x faster than MongoDB for aggregations, 10x faster than Elasticsearch, and 9,000x faster than DuckDB/PostgreSQL for analytics. Storage is 20% more compact than compressed files and 40% more efficient than MongoDB.

Should I migrate from JSONExtract on String columns to the native JSON type?

Yes. ALTER TABLE ... MODIFY COLUMN col JSON converts String columns to native JSON during background merges with no downtime (PR #70442). After migration, queries read only the paths they need instead of scanning the full string. JSONExtract functions continue to work on native JSON columns (PR #96711) and get rewritten to direct subcolumn reads by the planner.

What happened to ClickHouse Object('json') and how to migrate?

Object('json') (PR #23932) was ClickHouse's first attempt at native JSON storage, shipped in 2022. It suffered from type unification issues, metadata explosion, and race conditions. Rather than patching it, ClickHouse built an entirely new implementation from first principles using Variant, Dynamic, and JSON types. Object('json') was fully removed in v25.11 (PR #85718). Tables using it must be migrated via ALTER TABLE ... MODIFY COLUMN ... JSON (PR #71784) before upgrading.

Can I use JSON fields in ClickHouse primary keys and indexes?

Yes, since v24.12. PR #72644 enables JSON subcolumns in ORDER BY and data-skipping index expressions. PR #98886 adds bloom and text skip indexes on JSONAllPaths() for efficient key-presence filtering.

How does ClickHouse handle high-cardinality JSON with thousands of paths?

The max_dynamic_paths parameter (default 1024) controls how many paths get their own columnar subcolumn. Paths beyond this limit overflow into shared data storage. The advanced serialization mode (PR #83777) makes shared data reads efficient with per-granule path indexes. Use SKIP and SKIP REGEXP to exclude noisy paths from columnar storage.

Is ClickHouse JSON good for logs and observability?

Yes. ClickHouse's own observability stack (ClickStack) uses the native JSON type for OpenTelemetry log attributes, demonstrating 9x faster queries compared to the previous Map-based approach. SigNoz independently validated 30% faster log queries. The type preserves native numeric types, eliminating cast overhead for aggregations on log attributes.

Analysis based on 80+ GitHub pull requests, official ClickHouse changelogs, release blogs, and third-party benchmarks covering the period 2019-2026. Every claim maps to a specific merged PR. Verify the evidence yourself -- the commit history is public.

Are ClickHouse JOINs Slow? A 2026 PR-by-PR Analysis

Manveer Chawla — Wed, 15 Apr 2026 15:14:34 +0000

TL;DR

Are ClickHouse JOINs slow? Not since 2022. Over 50 merged PRs between 2022 and 2026 rebuilt the join engine from the ground up. The evidence is in the commit history.

We analyzed 50+ GitHub pull requests, official ClickHouse changelogs, and release blogs to trace the full evolution of JOIN support from 2022 through early 2026.
In 2021, the criticism was fair. ClickHouse had one join algorithm (hash join), no disk spilling, no cost-based optimization, and join order followed query syntax. If your right table exceeded memory, the query crashed.
By early 2026, ClickHouse ships six distinct join algorithms, cost-based global join reordering with dynamic programming, runtime bloom filters at the storage layer, parallel hash join as the default, correlated subquery decorrelation, and automatic build-side selection. None of this requires manual tuning.
The single highest-impact change is equivalence-set filter pushdown (PR #61216), which delivered 180×+ speedups by propagating predicates across join sides through column equivalence classes. PostgreSQL and Oracle's planners use the same technique, and ClickHouse implements it natively in its columnar vectorized engine.
Grace hash join (PR #38191) eliminated OOM crashes for memory-bound joins. Parallel hash join (PR #70788) became the default and scales near-linearly across CPU cores. Neither requires configuration.
Global join reordering with column statistics (PR #86822) produces 1,450× speedups on TPC-H SF100 by automatically finding the optimal join order. The DPsize dynamic programming algorithm (PR #91002) further improves this for complex multi-table queries.
Runtime bloom filters, enabled by default since February 2026 (PR #89314), dynamically prune probe-side data at the storage scan level. The v25.10 release blog reports a 2.1× speedup and 7× memory reduction on star-schema workloads.
Verdict: the "avoid JOINs in ClickHouse" advice made sense in 2020. Repeating it in 2026 is misinformation. ClickHouse's join engine now operates with the planning sophistication of a mature enterprise RDBMS, and it does so inside the columnar vectorized execution model that makes ClickHouse fast in the first place.

Why People Still Say "Avoid JOINs in ClickHouse"

If you've evaluated ClickHouse in the last few years, you've heard the warnings:

"Avoid JOINs in ClickHouse"
"ClickHouse doesn't handle JOINs well"
"Denormalize everything, always use flat tables"
"JOINs are slow in ClickHouse"
"Only hash join available, limited join algorithms"

Some of these started as legitimate ClickHouse documentation circa 2019–2020 that advised caution with joins. Others were amplified by competitors who found a convenient story: ClickHouse is fast for scans, but it can't join.

In 2020, the criticism was mostly fair. ClickHouse had a single hash join algorithm, no disk spilling, no cost-based optimizer, and join order followed query syntax. If your right table exceeded memory, the query crashed with OOM.

Then ClickHouse's engineering team spent four years dismantling every one of those limitations. Over 50 significant pull requests merged. They added six join algorithms, a cost-based optimizer with dynamic programming, runtime bloom filters, and automatic algorithm selection, build-side selection, join reordering, and predicate pushdown.

This article traces that evolution with PR-level evidence. No marketing claims. No benchmarks on toy datasets. Just the commit history.

Methodology: How We Analyzed ClickHouse's Join Commit History

We went through ClickHouse's GitHub commit history, pull requests, changelogs, and release blogs from 2022 through early 2026. The scope covered every PR that touched the join subsystem: algorithm changes, optimizer rewrites, planner passes, correctness fixes, and default configuration changes.

Each PR was classified by category (algorithm, optimizer, parallelism, correctness), impact severity, and whether it changed default behavior. We cross-referenced PR descriptions against changelog entries and release blog benchmarks to verify the claimed improvements. Where multiple PRs addressed the same subsystem, we traced the dependency chain to understand how the incremental changes compounded.

The result is a ranked list of 50 pull requests by impact, organized into eight thematic arcs, with full provenance. Every claim in this article maps to a specific merged PR that you can verify yourself on GitHub.

ClickHouse JOIN Features in 2026: What Ships by Default

The current state, as of early 2026:

Six distinct join algorithms: hash, parallel hash, grace hash (disk-spilling), full sorting merge, direct (key-value), and paste join. Each one is optimized for a different workload shape, and the engine selects automatically.
Cost-based global join reordering: Greedy and dynamic programming algorithms find the optimal join order using column statistics. No manual query rewriting needed.
Runtime bloom filters: Build-side join keys compile into bloom filters that get pushed down to probe-side storage scans, filtering non-matching rows before they reach the join.
Equivalence-set predicate pushdown: Filters propagate transitively across multi-table join chains. WHERE t1.id = 5 pushes to t2, t3, and beyond when joined on equivalent keys.
Parallel execution by default: Parallel hash join scales near-linearly with cores. No configuration required.
Correlated subquery support: EXISTS, scalar subqueries, and projection-list subqueries are automatically decorrelated into joins.

These aren't experimental features hidden behind flags. They're defaults that ship with every ClickHouse installation.

ClickHouse JOIN Myths vs. Reality: A 2026 Checklist

#	The FUD	Score	Evidence Volume	Reality (2026)
1	"Only hash join available"	🟢 False since 2022	6 algorithms, 10+ PRs	Six algorithms: hash, parallel hash, grace hash, full sorting merge, direct, paste. Auto-selected.
2	"JOINs cause OOM crashes"	🟢 Solved since late 2022	PR #38191 + follow-ups	Grace hash join spills to disk. Full sorting merge uses bounded memory. OOM joins are a solved problem.
3	"JOINs are slow in ClickHouse"	🟢 Outdated	50+ optimization PRs	180× from predicate pushdown, 1,450× from join reordering, 2.1× from runtime filters, all automatic.
4	"No query optimizer for JOINs"	🟢 False since 2024	PR #86822, #91002, #71577	Cost-based global join reordering with DPsize dynamic programming. Statistics-driven. Automatic.
5	"Always denormalize everything"	🟡 Nuanced	Architecture-dependent	Denormalization still has value for extreme query latency targets, but normalized star/snowflake schemas now perform well with automatic optimizations.
6	"JOINs don't scale across cores"	🟢 False since late 2024	PR #70788, #92068	Parallel hash join is the default. Near-linear scaling. Outer join completion parallelized in 2026.
7	"No predicate pushdown across JOINs"	🟢 False since April 2024	PR #61216, #96596	Equivalence-set pushdown across single and multi-join chains. Disjunctions supported.
8	"Can't handle star/snowflake schemas"	🟢 Outdated	Runtime filters + reordering	Runtime bloom filters and cost-based reordering specifically target star/snowflake schemas.
9	"No correlated subquery support"	🟢 False since mid-2025	PR #76078, #85107	Correlated subqueries decorrelated into joins. Beta since August 2025, enabled by default.
10	"Have to manually tune join order"	🟢 False since mid-2025	PR #86822, #89332, #93912	Automatic join reordering using column statistics and runtime hash table size feedback.

Phase 1 (2022): How Many Join Algorithms Does ClickHouse Support?

The FUD: "ClickHouse only has hash join"

In mid-2022, ClickHouse had exactly one production join algorithm: hash join. The criticism was valid. Then, in roughly six months, three new algorithms landed.

Full Sorting Merge Join: Memory-Bounded Joins (July 2022)

PR #35796 introduced full sorting merge join, a classical sort-merge algorithm integrated into ClickHouse's pipeline. Both sides sort by join keys (with external sorting if needed), then merge in streaming fashion. Memory is bounded by the sort buffer, not by hash table size.

This mattered for two reasons. First, it was the first non-memory-bound join algorithm in ClickHouse, so you could join tables larger than RAM without crashing. Second, it skips sorting entirely when physical row order already matches join keys, which makes it faster than hash join for pre-sorted data.

A follow-up optimization (PR #39418) builds an in-memory key set from the smaller table to pre-filter the larger table before sorting. That made full sorting merge competitive with hash join on general workloads, not just pre-sorted ones.

Grace Hash Join: Disk-Spilling for Out-of-Memory JOINs (November 2022)

PR #38191 was arguably the most important foundational change of this era. Grace hash join partitions both inputs into buckets via a secondary hash. Only one bucket pair is processed at a time, and inactive buckets spill to disk.

Before this PR, a join where the right table exceeded available memory crashed with OOM. After it, the join completed. It just took longer.

Grace hash initially supported only INNER and LEFT joins. FULL and RIGHT support arrived in July 2023 (PR #51013), and a cache locality optimization (PR #72237) delivered a ~24% speedup in late 2024. It graduated to GA in v24.3, which closed issue #11596, the most upvoted join-related issue in ClickHouse's history, open since June 2020.

Direct Join: O(1) Memory Key-Value Lookups (2022)

PR #35363 introduced direct join for EmbeddedRocksDB tables, and PR #38956 extended it to dictionaries with SEMI/ANTI support.

Direct join bypasses hash table construction entirely. It performs O(1) key-value lookups against the storage engine for each left-side row, and memory usage stays constant regardless of right table size.

ConcurrentHashJoin: The Foundation for Parallel Hash Join (May 2022)

PR #36415 laid the groundwork for what would become ClickHouse's most impactful default change. ConcurrentHashJoin creates multiple HashJoin instances, one per thread, and partitions both build and probe sides for concurrent execution. This was the foundation for parallel hash join, which became the default two and a half years later.

By the end of 2022, ClickHouse had five distinct join algorithms where it previously had one. The "only hash join available" criticism had a documented expiration date.

Phase 2 (2023–2024): Does ClickHouse Have a Query Optimizer for JOINs?

The FUD: "ClickHouse has no query optimizer for JOINs"

The promotion of ClickHouse's new query Analyzer to production status in v24.9 was the catalyst. The Analyzer provides richer semantic information about column relationships than the old parser-based planner did, which enabled a class of optimizations that were previously impossible.

Equivalence-Set Filter Pushdown: 180× Speedup (April 2024)

PR #61216 is the single highest-impact join optimization in this entire four-year period. It introduced equivalence-class-based predicate pushdown across join sides.

The logic is straightforward. When tables are joined on t1.id = t2.id, a filter WHERE t1.id = 5 is equivalent to t2.id = 5. The optimizer recognizes this equivalence and pushes the filter to both sides of the join before execution.

Before this PR, filters were applied only after the join completed, which forced full table scans of both sides. After it, filters propagate to both sides and prune data before it reaches the join. Benchmarks show up to 180×+ improvement.

This was later extended to work across chains of multiple INNER JOINs (PR #96596) using a Disjoint Set Union data structure to track transitive equalities. For a query joining t1, t2, and t3 on equivalent keys, a filter WHERE t1.id = 42 now pushes to all three tables.

Automatic OUTER JOIN to INNER JOIN Conversion (April 2024)

PR #62907 automatically converts OUTER JOINs to INNER JOINs when post-join filter conditions make the outer semantics unnecessary. A LEFT JOIN ... WHERE right_col IS NOT NULL is functionally an INNER JOIN, and the optimizer now recognizes this.

This matters beyond the immediate execution improvement (benchmarks show 32s to 0.006s in some cases) because it enables cascading optimizations. INNER JOINs allow predicate pushdown and join reordering that are structurally impossible for OUTER JOINs. Converting the join type first unlocks the full optimization pipeline downstream.

Right-Side Pushdown, OR Conditions, and Common Expression Extraction

The planner intelligence kept accumulating:

PR #50532 extended predicate pushdown to the right side of joins, delivering 27× improvement on applicable queries.
PR #84735 enabled pushdown of OR conditions through joins. Previously only AND conditions could be pushed.
PR #71537 extracted common expressions from WHERE/ON clauses, which reduced redundant hash table instantiation for BI-generated queries with complex OR conditions.
PR #78877 moved equality predicates from WHERE into JOIN ON conditions, enabling more efficient hash table lookups.

Each of these operates automatically. No query hints, and no manual rewriting. The planner just does the right thing.

Phase 3 (Late 2024): Do ClickHouse JOINs Scale Across CPU Cores?

The FUD: "JOINs don't scale across cores in ClickHouse"

Parallel Hash Join Becomes the Default Algorithm (November 2024)

PR #70788 changed the default join_algorithm from 'direct,hash' to 'direct,parallel_hash,hash'. Every ClickHouse installation now uses parallel hash join by default.

The parallel hash join builds hash tables using multiple threads via hash-based sharding. The probe phase shards the same way for lock-free concurrent execution. No configuration needed, and scaling is near-linear with CPU cores.

This was the most broadly impactful default configuration change in this period. Every hash join query on every ClickHouse installation benefits without any user action.

The path to default status was paved by years of incremental improvements. Hash table size statistics caching (PR #64553) pre-allocates tables on repeat queries. Zero-copy block scattering (PR #67782) eliminated redundant memory copies. An adaptive threshold (PR #76185) falls back to single-threaded hash join for small tables where parallelism would add overhead. Two-level hash maps in v25.1 yielded another ~40% speedup.

Parallelizing OUTER JOIN Completion (February 2026)

PR #92068 addressed the last remaining single-threaded bottleneck in parallel hash join. For FULL and RIGHT OUTER joins, the "non-joined rows" (rows from the build side with no match) were previously emitted by a single thread. That created an Amdahl's Law bottleneck that limited outer join scalability. The fix parallelizes non-joined row emission across all hash table buckets.

Phase 4 (2025–2026): ClickHouse Cost-Based Join Optimization

The FUD: "You have to manually optimize join order in ClickHouse"

Automatic Build-Side Selection for Hash Joins (November 2024)

PR #71577 introduced query_plan_join_swap_table = 'auto'. The optimizer estimates table sizes and places the smaller table on the build (right) side of hash joins. This was the first step toward automatic join reordering.

Statistics-Driven Global Join Reordering: 1,450× TPC-H Speedup (v25.9)

PR #86822 introduced global join reordering using a greedy algorithm with column statistics. For queries joining three or more tables, the optimizer evaluates estimated cardinalities and selects the join order that minimizes intermediate result sizes.

The numbers on TPC-H SF100: 1,450× speedup and 25× memory reduction compared to syntax-order execution. That kind of improvement turns "don't use JOINs" into "write whatever join order you want, the optimizer will figure it out."

DPsize Dynamic Programming Join Reordering (v25.12)

PR #91002 added a dynamic programming algorithm (DPsize) for more exhaustive join order search. The greedy algorithm makes locally optimal choices, but DPsize evaluates subsets of joined relations systematically. It produces ~4.7% further improvement over greedy on TPC-H, with bigger gains on complex multi-table queries.

The optimizer tries DPsize first and falls back to greedy if the complexity threshold is exceeded. That's how mature query planners work.

Automatic Statistics Collection for the Join Optimizer

The optimizer is only as good as its statistics. Column statistics moved from manual (ALTER TABLE ADD STATISTICS) to automatic. PR #89332 enabled allow_statistics_optimize by default in v25.10.

Runtime hash table size statistics (PR #93912) close the feedback loop between execution and planning. Actual observed sizes from previous queries inform future optimization decisions.

ClickHouse Runtime Bloom Filters and Star Schema JOIN Performance

The FUD: "ClickHouse can't handle star/snowflake schemas"

PR #89314, merged February 2026, enabled runtime bloom filters by default. During hash table construction, ClickHouse builds a bloom filter from the build-side join keys and pushes it down to the probe-side scan pipeline. Rows that don't match the bloom filter are discarded at the storage scan level, before they ever reach the join.

For star-schema workloads where fact tables are orders of magnitude larger than dimension tables, this is transformative. The v25.10 release blog reports a 2.1× overall query speedup and 7× memory reduction.

The implementation was hardened through 10+ follow-up correctness fixes addressing edge cases: Nullable keys (PR #94555), multi-key ANTI joins (PR #98871), const columns, Merge tables, and more. An adaptive mechanism (PR #91578) dynamically disables bloom filters at runtime when they become saturated or aren't filtering enough rows, which prevents negative ROI on non-selective joins. Coverage was extended to RIGHT OUTER joins (PR #96183).

Runtime filters can also be pushed into PREWHERE (PR #95838), ClickHouse's storage-layer pre-filtering mechanism, for maximum efficiency.

Does ClickHouse Support Correlated Subqueries? (2025 Decorrelation)

The FUD: "ClickHouse can't do correlated subqueries"

This one was true until April 2025. PR #76078 introduced the first correlated subquery decorrelation support, converting EXISTS with correlated references into joins. Scalar subquery support followed (PR #79600), then projection-list subqueries (PR #79925).

PR #85107 promoted correlated subqueries to beta with default enablement in August 2025. That closed issue #6697, one of the longest-standing SQL compatibility gaps in ClickHouse, open since 2019.

Teams migrating from PostgreSQL, MySQL, or Snowflake no longer need to manually rewrite correlated subqueries into explicit joins. The planner does it automatically.

ClickHouse Hash Join Internals: Low-Level Optimizations

Beyond the headline features, ClickHouse's most-used join algorithm received systematic low-level optimization that compounds across every query:

Main loop specialization (PR #82308): Compile-time elimination of null_map and join_mask checks for single-key joins. No more unnecessary branches on every row.
JoinUsedFlags vector optimization (PR #83043): Replaced hash-based flag tracking with atomic vectors, removing per-access hash computation in FULL/RIGHT joins.
Output size enforcement (PR #56996): max_joined_block_size_rows prevents catastrophic memory spikes from ALL JOIN row replication.
Cache locality improvements (PR #60341): Right-table reranging by join keys for cache-friendly access patterns.
Dynamic dispatch (PR #79573): Optimized ColumnVector::replicate in the hash join hot path, lowering CPU per output row.

None of these individually make a press release. Together, they compound into a materially faster join engine at every level of the stack.

ClickHouse JOIN Limitations and Trade-offs in 2026

Fairness matters. A few things still require awareness:

Denormalization still has value for extreme latency targets. If you need sub-10ms p99 on dashboard queries and you can afford the storage, flat tables remain faster than joins. The optimizer is good, but it isn't free.
Join reordering depends on statistics. When statistics are missing or stale, the optimizer can pick suboptimal plans. The system increasingly collects statistics automatically, but monitoring is still your responsibility.
Correlated subqueries are beta. They work for common patterns like EXISTS and scalar subqueries, but edge cases exist. For complex correlated logic, explicit join rewrites may still be necessary.
Grace hash join trades speed for completion. Disk-spilling joins complete instead of crashing, but they're slower than in-memory execution. If you consistently need to spill, you need more memory or a different data model.
Correctness fixes are ongoing. The volume of bug fixes following runtime filter enablement (10+ PRs) shows how complex cross-cutting optimizations get when they're enabled by default. ClickHouse's engineering team has been rigorous about correctness, but running the latest stable release matters.

These are real engineering trade-offs, and understanding them is part of making an informed decision.

ClickHouse JOIN Improvements Timeline (2022–2026)

Year	What Changed	Key PRs	Impact
2022	Algorithm diversification: hash to 5 algorithms	#35796, #38191, #35363, #36415	OOM joins eliminated. Sort-merge and direct join added. Parallel hash foundation laid.
2023	Grace hash FULL/RIGHT. PASTE JOIN. Output size limits.	#51013, #57995, #56996	Disk-spilling joins cover all join types. Safety valves for memory.
2024	Planner intelligence. Parallel hash default. Build-side selection.	#61216, #62907, #70788, #71577, #71537	180×+ from predicate pushdown. Multi-core joins default. OUTER to INNER conversion automatic.
2025	Cost-based optimization. Correlated subqueries. Statistics infra.	#86822, #91002, #76078, #85107, #89332	1,450× from join reordering. Correlated subqueries work. Statistics automatic.
2026	Runtime bloom filters default. Correctness hardening.	#89314, #92068, #96596, #98871	2.1× from runtime filters. Full outer join parallelism. Multi-join pushdown.

When Should You Use JOINs in ClickHouse?

Workload	Verdict	Reasoning
Star/snowflake schema analytics	✅ Yes	Runtime bloom filters, cost-based reordering, and predicate pushdown are purpose-built for this
Multi-table reporting queries	✅ Yes	Global join reordering eliminates the need for manual optimization. Write readable SQL.
Joins exceeding available memory	✅ Yes	Grace hash join and full sorting merge handle this without OOM. Completion is guaranteed.
Real-time dashboards with dimension lookups	✅ Yes	Direct join (O(1) memory) and parallel hash join handle typical enrichment patterns
Time-series ASOF joins	✅ Yes	ASOF JOIN with full_sorting_merge is 2× faster and uses 2× less memory than the hash-based version (PR #55051)
Sub-10ms p99 latency on complex joins	🟡 Depends	Flat tables still win for extreme latency. Joins add overhead even when well-optimized.
Correlated subqueries from legacy SQL	🟡 Mostly	Beta support covers common patterns. Edge cases may need manual rewriting.
10+ table joins with no statistics	🟡 Conditional	The optimizer needs statistics. Make sure `allow_statistics_optimize` is enabled (default since v25.10).

How to Respond to "Avoid JOINs in ClickHouse"

Run the PR numbers.

When someone tells you ClickHouse can't do joins in 2026, ask them if they've tested against a version that includes parallel hash join (default since v24.12), equivalence-set predicate pushdown (v24.4), grace hash join (GA since v24.3), runtime bloom filters (default since v25.10), or cost-based join reordering (v25.9).

If they're benchmarking against ClickHouse 23.x or earlier, or repeating 2020-era blog posts, they aren't evaluating ClickHouse. They're evaluating a system that no longer exists.

The commit history doesn't lie. 50+ pull requests. Six algorithms. Cost-based optimization. Runtime filtering. Automatic algorithm selection, build-side selection, join reordering, and predicate pushdown.

ClickHouse's join subsystem in 2026 bears no resemblance to the one that earned those early warnings. The engineers built a modern join engine, and the evidence is in the PRs.

Test it on your workload. That's the only benchmark that matters.

ClickHouse JOINs FAQ

Are JOINs production-ready in ClickHouse in 2026?

Yes. ClickHouse's join subsystem has been transformed since 2022. Six join algorithms, cost-based global join reordering, runtime bloom filters, and parallel execution all ship enabled by default. The "avoid JOINs" advice is outdated by four years and 50+ merged PRs.

What is the most impactful ClickHouse join optimization?

Equivalence-set filter pushdown (PR #61216), which delivers 180×+ speedups by propagating predicates across join sides. For multi-table workloads, global join reordering (PR #86822) with 1,450× speedup on TPC-H SF100 is equally transformative.

Does ClickHouse still crash with OOM on large joins?

No. Grace hash join (PR #38191) introduced disk-spilling in November 2022 and graduated to GA in v24.3. Full sorting merge join (PR #35796) provides a memory-bounded sort-merge alternative. Both algorithms guarantee completion regardless of data size.

How many join algorithms does ClickHouse support?

Six: hash join, parallel hash join (default), grace hash join (disk-spilling), full sorting merge join, direct join (O(1) memory key-value), and paste join (positional). The engine selects the appropriate one automatically.

Does ClickHouse have cost-based join optimization?

Yes, since v25.9. The optimizer uses column statistics for greedy join reordering and a DPsize dynamic programming algorithm (v25.12) for exhaustive search. Statistics collection is automatic since v25.10 (PR #89332). Runtime hash table statistics (PR #93912) feed execution data back into the planner.

Should I denormalize tables in ClickHouse instead of using JOINs?

It depends on your latency requirements. For sub-10ms p99 dashboard queries, flat tables remain the fastest path. For analytical workloads where query readability, data freshness, and storage efficiency matter, normalized star/snowflake schemas with JOINs are now well-optimized. The "always denormalize" advice is no longer a blanket recommendation.

Can ClickHouse handle star and snowflake schemas?

Yes. Runtime bloom filters (default since February 2026, PR #89314) specifically target star-schema patterns by filtering fact table rows at the storage layer using dimension table keys. Combined with cost-based join reordering and predicate pushdown, star/snowflake schemas are a first-class workload.

Does ClickHouse support correlated subqueries?

Yes, since mid-2025. Correlated subquery decorrelation (PR #76078) automatically converts EXISTS, scalar, and projection-list subqueries into joins. Promoted to beta with default enablement in August 2025 (PR #85107). Closes a feature gap that had been open since 2019.

Analysis based on 50+ GitHub pull requests, official ClickHouse changelogs, and release blogs covering the period 2022–2026. Every claim maps to a specific merged PR. Verify the evidence yourself, the commit history is public.