Skip to main content
The System supports table retention policies, an automated way to manage and delete data after a specific time. These policies help maintain storage efficiency on a schedule. Using DDL SQL statements, you can modify individual tables to specify their own retention policies for how long the system should keep data before automatic removal.

​
Requirements

To add table retention policies, you must have ALTER and DELETE privileges for the system and the specific table. In addition, a database table must meet these conditions to support retention policies:
  • Have a designated column (For details, see TimeKeys and Clustering Keys)
  • Be a user-created table or system-persistent table. You cannot edit virtual system tables.

​
Table Retention Properties

Table retention properties define the specific parameters that control how long data remains in a table before being automatically deleted. These properties determine both the duration of retention and the granularity at which the system evaluates data for removal, ensuring that retention policies operate consistently and predictably. By configuring these properties, you can fine-tune how the system balances data availability with storage efficiency.

​
Table Retention and TimeKey

Retention policies of a table are directly linked to the column defined as the TimeKey of the table. For each row in the table, the TimeKey column represents its age, which the system evaluates to determine whether to retain or delete the row. Table retention policies cannot function properly if the TimeKey column does not represent the age of each row. Additionally, the TimeKey bucket value also represents the frequency of evaluating the age of each row in the table. For example, a table with a TimeKey bucket of 1 HOUR and a retention period of 1 DAY would evaluate each row every hour to determine if it is more than one day old. Based on the TimeKey bucket frequency for culling older records, the system can potentially retain a row for slightly longer than the set period of a retention policy, up to a maximum retention time of the sum of the retention period and the TimeKey bucket.
The table retention policy also deletes older rows of any dependent global dictionary compression tables defined on columns of the original table.

​
Table Retention Tasks

The system deletes older records as a distributed task, which is visible in the sys.tasks and sys.subtasks catalog tables. Additionally, each table retention policy creates a scheduled process, which is visible in the sys.tkt_table_info system catalog table. For details, see Distributed Tasks.

​
Retention and Workload Management

Retention policies execute periodically to delete data in two separate phases: a truncate operation and a delete operation. The truncate operation interfaces directly with the storage layer of the system and is not affected by any workload management configuration. In contrast, the delete operation runs as a DELETE SQL statement and uses a designated Retention Policy service class for retention policies. This service class is a built-in class that administrative users can edit. All table retention policies use the same service class.

​
Default Retention Policies

A subset of persistent system tables has table retention policies set by default. All retention policies in the system, including those set by default, are visible in the System Catalog. You can also view a retention policy for an individual table by executing an EXPORT TABLE statement.

​
Add Retention Policies to Tables

You can add table retention policies using a CREATE TABLE or ALTER TABLE SQL statement. Table retention policies are optional for all user-defined tables. If a table lacks a retention policy, the default behavior is to retain data in perpetuity. Retention policies follow this syntax as part of either a CREATE TABLE option or an ALTER TABLE statement.
SQL
RETENTION POLICY AGE retention_granularity retention_value
ParameterTypeDescription
retention_granularitynumericA number that represents the amount of time before the system deletes data in the table. You must pair this number with a specific time type, represented by the retention_value.
retention_valuestringThe unit of time that represents how long the table retains data. Supported values are:
[ WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND ]

For example, 1 DAY schedules the table retention to a granularity of one day.
Example This example creates a retention policy that keeps rows only if they are less than one day old, as specified by the last line, RETENTION POLICY AGE 1 DAY. The retention policy depends on the created_at column because it is the TimeKey column. The TimeKey bucket value (1, HOUR) determines how frequently the system checks for any rows to remove.
SQL
CREATE TABLE IF NOT EXISTS "transact_data" (
    "created_at" TIMESTAMP TIME KEY BUCKET(1, HOUR) NOT NULL,
    "user_id" INT NOT NULL DEFAULT 0,
    "sell_id" BIGINT NOT NULL,
    "purchase_amount" BIGINT NOT NULL DEFAULT 0,
    "buyer_name" VARCHAR(1048576),
    CLUSTERING KEY "primary_index" ("sell_id", "user_id")
) RETENTION POLICY AGE 1 DAY;
Tables CREATE TABLE SQL Statement Examples
Last modified on May 27, 2026