TTL でデータの保持を管理する

このページでは、Spanner テーブルの有効期間（TTL）の使用方法について説明します。詳しくは、TTL についてをご覧ください。

準備

開始する前に、以下のベスト プラクティスに従ってください。

バックアップとポイントインタイム リカバリを有効にする

TTL をテーブルに追加する前に、Spanner のバックアップと復元を有効にすることをお勧めします。これにより、TTL ポリシーで誤ってデータを削除した場合に、データベースを完全に復元できます。

ポイントインタイム リカバリを有効にしている場合は、削除されたデータが構成されたバージョンの保持期間内にあれば、バックアップから完全に復元することなく、それを表示して復元できます。過去のデータの読み取りについては、ステイル読み取りを実行するをご覧ください。

古いデータをクリーンアップする

TTL を使用するのが初めてで、最初の実行で多数の行を削除する場合は、まずパーティション分割 DMLを手動で使用する古いデータのクリーンアップを検討します。これにより、リソースの使用状況を TTL バックグラウンド プロセスにまかせるのではなく、より細かく制御できます。TTL は低い優先度で実行され、増分クリーンアップに最適です。ただし、Spanner の内部作業スケジューラはユーザークエリなどの他の作業を優先するため、ビジー状態のデータベースで最初の行セットを削除するための時間が長くなる可能性があります。

条件を確認する

Google SQL テーブルでは、TTL を有効にする前に行削除ポリシーが影響を受けるデータを確認する場合、同じ条件を使用してテーブルに対してクエリを実行できます。例:

GoogleSQL

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

必要な権限

データベースのスキーマを変更するには、spanner.databases.updateDdl 権限を有する必要があります。詳細については、Spanner のアクセス制御をご覧ください。

行削除ポリシーを作成する

GoogleSQL

GoogleSQL を使用して行削除ポリシーを作成するには、新しいテーブルの作成時に ROW DELETION POLICY 句を定義するか、ポリシーを既存のテーブルに追加します。この句には列と間隔の式が含まれます。

テーブルの作成時にポリシーを追加するには:

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

ここで

• timestamp_column は、型が TIMESTAMP の既存の列である必要があります。commit タイムスタンプのある列は、生成された列のように、有効です。ただし、commit タイムスタンプ列を参照する生成された列は指定できません。

• num_days は、行が削除対象としてマークされている timestamp_column のタイムスタンプから経過した日数です。この値は、マイナスでない整数にする必要があります。単位は DAY のみサポートされています。

既存のテーブルにポリシーを追加するには、ALTER TABLE ステートメントを使用します。1 つのテーブルには、行の削除ポリシーを最大 1 つ含めることができます。ポリシーが存在するテーブルに行削除ポリシーを追加すると、エラーが発生します。さらに指定高度な行削除ロジックを指定するには、生成された列の TTLをご覧ください。

ALTER TABLE Albums
ADD ROW DELETION POLICY (OLDER_THAN(timestamp_column, INTERVAL num_days DAY));

PostgreSQL

PostgreSQL を使用して行削除ポリシーを作成するには、新しいテーブルの作成時に TTL INTERVAL 句を定義するか、ポリシーを既存のテーブルに追加します。

テーブルの作成時にポリシーを追加するには:

CREATE TABLE mytable (
  key bigint NOT NULL,
  timestamp_column_name TIMESTAMPTZ,
  PRIMARY KEY(key)
) TTL INTERVAL interval_spec ON timestamp_column_name;

ここで

• timestamp_column_name はデータ型 TIMESTAMPTZ を含む列である必要があります。この列は、CREATE TABLE ステートメントで作成する必要があります。commit タイムスタンプのある列は、生成された列のように、有効です。ただし、commit タイムスタンプ列を参照する生成された列は指定できません。

• interval_spec は、行が削除対象としてマークされている timestamp_column_name のタイムスタンプから経過した日数です。この値は、日数を表す負でない整数にする必要があります。たとえば、'3 days' は許されますが、'3 days - 2 minutes' はエラーを返します。

既存のテーブルにポリシーを追加するには、ALTER TABLE ステートメントを使用します。1 つのテーブルには、TTL ポリシーを最大 1 つ設定できます。ポリシーがすでに存在するテーブルに TTL ポリシーを追加すると、エラーが発生します。さらに高度な TTL ロジックを指定するには、生成された列の TTLをご覧ください。

既存のテーブルにポリシーを追加するには:

ALTER TABLE albums
ADD COLUMN timestampcolumn TIMESTAMPTZ;

ALTER TABLE albums
ADD TTL INTERVAL '5 days' ON timestampcolumn;

制限事項

行削除ポリシーには次の制限があります。

外部キーで参照されるテーブルの TTL

以下には行削除ポリシーを作成することはできません。

• ON DELETE CASCADE 制約を含まない外部キーで参照されるテーブル。
• ON DELETE CASCADE 参照アクションを含まない外部キーによって参照されるテーブルの親。

次の例では、Customers テーブルに行削除ポリシーを追加できません。これは、Orders テーブルの外部キーによって行削除ポリシーが参照されていて、ON DELETE CASCADE 制約がないためです。顧客を削除すると、この外部キー制約に違反する可能性があります。また、Districts テーブルに行削除ポリシーを追加することもできません。Districts から行を削除すると、子 Customers テーブルで削除がカスケードされ、Orders テーブルの外部キー制約に違反する可能性があります。

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)
);

ON DELETE CASCADE を使用する外部キー制約によって参照されるテーブルに行削除ポリシーを作成できます。次の例では、Customers テーブルに行削除ポリシーを作成できます。これは、Orders テーブルで定義された外部キー制約 CustomerOrder によって参照されます。TTL が Customers の行を削除すると、削除は Orders テーブル内の一致する行にカスケードされます。

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
);

同様に、ON DELETE CASCADE 外部キー制約によって参照されるテーブルの親に行削除ポリシーを作成できます。

デフォルト値が含まれる列に対する TTL

行削除ポリシーでは、デフォルト値があるタイムスタンプ列を使用できます。通常、デフォルト値は CURRENT_TIMESTAMP です。列に明示的に値が割り当てられていない場合や、INSERT または UPDATE ステートメントによって列がデフォルト値に設定されている場合は、デフォルト値がルール計算で使用されます。

次の例で、テーブル Customers にある列 CreatedAt のデフォルト値は、行が作成されたタイムスタンプです。

GoogleSQL

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

詳細については、GoogleSQL データ定義言語のDEFAULT（式）をご覧ください。

PostgreSQL

CREATE TABLE customers (
  customerid bigint NOT NULL,
  createdat timestamptz DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(customerid)
  );

詳細については、「PostgreSQL のデータ定義言語」のCREATE TABLE をご覧ください。

生成された列の TTL

行削除ポリシーでは、生成された列を使用して、より高度なルールを表現できます。たとえば、複数の列の greatest タイムスタンプ（GoogleSQL または PostgreSQL）に行削除ポリシーを定義することや、別の値をタイムスタンプにマッピングすることができます。

注: commit タイムスタンプ列を参照する生成された列を指定することはできません。

GoogleSQL

次のテーブル Orders は、販売注文を追跡します。テーブル所有者は、キャンセルされた注文を 30 日後、キャンセルされていない注文を 180 日後に削除する行削除ポリシーを設定したいと考えています。

Spanner TTL では、テーブルあたり 1 つの行削除ポリシーのみが許可されます。2 つの条件を 1 つの列に表現するには、IF ステートメントを含む生成列を使用します。

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));

ステートメントは、ExpiredDate という名前の列を作成し、注文ステータスに応じて 30 日または 180 日を LastModifiedDate に加算します。次に、INTERVAL 0 day を指定し、ExpiredDate 列に保存された日付の行を期限切れにするように、行削除ポリシーを定義します。

PostgreSQL

次のテーブル Orders は、販売注文を追跡します。テーブル オーナーは、30 日間の非アクティブ期間があった行を削除する行削除ポリシーを設定したいと考えています。

Spanner TTL では、テーブルあたり 1 つの行削除ポリシーのみが許可されます。2 つの条件を 1 つの列に表現するには、生成列を作成します。

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;

このステートメントは、2 つの日付（LastModifiedDate または CreateDate）の新しい方を求める ExpiredDate という名前の生成列を作成します。次に、注文後 30 日で行を期限切れにするか、その 30 日以内に注文が変更された場合は削除の期限がさらに 30 日延長される行削除ポリシーを定義します。

TTL とインターリーブされたテーブル

インターリーブされたテーブルは、1 対多の子テーブルにある関連する行を親テーブルの行に関連付けるパフォーマンス最適化です。親テーブルに行削除ポリシーを追加するには、インターリーブされた子テーブルで ON DELETE CASCADE を指定する必要があります。つまり、子行は親行とともにアトミックに削除されます。これにより、親テーブルに対する削除でも同じトランザクション内の関連する子行が削除されるように、参照整合性が確保されます。Spanner TTL は、ON DELETE NO ACTION をサポートしません。

トランザクションの最大サイズ

Spanner には、トランザクション サイズの上限があります。インデックス付きの列がある大規模な親子階層での削除のカスケードは、こうした上限を超え、1 つ以上の TTL オペレーションが失敗する可能性があります。オペレーションが失敗した場合、TTL は 1 つの親行まで縮小したバッチで再試行します。ただし、単一の親行でも、大きな子階層がミューテーションの上限を超える場合があります。

失敗したオペレーションは TTL 指標で報告されます。

1 つの行とそのインターリーブされた子が大きすぎて削除できない場合は、親テーブルのポリシーの他に、子テーブルにも行削除ポリシーを直接接続できます。子テーブルのポリシーは、親行より前に子行が削除されるように構成する必要があります。

次の 2 つのステートメントが適用される場合は、子テーブルに行削除ポリシーを付加することを検討してください。

• 子テーブルにはそれに関連付けられている任意のグローバル インデックスがあります。
• 親の行ごとに多数の（100 を超える）子の行が想定されます。

行削除ポリシーを削除する

既存の行削除ポリシーはテーブルから削除できます。テーブルに行削除ポリシーがない場合は、エラーが返されます。

GoogleSQL

ALTER TABLE MyTable
DROP ROW DELETION POLICY;

PostgreSQL

ALTER TABLE mytable
DROP TTL;

行削除ポリシーを削除すると、バックグラウンドで実行されている TTL プロセスが直ちに中止されます。進行中のプロセスですでに削除された行は、削除されたままになります。

行削除ポリシーによって参照されている列を削除する

Spanner では、行削除ポリシーによって参照されている列を削除できません。最初に、行削除ポリシーを削除してから、列を削除します。

テーブルの行削除ポリシーを表示する

Spanner テーブルの行削除ポリシーを表示できます。

GoogleSQL

SELECT TABLE_NAME, ROW_DELETION_POLICY_EXPRESSION
FROM INFORMATION_SCHEMA.TABLES
WHERE ROW_DELETION_POLICY_EXPRESSION IS NOT NULL;

詳細については、GoogleSQL 言語データベースの情報スキーマをご覧ください。

PostgreSQL

SELECT table_name, row_deletion_policy_expression
FROM information_schema.tables
WHERE row_deletion_policy_expression is not null;

詳細については、PostgreSQL 言語データベースの情報スキーマをご覧ください。

行削除ポリシーを変更する

既存の行削除ポリシーの列または間隔式は変更できます。次の例では、列を CreatedAt から ModifiedAt に切り替えて、間隔を 1 DAY から 7 DAY に拡張します。テーブルに行削除ポリシーがない場合は、エラーが返されます。

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;