Is Google Cloud Spanner table have any TTL(Time to Live) setup for required data or table

Question

Dear Cloud Spanner Support

Is there any option for TTL(Time to Live) on Google Cloud Spanner table .

If yes Please provide any additional information

Thank you Sreeni

Answer 1

Sorry, it's not supported at the moment. It is recommended to submit this as a feature request to the engineering team in the Google Public Issue Tracker.

Answer 2

As of 21-July-2021, Cloud Spanner supports table TTL (in public preview) via the ROW DELETION POLICY clause of CREATE TABLE or ADD DELETION POLICY clause of ALTER TABLE

See the Cloud Documentation for additional information.

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

Answer 3

Let suppose we have following scenario: You have table with say load_date and data should be deleted when load_date < now - 30 You can create parent table with only one column load_date (and maybe some additional stats per this day if needed) - after this you create interleaved child table with all information and with cascade deletion So when you need to clean up child table you just delete one record from parent and all associated data will be removed from child automatically

CREATE TABLE parent (
  parent_id STRING(MAX) NOT NULL,
  load_time TIMESTAMP
) PRIMARY KEY(parent_id);

CREATE TABLE child (
  parent_id STRING(MAX) NOT NULL,
  child_id INT64 NOT NULL,
  data_1 STRING(MAX) NOT NULL,
  data_2 INT64 NOT NULL
) PRIMARY KEY(parent_id, child_id),
  INTERLEAVE IN PARENT parent ON DELETE CASCADE;