使用 TTL 管理資料保留期限

本頁面將說明如何在 Google SQL 方言資料庫和 PostgreSQL 方言資料庫的 Spanner 資料表中，使用存留期限 (TTL)。詳情請參閱「關於存留時間」。

事前準備

開始前，請遵循下列最佳做法。

啟用備份和時間點復原功能

建議您先啟用 Spanner 備份和還原功能，再將 TTL 新增至資料表。這樣一來，如果您不小心使用 TTL 政策刪除資料，就能完全還原資料庫。

如果您已啟用指定時間點復原功能，只要刪除的資料仍在設定的版本保留期限內，您就能查看及還原刪除的資料，而無需從備份中完整還原資料。如要瞭解如何讀取過去的資料，請參閱「執行過時讀取」。

清理舊資料

如果您是第一次使用 TTL，且預期第一次執行時會刪除許多資料列，建議您先使用分區 DML 手動清理舊資料。這樣您就能進一步控管資源使用情形，而非交由 TTL 背景程序處理。TTL 會以低優先順序執行，非常適合用於逐步清理。不過，由於 Spanner 的內部工作排程器會將優先順序給予其他工作 (例如使用者查詢)，因此這可能會延長刪除繁忙資料庫中初始資料列組所需的時間。

確認條件

針對 GoogleSQL 資料表，如果您想在啟用 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 類型的現有資料欄。含有修訂時間戳記的資料欄和產生的資料欄皆有效。不過，您無法指定參照修訂時間戳記欄的產生資料欄。

  • num_days 是 timestamp_column 中標示要刪除資料列的時間戳記過後的天數。這個值必須為非負整數，且僅支援 DAY 這個單位。

如要在現有資料表中新增政策，請使用 ALTER TABLE 陳述式。每個資料表最多只能有一個資料列刪除政策。在已有政策的資料表中新增資料列刪除政策時，系統會傳回錯誤。請參閱「產生資料欄的 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_specvar> ON timestamp_column_name;

更改下列內容：

  • timestamp_column_name 必須是資料類型 TIMESTAMPTZ 的欄。您需要在 CREATE TABLE 陳述式中建立這個欄。含有修訂時間戳記的資料欄和產生的資料欄皆有效。不過，您無法指定參照修訂時間戳記欄的產生資料欄。

  • interval_spec 是 timestamp_column_name 中所標示要刪除資料列的時間戳記過後的天數。值必須為非負整數，且必須評估為整數天數。舉例來說，系統允許 '3 days'，但 '3 days - 2 minutes' 會傳回錯誤。

如要在現有資料表中新增政策，請使用 ALTER TABLE 陳述式。一個資料表最多只能有一個 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 外鍵限制參照的資料表父項上建立資料列刪除政策。

含有預設值的資料欄的存留時間

資料列刪除政策可以使用含有預設值的時間戳記資料欄。一般預設值為 CURRENT_TIMESTAMP。如果沒有明確將值指派給資料欄，或是資料欄已由 INSERT 或 UPDATE 陳述式設為預設值，則會在規則計算中使用預設值。

在以下範例中，表格 Customers 中 CreatedAt 資料欄的預設值是建立資料列的時間戳記。

GoogleSQL 

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

詳情請參閱「DEFAULT (運算式)」。

PostgreSQL

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

詳情請參閱 CREATE TABLE。

產生欄的存留時間

資料列刪除政策可以使用產生的資料欄，表達更複雜的規則。舉例來說，您可以針對多個資料欄的 greatest 時間戳記 (GoogleSQL 或 PostgreSQL) 定義資料列刪除政策，或是將其他值對應至時間戳記。

GoogleSQL

下表名為 Orders，用於追蹤銷售訂單。表格擁有者想設定一項資料列刪除政策，在 30 天後刪除已取消的訂單，在 180 天後刪除未取消的訂單。

Spanner TTL 僅允許每個資料表有一個資料列刪除政策。如要在單一資料欄中表示兩個條件，您可以使用產生的資料欄搭配 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 僅允許每個資料表有一個資料列刪除政策。如要在單一資料欄中表示兩個條件，您可以建立產生的資料欄：

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;

這項陳述式會建立名為 ExpiredDate 的產生欄，用於評估兩個日期 (LastModifiedDate 或 CreateDate) 中較近期的日期。接著，它會定義資料列刪除政策，在訂單建立後 30 天後讓資料列到期，如果訂單在 30 天內經過修改，則會將刪除時間延長 30 天。

存留時間和交錯式資料表

交錯式資料表是一種效能最佳化做法，可將一對多子資料表中的相關資料列與父項資料表中的資料列建立關聯。如要在父項資料表上新增資料列刪除政策，所有交錯式子項資料表都必須指定 ON DELETE CASCADE，表示子項列會與父項列一起以原子方式刪除。這可確保參照完整性，例如刪除父項資料表時，也會刪除相同交易中的相關子項資料列。Spanner TTL 不支援 ON DELETE NO ACTION。

交易金額上限

Spanner 有交易大小限制。在含有索引欄的大型父子階層上執行連鎖刪除作業，可能會超出這些限制，並導致一或多個 TTL 作業失敗。針對失敗的作業，TTL 會以較小的批次重試，直到單一父項資料列為止。不過，即使單一父項資料列的子項階層龐大，仍可能會超出變異限制。

失敗的作業會在 TTL 指標中回報。

如果單一資料列及其交錯式子項過大而無法刪除，除了在父項資料表中附加資料列刪除政策外，您也可以直接在子項資料表中附加這項政策。您應設定子項資料表的政策，以便在刪除父項資料列之前先刪除子項資料列。

在下列兩種情況下，建議您為子項資料表附加資料列刪除政策：

  • 子資料表含有任何相關聯的全域索引。
  • 每個父項資料列都會有大量的子項資料列 (超過 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;