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 typeTIMESTAMP
. 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 thetimestamp_column
in which the row is marked for deletion. The value must be a non-negative integer andDAY
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 typeTIMESTAMPTZ
. You need to create this column in theCREATE 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 thetimestamp_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;