Updating and deleting data in ClickHouse: which method fits your corrections
Choose the method by how often you correct data and how many rows each correction touches. A few corrections a day across a small slice of a large table point toward lightweight UPDATE and DELETE. Regular corrections that rewrite a whole time range, such as reprocessed or re-keyed records, point toward ALTER mutations or partition drops, which remove or replace whole partitions in one operation. Frequent upserts of the same entity usually belong in a ReplacingMergeTree design rather than in row-level edits. Teams need any of this only when source systems send late corrections, duplicates or deletion requests that must reach analytical history, because most event and telemetry data is append-only by nature.
The reason these choices differ from an OLTP database is storage layout. ClickHouse keeps each column in separate files, organizes data into immutable parts, and merges parts in the background. An in-place change does not overwrite a row. It is recorded and applied later, so the cost of a change depends on how much data must be rewritten and when the merges run. This is why a DELETE on a large table can look instant to a query while the underlying data still exists until merges or mutations complete. Before you choose, confirm with your ClickHouse version's documentation how each statement is applied, whether it is visible to queries immediately, and how it interacts with replicas and backups.
Test the chosen pattern on a copy of production-sized data before it runs against live tables. Measure the rows and parts a correction touches, how long until queries stop seeing old values, and whether the same statement is safe to rerun after a failure. Also confirm how the correction is handled by ClickHouse replication, and make sure your backup schedule covers the window in which a mistaken delete could be applied. Your team should record which method applies to which kind of correction, so that routine fixes do not default to an expensive mutation.
How each update and delete method works
Lightweight DELETE and UPDATE statements mark affected rows with a mask so that later queries skip them, instead of rewriting every data part when the statement runs. Masked rows stop appearing in SELECT results once the statement completes, but their bytes stay on disk until background merges process the affected parts. Storage is reclaimed on a merge schedule, which is why disk usage can stay flat right after a large delete.
- Visibility: Affected rows disappear from subsequent queries once the statement finishes, so application reads see the correction quickly.
- Physical removal: Disk space is recovered only when merges rewrite the affected parts, so do not expect an immediate drop in storage.
- Engine and type support: Confirm which table engines and column types accept lightweight operations in your version, since support varies across MergeTree variants.
- Cost on large tables: Work grows with the number of parts and the volume of data the predicate touches, so measure a broad filter on a copy before running it in production.
- Replicas and backups: Confirm how the statement propagates across replicas and what a backup taken while merges are pending will contain.
Check the documented limitations, supported engines and any mask-related settings for your version in the ClickHouse production FAQ and the current release notes before you write a runbook around these statements.
ALTER TABLE UPDATE and ALTER TABLE DELETE run as mutations, which are asynchronous background jobs that rewrite each data part the statement touches. The ALTER returns before the work finishes, and the new data becomes visible only as parts are replaced. Mutations are built for infrequent, bulk corrections, and they carry a rewrite cost that scales with the data they read.
- Asynchronous execution: The statement returns quickly while the job runs in the background, so confirm completion through the mutation status views before you assume the change is live.
- Rewrite cost: Each affected part is rewritten, so I/O and CPU track the size of the touched data, which can exceed the number of rows actually changed.
- Queue behavior: Mutations are applied in order, and a failing mutation can hold back those queued behind it, so monitor the mutation queue for stuck entries.
- Recovery: A completed mutation cannot be reversed with a second statement, so take a backup or freeze the affected parts before large corrections.
- Replicated tables: Confirm how mutations are coordinated across replicas in your version and what operator action a stalled mutation requires.
Verify the mutation status tables, kill and retry behavior, and the expected rewrite impact for your version in the current documentation before you schedule bulk corrections in a maintenance window.
ReplacingMergeTree keeps one row per sorting key when background merges combine parts, and when you define a version column it keeps the row with the highest version. Until those merges run, older copies remain in storage and can appear in query results. The engine therefore provides eventual deduplication, and correctness at read time depends on how you query the table.
- Visibility: Duplicate rows stay visible until a merge combines them, so aggregates can double count unless queries use FINAL or account for duplicates explicitly.
- Version column: Define the version column so that the latest correction wins during a merge, and confirm the tie-breaking behavior in the documentation for your version.
- Sorting key as identity: Rows are identified by the ORDER BY key, so a correction must reuse the same key values to replace the earlier row.
- Read cost: FINAL performs merge work at query time, and that cost grows with table size, so benchmark it on representative dashboard queries.
- Forced merges: Running OPTIMIZE FINAL on large tables is expensive, so reserve it for planned maintenance rather than a routine job.
Confirm in the current ReplacingMergeTree and FINAL documentation for your version whether a deleted-row marker column is supported, and test your dashboard queries against a copy of the table before you rely on the deduplication behavior.
DROP PARTITION removes every part that belongs to one partition value in a single operation. It is the most economical bulk delete when the partition key matches the deletion boundary, such as a retention period, because the remaining partitions are left untouched. The partition key is fixed when the table is created, so the key has to be settled before data arrives.
- Visibility: Dropped partitions disappear from queries when the statement completes, without waiting for merges to run.
- Scope: Only whole partitions can be removed, so correcting individual rows inside a partition requires one of the row-level methods above.
- Cost profile: Remaining data is not rewritten, which makes the operation far cheaper than a mutation for retention work, but confirm the behavior on replicated tables in your version.
- Safety controls: Check whether drop size limits or confirmation settings apply in your deployment before automating drops.
- Retention alignment: Partitions that match TTL or retention boundaries make deletions predictable, and misaligned keys often push teams back toward mutations.
Confirm the partition drop syntax, replication behavior and any safety settings for your version in the current documentation, and rehearse a drop on a non-production copy before you add it to a scheduled job.
Which method to use for which correction
This table compares five ways to change or remove rows in self-managed ClickHouse by scope, visibility to queries, write and rewrite cost, and typical use. Read each row to see when a correction appears to the next query and when the work lands in background merges. The main takeaway is that row-level erasure, field-level fixes and late-arriving corrections have different costs, so match the method to the unit of change.
| Method | Scope and visibility | Write and rewrite cost | Typical use |
|---|---|---|---|
| Lightweight DELETE | Individual matching rows, hidden from later queries once the statement completes; physical removal waits for merges | Low at statement time; deleted rows stay on disk until merges run | Removing a small set of bad rows or single-subject erasure requests in recent data |
| Lightweight UPDATE | Specific column values on matching rows, visible to subsequent queries | Low to moderate; depends on release and storage behavior, so confirm for your version | Correcting a few fields on known rows, such as a status or misclassified label |
| ALTER TABLE mutations | Any rows matching a predicate across all parts; changes appear part by part as rewrites finish | High; rewrites affected parts in the background and competes with reads and inserts | Bulk corrections or erasure across older data where lightweight options are impractical |
| ReplacingMergeTree with version column | Newest row version wins per sorting key; duplicates collapse during merges, so queries need FINAL or equivalent | Writes are plain inserts; reads pay deduplication cost until merges catch up | Frequent or late corrections to records identified by a stable key |
| Partition drop | Whole partition at once, so the partition key must match the unit of removal | Low; removes data by dropping parts with no row-level rewrite | Retention cleanup, or replacing a full day or month after a bad load |
What makes updates and deletes expensive on large tables
Mutations in ClickHouse rewrite data parts, so their cost follows how much stored data they touch, not how many rows the statement changes. An ALTER TABLE ... UPDATE or ALTER TABLE ... DELETE mutation finds every data part that contains matching rows and writes new versions of those parts without the old values. A single corrected row in a part holding millions of rows still forces the whole part to be rewritten. This is why the cost grows with table size and with how widely the affected rows are spread across partitions and parts. A correction that falls into one recent partition is a very different job from one that matches a few rows in every partition of a multi-year table.
The ordering key and the partitioning key decide how much data a mutation has to read and rewrite. When the WHERE clause of a mutation uses columns that lead the ORDER BY key or the partition key, ClickHouse can narrow the work to fewer parts. When the filter uses a column outside both keys, the mutation scans broadly and touches parts that contain no matching rows at all. Before running a large correction, check the table's ORDER BY and PARTITION BY definitions against the predicate you plan to use, and confirm the expected scope with a SELECT count() over the same filter, grouped by partition. Those two checks tell you more about cost than any general rule. Mutations also queue behind one another on a table, so a backlog of corrections can delay the ones that matter most.
Frequent corrections cost more than the individual statements suggest because each rewrite creates new parts that the background merge process must combine and clean up. Many small mutations on the same data keep that merge work active, consume I/O and CPU that dashboards and ingestion also need, and leave more parts for queries to read until merges catch up. The system.mutations table shows which mutations are still pending and whether they have failed, so it is the first place to look when a correction seems stuck. If your corrections arrive continuously, plan for them in the schema, using patterns such as versioned rows or partition-level replacement, rather than treating each one as a standalone rewrite. Measure the merge backlog in your own environment with your own correction volume before you commit to a design.
Patterns for frequent corrections
Frequent corrections rarely need a single mechanism. Each pattern below trades query complexity, storage growth and merge behavior differently, so the right choice depends on how often corrections arrive, how large the affected data is and how much of it dashboards read. Check each pattern against your own correction volume on a copy of production data before standardizing on one.
Append-only correction rows
Write each correction as a new row with a version or timestamp column rather than changing the original. The original data stays intact for audit, and the latest value is selected at query time. The cost is storage growth and queries that must pick the newest version of each key. Check how many corrections a key typically receives before this pattern gets expensive to query.
ReplacingMergeTree with FINAL
Store corrected rows in a ReplacingMergeTree table keyed by the sorting key, with a version column that identifies the newest row. Deduplication happens during background merges, so duplicates can remain visible until a merge runs. Queries that need exact results add FINAL, which costs CPU on every read. Confirm the sorting key and version column with the ClickHouse documentation before you rely on it.
Partition-scoped rewrites
When a correction affects a known time range, rewrite only the partition that holds it, for example by inserting corrected rows and dropping or replacing the old partition. Work stays bounded to the affected data instead of the full table. Partition boundaries must match the correction window, or the rewrite touches more data than intended. Test the partition expression on a copy first.
Staging and swapping a corrected partition
Build the corrected partition in a staging table with the same structure, verify row counts and checksums against the source, then move it into the live table. Readers see either the old data or the corrected data, never a half-applied state. Plan for the extra storage the staging copy occupies during the build, and keep the old partition until the swap is verified and a backup exists.
Batching corrections
Group many small corrections into fewer, larger writes instead of issuing one statement per row. Each ALTER mutation or lightweight update rewrites or marks data, so batching reduces the number of rewrite passes and the merge pressure they create. The trade-off is latency: corrections wait for the batch window before they become visible. Agree on that delay with the teams that depend on the data.
Keeping corrections out of hot dashboards
Route corrections into a separate ingestion path and promote them to the dashboard tables on a schedule that suits the report owners. Interactive dashboards then avoid FINAL, mutations and duplicate rows during busy hours. The cost is that dashboards can lag corrections, so label the freshness of each dashboard and confirm with its owners which lag is acceptable.
How to plan and test a correction workflow
This sequence turns ad hoc corrections into a repeatable, measured process, so the team knows which method fits each table, what a correction costs in rows and time, and what the restored or corrected data looks like before production changes.
Inventory tables that take corrections
List every table that receives updates, deletes or late-arriving duplicates, and record the source of each correction, how often it happens, and the typical number of rows involved. Include the owner of each table and whether downstream dashboards or exports read it directly. The step is done when the team has a short register with one row per table and a correction frequency it can defend. Confirm the frequencies with the people who file the correction requests, not only with the engineers who run the pipeline.
Pick a method for each use case
For each row in the register, choose between a lightweight DELETE or UPDATE, an ALTER mutation, a ReplacingMergeTree table that deduplicates during merges, or a partition drop when whole time ranges are replaced. Write down the reason for each choice, including how the query side will handle rows that are not yet merged. Check the chosen method against the ClickHouse documentation for your installed version, because statement behavior and visibility rules change between releases. Done when every table has one method, one owner and one written reason.
Build a representative copy
Create a test cluster or a schema-equivalent test database loaded with a representative sample: same table engines, ordering keys, partitioning, and a data slice that includes the largest partitions and the oldest rows. Use the same ClickHouse version you run in production. Run a few queries that production dashboards use so you can compare results later. The check is that row counts and key distributions in the copy match production within the sample you chose, and you can state that sample's date range in writing.
Reproduce a correction and measure it
Run the chosen correction on the copy and record rows affected, elapsed time, and the time until the change is visible to a plain SELECT. Check system.mutations for the status and any failure reason when you use ALTER mutations, and watch merge activity on the affected partitions. Repeat the run at the size of your largest expected correction, not only with a small example. The step closes when you have a measured table of rows, duration and visibility delay for each method and each correction size.
Compare query results with and without FINAL
For ReplacingMergeTree tables, run the same business query with and without FINAL and compare the results against a known-good expected output. Note any difference in totals, counts or latest-version values, and decide which query form the dashboards and APIs will use. Also confirm how the query behaves before a background merge has run. The check is that the team has written, for each table, which query form is correct and why, and that the expected output matches for every correction in the test set.
Verify restore before production runs
Take a backup of the test copy and of the production tables that the correction will touch, using BACKUP to storage you control. Restore the backup to a spare cluster and confirm row counts and a sample of checksums or key queries match. The ClickHouse backup documentation advises regular restore tests because an untested backup may not restore when needed, so this is the point to prove the restore path, not assume it. Done when the restored copy matches the source for the affected tables.
Run the production correction with a rollback plan
Schedule the production change in a window agreed with the table owners, apply the same statement you tested, and watch mutation status, merges, and the query checks you wrote in earlier steps. Keep the pre-change backup available until the owners have signed off on the results. Record the actual rows affected and visibility time next to the test measurements. The step is done when the post-change checks match the expected output and the measurements are filed with the correction record, so the next run starts from evidence.
Security and audit considerations for deletions
Deletions in ClickHouse are easy to run and hard to undo, so the controls around them matter as much as the statement itself. The cards below cover who may run destructive statements, how to see them afterward, what happens between a logical delete and physical removal, and how backups and replicas respond to a mistake.
Limit who can run destructive statements
ALTER, DELETE and DROP PARTITION are granted through users, roles and settings profiles, so only a small operator role should hold them. The default user starts with all permissions, so restrict or disable it before any application or analyst account exists. Check grants with SHOW GRANTS for each service account, and confirm that dashboard and ingestion accounts cannot issue mutations at all.
Trace every mutation in query logs
Mutations and partition drops appear in system.query_log with the user, client address and statement text, which gives you the basis for an audit trail. Confirm that query logging is enabled on every replica, since each node records only the queries it receives. Ship these records to storage outside the cluster, because a compromised operator can otherwise alter the same tables they used to delete data.
Separate logical deletion from physical removal
A lightweight DELETE hides rows at once, but the bytes can remain on disk until parts are rewritten, and an ALTER mutation rewrites affected parts asynchronously. Track mutation progress in system.mutations until each one completes, and do not treat a query returning no rows as proof that data is gone. Ask your compliance owner which removal standard applies, then verify it against the part files and any frozen backups.
Account for backups before deleting
A BACKUP taken before a deletion still holds the deleted rows, so retention and erasure policies must cover backup sets as well as live tables. Define who may restore, how long backups are kept and how restored data is re-deleted if it must not persist. The backup documentation warns that a backup never test-restored will probably not restore properly when needed, so schedule restore tests on a spare cluster.
Expect replicas to copy mistakes
ReplicatedMergeTree keeps replicas in sync, so a mistaken DROP PARTITION or mutation is applied on every healthy copy within minutes. The replication documentation's point holds here: replication does not protect against human error or corruption. Gate destructive statements behind a change ticket, run them first on a non-production replica set with the same schema, and rely on backups, not replicas, as the recovery path.
Prepare evidence for auditors
Assessors typically ask for the deletion procedure, the list of accounts able to run it, logs of past runs and proof that restores were tested. DeployOpen can help assemble and configure these controls and evidence, but it does not issue audit reports, attestations or certifications. Confirm with your auditor which records they will accept, and agree with them in advance how long query log and backup evidence must be kept.
Questions teams ask before changing data in ClickHouse
Is DELETE in ClickHouse safe for GDPR erasure requests?
Yes, a lightweight DELETE or an ALTER mutation removes the rows from query results, but the erasure is only complete once the change has been applied to every replica and every copy of the data has been handled. Lightweight deletes mark rows and are cheap to issue, while mutations rewrite affected data parts and cost more on large tables. The team must also account for backups, because a restore from an older backup can bring erased rows back. Ask your compliance lead to define what counts as erased for your records, then document how deletes, replicas and backup retention work together. Our compliance guidance on erasure and retention explains that evidence in more detail.
How long does a correction take to appear in query results?
It depends on the method. A lightweight UPDATE or DELETE is visible to new queries once it completes, but mutations run asynchronously, so a large ALTER can take a long time to finish across all parts. ReplacingMergeTree deduplicates only during background merges, so older versions can remain visible until a merge happens or a query uses FINAL. The main drivers are table size, the number of affected parts, merge activity, replica lag and the concurrent insert load. Measure completion on a copy of your data using the system tables that track mutations before you commit to a schedule.
Is OPTIMIZE FINAL required after a correction?
No, it is not required, and running it routinely is usually a poor choice. OPTIMIZE FINAL forces a merge of many parts, which consumes significant CPU and IO on large tables and can slow ingestion and dashboards while it runs. Most teams instead rely on background merges and use the FINAL modifier in queries where exact results matter, accepting the extra read cost. Reserve manual merges for specific partitions after a planned backfill, and test the impact on a staging copy first. Confirm the behavior against the ClickHouse documentation for your release.
What does the internal team need to run correction workflows?
The team needs working knowledge of MergeTree tables, ordering keys and partitions, plus the ability to read system tables that show mutation and merge progress. Engineers should also own the correction contract, meaning which tables accept updates, which use deletes or partition drops, and who approves a mutation on production data. Operations staff need monitoring for stuck mutations and replica lag, and a tested restore path. Access rights should be scoped so that ad hoc ALTER statements do not run under a broad default user. Plan time for these skills before the first production correction, not during an incident.
What drives the effort of a correction design?
The effort depends on how many tables receive corrections, how often corrections arrive, and how large those tables are. A design that uses ReplacingMergeTree with a version column is simpler than one that mutates history in place, but it requires careful choice of the sorting key so duplicates are identified correctly. Partition drops are fast for bulk retirement but only fit data organized by time or tenant. Other cost drivers include the number of downstream materialized views that must be rebuilt, the replica count, backup retention rules and the amount of testing against representative data. Ask any provider to itemize the design work by table group, so you can see what scales with data volume and what is fixed.
Can our team handle corrections alone, or do we need a partner?
A capable platform team can handle simple corrections alone, especially if the data model is stable and changes are rare. A partner adds the most value when the design spans many tables, when corrections touch regulated personal data, or when the team lacks time to build tests and runbooks. Consider the cost of learning on production data against the cost of a fixed-scope engagement that delivers the design, the tested procedures and the handover. Engagements are usually agreed in writing before work starts, so ask for the deliverables, the test evidence and the operational documentation in any quote.
How do backups fit into a change or deletion plan?
Backups are the recovery path when a correction goes wrong, and replication does not provide that protection. ClickHouse BACKUP and RESTORE can take full or incremental backups of tables, partitions or databases, and the backup documentation notes that replication alone does not protect against human error or corruption. Take a backup or a frozen snapshot before large mutations or partition drops, and make sure retention rules do not conflict with erasure obligations. Most importantly, restore to a spare cluster on a regular schedule, because an untested backup often fails when it is needed. The backup documentation recommends exactly this practice.
How DeployOpen scopes and delivers ClickHouse correction workflows
DeployOpen plans, implements, tests and documents correction workflows for self-managed ClickHouse inside your own infrastructure. Engagements are fixed-scope, with the written scope fixing which tables, correction patterns and tests are covered before work starts.
How we scope
- Inventory which tables receive corrections, how often, and whether rows change in place or arrive as later versions.
- Map each correction type to lightweight DELETE, ALTER mutations, ReplacingMergeTree or partition drops, with visibility and cost notes.
- Agree in writing which replicated tables, retention rules and backup dependencies the correction design must respect.
How we build
- Build and benchmark each correction pattern on a copy of production-shaped data before any change touches live tables.
- Set up ingestion patterns for corrections, including version columns and query-time handling of duplicate rows.
- Validate that corrections replicate correctly across ClickHouse Keeper-coordinated replicas and that backups capture the result.
How we hand over
- Document each correction runbook, including which statements are safe to run during business hours and which need a window.
- Provide monitoring checks for stuck or long-running mutations and for merges that have not yet removed superseded rows.
- Run a handover session with your operators and record the restore test results against the backup design.
How we support
- Review the correction design when schemas, query patterns or ClickHouse versions change, against the release notes you plan to adopt.
- Available as a retainer for ongoing support, scheduled upgrades and periodic restore tests after handover.
- Ask for the support scope, response expectations and change control terms in the written quote before signing.
