Manage data retention with TTL

This page discusses how to use time to live (TTL) on Spanner tables. To learn more, see About TTL.

Before you begin

Before you begin, follow these best practices.

Enable backup and point-in-time recovery

Before adding TTL to your table, we recommend enabling Spanner backup and restore. This lets you fully restore a database in case you accidentally delete your data with the TTL policy.

If you've enabled point-in-time recovery, you can view and restore deleted data—without a full restore from backup—if it is within the configured version retention period. For information on reading data in the past, see Perform a stale read.

Clean up old data

If this is the first time you're using TTL and you expect the first run to delete many rows, consider first cleaning up old data manually using partitioned DML. This gives you more control over the resource usage, instead of leaving it to the TTL background process. TTL runs at a low priority, ideal for incremental clean-up. However, this will likely lengthen the time it takes to delete the initial set of rows in a busy database because Spanner's internal work scheduler will prioritize other work, such as user queries.

Verify your conditions

For GoogleSQL tables, if you want to verify the data that the row deletion policy will affect before enabling TTL, you can query your table using the same conditions. For example:

GoogleSQL

  SELECT COUNT(*)
  FROM CalculatedRoutes
  WHERE TIMESTAMP_ADD(CreatedAt, INTERVAL 30 DAY) < CURRENT_TIMESTAMP();

Required permissions

To change the database's schema, you must have the spanner.databases.updateDdl permission. For details, see Access control for Spanner.

Create a row deletion policy

GoogleSQL

To create a row deletion policy using GoogleSQL, you can define a ROW DELETION POLICY clause when you create a new table, or add a policy to an existing table. This clause contains an expression of a column and an interval.

To add a policy at the time of table creation:

CREATE TABLE MyTable(
Key INT64,
CreatedAt TIMESTAMP,
) PRIMARY KEY (Key),
ROW DELETION POLICY (OLDER_THAN(timestamp_column, INTERVAL num_days DAY));

Where:

timestamp_column must be an existing column with type TIMESTAMP. Columns with commit timestamps are valid, as are generated columns. However, you cannot specify a generated column that references a commit timestamp column.
num_days is the number of days past the timestamp in the timestamp_column in which the row is marked for deletion. The value must be a non-negative integer and DAY is the only supported unit.

To add a policy to an existing table, use the ALTER TABLE statement. A table can have at most one row deletion policy. Adding a row deletion policy to a table with an existing policy fails with an error. See TTL on generated columns to specify more sophisticated row deletion logic.

ALTER TABLE Albums
ADD ROW DELETION POLICY (OLDER_THAN(timestamp_column, INTERVAL num_days DAY));

PostgreSQL

To create a row deletion policy using PostgreSQL, you can define a TTL INTERVAL clause when you create a new table, or add a policy to an existing table.

To add a policy at the time of table creation:

CREATE TABLE mytable (
  key bigint NOT NULL,
  timestamp_column_name TIMESTAMPTZ,
  PRIMARY KEY(key)
) TTL INTERVAL interval_spec ON timestamp_column_name;

Where:

timestamp_column_name must be a column with data type TIMESTAMPTZ. You need to create this column in the CREATE TABLE statement. Columns with commit timestamps are valid, as are generated columns. However, you cannot specify a generated column that references a commit timestamp column.
interval_spec is the number of days past the timestamp in the timestamp_column_name on which the row is marked for deletion. The value must be a non-negative integer and it must evaluate to a whole number of days. For example, '3 days' is allowed, but '3 days - 2 minutes' returns an error.

To add a policy to an existing table, use the ALTER TABLE statement. A table can have at most one TTL policy. Adding a TTL policy to a table with an existing policy fails with an error. See TTL on generated columns to specify more sophisticated TTL logic.

To add a policy to an existing table:

ALTER TABLE albums
ADD COLUMN timestampcolumn TIMESTAMPTZ;

ALTER TABLE albums
ADD TTL INTERVAL '5 days' ON timestampcolumn;

Restrictions

Row deletion policies have the following restrictions.

TTL on tables referenced by a foreign key

You cannot create a row deletion policy:

On a table that is referenced by a foreign key that does not include the ON DELETE CASCADE constraint.
On the parent of a table that is referenced by a foreign key that does not include the ON DELETE CASCADE referential action.

In the following example, you cannot add a row deletion policy to the Customers table, because it is referenced by a foreign key in the Orders table, which does not have the ON DELETE CASCADE constraint. Deleting customers might violate this foreign key constraint. You also cannot add a row deletion policy to the Districts table. Deleting a row from Districts might cause deletes to cascade in the child Customers table, which might violate the foreign key constraint on the Orders table.

GoogleSQL

CREATE TABLE Districts (
  DistrictID INT64
) PRIMARY KEY (DistrictID);

CREATE TABLE Customers (
  DistrictID INT64,
  CustomerID INT64,
  CreatedAt TIMESTAMP
) PRIMARY KEY (DistrictID, CustomerID),
INTERLEAVE IN PARENT Districts ON DELETE CASCADE;

CREATE TABLE Orders (
  OrderID INT64,
  DistrictID INT64,
  CustomerID INT64,
  CONSTRAINT FK_CustomerOrder FOREIGN KEY (DistrictID, CustomerID) REFERENCES Customers (DistrictID, CustomerID)
) PRIMARY KEY (OrderID);

PostgreSQL

CREATE TABLE districts (
  districtid   bigint NOT NULL,
  PRIMARY KEY(districtid)
);

CREATE TABLE customers (
  districtid   bigint NOT NULL,
  customerid   bigint NOT NULL,
  createdat  timestamptz,
  PRIMARY KEY(districtid, customerid)
) INTERLEAVE IN PARENT districts ON DELETE CASCADE;

CREATE TABLE orders (
  orderid bigint NOT NULL,
  districtid   bigint,
  customerid bigint,
  PRIMARY KEY(orderid),
  CONSTRAINT fk_customerorder FOREIGN KEY (districtid, customerid) REFERENCES customers (districtid, customerid)
);

You can create a row deletion policy on a table that is referenced by a foreign key constraint that uses ON DELETE CASCADE. In the following example, you can create a row deletion policy on the Customers table which is referenced by the foreign key constraint CustomerOrder, defined on the Orders table. When TTL deletes rows in Customers, the deletion cascades down to matching rows that are in the Orders table.

GoogleSQL

 CREATE TABLE Districts (
  DistrictID INT64,
  CreatedAt TIMESTAMP
) PRIMARY KEY (DistrictID),
ROW DELETION POLICY (OLDER_THAN(CreatedAt, INTERVAL 1 DAY));

CREATE TABLE Customers (
  DistrictID INT64,
  CustomerID INT64,
  CreatedAt TIMESTAMP
) PRIMARY KEY (DistrictID, CustomerID),
INTERLEAVE IN PARENT Districts ON DELETE CASCADE,
ROW DELETION POLICY (OLDER_THAN(CreatedAt, INTERVAL 1 DAY));

CREATE TABLE Orders (
  OrderID INT64,
  DistrictID INT64,
  CustomerID INT64,
  CONSTRAINT FK_CustomerOrder FOREIGN KEY (DistrictID, CustomerID) REFERENCES Customers (DistrictID, CustomerID) ON DELETE CASCADE
) PRIMARY KEY (OrderID);

PostgreSQL

CREATE TABLE districts (
  districtid   bigint NOT NULL,
  createdat  timestamptz,
  PRIMARY KEY(districtid)
) TTL INTERVAL '1 day' ON createdat;

CREATE TABLE customers (
  districtid   bigint NOT NULL,
  customerid   bigint NOT NULL,
  createdat  timestamptz,
  PRIMARY KEY(districtid, customerid)
) INTERLEAVE IN PARENT districts ON DELETE CASCADE
TTL INTERVAL '1 day' ON createdat;

CREATE TABLE orders (
  orderid bigint NOT NULL,
  districtid bigint,
  customerid bigint,
  PRIMARY KEY(orderid),
  CONSTRAINT fk_customerorder FOREIGN KEY (districtid, customerid) REFERENCES customers (districtid, customerid) ON DELETE CASCADE
);

Similarly, you can create a row deletion policy on a parent of a table that is referenced by a ON DELETE CASCADE foreign key constraint.

TTL on columns with default values

A row deletion policy can use a timestamp column with a default value. A typical default value is CURRENT_TIMESTAMP. If no value is explicitly assigned to the column, or if the column is set to its default value by an INSERT or UPDATE statement, the default value is used in the rule calculation.

In the following example, the default value for the column CreatedAt in table Customers is the timestamp at which the row is created.

GoogleSQL

CREATE TABLE Customers (
  CustomerID INT64,
  CreatedAt TIMESTAMP DEFAULT (CURRENT_TIMESTAMP())
) PRIMARY KEY (CustomerID);

For more information, see DEFAULT (expression) in "GoogleSQL data definition language."

PostgreSQL

CREATE TABLE customers (
  customerid bigint NOT NULL,
  createdat timestamptz DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(customerid)
  );

For more information, see CREATE TABLE in "PostgreSQL data definition language."

TTL on generated columns

Row deletion policies can use generated columns to express more sophisticated rules. For example, you can define a row deletion policy on the greatest timestamp (GoogleSQL or PostgreSQL) of multiple columns, or map another value to a timestamp.

GoogleSQL

The following table named Orders tracks sales orders. The table owner wants to set up a row deletion policy that deletes cancelled orders after 30 days, and non-cancelled orders after 180 days.

Spanner TTL only allows one row deletion policy per table. To express the two criteria in a single column, you can use a generated column with an IF statement:

CREATE TABLE Orders (
  OrderId INT64 NOT NULL,
  OrderStatus STRING(30) NOT NULL,
  LastModifiedDate TIMESTAMP NOT NULL,
  ExpiredDate TIMESTAMP AS (IF(OrderStatus = 'Cancelled',
    TIMESTAMP_ADD(LastModifiedDate, INTERVAL 30 DAY),
    TIMESTAMP_ADD(LastModifiedDate, INTERVAL 180 DAY))) STORED,
) PRIMARY KEY(OrderId),
ROW DELETION POLICY (OLDER_THAN(ExpiredDate, INTERVAL 0 DAY));

The statement creates a column named ExpiredDate that adds either 30 days or 180 days to the LastModifiedDate depending on the order status. Then, it defines the row deletion policy to expire rows on the day stored in the ExpiredDate column by specifying INTERVAL 0 day.

PostgreSQL

The following table named Orders tracks sales orders. The table owner wants to set up a row deletion policy that deletes rows after 30 days of inactivity.

Spanner TTL only allows one row deletion policy per table. To express the two criteria in a single column, you can create a generated column:

CREATE TABLE orders (
    orderid bigint NOT NULL,
    orderstatus varchar(30) NOT NULL,
    createdate timestamptz NOT NULL,
    lastmodifieddate timestamptz,
    expireddate timestamptz GENERATED ALWAYS AS (GREATEST(createdate, lastmodifieddate)) STORED,
    PRIMARY KEY(orderid)
) TTL INTERVAL '30 days' ON expireddate;

The statement creates a generated column named ExpiredDate that evaluates the most recent of the two dates (LastModifiedDate or CreateDate). Then, it defines the row deletion policy to expire rows 30 days after the order was created, or if the order was modified within those 30 days, it'll extend the deletion by another 30 days.

TTL and interleaved tables

Interleaved tables are a performance optimization that associates related rows in a one-to-many child table with a row in a parent table. To add a row deletion policy on a parent table, all interleaved child tables must specify ON DELETE CASCADE, meaning the child rows will be deleted atomically with the parent row. This ensures referential integrity such that deletes on the parent table also delete the related child rows in the same transaction. Spanner TTL does not support ON DELETE NO ACTION.

Maximum transaction size

Spanner has a transaction size limit. Cascading deletes on large parent-child hierarchies with indexed columns could exceed these limits and cause one or more TTL operations to fail. For failed operations, TTL will retry with smaller batches, down to a single parent row. However, large child hierarchies for even a single parent row may still exceed the mutation limit.

Failed operations are reported in TTL metrics.

If a single row and its interleaved children is too large to delete, you can attach a row deletion policy directly on the child tables, in addition to the one on the parent table. The policy on child tables should be configured such that child rows are deleted prior to parent rows.

Consider attaching a row deletion policy to child tables when the following two statements apply:

The child table has any global indexes associated with it; and
You expect a large number of (>100) child rows per parent row.

Delete a row deletion policy

You can drop an existing row deletion policy from a table. This returns an error if there is no existing row deletion policy on the table.

GoogleSQL

ALTER TABLE MyTable
DROP ROW DELETION POLICY;

PostgreSQL

ALTER TABLE mytable
DROP TTL;

Deleting a row deletion policy immediately aborts any TTL processes running in the background. Any rows already deleted by the in-progress processes remain deleted.

Delete a column referenced by a row deletion policy

Spanner doesn't let you delete a column that is referenced by a row deletion policy. You must first delete the row deletion policy before deleting the column.

View the row deletion policy of a table

You can view the row deletion policies of your Spanner tables.

GoogleSQL

SELECT TABLE_NAME, ROW_DELETION_POLICY_EXPRESSION
FROM INFORMATION_SCHEMA.TABLES
WHERE ROW_DELETION_POLICY_EXPRESSION IS NOT NULL;

For more information, see Information schema for GoogleSQL-dialect databases.

PostgreSQL

SELECT table_name, row_deletion_policy_expression
FROM information_schema.tables
WHERE row_deletion_policy_expression is not null;

For more information, see Information schema for PostgreSQL-dialect databases.

Modify a row deletion policy

You can alter the column or the interval expression of an existing row deletion policy. The following example switches the column from CreatedAt to ModifiedAt and extends the interval from 1 DAY to 7 DAY. This returns an error if there is no existing row deletion policy on the table.

GoogleSQL

ALTER TABLE MyTable
REPLACE ROW DELETION POLICY (OLDER_THAN(ModifiedAt, INTERVAL 7 DAY));

PostgreSQL

ALTER TABLE mytable
ALTER TTL INTERVAL '7 days' ON timestampcolumn;