---
title: Table Retention Policies
slug: 5vDc-table-ret
description: Ocient table retention policies automate data lifecycle management by removing aged records based on time-key columns, reducing manual maintenance and optimizing database performance.
docTags: 
createdAt: Thu Aug 14 2025 21:18:40 GMT+0000 (Coordinated Universal Time)
updatedAt: Wed Apr 22 2026 20:48:00 GMT+0000 (Coordinated Universal Time)
---

The Ocient® 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.&#x20;

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.&#x20;

## Requirements

To add table retention policies, you must have `ALTER` and `DELETE` privileges for the system and the specific table.&#x20;

In addition, a database table must meet these conditions to support retention policies:&#x20;

- Have a designated TimeKey® column (For details, see [TimeKeys and Clustering Keys](<./TimeKeys and Clustering Keys.md>))
- 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.&#x20;

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.&#x20;

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.

:::hint{type="info"}
The table retention policy also deletes older rows of any dependent global dictionary compression tables defined on columns of the original table.&#x20;
:::

### Table Retention Tasks

The system deletes older records as a distributed task, which is visible in the [sys.tasks](<./././SQL Reference/System Catalog Reference/System Catalog.md>) and [sys.subtasks](<./././SQL Reference/System Catalog Reference/System Catalog.md>) catalog tables. Additionally, each table retention policy creates a scheduled process, which is visible in the [sys.tkt\_table\_info](<./././SQL Reference/System Catalog Reference/System Catalog.md>) system catalog table. For details, see [Distributed Tasks](<./././SQL Reference/Data Definition Language _DDL_ Statement Reference/Distributed Tasks.md>).

### Retention and Workload Management

Retention policies execute periodically to delete data in two separate phases: a truncate operation and a delete operation.&#x20;

The truncate operation interfaces directly with the storage layer of the system and is not affected by any workload management configuration.&#x20;

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](<./././SQL Reference/System Catalog Reference/System Catalog.md>).&#x20;

You can also view a retention policy for an individual table by executing an [EXPORT TABLE](<./././SQL Reference/Data Definition Language _DDL_ Statement Reference/Tables.md>) statement.&#x20;

## Add Retention Policies to Tables

You can add table retention policies using a `CREATE TABLE` or `ALTER TABLE` SQL statement.&#x20;

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](<./././SQL Reference/Data Definition Language _DDL_ Statement Reference/Tables.md>) or an [ALTER TABLE](<./././SQL Reference/Data Definition Language _DDL_ Statement Reference/Tables.md>) statement.&#x20;

```sql
RETENTION POLICY AGE retention_granularity retention_value
```

| **Parameter**           | **Type** | **Description**                                                                                                                                                                                                                              |
| ----------------------- | -------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `retention_granularity` | numeric  | A 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_value`       | string   | The unit of time that represents how long the table retains data. Supported values are: <br />`[ WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND ]`<br /><br />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.&#x20;

```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;
```

## Related Links

[Tables](<./././SQL Reference/Data Definition Language _DDL_ Statement Reference/Tables.md>)&#x20;

[CREATE TABLE SQL Statement Examples](<./CREATE TABLE SQL Statement Examples.md>)&#x20;

















