Time to Live (TTL)
This section describes how the TTL mechanism works and what its limits are. It also gives examples of commands and code snippets that can be used to enable, configure, and disable TTL.
How it works
YDB allows you to specify a TTL column in both row-oriented and column-oriented tables. Values in TTL columns determine the table rows lifetime.
Warning
An item with the NULL
value in the TTL column is never deleted.
The timestamp for deleting a table item is determined by the formula:
expiration_time = valueof(ttl_column) + expire_after_seconds
Note
TTL doesn't guarantee that the item will be deleted exactly at expiration_time
, it might happen later. If it's important to exclude logically obsolete but not yet physically deleted items from the selection, use query-level filtering.
Data is deleted by the Background Removal Operation (BRO), consisting of two stages:
- Checking the values in the TTL column.
- Deleting expired data.
The BRO has the following properties:
- The concurrency unit is a table partition.
- For tables with secondary indexes, the delete stage is a distributed transaction.
Guarantees
- For the same partition BRO is run at the intervals set in the TTL settings. The default run interval is 1 hour, the minimum allowable value is 15 minutes.
- Data consistency is guaranteed. The TTL column value is re-checked during the delete stage. This means that if the TTL column value is updated between stages 1 and 2 (for example, with
UPDATE
) and ceases to meet the delete criteria, the row will not be deleted.
Limitations
-
The TTL column must be of one of the following types:
Date
.Datetime
.Timestamp
.Uint32
.Uint64
.DyNumber
.
-
The value in the TTL column with a numeric type (
Uint32
,Uint64
, orDyNumber
) is interpreted as a Unix time value. The following units are supported (set in the TTL settings):- Seconds.
- Milliseconds.
- Microseconds.
- Nanoseconds.
-
You can't specify multiple TTL columns.
-
You can't delete the TTL column. However, if this is required, you should first disable TTL for the table.
Setup
Currently, you can manage TTL settings using:
- YQL.
- YDB console client.
- YDB C++, Go and Python SDK.
Enabling TTL for an existing table
In the example below, the items of the mytable
table will be deleted an hour after the time set in the created_at
column:
ALTER TABLE `mytable` SET (TTL = Interval("PT1H") ON created_at);
$ ydb -e <endpoint> -d <database> table ttl set --column created_at --expire-after 3600 mytable
session.AlterTable(
"mytable",
TAlterTableSettings()
.BeginAlterTtlSettings()
.Set("created_at", TDuration::Hours(1))
.EndAlterTtlSettings()
);
err := session.AlterTable(ctx, "mytable",
options.WithSetTimeToLiveSettings(
options.NewTTLSettings().ColumnDateType("created_at").ExpireAfter(time.Hour),
),
)
session.alter_table('mytable', set_ttl_settings=ydb.TtlSettings().with_date_type_column('created_at', 3600))
Tip
When setting up TTL using YQL, an Interval
is created from a string literal in ISO 8601 format with some restrictions.
The example below shows how to use the modified_at
column with a numeric type (Uint32
) as a TTL column. The column value is interpreted as the number of seconds since the Unix epoch:
ALTER TABLE `mytable` SET (TTL = Interval("PT1H") ON modified_at AS SECONDS);
$ ydb -e <endpoint> -d <database> table ttl set --column modified_at --expire-after 3600 --unit seconds mytable
session.AlterTable(
"mytable",
TAlterTableSettings()
.BeginAlterTtlSettings()
.Set("modified_at", TTtlSettings::EUnit::Seconds, TDuration::Hours(1))
.EndAlterTtlSettings()
);
err := session.AlterTable(ctx, "mytable",
options.WithSetTimeToLiveSettings(
options.NewTTLSettings().ColumnSeconds("modified_at").ExpireAfter(time.Hour),
),
)
session.alter_table('mytable', set_ttl_settings=ydb.TtlSettings().with_value_since_unix_epoch('modified_at', UNIT_SECONDS, 3600))
Enabling TTL for a newly created table
For a newly created table, you can pass TTL settings along with the table description:
CREATE TABLE `mytable` (
id Uint64,
expire_at Timestamp,
PRIMARY KEY (id)
) WITH (
TTL = Interval("PT0S") ON expire_at
);
session.CreateTable(
"mytable",
TTableBuilder()
.AddNullableColumn("id", EPrimitiveType::Uint64)
.AddNullableColumn("expire_at", EPrimitiveType::Timestamp)
.SetPrimaryKeyColumn("id")
.SetTtlSettings("expire_at")
.Build()
);
err := session.CreateTable(ctx, "mytable",
options.WithColumn("id", types.Optional(types.TypeUint64)),
options.WithColumn("expire_at", types.Optional(types.TypeTimestamp)),
options.WithTimeToLiveSettings(
options.NewTTLSettings().ColumnDateType("expire_at"),
),
)
session.create_table(
'mytable',
ydb.TableDescription()
.with_column(ydb.Column('id', ydb.OptionalType(ydb.DataType.Uint64)))
.with_column(ydb.Column('expire_at', ydb.OptionalType(ydb.DataType.Timestamp)))
.with_primary_key('id')
.with_ttl(ydb.TtlSettings().with_date_type_column('expire_at'))
)
Disabling TTL
ALTER TABLE `mytable` RESET (TTL);
$ ydb -e <endpoint> -d <database> table ttl reset mytable
session.AlterTable(
"mytable",
TAlterTableSettings()
.BeginAlterTtlSettings()
.Drop()
.EndAlterTtlSettings()
);
err := session.AlterTable(ctx, "mytable",
options.WithDropTimeToLive(),
)
session.alter_table('mytable', drop_ttl_settings=True)
Getting TTL settings
The current TTL settings can be obtained from the table description:
$ ydb -e <endpoint> -d <database> scheme describe mytable
auto desc = session.DescribeTable("mytable").GetValueSync().GetTableDescription();
auto ttl = desc.GetTtlSettings();
desc, err := session.DescribeTable(ctx, "mytable")
if err != nil {
// process error
}
ttl := desc.TimeToLiveSettings
desc = session.describe_table('mytable')
ttl = desc.ttl_settings