CockroachDB 22.1 TTL schedules deletion after expiration

An expired row can still be present and visible. That is the most consequential detail of CockroachDB's new row-level TTL preview. The database records when a row becomes eligible for deletion, then a scheduled job finds and removes eligible rows. The expiration timestamp is not a promise that every query will stop returning the row at that instant.

The May 24 release notes for CockroachDB 22.1 introduce the preview as a way to move retention cleanup out of application-managed jobs. The implementation still performs SQL selection and deletion. Its value lies in giving that work a database-managed schedule and controls, with limitations documented for this first version.

I like CockroachDB's preference for making consistency and coordination costs explicit. Retention cleanup benefits from the same clarity. A table option is useful when the operator can still understand what runs, when it runs, and which application semantics it does not supply.

Expiration is stored with the row

The TTL documentation as published with 22.1 uses a table storage parameter to enable the feature. For an illustrative events table with a three-month interval, the definition is straightforward.

CREATE TABLE events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  description TEXT,
  inserted_at TIMESTAMP DEFAULT current_timestamp()
) WITH (ttl_expire_after = '3 months');

This creates a repeating scheduled job and a hidden, not-visible TIMESTAMPTZ column named crdb_internal_expiration. The generated definition supplies both a default and an update expression based on the current timestamp plus the interval. The separate inserted_at column in the example is not what the TTL machinery reads as its automatic expiration value.

That distinction affects the meaning of retention. A reader should inspect the generated definition rather than assume that the application timestamp controls deletion. In this preview, the documentation says the TTL cannot be customized using values from other columns. A table may contain a business event time without having that time determine the automatically managed expiry.

The documentation shows the hidden column explicitly selected alongside ordinary fields. It also uses SHOW CREATE TABLE to expose the storage parameters and generated column definition. Those inspection commands make the policy visible as part of the schema, including the update behavior that is easy to miss in a short creation example.

The scheduler determines when eligible rows are processed

Rows become eligible when their expiration passes. The job runs according to ttl_job_cron, whose documented default is hourly. Changing the expiration interval changes eligibility, while changing the schedule changes when the worker looks for eligible data. Those settings express different parts of the policy.

A short lifetime paired with an infrequent job schedule can therefore leave expired rows waiting. That follows from the documented separation between eligibility and execution. It is not a clock precision issue, and selecting a very small interval does not turn the job into continuous per-row execution.

The documentation supplies a daily schedule example and allows changing the cron parameter with ALTER TABLE. A schedule can also be paused through the TTL storage parameters. Pausing stops the scheduled deletion work rather than changing every row's expiration into a new value.

This makes the feature suitable for background retention with an understood delay. An application requirement that data become inaccessible at an exact time needs a visibility rule in its reads. The cleanup schedule alone does not establish that rule.

The job selects historically and deletes in batches

The worker issues selection queries at a historical timestamp to identify expired rows, then batched DELETE statements remove them. Separate parameters control selection batch size and deletion batch size. The published defaults are 500 selected rows and 100 deleted rows per batch.

The job also has a delete-rate setting expressed in rows per second. Its default of zero means no configured rate limit. That detail matters because saying the feature has rate controls is not the same as saying every installation has chosen a finite deletion rate. The controls make the tradeoff available in the schema configuration.

Admission control gives the background deletion queries a lower priority than foreground application work. This supplies a mechanism for coordinating cleanup with other requests, but it does not mean deletion consumes no resources. The worker still reads data, issues writes, and maintains the indexes affected by deleting rows.

I find these explicit controls useful because cleanup volume can vary independently of foreground traffic. A large group of rows can become eligible together even when the application's current request rate is low. The selection and deletion settings describe how the database processes that accumulated work.

Expired rows remain ordinary query inputs until deletion

The preview limitations published with the release explicitly say queries do not filter expired rows from results. The statement includes updates and deletes. TTL does not introduce a universal predicate silently applied to application SQL.

This matters for short-lived tokens, events, or other records whose business validity ends at a timestamp. Retaining a row for later deletion and accepting it as currently valid are different decisions. The application cannot infer present validity solely from the fact that a row remains in the table.

The documentation provides guidance for filtering expired data in selection queries. Such a predicate defines which records a read accepts while the cleanup job handles eventual removal. The two operations can use the same expiration value, but they occur through different paths.

An outbox is one documented use case. Processed event data can accumulate without cleanup, so deleting it after an interval limits growth. That does not make TTL an acknowledgement protocol for an external consumer. The table option schedules deletion according to expiration, rather than proving that another system has processed the row.

The preview has structural restrictions

The initial implementation prohibits foreign-key references to or from a TTL table. That is a schema-level limitation, not merely advice to choose smaller batches. A table participating in those relationships cannot simply acquire the feature without addressing that restriction.

Adding TTL to an existing table also causes a schema change that populates the expiration column for its rows. The documentation warns that this can be expensive depending on table size. Defining retention on a new empty table and adding it to a populated table therefore involve different amounts of initial work, even if their eventual storage parameters look similar.

The preview's deletion queries do not use an index on the expiration column and do not take node locality into account. The limitations also say all deletes run on one node. Those properties qualify any assumption that a distributed database necessarily distributes every part of its retention process across the cluster.

The release labels the feature a preview because it may not satisfy every workload's functional or performance requirements. That qualification has specific content here. Foreign-key support, customizable expiration, and the deletion execution arrangement constrain the schemas and workloads the first implementation can handle.

Cleanup has inspectable jobs and metrics

The documentation exposes schedules through SHOW SCHEDULES and running work through the jobs view. A filtered inspection of current TTL work uses the documented job type.

SELECT * FROM [SHOW JOBS]
WHERE job_type = 'ROW LEVEL TTL';

That view describes execution, while the schedule describes recurrence. Inspecting only a configured interval does not reveal whether the most recent run completed. The distinction is useful when eligible rows remain present longer than an operator expects.

The published metrics include selected and deleted row counts as well as selection and deletion duration. Optional row-statistics polling reports approximate table and expired-row counts. These measurements describe the work and the remaining eligible data, rather than establishing a hard deletion deadline.

Metrics aggregate across TTL tables by default. The documentation describes enabling child metrics and a table storage option to label them by table name, with an explicit cardinality cost. Aggregated counts answer how much TTL work the cluster is doing. Table labels help attribute that work to a particular retention policy, while creating more metric series. This distinction becomes useful when several tables have different expiration intervals or schedules. A rise in the aggregate expired-row count does not identify which table is waiting, and a table-specific count still needs its schedule and job state to explain the delay.

Row-level TTL in 22.1 therefore moves a defined background process into the database. The schema records eligibility, the schedule initiates work, and the job deletes in controlled batches. Applications that need immediate expiration semantics still express that requirement in their queries while the scheduled worker removes data that no longer needs to remain stored.