Automatically Delete Old Rows in CockroachDB with Row-Level TTL (2026)

CockroachDB’s DROP DATABASE command, surprisingly, doesn’t actually remove all data immediately.

Let’s see how CockroachDB handles data expiration and then how you can leverage that for automatic cleanup.

Imagine you have a table that logs events, and you only care about the last 30 days of data. Keeping it indefinitely is wasteful. CockroachDB’s Row-Level Time-To-Live (TTL) feature lets you define an expiration time for individual rows. When a row’s TTL expires, it’s marked for deletion and eventually garbage collected.

Here’s a simple events table with a created_at timestamp and a TTL set to expire rows after 30 days:

CREATE TABLE events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    event_type STRING,
    payload JSONB,
    created_at TIMESTAMPTZ DEFAULT now()
);

ALTER TABLE events EXPERIMENT (row_level_ttl); -- Enable the TTL experiment if not already enabled globally

ALTER TABLE events
PARTITION BY RANGE (created_at)
SUBPARTITION BY RANGE (id) -- Optional but good practice for performance
(
    PARTITION p_old VALUES < '2023-01-01'
);

ALTER TABLE events
SET (
    ttl_expire_after = '30 days',
    ttl_job_schedule = '1 hour'
);

In this setup:

• ttl_expire_after = '30 days' tells CockroachDB that any row inserted will be eligible for deletion 30 days after its created_at timestamp.
• ttl_job_schedule = '1 hour' defines how often CockroachDB’s background TTL job should run to check for and delete expired rows.

The partitioning strategy (PARTITION BY RANGE (created_at)) is crucial here. When you set a TTL based on a timestamp column, CockroachDB can efficiently drop entire partitions of data that are older than the TTL. For example, if your ttl_expire_after is 30 days, and you have a partition for data older than 90 days, that entire partition can be removed much faster than scanning and deleting individual rows. The SUBPARTITION BY RANGE (id) is good practice for distributing data and queries more evenly.

The actual deletion happens in two stages. First, expired rows are "garbage collected" by a background process. This process marks rows for deletion. Then, a separate garbage collection process reclaims the disk space. You can observe this in the crdb_internal.ranges table, where expired ranges will eventually show a lease_status of ABANDONED and closed_timestamp will be populated.

The most surprising thing about row-level TTL is that it doesn’t require explicit DELETE statements from your application. The system handles it entirely, reducing application complexity and potential for missed cleanup. It’s a declarative approach to data lifecycle management.

Consider a scenario where you have a very high volume of writes, and your TTL is set to 1 day. If your ttl_job_schedule is set to 24 hours, you might find that expired data isn’t being cleaned up fast enough, leading to storage growth. In such cases, reducing the ttl_job_schedule to something like 1 hour or even 15 minutes would make the background TTL cleanup process more aggressive.

The ttl_job_schedule is not about when rows expire, but how frequently CockroachDB checks for expired rows and initiates their deletion. A shorter schedule means more frequent checks and faster cleanup, but it also consumes more cluster resources.

You can monitor the TTL jobs using SHOW JOBS. Look for jobs with job_type = 'GC'. The created and finished timestamps on these jobs will give you an idea of how long the cleanup is taking. If you see a backlog of GC jobs or jobs that take an unusually long time to complete, it might indicate that your cluster is under-resourced for the TTL workload, or that your TTL configuration isn’t optimal for your data ingestion rate.

The crdb_internal.cluster_settings table contains a setting sql.gc.ttl_max_age. This is an override for the ttl_expire_after setting at the table level. If sql.gc.ttl_max_age is set, it dictates the maximum age of data that the garbage collector will consider for deletion, effectively acting as a global safety net. If your table TTL is set to '7 days', but sql.gc.ttl_max_age is set to '1 day', then only data older than 1 day will be eligible for TTL deletion. This setting is often 0, meaning it’s disabled and table-level TTLs are respected.

The next step after implementing TTL is understanding how to optimize the underlying garbage collection process for very large datasets.