DEV Community

Manveer Chawla
Manveer Chawla

Posted on • Originally published at dataanalyticsguide.substack.com

Does ClickHouse Support UPDATEs? A 2026 Data Analysis

#sql #dataengineering #database #analytics

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:

  1. The command is persisted (to ZooKeeper for ReplicatedMergeTree, or to a local mutation_*.txt file for non-replicated tables).
  2. 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.
  3. 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.
  4. 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.

Top comments (1)

Collapse
 
toshihiro_shishido profile image
toshihiro shishido •

For analytics workloads, the lack of cheap UPDATEs in ClickHouse is usually a feature, not a bug — append-only fits revenue/session event tables better than mutable rows. The pattern that matters: aggregate sessions and orders into channel-level RPS at query time using window functions, never store the rollup. Mutable rollups are where stale numbers come from. Glad you covered the 2026 state — most blog posts on this are 2-3 years stale.