How ClickHouse TTL enforces retention without a cleanup job
A team holding event, log or telemetry data in self-managed ClickHouse that must be kept for a fixed period and then removed should start with TTL, the built-in mechanism for scheduled expiry of rows and columns. A TTL rule attaches an expiry condition to a table, based on a date or timestamp column, so that data past its age is deleted without a separate cron job or application-side purge. Teams usually need this for three reasons: retention policies set by the business or a regulator, storage limitation duties under the GDPR (which requires personal data to be kept no longer than necessary), and cost control on disks that fill up with data nobody queries anymore. The detail that matters most for planning is timing. ClickHouse applies TTL rules during background merges of data parts, so expired rows disappear when a merge touches them, not at the instant they expire. Expiry is therefore scheduled and eventually consistent, and the lag depends on merge activity, part layout and the table's settings. Anyone promising a deletion deadline to an auditor needs to measure that lag on their own cluster under realistic write volumes. Before writing any rule, confirm the exact syntax and behavior against the ClickHouse TTL documentation for the release you run. That page is not among the sources this section draws on, and TTL details such as merge controls and how expired data is handled can change between releases. Check the ClickHouse production FAQ for your version policy, then test the rule on a pre-production copy with representative data before it touches production tables.
Which TTL rule fits each retention requirement
This table compares the four TTL forms a team can choose between in self-managed ClickHouse: row deletes, single-column clears, conditional deletes and partition-aligned rules. Read each row for what the rule removes, what it leaves behind and the retention requirements it fits, then confirm exact clause behavior in the documentation for your release before you depend on it.
| TTL form | What it removes or leaves | When it fits |
|---|---|---|
| Row-level delete TTL | Removes whole rows once their time expression passes the threshold; the remaining rows and parts stay in place until merges rewrite them | Uniform retention for an entire table, such as keeping event records for a fixed policy period |
| Column-level TTL | Clears the values of one column after expiry while the row and its other columns remain | Sensitive or bulky fields, such as personal identifiers or payloads, that must expire earlier than the rest of the record |
| Conditional delete TTL | Removes rows only when a column value or expression meets a condition, so the rule depends on data content | Mixed-purpose tables where categories such as account closure or consent status set different lifetimes |
| Partition-aligned TTL | Ties expiry to partition boundaries so whole partitions can be dropped instead of rewritten row by row | Time-partitioned tables where retention periods are set in whole days, weeks or months and cleanup must be predictable |
| Merge-dependent expiry | Expired data may remain readable until background merges process the affected parts, so removal timing is not instant | Any schedule where auditors ask for a removal date; document the merge settings and verify with test data |
| Rule interaction with backups | Expired rows are gone from the live table but may persist in existing backups until those backups are themselves retired | Retention policies that cover backup copies as well as live data; align backup retention with the TTL policy |
Why partition boundaries and merges decide when expired data disappears
TTL removal happens during merges, not at the moment a row expires. A row that has passed its TTL can remain on disk until ClickHouse merges the part that contains it, so the time between expiry and physical deletion depends on merge activity. Plan for expired data to persist for some period after its TTL passes, and write that period into your retention policy so auditors see the intended behavior rather than a gap. Whether a merge happens at all is governed by the table's merge settings and the load on the server, so the honest answer to "when is it gone" is a measurement you make on your own cluster, not a constant.
The partition key determines how cleanly expired data can leave. When the partition key matches the retention unit, such as one day or one month of events, ClickHouse can drop a whole partition with ALTER TABLE ... DROP PARTITION instead of waiting for row-level TTL merges, which is faster and easier to evidence. Choosing a partition key that is too fine creates many small parts that merge slowly, while one that is too coarse keeps expired rows mixed with live ones for longer. Decide the retention unit first, then align the partition key to it, and confirm that the partitioning rules for your release still match this design before you commit to a schema.
Merge timeouts and sustained ingestion load are the usual reasons expired data lingers. Heavy inserts can keep producing parts faster than background merges consume them, and some partitions may never fully merge, leaving old rows in parts that TTL cannot rewrite on schedule. On a test cluster with representative volumes, measure the age of the oldest expired rows still on disk, the count of unmerged parts per partition, and the merge queue depth during peak ingestion. Confirm the merge and TTL setting names, defaults and behavior against the ClickHouse release you run, since these settings change between versions, and review the ClickHouse production FAQ before upgrading.
How to set up TTL retention and verify it on a test cluster
This sequence turns a retention requirement into a tested TTL configuration on a pre-production ClickHouse cluster, with checks that show expired data is removed from every table, replica and backup on schedule. The output is a written evidence record that your team and auditors can review.
Write the retention policy by data class
Start by listing each dataset as a row in a table with its data class, owner, lawful or business reason for keeping it, retention period, and whether the data holds personal information. The data owner and the compliance lead sign off on the table before any DDL is written. Done means every table in scope has one agreed period and one owner. Check that no table is missing from the list by comparing it against the system tables on the source cluster.
Choose partition and ordering keys for retention
Decide the partition key so that whole partitions correspond to retention windows, typically by a date or month column that matches the TTL expression. Confirm the ordering key so that the TTL column and common filters are supported, and avoid partitioning on high-cardinality values that create many small parts. Review the design with a query engineer and record the chosen keys next to the retention table. The check is an EXPLAIN or test query showing that a date-bounded delete or drop touches only expected partitions.
Add the TTL rule in a pre-production cluster
Load representative data into a pre-production environment that mirrors production table definitions, volume and query patterns, as the ClickHouse production FAQ recommends. Add the TTL clause with a DELETE action to a test table, and where a column must expire separately, add a column-level TTL. Confirm the rule with SHOW CREATE TABLE and by reading the table's TTL expression in system.tables. Done means the rule is present, matches the policy row, and is applied to the test cluster and not yet to production.
Run merges and confirm expired rows are gone
TTL removes data during background merges, so force merges on the test table with OPTIMIZE TABLE ... FINAL only in the test environment, or wait for normal merge activity. Query system.parts and system.part_log to review merge activity and confirm that parts were rewritten. Then run a query for rows whose TTL timestamp is older than the cutoff and expect zero results. Record the query, the cutoff value, the row count, and the time of the check. If rows remain, review merge timeouts and part sizes before moving on.
Confirm replicas and backups follow the same expiry
On a replicated test table, compare row counts and the oldest timestamp on each replica after merges complete, since TTL runs per replica and replication does not change the expiry logic. Then take a BACKUP of the test table, restore it to a spare cluster, and verify that expired rows are absent or that the backup retention policy is documented separately. Note that backups keep copies of data for as long as they exist, so backup retention must be set in your own policy. Done means replica counts match and the restore check result is recorded.
Record the evidence for auditors
Assemble a retention evidence record that contains the signed policy table, the DDL with the TTL expression, the partition and ordering key decisions, the query outputs showing zero expired rows, the merge and replica comparisons, and the restore test result. Store the record in version control or your document system with dates and the names of the people who ran each check. Ask your external auditor or compliance team which format they expect before the record is finalized, since DeployOpen prepares evidence but does not issue audit opinions.
Repeat the checks after each schema change
Schedule the same checks to run whenever a TTL expression, partition key, storage policy or ClickHouse version changes, and before any production rollout. Keep the query set as a versioned script so the result is comparable over time. The sequence is complete for a table when the policy row, the DDL, the expiry queries and the replica comparison all pass on the latest pre-production run. Production rollout should follow the same steps on a production copy with the change log reviewed first.
Retention rules by data type and situation
Personal data is the case where TTL is most often mistaken for an erasure mechanism. TTL removes rows or columns when a time or expression condition is met, on a background merge schedule. It does not find one person's records on request. If a data subject asks for erasure, you need to locate every affected row across tables, replicas and backups, and that search is only practical when the table is keyed or partitioned so the person's data can be reached without scanning everything.
- Partition by a retention unit, not by person: partitioning by month lets TTL drop whole parts cheaply, but a single subject's rows are spread across partitions unless you design for lookup.
- Separate identifiers from event data: keeping direct identifiers in a small, dedicated table makes erasure a targeted delete instead of a rewrite of large fact tables.
- Decide the clock: choose whether retention starts at collection, at last activity or at contract end, and document the legal basis for each.
- Plan backups as part of erasure: a deleted row remains in older backups until they expire, so decide how long backups live and whether that period is disclosed.
- Confirm with counsel: the erasure obligations and exceptions are set in the GDPR, and the controller decides how they apply to your data.
Before configuring anything, confirm with your data protection lead which fields are personal data, which retention period applies to each, and how an erasure request will be located and verified.
Operational logs usually have a fixed retention set by security, operations or an internal policy, and they are the simplest case for TTL. Rows are time-stamped, rarely updated and deleted as whole periods. A TTL expression on the event time, combined with monthly partitioning, lets ClickHouse drop expired parts without rewriting active data. The main risk is a mismatch between the retention window and the partition boundary, which leaves data alive longer than the policy states.
- Align partitions with the policy: a partition unit that is coarser than the retention period keeps expired rows until the whole partition is past expiry.
- Use the event time column: TTL keyed on ingestion time can keep late-arriving events beyond the intended window.
- Set different windows per source: authentication logs and debug logs often need different retention, so use separate tables rather than conditional logic.
- Account for merge timing: TTL runs during merges, so expired rows may remain briefly after their expiry time.
- Keep a log of deletions: record the policy version in table metadata or a change ticket so auditors can match it to the deletion evidence.
Before configuring anything, confirm the retention period for each log source with the owner, the partition unit that matches it and whether logs fall under any incident hold.
Aggregated metrics keep long history after raw detail is removed, for trend analysis or capacity planning. TTL can express this with a GROUP BY rule that rolls old rows into summaries, or you can use a materialized view that writes aggregates continuously while raw data expires separately. The choice affects what can be recovered later. Once raw rows are rolled up, the detail is gone, so the aggregation grain is a permanent decision.
- Choose the grain before retention: a summary by hour or by day cannot later answer questions that need per-event detail.
- Check whether aggregates are personal data: counts grouped by small segments can still identify individuals, so review the grouping keys.
- Pick TTL GROUP BY or a materialized view: TTL rollups change the stored table in place, while materialized views keep raw and summary data separate.
- Verify the summary logic: test aggregates against raw data on a restored copy before the raw source is removed.
- Set the summary retention separately: the aggregate table often has a longer window than raw data, so give it its own expiry rule.
Before configuring anything, confirm the questions the aggregates must still answer, the smallest grouping that is acceptable and whether aggregates count as personal data under your assessment.
Regulated records have minimum retention periods set by sector rules or contracts, and legal holds can require that deletion stops for specific data even after its normal period ends. TTL runs on a fixed rule, so it cannot know that a hold exists. A hold must be enforced by how tables and partitions are arranged, by suspending or excluding TTL for affected data, or by copying held data to a separate location before expiry. Deleting data that is under hold is a compliance failure, and it is usually harder to explain than keeping data too long.
- Separate held data: move records under hold into their own table or partition so TTL on the main table does not touch them.
- Record each hold: keep a register with scope, start date, owner and release condition, and link it to the TTL rule that is affected.
- Test before changing rules: a TTL change applies to existing data on the next merge, so review the expiry impact on a copy first.
- Cover backups: a hold must also prevent deletion from backup sets that contain held records, which needs a separate schedule.
- Define release: decide who can lift a hold and how deletion then resumes, so the release is also auditable.
Before configuring anything, confirm with legal and compliance which record types have minimum periods, who issues and releases holds, and how holds interact with erasure requests for the same data.
Controls that stop retention rules from deleting the wrong data
A TTL rule runs without anyone watching it, so a single wrong edit can remove data across every replica within a merge cycle. The controls below place review, permissions, backups and records around each TTL definition so that a mistake is caught before it runs, can be reversed, and can be shown to an auditor afterward.
Change control for TTL definitions
Keep every TTL clause in the schema repository as versioned DDL, with the same review rules applied to table changes as to application code. A reviewer should confirm the retention period, the column the expression reads, and whether the rule deletes whole rows or only a column. Test each change against a pre-production cluster with representative data before it reaches production, since the production FAQ recommends that step for version and schema work alike.
Least-privilege rights to alter TTL
Changing a table's TTL is an ALTER operation, so restrict it to a named operator role rather than to the users who load or query data. ClickHouse access control provides users, roles, row policies and settings profiles for this. Restrict the default user, which starts with all permissions, and confirm which accounts can run ALTER TABLE in each database before go-live.
Replication does not undo deletes
Replicas copy deletions as faithfully as they copy inserts, so a TTL that removes rows on one replica removes them on all of them. A recovered replica re-syncs from a healthy peer, which means a bad retention rule is replicated too. Treat replication as availability protection only, and plan recovery from backups for mistakes like an over-broad TTL expression.
Backups that match the retention window
Decide how long backups hold data relative to the TTL. If backups keep rows longer than the live table, a restore can bring back data that policy requires you to delete, which creates a retention gap in its own right. Record which backups contain which date ranges, and test-restore a sample to a spare cluster on a schedule, because the backup documentation warns that a backup never test-restored will probably not restore properly.
Audit trail for schema changes
Record who changed a TTL, when, and what the expression was before and after. Combine the schema repository history with ClickHouse query logging, which shows the ALTER statements that actually ran against each cluster. Compare the two periodically, since a change applied outside the repository will appear in the logs but not in review. Ask the auditor which evidence format they will accept before you design the report.
Protected backup storage with its own retention
Backups hold the deleted data you are trying to remove, so storage for them needs its own access control, encryption and expiry. Give backup buckets or volumes a separate credential from the live cluster, deny delete rights to routine operators, and set a documented expiry that matches your retention policy. Confirm with your storage team which immutability or object lock features the platform supports before you rely on them.
Proving to auditors that data was removed on schedule
The GDPR does not require an audit, attestation or certificate, and certification under Article 42 is voluntary. Certification does not reduce the controller's or processor's responsibility for compliance. What the regulation does require is that you can show your processing is controlled, including the storage limitation and security measures you apply. For a ClickHouse estate, that means being able to demonstrate that rows past their retention period are removed on the schedule you wrote down. DeployOpen prepares systems and evidence for this, but independent auditors and certification bodies issue any assessment, and the controller remains responsible under the GDPR.
The evidence set should be built from the system's own records. Start with the written retention schedule, mapped to each table and its TTL expression as deployed, not as intended. Keep the CREATE TABLE statements under version control so the definitions an auditor sees are the ones that ran. Then add periodic check queries that count rows older than each cutoff in every table, with the results stored with timestamps. Pair these with records of merge health, including replication queue state from the replication system tables, and with the backup retention settings and the results of restore tests. A backup that holds expired data can undermine a deletion claim, so confirm how long backups are kept and document that period alongside the TTL schedule.
Infringements of the basic principles for processing, which include storage limitation, fall in the higher fine tier under Article 83, with a maximum of EUR 20 million or 4% of total worldwide annual turnover, whichever is higher. Because the outcome depends on how your processing is classified and what your obligations are, confirm the retention periods, backup exceptions and any legal holds with your counsel or data protection officer before you finalize the schedule. Decide early who signs off the schedule, who runs the periodic checks, and where the evidence is archived. The EUR-Lex text of the GDPR is the primary source for the provisions your counsel will reference.
What to confirm before you rely on TTL for compliance
TTL deletes expired data on a schedule, but it is a mechanism, not a compliance outcome. The cards below list the points to verify in your own environment before an auditor or a data subject relies on it. Each one depends on your ClickHouse release, your merge load, and the systems around the cluster.
Release-specific TTL and merge behavior
Confirm that the TTL behavior, merge settings and documented options you plan to use exist in the exact ClickHouse release you run, and that your release is still on the supported list in the security policy. Read the changelog for backward-incompatible changes before any upgrade, because a TTL rule that behaved one way in an earlier release may behave differently after one. Test the rule on a pre-production copy with representative data and confirm the result with a count query, not only with the configuration file.
How long expired rows persist on disk
TTL removes rows during background merges, so expired data can remain on disk and still be readable in some queries until a merge rewrites the affected parts. Measure how long this takes on your cluster under normal insert and merge load, and record the worst case you accept. Ask whether your retention promise is about logical deletion, physical deletion from disk, or both, and write that definition down before you test.
Individual erasure requests need a delete process
TTL deletes by age or by a column condition. It does not locate one person's records across tables on request. If a data subject asks for erasure before retention expires, you need a separate, documented delete process that finds the records, runs the deletion, and confirms the physical removal. Confirm with your legal and privacy team which requests fall under that process and how long each step is allowed to take.
Backups and archived copies also expire
A TTL rule does not reach backups made with BACKUP, snapshots of disks, or copies held in object storage buckets with their own lifecycle rules. Those copies can keep expired data long after the table has dropped it. Map every backup destination, its retention period, and its encryption, and decide whether expired records must be removed from backups or whether restoring them only into a controlled environment is acceptable. Replication does not solve this, because it copies the deletion and the bad data alike.
Downstream systems fed from ClickHouse
Pipelines that feed ClickHouse, such as Kafka topics, PostgreSQL tables used for staging, or files exported for reporting, hold their own copies of the data. TTL on ClickHouse does not clear them. Trace each data flow from source to every destination, set retention on each one, and check whether consumers have replayed older offsets or materialized the data elsewhere. An auditor will ask about every copy, not only the main table.
Regulatory changes and procedural rules
Regulation (EU) 2025/2518 sets procedural rules for cross-border GDPR enforcement and applies from a stated date, while the Omnibus proposals are still moving through the EU process. Confirm with counsel whether any of these change your retention obligations, your supervisory authority, or the evidence you must keep. Record the date of your review, because the status of pending proposals can change between your assessment and an audit.
Questions teams ask before setting ClickHouse TTL retention
What does TTL retention work cost, and what drives the price?
Cost depends on the written scope, not on a list price. DeployOpen does not publish prices, and a quote follows from the agreed scope. The main cost components are design of the retention rules per table and retention class, configuration of TTL expressions and partitions, testing on representative data, and the evidence pack that shows deletes happen on schedule. Most of this is one-time work. Recurring costs appear when retention policies change, when new tables join the estate, or when a retainer covers monitoring and periodic review. Ask any provider for a quote that separates design, build, testing, evidence and ongoing support, and that lists the tables, retention classes and environments in scope.
How long does a ClickHouse TTL retention project take?
Duration is set by scope, and no fixed figure applies to every estate. The main drivers are the number of tables with retention rules, the data volume and partition layout already in place, the number of distinct retention classes, and whether auditors need documented proof of deletion. A single table with one rule can move quickly. A regulated estate with several retention classes, legal holds and merge behavior to verify takes longer. Confirm the timeline in the written scope, and ask for the milestones that depend on your change windows and on access to a non-production cluster.
What does our team need to provide before the work starts?
The engagement needs a named retention policy owner who can approve retention periods per data class, plus access to a non-production ClickHouse cluster that matches production table definitions. Representative data matters, because TTL behavior depends on partition boundaries, row counts and how parts merge. Engineers should also provide schema definitions, current partition keys and any existing TTL expressions, along with change windows for applying rule changes in production. Agree on who signs off on test results and who keeps the evidence after handover.
Can an internal team set up ClickHouse TTL on its own?
A capable internal team can set up TTL for a simple case, such as one MergeTree table where a single column defines the retention date and partitions are aligned with it. The work becomes harder with multiple tables that share a retention requirement, replicated tables where rules must match on every replica, conditional deletes, and regulated data that needs documented proof. Those cases need careful testing of how and when merges apply TTL, along with a reproducible evidence process. Teams can decide between building in-house and engaging a partner by comparing these factors against their own skills and deadlines.
Does ClickHouse TTL alone satisfy GDPR erasure?
No. TTL removes expired rows or columns on a schedule, which helps with storage limitation, but it does not by itself answer an individual erasure request. GDPR does not require a particular tool. Controllers must be able to show that personal data is deleted when no longer needed and that erasure requests are handled. Erasure of specific individuals usually needs targeted deletes, documented procedures, and checks on backups and replicas. The Regulation (EU) 2016/679 text sets these obligations, and the GDPR does not require an audit or certificate, so the evidence you keep is what auditors and supervisory authorities will review.
What evidence should we keep to show auditors that data is deleted on schedule?
Keep the TTL definitions as version-controlled configuration, the retention schedule that maps each table to a data class and period, and the test results from a non-production cluster. Record when rules were changed and who approved them. For operations, keep logs or system table queries that show parts removed after their expiry, along with backup retention settings, since backups can hold expired data. No specific format is mandated by the GDPR. Confirm with your auditor or supervisory authority which evidence they expect for your sector.
Who is responsible for retention decisions after the build?
The controller, meaning your organization, owns the retention decisions and the legal basis for keeping data. The implementation partner or internal engineers own the technical rules and their correctness. Ongoing responsibility includes reviewing rules when data classes change, checking that merges apply TTL as expected after ClickHouse upgrades, and keeping evidence current. Agree these roles in writing so that nobody assumes another team will review retention periods or re-test after an upgrade.
How DeployOpen scopes and delivers ClickHouse retention work
DeployOpen plans, implements, documents and supports self-hosted ClickHouse inside your own infrastructure. For Data retention with ClickHouse TTL, we start from your retention obligations, then build and test TTL rules against your tables and hand over the evidence your auditors will ask for.
How we scope
- Inventory every table holding personal or regulated data, with its owner, ordering key and partitioning scheme
- Agree retention periods per dataset and the legal or policy basis for each, recorded in the written scope
- Define which deletions are full, column-level or conditional, and which backups must follow the same schedule
- Engagements are fixed-scope, with scope, deliverables and timelines agreed in writing before work starts
How we build
- Align partition keys with retention windows so expired data drops as whole partitions where the design allows
- Write TTL expressions and test them on a pre-production cluster with representative data and merge behavior
- Check merge timeouts and monitor whether expired parts are actually removed on the schedule you set
- Configure backups and restore tests so retention rules also apply to retained copies of deleted data
How we hand over
- Document each TTL rule, its retention rationale, its owner and the change process for altering it
- Provide queries and runbooks that show expired row counts and removal timing for audit requests
- Deliver a restore test record and a retention exception procedure for legal holds
- Train your operators on reviewing TTL changes before they reach production
How we support
- Ongoing support, version upgrades and periodic retention reviews are available as a retainer
- Review TTL behavior after ClickHouse upgrades, since release notes must be read before each upgrade
- Confirm with your legal and compliance teams which retention periods and erasure rules apply to your data
- Keep the evidence pack current as tables, sources and retention requirements change
