Working with TTL

This page discusses time to live (TTL) on Cloud Spanner tables. To learn about TTL, read About TTL.

Before you begin

Enabling backup and point-in-time recovery

Before adding a row deletion policy to your table, we recommend enabling Cloud Spanner Backup and Restore. This allows you to fully restore a database in case you accidentally delete your data with a row deletion 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.

Cleaning up old data

If this is the first time you're adding a row deletion policy to a table and you expect the first run to delete many rows, consider first cleaning up old data manually via PartitionDML. This gives you more control over the resource usage, instead of leaving it to the TTL background process. Row deletion policies run 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 Cloud Spanner's internal work scheduler will prioritize other work, such as user queries.

Verifying your conditions

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

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

Required permissions

To change your database's schema, you must have spanner.databases.updateDdl permission. See Access control for Cloud Spanner for details.

Syntax

You define a row deletion policy on a table using a row deletion policy clause in a CREATE TABLE or ALTER TABLE statement. This clause contains an expression of a column and an interval.

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 on which the row is marked for deletion. The value must be a non-negative integer and DAY is the only supported unit.

Creating a row deletion policy

You can specify a row deletion policy when creating 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 INT64,
  CreatedAt TIMESTAMP,
) PRIMARY KEY (Key)
, ROW DELETION POLICY (OLDER_THAN(CreatedAt, INTERVAL 30 DAY));

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 MyTable ADD ROW DELETION POLICY (OLDER_THAN(CreatedAt, INTERVAL 1 DAY));

Restrictions

You cannot create a row deletion policy:

  • on a table that is referenced by a foreign key.
  • on the parent of a table that is referenced by a foreign key.

In the example below, you cannot add a row deletion policy to the Customers table, because it is referenced by a foreign key in the Orders table and deleting customers could violate this constraint.

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

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

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.

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

For more information, see DEFAULT (expression) in "Google Standard SQL 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 of multiple columns, or map another value to a timestamp.

Example

The following example shows a table that tracks sales orders in OrderTable. 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.

Cloud 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 OrderTable (
  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.

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. Cloud Spanner TTL does not support ON DELETE NO ACTION.

Maximum transaction size

Cloud 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 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.

Deleting 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.

ALTER TABLE MyTable DROP ROW DELETION POLICY;

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.

Deleting a column referenced by a row deletion policy

Cloud Spanner does not allow you to delete a column that is referenced by a row deletion policy. You must first delete the row deletion policy before deleting the column.

Modifying a row deletion policy

You can alter the column or the interval expression of an existing row deletion policy. The example below 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.

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