Manage data retention with TTL

This page discusses how to use time to live (TTL) on Spanner tables inGoogleSQL-dialect databases and PostgreSQL-dialect databases. For more information, seeAbout 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 enablingSpanner backup and restore. This letsyou fully restore a database in case you accidentally delete your data withthe TTL policy.

If you've enabledpoint-in-time recovery, you can viewand restore deleted data—without a full restore from backup—if it'swithin the configuredversion retention period. Forinformation on reading data in the past, seePerform a stale read.

Clean up old data

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

Verify your conditions

For GoogleSQL tables, if you want to verify the data that therow deletion policy affects before enabling TTL, you can query your tableusing the same conditions. For example:

GoogleSQL

SELECTCOUNT(*)FROMCalculatedRoutesWHERETIMESTAMP_ADD(CreatedAt,INTERVAL30DAY) <CURRENT_TIMESTAMP();

Required permissions

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

Create a row deletion policy

GoogleSQL

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

To add a policy at the time of table creation, do the following:

CREATETABLEMyTable(KeyINT64,CreatedAtTIMESTAMP,)PRIMARYKEY(Key),ROWDELETIONPOLICY(OLDER_THAN(timestamp_column,INTERVALnum_daysDAY));

Replace the following:

  • timestamp_column must be an existing columnwith typeTIMESTAMP. Columns withcommit timestamps are valid, as aregenerated columns. However, you can'tspecify a generated column that references a commit timestamp column.

  • num_days is the number of days past thetimestamp in thetimestamp_column in which the row is marked fordeletion. The value must be a non-negative integer andDAY is the onlysupported unit.

To add a policy to an existing table, use theALTER TABLE statement. Atable can have at most one row deletion policy. Adding a row deletion policyto a table with an existing policy fails with an error. SeeTTL on generated columns to specify moresophisticated row deletion logic.

ALTERTABLEAlbumsADDROWDELETIONPOLICY(OLDER_THAN(timestamp_column,INTERVALnum_daysDAY));

PostgreSQL

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

To add a policy at the time of table creation, do the following:

CREATETABLEmytable(keybigintNOTNULL,timestamp_column_nameTIMESTAMPTZ,PRIMARYKEY(key))TTLINTERVALinterval_specvar>ONtimestamp_column_name;

Replace the following:

  • timestamp_column_name must be a column withdata typeTIMESTAMPTZ. You need to create this column in theCREATETABLE statement. Columns withcommit timestamps are valid, as aregenerated columns. However, you can'tspecify a generated column that references a commit timestamp column.

  • interval_spec is the number of days past thetimestamp in thetimestamp_column_name on which the row is marked fordeletion. The value must be a non-negative integer and it must evaluateto a whole number of days. For example,'3 days' is allowed, but'3days - 2 minutes' returns an error.

To add a policy to an existing table, use theALTER TABLE statement. Atable can have at most one TTL policy. Adding a TTL policy to a table withan existing policy fails with an error. SeeTTL on generated columns to specify moresophisticated TTL logic.

To add a policy to an existing table, do the following:

ALTERTABLEalbumsADDCOLUMNtimestampcolumnTIMESTAMPTZ;ALTERTABLEalbumsADDTTLINTERVAL'5 days'ONtimestampcolumn;

Restrictions

Row deletion policies have the following restrictions.

TTL on tables referenced by a foreign key

You can't create a row deletion policy:

  • On a table that's referenced by aforeign key that doesn't include theONDELETE CASCADE constraint.
  • On the parent of a table that's referenced by a foreign key that doesn'tinclude the ON DELETE CASCADE referential action.

In the following example, you can't add a row deletion policy to theCustomers table, because it's referenced by a foreign key in theOrderstable, which doesn't have theON DELETE CASCADE constraint.Deleting customers might violate this foreign key constraint. You also can'tadd a row deletion policy to theDistricts table. Deleting a row fromDistricts might cause deletes to cascade in the childCustomers table, whichmight violate the foreign key constraint on theOrders table.

GoogleSQL

CREATETABLEDistricts(DistrictIDINT64)PRIMARYKEY(DistrictID);CREATETABLECustomers(DistrictIDINT64,CustomerIDINT64,CreatedAtTIMESTAMP)PRIMARYKEY(DistrictID,CustomerID),INTERLEAVEINPARENTDistrictsONDELETECASCADE;CREATETABLEOrders(OrderIDINT64,DistrictIDINT64,CustomerIDINT64,CONSTRAINTFK_CustomerOrderFOREIGNKEY(DistrictID,CustomerID)REFERENCESCustomers(DistrictID,CustomerID))PRIMARYKEY(OrderID);

PostgreSQL

CREATETABLEdistricts(districtidbigintNOTNULL,PRIMARYKEY(districtid));CREATETABLEcustomers(districtidbigintNOTNULL,customeridbigintNOTNULL,createdattimestamptz,PRIMARYKEY(districtid,customerid))INTERLEAVEINPARENTdistrictsONDELETECASCADE;CREATETABLEorders(orderidbigintNOTNULL,districtidbigint,customeridbigint,PRIMARYKEY(orderid),CONSTRAINTfk_customerorderFOREIGNKEY(districtid,customerid)REFERENCEScustomers(districtid,customerid));

You can create a row deletion policy on a table that's referenced by a foreignkey constraint that usesON DELETE CASCADE. In the following example, you cancreate a row deletion policy on theCustomers table which is referenced bythe foreign key constraintCustomerOrder, defined on theOrders table. WhenTTL deletes rows inCustomers, the deletion cascades down to matching rowsthat are in theOrders table.

GoogleSQL

CREATETABLEDistricts(DistrictIDINT64,CreatedAtTIMESTAMP)PRIMARYKEY(DistrictID),ROWDELETIONPOLICY(OLDER_THAN(CreatedAt,INTERVAL1DAY));CREATETABLECustomers(DistrictIDINT64,CustomerIDINT64,CreatedAtTIMESTAMP)PRIMARYKEY(DistrictID,CustomerID),INTERLEAVEINPARENTDistrictsONDELETECASCADE,ROWDELETIONPOLICY(OLDER_THAN(CreatedAt,INTERVAL1DAY));CREATETABLEOrders(OrderIDINT64,DistrictIDINT64,CustomerIDINT64,CONSTRAINTFK_CustomerOrderFOREIGNKEY(DistrictID,CustomerID)REFERENCESCustomers(DistrictID,CustomerID)ONDELETECASCADE)PRIMARYKEY(OrderID);

PostgreSQL

CREATETABLEdistricts(districtidbigintNOTNULL,createdattimestamptz,PRIMARYKEY(districtid))TTLINTERVAL'1 day'ONcreatedat;CREATETABLEcustomers(districtidbigintNOTNULL,customeridbigintNOTNULL,createdattimestamptz,PRIMARYKEY(districtid,customerid))INTERLEAVEINPARENTdistrictsONDELETECASCADETTLINTERVAL'1 day'ONcreatedat;CREATETABLEorders(orderidbigintNOTNULL,districtidbigint,customeridbigint,PRIMARYKEY(orderid),CONSTRAINTfk_customerorderFOREIGNKEY(districtid,customerid)REFERENCEScustomers(districtid,customerid)ONDELETECASCADE);

Similarly, you can create a row deletion policy on a parent of a table that'sreferenced by aON 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 typicaldefault value isCURRENT_TIMESTAMP. If no value is explicitly assigned tothe column, or if the column is set to its default value by anINSERT orUPDATE statement, the default value is used in the rule calculation.

In the following example, the default value for the columnCreatedAt in tableCustomers is the timestamp at which the row is created.

GoogleSQL

CREATETABLECustomers(CustomerIDINT64,CreatedAtTIMESTAMPDEFAULT(CURRENT_TIMESTAMP()))PRIMARYKEY(CustomerID);

For more information, seeDEFAULT (expression).

PostgreSQL

CREATETABLEcustomers(customeridbigintNOTNULL,createdattimestamptzDEFAULTCURRENT_TIMESTAMP,PRIMARYKEY(customerid));

For more information, seeCREATE TABLE.

TTL on generated columns

Row deletion policies can usegenerated columnsto express more sophisticated rules. For example, you can define a row deletionpolicy on thegreatest timestamp (GoogleSQLorPostgreSQL)of multiple columns, or map another value to a timestamp.

Note: You can't specify a generated column that references acommit timestamp column.

GoogleSQL

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

Spanner TTL only allows one row deletion policy per table. Toexpress the two criteria in a single column, you can use a generated columnwith anIF statement:

CREATETABLEOrders(OrderIdINT64NOTNULL,OrderStatusSTRING(30)NOTNULL,LastModifiedDateTIMESTAMPNOTNULL,ExpiredDateTIMESTAMPAS(IF(OrderStatus='Cancelled',TIMESTAMP_ADD(LastModifiedDate,INTERVAL30DAY),TIMESTAMP_ADD(LastModifiedDate,INTERVAL180DAY)))STORED,)PRIMARYKEY(OrderId),ROWDELETIONPOLICY(OLDER_THAN(ExpiredDate,INTERVAL0DAY));

The statement creates a column namedExpiredDate that adds either 30 daysor 180 days to theLastModifiedDate depending on the order status. Then,it defines the row deletion policy to expire rows on the day stored in theExpiredDate column by specifyingINTERVAL 0 day.

PostgreSQL

The following table namedOrders tracks sales orders.The table owner wants to set up a row deletion policy that deletes rowsafter 30 days of inactivity.

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

CREATETABLEorders(orderidbigintNOTNULL,orderstatusvarchar(30)NOTNULL,createdatetimestamptzNOTNULL,lastmodifieddatetimestamptz,expireddatetimestamptzGENERATEDALWAYSAS(GREATEST(createdate,lastmodifieddate))STORED,PRIMARYKEY(orderid))TTLINTERVAL'30 days'ONexpireddate;

The statement creates a generated column namedExpiredDate that evaluatesthe most recent of the two dates (LastModifiedDate orCreateDate).Then, it defines the row deletion policy to expire rows 30 days after theorder 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 tablesare a performance optimization that associates related rows in a one-to-manychild table with a row in a parent table. To add a row deletion policy on aparent table, all interleaved child tables must specifyON DELETE CASCADE,meaning the child rows are deleted atomically with the parent row. Thisensures referential integrity such that deletes on the parent table also deletethe related child rows in the same transaction. Spanner TTL doesnot supportON DELETE NO ACTION.

Maximum transaction size

Spanner has atransaction size limit.Cascading deletes on large parent-child hierarchies with indexed columns couldexceed these limits and cause one or more TTL operations to fail. For failedoperations, TTL retries with smaller batches, down to a single parent row.However, large child hierarchies for even a single parent row could still exceedthe mutation limit.

Failed operations are reported inTTL metrics.

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

Consider attaching a row deletion policy to child tables when the following twostatements 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 errorif there's no existing row deletion policy on the table.

GoogleSQL

ALTERTABLEMyTableDROPROWDELETIONPOLICY;

PostgreSQL

ALTERTABLEmytableDROPTTL;

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

Delete a column referenced by a row deletion policy

Spanner doesn't let you delete a column that's referencedby a row deletion policy. You must firstdelete the row deletion policy beforedeleting the column.

View the row deletion policy of a table

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

GoogleSQL

SELECTTABLE_NAME,ROW_DELETION_POLICY_EXPRESSIONFROMINFORMATION_SCHEMA.TABLESWHEREROW_DELETION_POLICY_EXPRESSIONISNOTNULL;

For more information, seeInformation schema for GoogleSQL-dialect databases.

PostgreSQL

SELECTtable_name,row_deletion_policy_expressionFROMinformation_schema.tablesWHERErow_deletion_policy_expressionisnotnull;

For more information, seeInformation schema for PostgreSQL-dialect databases.

Modify a row deletion policy

You can alter the column or the interval expression of an existing row deletionpolicy. The following example switches the column fromCreatedAt toModifiedAt and extends the interval from1 DAY to7 DAY. This returns anerror if there's no existing row deletion policy on the table.

GoogleSQL

ALTERTABLEMyTableREPLACEROWDELETIONPOLICY(OLDER_THAN(ModifiedAt,INTERVAL7DAY));

PostgreSQL

ALTERTABLEmytableALTERTTLINTERVAL'7 days'ONtimestampcolumn;

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-12-15 UTC.