[hypequery](https://hypequery.com/) › [Recipes](https://hypequery.com/recipes) › Per-plan data retention in ClickHouse with TTL and a typed dataset Index for agents: https://hypequery.com/llms.txt # Per-plan data retention in ClickHouse with TTL and a typed dataset (https://hypequery.com/recipes/ttl-data-retention) Expire free-plan events after 30 days and starter events after 180 with TTL WHERE, clear a column with column TTL, and pin the result on any run date. ## Run it ```bash npx @hypequery/cli create ttl-data-retention -y cd ttl-data-retention pnpm install pnpm dev ``` - Packages: @hypequery/datasets, @hypequery/clickhouse (tested from hypequery 2.14.0) - Stack: node, clickhouse - Source: https://github.com/hypequery/hypequery/tree/main/recipes/ttl-data-retention - Test: `pnpm test` - Verify: `node dist/recipe.js --json` | Env var | Required | Description | | --- | --- | --- | | CLICKHOUSE_URL | no | ClickHouse HTTP endpoint (default http://localhost:8123) | | CLICKHOUSE_USERNAME | no | ClickHouse user (default: default) | | CLICKHOUSE_PASSWORD | no | ClickHouse password (default: hypequery, the seed's password) | | CLICKHOUSE_DATABASE | no | Database holding the seed tables (default hypequery_examples) | **Short answer:** a table TTL is a delete rule ClickHouse applies when it merges parts: `TTL toDate(occurred_at) + INTERVAL 30 DAY WHERE plan = 'free'` removes free-plan rows 30 days after their date, and several rules, each with its own `WHERE`, can share one table (checked on 25.8). A column TTL (`path String TTL toDate(occurred_at) + INTERVAL 7 DAY`) resets just that column to its default and keeps the row. Neither is a read filter: expired rows stay visible until a merge removes them, so after adding or changing a rule run `ALTER TABLE ... MATERIALIZE TTL` to apply it to existing parts. The hypequery dataset needs no change; it reads what is left. ## Demo ```text TTL rules declared, not yet materialized plan events eventsWithPath oldestEvent enterprise 855125 855125 2025-11-01 00:01:14 free 2063 2063 2025-11-01 15:18:19 growth 126853 126853 2025-11-01 00:11:30 starter 15959 15959 2025-11-01 03:26:36 After ALTER TABLE ... MATERIALIZE TTL plan events eventsWithPath oldestEvent enterprise 855125 24632 2025-11-01 00:01:14 free 261 59 2026-10-03 06:10:03 growth 126853 4863 2025-11-01 00:11:30 starter 11259 636 2026-05-06 00:04:44 ``` Free tenants keep 30 days, starter 180, growth and enterprise everything; every plan keeps request paths for 7 days. Both states are pinned in `states.json`, the second one in `output.json`. ## Install ```bash npx @hypequery/cli create ttl-data-retention -y cd ttl-data-retention docker compose -f seed/docker-compose.yml up -d --wait && (cd seed && pnpm install && pnpm seed) # or set CLICKHOUSE_* for your own ClickHouse pnpm install pnpm dev # creates ttl_data_retention_events, prints before and after MATERIALIZE TTL pnpm teardown # drops it ``` The table is a copy of the seed's `events` with each tenant's `plan` stamped on the row; the seed tables are only read. ## The SQL What you would run in production: ```sql ALTER TABLE events_with_plan MODIFY TTL toDate(occurred_at) + INTERVAL 30 DAY WHERE plan = 'free', toDate(occurred_at) + INTERVAL 180 DAY WHERE plan = 'starter'; ALTER TABLE events_with_plan MODIFY COLUMN path String TTL toDate(occurred_at) + INTERVAL 7 DAY; -- Apply both to the parts that already exist. A mutation. ALTER TABLE events_with_plan MATERIALIZE TTL SETTINGS mutations_sync = 1; ``` `sql/setup.sql` runs the same statements with two differences, both so the output is the same on every run: - **The seed is frozen in time.** Its events end on 2026-10-31, but TTL is measured against the server clock. Each interval gets `+ INTERVAL {seed_age_days} DAY`, the days between 2026-11-01 and the server's `today()`, filled in by `src/setup.ts`. The cut-offs then always land on the same seed dates: free keeps 2026-10-03 onwards, starter 2026-05-06 onwards, paths 2026-10-26 onwards. - **TTL is on `toDate(occurred_at)`, not the timestamp.** A `DateTime` TTL moves the cut-off every second; a `Date` one moves it once a day. The only remaining edge is a run that starts before midnight UTC and materializes after it. Setup also runs `SYSTEM STOP MERGES` on the table and declares the rules with `materialize_ttl_after_modify = 0`, so nothing is removed until `MATERIALIZE TTL` and the "before" state is stable. Neither belongs in production. ## The dataset `src/dataset.ts` is an events dataset with nothing TTL-specific in it. `eventsWithPath` is a filtered count (`measure.count('eventId', { filters: [neq('path', '')] })`), which is how the column TTL shows up: ```ts export const query = { dimensions: ['plan'], measures: ['events', 'eventsWithPath', 'oldestEvent', 'newestEvent'], orderBy: [{ field: 'plan', direction: 'asc' }], } satisfies DatasetQueryFor; ``` The smoke test checks the retained rows against the same policy applied by hand to the seed `events` and `tenants`. ## Trade-offs and pitfalls (checked on 25.8) - **Expired is not deleted.** With TTL merges stopped, an insert already past its TTL was stored and counted. In a fresh table a `TTLDeleteMerge` removed such a row within a second (`system.part_log`), which looks like insert-time filtering but is not. Queries that must never show expired data need their own `WHERE`. - **`merge_with_ttl_timeout` is 14400 seconds.** After a TTL merge, ClickHouse waits up to four hours before the next one in the same partition, so expiry is late by design. `MATERIALIZE TTL` or `OPTIMIZE ... FINAL` force it. - **The cut-off is midnight of the expiry day.** With `toDate(occurred_at) + INTERVAL 30 DAY`, rows dated 2026-10-02 expire at 2026-11-01 00:00, so on 2026-11-01 free tenants have 29 full days plus today. Add a day if "30 days" must mean 30 complete days. - **`ttl_only_drop_parts = 1` only drops whole parts.** On a table with monthly partitions and a 50-day TTL, a partition with some expired and some live rows kept all of them. It is cheap (no rewrite) but coarse; with per-plan `WHERE` rules and parts that mix plans, expect little to be dropped. - **The rule can read another table.** `TTL ... WHERE tenant_id IN (SELECT tenant_id FROM hypequery_examples.tenants WHERE plan = 'free')` was accepted (the table name must be qualified) and deleted the right rows. It is evaluated at merge time, so a tenant that upgrades after its rows were merged does not get them back. Stamping the plan on the row at ingest, as here, makes the rule explicit. - **`now()` is accepted in a TTL expression.** `TTL d + (now() - toDateTime('2026-11-01'))` created without error. It is evaluated when the part is written or merged, so the expiry of a row depends on when that happens; avoid it. - **MATERIALIZE TTL rewrites affected parts.** Here it took about 0.3 seconds for a million rows; on a large table it is a heavy mutation, so schedule it. ## Adapt it - **Plans from a dictionary.** If events do not carry the plan, look it up at ingest with `dictGet` (see [dictionary-lookups](https://hypequery.com/recipes/dictionary-lookups)) and store it on the row. - **Move instead of delete.** `TTL ... TO VOLUME 'cold'` moves old parts to cheaper storage; it needs a storage policy with that volume, which the seed server does not have, so it is not exercised here. - **Roll up before deleting.** `TTL d + INTERVAL 30 DAY GROUP BY k SET v = sum(v)` collapsed 100 expired rows into one row per `k` after `OPTIMIZE ... FINAL` (the `GROUP BY` must be a prefix of `ORDER BY`). Or keep a materialized-view rollup ([materialized-view-rollups](https://hypequery.com/examples/materialized-view-rollups)) with a longer TTL than the raw table. - **Scope the dataset.** Set `tenantKey: 'tenant_id'` on the dataset so each tenant's dashboard only sees its own retained events. ## Related recipes - [replacingmergetree-upserts](https://hypequery.com/recipes/replacingmergetree-upserts): the other merge-time behaviour that queries see before it happens. - [dictionary-lookups](https://hypequery.com/recipes/dictionary-lookups): tenant attributes without a JOIN. ## Related examples - [multi-tenant-embedded-analytics](https://hypequery.com/examples/multi-tenant-embedded-analytics): per-tenant datasets over the same events. Tested on ClickHouse 25.8 with `@hypequery/datasets` 0.29 and `@hypequery/clickhouse` 2.14. ## Key file: sql/setup.sql ```sql -- A private copy of the seed events with each tenant's plan stamped on the -- row at ingest, so retention rules can depend on it. The seed tables are -- only read. DROP TABLE IF EXISTS ttl_data_retention_events; CREATE TABLE ttl_data_retention_events ( event_id UInt64, tenant_id UInt32, plan LowCardinality(String), event_type LowCardinality(String), path String, latency_ms UInt32, occurred_at DateTime('UTC') ) ENGINE = MergeTree PARTITION BY toYYYYMM(occurred_at) ORDER BY (tenant_id, occurred_at, event_id); -- Hold merges so nothing is deleted before MATERIALIZE TTL, on every run. -- Test technique only. SYSTEM STOP MERGES ttl_data_retention_events; INSERT INTO ttl_data_retention_events SELECT e.event_id, e.tenant_id, t.plan, e.event_type, e.path, e.latency_ms, e.occurred_at FROM events AS e INNER JOIN tenants AS t ON t.tenant_id = e.tenant_id; -- Retention per plan. In production this reads -- toDate(occurred_at) + INTERVAL 30 DAY WHERE plan = 'free' -- {seed_age_days} is the number of days between the seed's "today" -- (2026-11-01) and the real today, filled in by src/setup.ts. Adding it makes -- the cut-off land on the same seed date whenever the recipe runs. -- materialize_ttl_after_modify = 0 records the rules without applying them -- to existing parts yet, so the before/after is visible. ALTER TABLE ttl_data_retention_events MODIFY TTL toDate(occurred_at) + INTERVAL 30 DAY + INTERVAL {seed_age_days} DAY WHERE plan = 'free', toDate(occurred_at) + INTERVAL 180 DAY + INTERVAL {seed_age_days} DAY WHERE plan = 'starter' SETTINGS materialize_ttl_after_modify = 0; -- Column TTL: keep the request path for 7 days, then reset it to the -- column default ('') while the row itself stays. ALTER TABLE ttl_data_retention_events MODIFY COLUMN path String TTL toDate(occurred_at) + INTERVAL 7 DAY + INTERVAL {seed_age_days} DAY SETTINGS materialize_ttl_after_modify = 0; ``` ## Dataset: src/dataset.ts ```typescript import { dataset, dimension, measure, neq } from '@hypequery/datasets'; /** * Events under a retention policy. The dataset knows nothing about TTL: * expired rows are simply gone after ClickHouse applies it, and expired * column values are reset to the column default (an empty path). */ export const Events = dataset('events', { source: 'ttl_data_retention_events', timeKey: 'occurred_at', dimensions: { eventId: dimension.number({ column: 'event_id' }), tenantId: dimension.number({ column: 'tenant_id' }), plan: dimension.string({ label: 'Plan' }), eventType: dimension.string({ column: 'event_type', label: 'Event type' }), path: dimension.string(), occurredAt: dimension.timestamp({ column: 'occurred_at' }), }, measures: { events: measure.count('eventId', { label: 'Events' }), eventsWithPath: measure.count('eventId', { label: 'Events with a path', filters: [neq('path', '')] }), oldestEvent: measure.min('occurredAt', { label: 'Oldest event' }), newestEvent: measure.max('occurredAt', { label: 'Newest event' }), }, }); ``` ## Generated SQL ```sql SELECT ttl_data_retention_events.plan AS `plan`, COUNT(ttl_data_retention_events.event_id) AS `events`, COUNT(if((ttl_data_retention_events.path != ''), ttl_data_retention_events.event_id, NULL)) AS `eventsWithPath`, MIN(ttl_data_retention_events.occurred_at) AS `oldestEvent`, MAX(ttl_data_retention_events.occurred_at) AS `newestEvent` FROM ttl_data_retention_events GROUP BY `plan` ORDER BY `plan` ASC ``` ## Output on the seed dataset ```json [ { "plan": "enterprise", "events": "855125", "eventsWithPath": "24632", "oldestEvent": "2025-11-01 00:01:14", "newestEvent": "2026-10-31 23:58:36" }, { "plan": "free", "events": "261", "eventsWithPath": "59", "oldestEvent": "2026-10-03 06:10:03", "newestEvent": "2026-10-31 16:11:29" }, { "plan": "growth", "events": "126853", "eventsWithPath": "4863", "oldestEvent": "2025-11-01 00:11:30", "newestEvent": "2026-10-31 23:59:38" }, { "plan": "starter", "events": "11259", "eventsWithPath": "636", "oldestEvent": "2026-05-06 00:04:44", "newestEvent": "2026-10-31 23:48:31" } ] ``` ## Related - [Upserts and deletes in ClickHouse with ReplacingMergeTree and a typed dataset](https://hypequery.com/recipes/replacingmergetree-upserts): Upsert and delete rows with ReplacingMergeTree, read the latest version with FINAL or argMax, and pin the results before and after OPTIMIZE. - [Multi-tenant embedded analytics on ClickHouse with signed tokens](https://hypequery.com/examples/multi-tenant-embedded-analytics): Embed tenant-scoped ClickHouse analytics in your product: your server mints a short-lived token, hypequery enforces the tenant on every query. - [All recipes](https://hypequery.com/recipes) - [/clickhouse-multi-tenant-analytics](https://hypequery.com/clickhouse-multi-tenant-analytics) - [/clickhouse-saas-analytics](https://hypequery.com/clickhouse-saas-analytics) - [/clickhouse-schema](https://hypequery.com/clickhouse-schema) - [/clickhouse-time-series](https://hypequery.com/clickhouse-time-series)