GDPR erasure in ClickHouse: delete and prove it

How ClickHouse helps you handle GDPR erasure requests in your own infrastructure

An analytics team holding event or log rows that contain personal data, with an erasure request and a deadline on the table, needs to know two things first: which rows must go, and how the team can show they are gone. ClickHouse offers DELETE mechanisms, replication and backup primitives that support erasure, but the controller must design the retention rules and the deletion scope. GDPR requires no certificate for this work, so the evidence you produce is the record that matters. The regulation's erasure right sits in Article 17, and the security of processing duties in Article 32 apply to every copy of the data your erasure process touches.

ClickHouse does not decide which rows belong to a data subject, which copies must be reached, or how long a deletion may take. Your team must map where personal data lands, including raw event tables, materialized views, aggregates, backups and replicas, and decide which of them an erasure request reaches. You also need a documented position on whether a restored backup that still holds an erased row counts as a breach of the request, and how that is handled. Confirm these decisions with your legal and data protection leads before the first request arrives, because the time to answer a request is not the time to define its scope.

The practical work starts with the table design. Keying tables so a subject's rows can be located cheaply, and keeping personal identifiers in a small, separately managed table where possible, makes erasure a bounded operation instead of a full-table rewrite. Then test the chosen DELETE approach on a pre-production copy with representative data, confirm which system tables and merges still hold the old values, and record the steps and timings. Review the current ClickHouse documentation for the exact behavior of each DELETE statement in your target version, because the details of physical removal and replica propagation are version-dependent and should be verified rather than assumed.

Mapping GDPR obligations to ClickHouse capabilities and configuration

This table maps the GDPR obligations that shape an erasure workflow (Articles 17, 32 and 83 exposure) to documented ClickHouse capabilities and the settings your team must choose. Rows marked for confirmation depend on the vendor documentation for the exact version you deploy. Read each row as a question your design must answer with evidence, not as a claim that the feature alone satisfies the regulation.

Requirement or workflow step	Documented ClickHouse capability	Configuration the team must set
Erasure workflow: find and remove a data subject's rows across all copies	Lightweight DELETE and ALTER DELETE act on table data, but backups and replicas hold their own copies that the delete does not reach	Maintain a map of tables and partitions holding subject identifiers; define a deletion job per table; confirm delete semantics for the chosen version with the vendor documentation
Security of processing (Article 32): resilience of processing systems	ReplicatedMergeTree coordinated by ClickHouse Keeper; a recovered replica automatically syncs missing data from a healthy replica	Run at least three Keeper nodes on dedicated hosts in production; confirm replica sync behavior for your version
Security of processing (Article 32): restore after incident	BACKUP and RESTORE support full and incremental backups; the documentation warns replication does not protect against human error or corruption	Schedule backups that exclude or expire erased subject data where policy allows; run restore tests on a spare cluster before relying on backups
Security of processing (Article 32): access to personal data	Users, roles, row policies and settings profiles; the default user starts with all permissions	Restrict or disable the default user; grant erasure jobs a dedicated role; use row policies where access must be limited by subject or tenant
Security of processing (Article 32): strong authentication	External authenticators including LDAP, Kerberos, SSL X.509 certificates, HTTP authenticator and JWT	Bind operator and service accounts to your identity provider; confirm the authenticator options supported by your release
Controller exposure under Article 83: documented and provable compliance	Access control and replication are configuration facts; they do not produce an erasure record on their own	Log each erasure request, scope, executed statements and completion time; retain evidence outside the cluster; confirm audit output with the vendor documentation
Supported version for security updates	The ClickHouse security policy lists supported versions; all other versions are unsupported	Confirm the supported version list on the security policy at deployment time and plan upgrades before a line falls out of support

Choosing between lightweight DELETE and ALTER DELETE for erasure

Erasing one data subject from ClickHouse can be done several ways, and each leaves a different amount of data on disk and costs a different amount of work. The cards below compare the options and the table design choices that make erasure cheaper, so you can match each mechanism to the removal time you can prove.

Lightweight DELETE

A lightweight DELETE FROM marks matching rows as deleted so queries stop returning them quickly, while the bytes stay in data parts until background merges rewrite those parts. It is the natural first choice for small, frequent erasures. Confirm in your target version when the marked rows are physically gone, because a logical delete alone does not meet a removal claim.

ALTER DELETE mutations

ALTER TABLE ... DELETE is a mutation that rewrites every affected data part in the background. It physically removes rows, but the cost scales with the number of parts that contain the subject's data, not with the rows removed. Run mutations in batches, monitor the mutation queue, and record the completion time as evidence for each request.

Partition drops

If subject data sits in a partition defined by a column such as month or tenant, dropping that partition removes whole parts without rewriting them. This is cheap and clearly complete, but it only works when the partition key matches erasure scope. A partition that mixes many subjects cannot be dropped for one of them, so check the key before you rely on it.

Ordering keys that localize subject rows

When the primary key begins with a subject or tenant identifier, a mutation touches fewer granules and fewer parts. Subject rows scattered across the whole table make every erasure a near full-table rewrite. Test the key against your real query patterns, because an ordering key chosen only for erasure can slow the analytical queries the cluster exists to serve.

Pseudonymous subject keys

Storing a surrogate key in analytical tables and keeping the mapping to identity in a separate, narrowly controlled table lets erasure delete or overwrite one small mapping row. The event data can then stay largely intact, though whether pseudonymized data falls outside the regulation depends on your own assessment. Document that reasoning with your privacy team.

Erasure batching and verification

Erasure requests arrive individually, but mutations are costly, so batch several subjects into one mutation where your process allows it. After each run, query system.mutations and confirm completion for every subject ID. Ask your integrator to show this verification query, its output and the mutation history as part of the erasure runbook.

Forcing physical removal so erased rows leave disk, replicas and backups

A logical delete in ClickHouse does not mean the erased rows are gone from storage. Lightweight deletes and mutations mark or rewrite data inside existing parts, and the old bytes can remain on disk until background merges rewrite those parts. The same applies on every replica, because each ReplicatedMergeTree replica holds its own copy of the parts, and the data also persists in any backup taken before the delete. Your erasure procedure therefore has to cover four places: the table on each replica, the merge state of the affected parts, the backups, and the evidence that you checked all of them.

Start by confirming, for your exact version, how the delete was applied and whether the affected parts have been rewritten. Check the ClickHouse documentation for your release on lightweight deletes and mutations, and on the commands that force a merge or part rewrite, before you run anything in production. Then verify removal on every replica rather than on the one you queried. The replication guide says a recovered replica syncs missing data from a healthy replica automatically, so a replica that was offline during the erasure will eventually converge on the post-delete state, but you should check that it has done so and not assume it. Record the query and the result for each replica as part of the erasure record.

Replication does not protect against human error or corruption, so erasure has a direct effect on backup retention. The ClickHouse backup documentation makes this point, and it means a backup taken before an erasure request still holds the subject's rows. Decide, with your privacy and legal owners, how long pre-erasure backups may be kept and how they are expired or rebuilt, and document that decision. Restore tests should confirm that a restored environment does not reintroduce erased subjects. The backup documentation also warns that a backup never test-restored will probably not restore properly, so use those tests to check your erasure controls too. Confirm the exact backup and restore syntax for your version before you write the runbook.

Configuration sequence for a provable erasure process

This sequence turns erasure from an ad hoc query into a repeatable, tested process: you know which tables hold a subject's data, how cheaply they can be found, who may delete it, and what evidence proves the rows are gone from every replica and from backups.

Inventory personal data by table
The data protection owner and the ClickHouse administrator list every table, materialized view, Distributed table and downstream copy that can hold personal data, with the subject identifier each one uses (user ID, email hash, device ID or account number) and the lawful basis and retention period recorded for it. Include tables fed by Kafka or PostgreSQL engines, since those pipelines can write personal data into ClickHouse too. The output is a signed register that the erasure runbook references. Confirm the register against the actual schema with SHOW CREATE TABLE on every node, not against the design documents.
Set ordering keys and partitions for subject lookup
For each table in the register, the ClickHouse engineer checks whether the subject identifier is part of the ORDER BY key or the partition key. An identifier that leads the sorting key lets ClickHouse skip most granules when it searches for one subject, which keeps deletions cheap. Where subjects are spread across many partitions, decide whether a skip index on the identifier is worth its storage cost, and test it. The check is complete when an EXPLAIN of a lookup query on a representative table shows the expected index use. Changing an ordering key on a populated table is a migration, so make this decision before data volume grows.
Restrict DELETE rights with roles and a restricted default user
The platform administrator disables or restricts the default user, which starts with all permissions, and creates named users assigned to roles. Only a small erasure role receives the ALTER DELETE and lightweight DELETE privileges, and that role is granted to named operators rather than to applications. Settings profiles and quotas limit what the erasure role can do in a single session. The access control documentation describes this SQL-driven workflow. The check is an exported grant list, reviewed by someone other than the person who made the grants, showing that no general-purpose account can delete rows.
Run a test erasure on a pre-production cluster
The team loads a copy of production-shaped data into a pre-production cluster, runs the erasure procedure for a set of test subjects, and records the exact statements, the time to completion and the row counts before and after. The ClickHouse production guidance recommends a pre-production environment with representative data and queries before changes reach production. Run the test once with lightweight DELETE and once with ALTER DELETE for each table type, then choose the method per table and document why. The output is a test record that a reviewer can repeat on the same data.
Verify removal across all replicas
After each erasure, the operator queries every replica and every shard directly, not only through a Distributed table, and confirms that the subject identifier returns zero rows. ReplicatedMergeTree replicates the mutation, but the operator should confirm that replicas have caught up and that no mutation is stuck, using the system tables that show mutation status. A replica that was offline is expected to sync from a healthy replica when it recovers, so the check should be repeated after any node outage. Store the query output, timestamps and the node list as the erasure evidence for that request.
Document backup retention and restore-test handling
Backups taken before an erasure still contain the subject's rows, because BACKUP captures the data as it existed at that time. The data protection owner and the backup administrator decide, in writing, how backups are retained, how long erased data may persist in them, and what happens if a backup is restored and the subject must be erased again. Restore tests run on spare clusters should include a step that reapplies pending erasures after restore. The ClickHouse backup documentation recommends regular restore tests, so the record should show that a restored copy was checked for erased subjects. Confirm the retention position with counsel before the process goes live.

Evidence to keep so you can show an erasure was completed

An erasure is only as defensible as the records behind it. The cards below cover the artifacts that together show a request was received, the rows were removed, the change reached every replica, and the removal survived backups. Keep them together, keyed to the request reference, so an assessor can follow one request from start to finish.

Erasure request log

Record each request with its reference, the date received, the identity check performed, the tables and partitions in scope, the decision on any exemption, and the date the erasure was confirmed to the data subject. This is the document an assessor reads first, so it should link to every other card on this list. Check that the log is written by the team that runs the deletion, not reconstructed afterwards from memory. Store it outside the ClickHouse cluster so a cluster failure does not remove it.

query_log entries for DELETE statements

The system.query_log table records each DELETE or ALTER DELETE statement with its text, user, start time and outcome. Use it to prove which statement ran and when, and that it finished without an exception. Query log rows are kept only as long as your retention settings allow, so export the entries for each erasure to durable storage before they age out. Masking of sensitive values in these logs is covered in the sibling audit logging topic.

Access-control configuration exports

Export users, roles, row policies and grants on a schedule, and keep each export with its date. Erasure work usually runs under a specific role, so the export shows who was allowed to delete and whether the default user was still unrestricted at the time. Store exports under version control so changes between them are visible. A diff between two exports is often the quickest evidence that access was tightened.

Replica verification results

After a deletion, check that each replica holds the same row counts and the same mutation status for the affected tables. Record the query, the host list and the result. A ReplicatedMergeTree replica that was offline during the delete catches up automatically when it recovers, so the verification should be run after recovery, not during an outage. Keep the output with the request reference so the claim that erased data is gone from every copy in the cluster can be checked.

Backup retention records

Backups keep personal data after the live rows are deleted, so record which backups contain the subject's data, their creation dates and the retention rule that will remove them. Document how the team handles restoring a backup that predates an erasure, including a step that reapplies the erasure before the data is used. Your retention decision for backups should be written down and approved, since the backup documentation notes that replication alone does not protect against human error.

Restore-test results

Keep the record of each restore test on a spare cluster: the backup used, the restore duration, the row counts checked and whether the erasure was reapplied after restore. The backup documentation warns that a backup never test-restored will probably not restore properly when needed, so these records show the backups are usable. Retain the results alongside the backup retention records so a restore after an erasure is traceable end to end.

Handling erasure across replicas, backups and restore tests

Erasure on a live cluster has to reach every replica of every affected table, not only the node that accepted the statement. ClickHouse replicates data with ReplicatedMergeTree tables coordinated by ClickHouse Keeper, and a recovered replica automatically syncs missing data from a healthy replica. That means a delete applied consistently across the replicated set is the state the cluster converges to, but you must verify it rather than assume it. Confirm that the mutation has finished on every replica of each shard, that no replica is lagging or out of sync, and that the cluster is not in a degraded state when you record completion.

Mutation completion: Check the mutation status on each replica until every part affected by the erasure has been rewritten.
Replica health: Confirm each replica is active and its replication queue is empty before you close the request.
Distributed tables: Verify that queries through Distributed tables return no rows for the subject on every shard.
Keeper state: Record that Keeper quorum was healthy during the operation, since replication depends on it.
Evidence to retain: Keep the mutation IDs, timestamps, per-replica query results and the operator who ran each step.

Check that a replica that was offline during the erasure cannot later reintroduce the deleted rows when it returns.

What ClickHouse and this page do not cover for GDPR

Deleting rows from ClickHouse is only one part of meeting GDPR obligations. The decisions around it sit with the controller, and several of them depend on facts about your data that no database setting can settle. The cards below mark where that responsibility starts and where it ends.

Legal basis and retention decisions

Whether a row must be erased at all depends on the lawful basis you rely on and on the retention period your organization has documented for that processing. A legal obligation to keep records, or a fraud or security interest, can justify keeping data that a request asks you to remove. Confirm each event table's basis and retention rule with your data protection officer before you write any DELETE statement. ClickHouse will run whatever deletion you specify, so the decision has to be made upstream and recorded.

Deciding which requests are valid

Not every message from a data subject is a valid erasure request, and not every valid request must be fulfilled in full. Identity verification, the scope of the request and any exemptions all shape the response. Your support or privacy team owns this triage, and the database team should receive a clear instruction with the identifiers to remove. Keep the intake and decision record separate from the ClickHouse tables so the evidence trail does not depend on the data you are deleting.

Data in downstream systems and exports

A ClickHouse erasure does not reach copies held elsewhere. Dashboards built on exported CSV files, data sent to a SIEM, Kafka topics, object storage used for data lake queries and analyst workstations can all keep personal data after the table is cleaned. Map every pipeline that reads from the tables and decide how each copy is erased or expired. Ask your team to list those destinations before the first erasure run, because the gap is often in an export nobody remembers creating.

Transfers under Chapter V

If your ClickHouse cluster, its backups or its operators sit outside the EEA, or if data is sent to a third country, Chapter V of the GDPR (Articles 44 to 50) applies to those flows. Erasure must also reach replicas and backup copies in those locations. This page does not assess transfer mechanisms or adequacy for any specific country. Confirm where every replica, backup target and remote administrator is located with your legal team before you choose a topology.

Pending Omnibus proposals

The GDPR text has not been amended as of October 2026. Two proposals are in progress: Omnibus IV (procedure 2025/0130(COD)), where a text was agreed in interinstitutional negotiations on 26 June 2026 and awaits Parliament's first-reading vote, and the Digital Omnibus (procedure 2025/0360(COD)), which awaits committee decision. Either could change obligations that affect erasure workflows. Check the Legislative Observatory status before you finalize a runbook, and schedule a review of the design once a final text is adopted.

No certificate from ClickHouse or DeployOpen

Neither ClickHouse nor DeployOpen issues audit reports, attestations or certificates for GDPR compliance. The GDPR requires no audit or certificate at all. Certification under Article 42 is voluntary, is issued for at most three years by accredited certification bodies or supervisory authorities, and does not reduce the controller's or processor's responsibility. Treat the procedures and evidence described here as inputs to your own compliance program, and ask your certification body or auditor what they will accept.

Questions buyers ask about GDPR erasure in ClickHouse

What does GDPR erasure work in ClickHouse cost, and what drives it?

Cost depends on how many tables hold personal data, how those tables are partitioned and ordered, how many replicas and backup copies exist, and whether an erasure procedure already exists. The work is usually a one-time design and build effort (data mapping, table changes, a tested deletion runbook, and backup handling) followed by smaller recurring costs for running requests and reviewing evidence. Ask any partner for a fixed-scope quote built from written deliverables, and confirm which items are one-time and which recur, such as retainer support for request handling. DeployOpen does not publish prices, so the quote is the source for figures.

How long does a scoped ClickHouse erasure implementation take?

Duration depends on the number of tables and data flows in scope, how much schema change the existing tables need, and how many environments must be tested before production. Teams usually lose the most time on data mapping and on agreeing how backups are handled, not on the SQL itself. No duration figure is given here; ask for a timeline in the written scope that lists the environments, the test restores and the sign-off steps it covers.

What does our team need to provide for an erasure project?

Expect to provide the following before design starts:

A current inventory of tables and columns that hold personal data, with the identifier used to link rows to a data subject.
Access to a pre-production cluster with representative data and the queries that read it.
Your retention rules, backup schedule and the locations where backups are stored.
A named owner for privacy requests and for approving changes to production tables.

Confirm these items in the scope so that delays are not billed as change requests.

Can our own engineers handle GDPR erasure in ClickHouse in-house?

Yes, if your team already runs ClickHouse in production and has people who understand ReplicatedMergeTree, mutations and backups. The harder parts are the design decisions: choosing partition keys that make erasure cheap, deciding how replicas and backups are cleaned, and proving the outcome to an auditor. Many teams keep ownership of request handling and use a partner for the one-time design and test restores. Decide this based on skills you can staff over time, and write down which parts you will own after handover.

Can a restore from backup undo a GDPR erasure?

It can, and this is one of the most common failure points. The ClickHouse backup documentation notes that replication alone does not protect against human error or corruption, and a restore brings back the data as it was when the backup was taken. A backup that contains an erased subject's rows therefore needs a documented rule: either the erasure is replayed after restore, or backups are rotated so that personal data does not survive beyond the agreed window. Test this with a restore on a spare cluster, and record the result.

Does ClickHouse certify GDPR compliance?

No. ClickHouse is open-source software under the Apache License 2.0, and the GDPR itself requires no audit, attestation or certificate. Certification under Article 42 is voluntary, issued by accredited bodies or supervisory authorities for at most three years, and it does not reduce the controller's or processor's responsibility. Compliance comes from your processes, configuration and evidence. DeployOpen prepares systems, controls and evidence for compliance assessments but does not issue audit reports or certifications.

Do GDPR rules for erasure change soon?

The GDPR text has not been amended as of October 2026. The Omnibus IV proposal (procedure 2025/0130(COD)) has a text agreed in interinstitutional negotiations on 26 June 2026 and is awaiting Parliament's first-reading vote, and the Digital Omnibus proposal (procedure 2025/0360(COD)) awaits committee decision. Regulation (EU) 2025/2518 on cross-border enforcement procedure applies from 2 April 2027. Ask your counsel to confirm the current position before fixing retention periods in design documents.

Official sources for GDPR and ClickHouse erasure controls

Start with the regulation itself. The *GDPR* text on EUR-Lex is the only source that defines what an erasure obligation requires of a controller, and Article 32 on security of processing is the provision your ClickHouse design has to satisfy alongside it. Read the erasure and data subject rights provisions with your data protection lead, then map each obligation to a concrete table, partition or backup location. The regulation does not prescribe a ClickHouse technique, so the mapping is your responsibility and should be written down before the first deletion job runs.

For the ClickHouse side, the backup and restore documentation is the most important read. It states that replication does not protect against human error or corruption, which means a deleted row can survive in a backup taken before the delete. Confirm how your chosen version handles deletes in frozen parts, backups and replicas, and decide how long pre-erasure backups are kept and how they are restored without reintroducing erased rows. The backup and restore documentation is the natural starting point for that decision.

Next, read the replication guide to confirm how a delete propagates through ReplicatedMergeTree tables coordinated by ClickHouse Keeper, and the access control documentation to restrict which roles can run DELETE and ALTER DELETE statements. Behavior around mutations, merges and deletion tracking has changed across releases, so test each step on the exact version you run in production and record the result as evidence. Treat the documentation as the reference for the version it describes, and confirm version-specific behavior against the release notes for your build before you rely on it.

Handling GDPR erasure requests in ClickHouse