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();
To change your database's schema, you must have spanner.databases.updateDdl permission. See Access control for Cloud Spanner for details.
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));
timestamp_columnmust 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_daysis the number of days past the timestamp in the
timestamp_columnon which the row is marked for deletion. The value must be a non-negative integer and
DAYis 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));
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
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.
The following example shows a table that tracks sales orders in
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
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
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
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
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));