Datenaufbewahrung mit TTL verwalten

Auf dieser Seite wird erläutert, wie Sie die Gültigkeitsdauer (TTL) für Spanner-Tabellen verwenden. Weitere Informationen finden Sie unter Informationen zur TTL.

Hinweise

Beachten Sie die folgenden Best Practices, bevor Sie beginnen.

Sicherung und Wiederherstellung zu einem bestimmten Zeitpunkt aktivieren

Bevor Sie Ihrer Tabelle eine TTL hinzufügen, empfehlen wir, Spanner-Sicherung und -Wiederherstellung zu aktivieren. Auf diese Weise können Sie eine Datenbank vollständig wiederherstellen, für den Fall, dass Sie Ihre Daten mit der TTL-Richtlinie versehentlich löschen.

Wenn Sie die Wiederherstellung zu einem bestimmten Zeitpunkt aktiviert haben, können Sie gelöschte Daten – ohne vollständige Wiederherstellung aus der Sicherung – aufrufen und wiederherstellen, solange die für die Version konfigurierte Aufbewahrungsdauer noch nicht überschritten ist. Informationen zum Lesen von Daten in der Vergangenheit finden Sie unter Veralteten Lesevorgang durchführen.

Alte Daten bereinigen

Wenn Sie zum ersten Mal TTL verwenden und davon ausgehen, dass bei der ersten Ausführung viele Zeilen gelöscht werden, sollten Sie zuerst alte Daten mit partitionierter DML manuell bereinigen. Dadurch erhalten Sie mehr Kontrolle über die Ressourcennutzung, anstatt sich dem TTL-Hintergrundprozess zu überlassen. Die TTL wird mit niedriger Priorität ausgeführt, was sich ideal für eine schrittweise Bereinigung eignet. Dadurch wird jedoch wahrscheinlich die Zeit zum Löschen des ersten Zeilensatzes in einer ausgelasteten Datenbank verlängert, da der interne Arbeitsplaner von Spanner andere Aufgaben wie Nutzerabfragen priorisiert.

Bedingungen überprüfen

Wenn Sie bei GoogleSQL-Tabellen vor dem Aktivieren der TTL überprüfen möchten, welche Daten sich auf die Richtlinie zum Löschen von Zeilen auswirken, können Sie die Tabelle mit denselben Bedingungen abfragen. Beispiel:

GoogleSQL

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

Erforderliche Berechtigungen

Zum Ändern des Datenbankschemas benötigen Sie die Berechtigung spanner.databases.updateDdl. Weitere Informationen finden Sie unter Zugriffssteuerung für Spanner.

Richtlinie zum Löschen von Zeilen erstellen

GoogleSQL

Um mit GoogleSQL eine Richtlinie für das Löschen von Zeilen zu erstellen, können Sie beim Erstellen einer neuen Tabelle eine ROW DELETION POLICY-Klausel definieren oder einer vorhandenen Tabelle eine Richtlinie hinzufügen. Diese Klausel enthält einen Ausdruck einer Spalte und ein Intervall.

So fügen Sie eine Richtlinie zum Zeitpunkt der Tabellenerstellung hinzu:

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

Wobei:

• timestamp_column muss eine vorhandene Spalte vom Typ TIMESTAMP sein. Spalten mit Commit-Zeitstempeln sowie generierte Spalten sind gültig. Sie können jedoch keine generierte Spalte angeben, die auf eine Commit-Zeitstempelspalte verweist.

• num_days ist die Anzahl der Tage nach dem Zeitstempel in timestamp_column, in denen die Zeile zum Löschen markiert wird. Der Wert muss eine nicht negative Ganzzahl sein und DAY ist die einzige unterstützte Einheit.

Verwenden Sie die Anweisung ALTER TABLE, um einer vorhandenen Tabelle eine Richtlinie hinzuzufügen. Eine Tabelle kann nur eine Richtlinie zum Löschen von Zeilen haben. Das Hinzufügen einer Richtlinie für das Löschen von Zeilen zu einer Tabelle mit einer vorhandenen Richtlinie schlägt mit einem Fehler fehl. Unter TTL für generierte Spalten finden Sie eine komplexere Logik für das Löschen von Zeilen.

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

PostgreSQL

Wenn Sie mit PostgreSQL eine Richtlinie für das Löschen von Zeilen erstellen möchten, können Sie beim Erstellen einer neuen Tabelle eine TTL INTERVAL-Klausel definieren oder einer vorhandenen Tabelle eine Richtlinie hinzufügen.

So fügen Sie eine Richtlinie zum Zeitpunkt der Tabellenerstellung hinzu:

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

Wobei:

• timestamp_column_name muss eine Spalte mit dem Datentyp TIMESTAMPTZ sein. Sie müssen diese Spalte in der CREATE TABLE-Anweisung erstellen. Spalten mit Commit-Zeitstempeln sowie generierte Spalten sind gültig. Sie können jedoch keine generierte Spalte angeben, die auf eine Commit-Zeitstempelspalte verweist.

• interval_spec ist die Anzahl der Tage nach dem Zeitstempel in timestamp_column_name, für die die Zeile zum Löschen markiert wird. Der Wert muss eine nicht negative Ganzzahl sein und eine ganze Anzahl von Tagen ergeben. Beispielsweise ist '3 days' zulässig, aber '3 days - 2 minutes' gibt einen Fehler zurück.

Verwenden Sie die Anweisung ALTER TABLE, um einer vorhandenen Tabelle eine Richtlinie hinzuzufügen. Eine Tabelle kann nur eine TTL-Richtlinie haben. Das Hinzufügen einer TTL-Richtlinie zu einer Tabelle mit einer vorhandenen Richtlinie schlägt mit einem Fehler fehl. Unter TTL in generierten Spalten finden Sie eine komplexere TTL-Logik.

So fügen Sie einer vorhandenen Tabelle eine Richtlinie hinzu:

ALTER TABLE albums
ADD COLUMN timestampcolumn TIMESTAMPTZ;

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

Einschränkungen

Für Richtlinien zum Löschen von Zeilen gelten die folgenden Einschränkungen.

TTL für Tabellen, auf die durch einen Fremdschlüssel verwiesen wird

In folgenden Fällen können Sie keine Richtlinie für das Löschen von Zeilen erstellen:

• Für eine Tabelle, auf die von einem Fremdschlüssel verwiesen wird, der nicht die Einschränkung ON DELETE CASCADE enthält.
• Für das übergeordnete Element einer Tabelle, auf die durch einen Fremdschlüssel verwiesen wird, der nicht die referenzielle Aktion ON DELETE CASCADE enthält.

Im folgenden Beispiel können Sie der Tabelle Customers keine Richtlinie zum Löschen von Zeilen hinzufügen, da von einem Fremdschlüssel in der Tabelle Orders darauf verwiesen wird, für die die Einschränkung ON DELETE CASCADE nicht gilt. Wenn Sie Kunden löschen, verstoßen Sie möglicherweise gegen diese Fremdschlüsseleinschränkung. Außerdem können Sie der Tabelle Districts keine Richtlinie zum Löschen von Zeilen hinzufügen. Das Löschen einer Zeile aus Districts kann dazu führen, dass Löschvorgänge in der untergeordneten Tabelle Customers kaskadiert werden, was möglicherweise gegen die Fremdschlüsseleinschränkung für die Tabelle Orders verstößt.

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

Sie können eine Richtlinie zum Löschen von Zeilen für eine Tabelle erstellen, auf die von einer Fremdschlüsseleinschränkung verwiesen wird, die ON DELETE CASCADE verwendet. Im folgenden Beispiel können Sie eine Richtlinie zum Löschen von Zeilen für die Tabelle Customers erstellen, auf die von der Fremdschlüsseleinschränkung CustomerOrder verwiesen wird, die in der Tabelle Orders definiert ist. Wenn Zeilen in Customers gelöscht werden, erfolgt der Löschvorgang kaskadierend an die übereinstimmenden Zeilen in der Tabelle 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
);

Ebenso können Sie eine Richtlinie zum Löschen von Zeilen für ein übergeordnetes Element einer Tabelle erstellen, auf die durch eine Fremdschlüsseleinschränkung ON DELETE CASCADE verwiesen wird.

TTL für Spalten mit Standardwerten

Eine Richtlinie für das Löschen von Zeilen kann eine Zeitstempelspalte mit einem Standardwert verwenden. Ein typischer Standardwert ist CURRENT_TIMESTAMP. Wenn der Spalte kein Wert explizit zugewiesen ist oder wenn für die Spalte durch eine INSERT- oder UPDATE-Anweisung der Standardwert festgelegt wird, wird bei der Regelberechnung der Standardwert verwendet.

Im folgenden Beispiel ist der Standardwert für die Spalte CreatedAt in der Tabelle Customers der Zeitstempel, an dem die Zeile erstellt wird.

GoogleSQL

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

Weitere Informationen finden Sie unter STANDARD (Ausdruck) in "GoogleSQL-Datendefinitionssprache".

PostgreSQL

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

Weitere Informationen finden Sie unter CREATE TABLE in "PostgreSQL-Datendefinitionssprache".

TTL für generierte Spalten

Richtlinien für das Löschen von Zeilen können mit generierten Spalten komplexere Regeln definieren. Sie können beispielsweise eine Richtlinie für das Löschen von Zeilen für den Zeitstempel greatest (GoogleSQL oder PostgreSQL) mehrerer Spalten definieren oder einen anderen Wert einem Zeitstempel zuordnen.

Hinweis: Sie können keine generierte Spalte angeben, die auf eine Commit-Zeitstempelspalte verweist.

GoogleSQL

In der folgenden Tabelle mit dem Namen Orders werden Verkaufsaufträge erfasst. Der Tabelleninhaber möchte eine Richtlinie zum Löschen von Zeilen einrichten, mit der stornierte Bestellungen nach 30 Tagen und nicht stornierte Bestellungen nach 180 Tagen gelöscht werden.

Die Spanner-TTL lässt nur eine Richtlinie für das Löschen von Zeilen pro Tabelle zu. Um die beiden Kriterien in einer einzigen Spalte auszudrücken, können Sie eine generierte Spalte mit einer IF-Anweisung verwenden:

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

Mit der Anweisung wird eine Spalte namens ExpiredDate erstellt, die je nach Bestellstatus entweder 30 Tage oder 180 Tage zum LastModifiedDate hinzufügt. Anschließend definiert er die Richtlinie für das Löschen von Zeilen so, dass Zeilen an dem in der Spalte ExpiredDate gespeicherten Tag ablaufen. Dazu wird INTERVAL 0 day angegeben.

PostgreSQL

In der folgenden Tabelle mit dem Namen Orders werden Verkaufsaufträge erfasst. Der Tabelleninhaber möchte eine Richtlinie zum Löschen von Zeilen einrichten, die Zeilen nach 30 Tagen Inaktivität löscht.

Die Spanner-TTL lässt nur eine Richtlinie für das Löschen von Zeilen pro Tabelle zu. Um die beiden Kriterien in einer einzigen Spalte auszudrücken, können Sie eine generierte Spalte erstellen:

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;

Mit der Anweisung wird eine generierte Spalte mit dem Namen ExpiredDate erstellt, die den letzten der beiden Datumsangaben auswertet (LastModifiedDate oder CreateDate). Anschließend legt sie fest, dass Zeilen 30 Tage nach Erstellung des Auftrags ablaufen. Wenn der Auftrag innerhalb dieser 30 Tage geändert wurde, wird das Löschen um weitere 30 Tage verlängert.

TTL und verschränkte Tabellen

Verschränkte Tabellen sind eine Leistungsoptimierung, bei der verwandte Zeilen in einer untergeordneten 1:n-Tabelle mit einer Zeile in einer übergeordneten Tabelle verknüpft werden. Um eine Richtlinie zum Löschen von Zeilen für eine übergeordnete Tabelle hinzuzufügen, müssen alle verschränkten untergeordneten Tabellen ON DELETE CASCADE angeben. Das bedeutet, dass die untergeordneten Zeilen gemeinsam mit der übergeordneten Zeile gelöscht werden. Dadurch wird die referenzielle Integrität sichergestellt, sodass beim Löschen in der übergeordneten Tabelle auch die zugehörigen untergeordneten Zeilen in derselben Transaktion gelöscht werden. Spanner-TTL unterstützt ON DELETE NO ACTION nicht.

Maximale Transaktionsgröße

Spanner hat ein Limit für die Transaktionsgröße. Durch kaskadierende Löschvorgänge bei großen Hierarchien von über- und untergeordneten Hierarchien mit indexierten Spalten werden diese Limits möglicherweise überschritten, was dazu führen kann, dass ein oder mehrere TTL-Vorgänge fehlschlagen. Bei fehlgeschlagenen Vorgängen wiederholt TTL den Vorgang für kleinere Batches bis zu einer einzelnen übergeordneten Zeile. Große untergeordnete Hierarchien für sogar eine einzelne übergeordnete Zeile können jedoch weiterhin das Mutationslimit überschreiten.

Fehlgeschlagene Vorgänge werden in den TTL-Messwerten gemeldet.

Wenn eine einzelne Zeile und ihre verschränkten untergeordneten Tabellen zu groß sind, um sie zu löschen, können Sie eine Richtlinie zum Löschen von Zeilen direkt an die untergeordneten Tabellen anhängen, zusätzlich zu der Richtlinie in der übergeordneten Tabelle. Die Richtlinie für untergeordnete Tabellen sollte so konfiguriert sein, dass untergeordnete Zeilen vor übergeordneten Zeilen gelöscht werden.

Ziehen Sie in Betracht, eine Richtlinie für das Löschen von Zeilen an untergeordnete Tabellen anzuhängen, wenn die folgenden beiden Punkte zutreffen:

• Die untergeordnete Tabelle ist mit globalen Indexen verknüpft und
• Sie erwarten eine große Anzahl (>100) von untergeordneten Zeilen pro übergeordneter Zeile.

Richtlinie zum Löschen von Zeilen löschen

Sie können eine vorhandene Richtlinie für das Löschen von Zeilen aus einer Tabelle löschen. Dies gibt einen Fehler zurück, wenn in der Tabelle keine Richtlinie für das Löschen von Zeilen vorhanden ist.

GoogleSQL

ALTER TABLE MyTable
DROP ROW DELETION POLICY;

PostgreSQL

ALTER TABLE mytable
DROP TTL;

Wenn Sie eine Richtlinie zum Löschen von Zeilen löschen, werden alle TTL-Prozesse abgebrochen, die im Hintergrund ausgeführt werden. Alle Zeilen, die bereits von den laufenden Prozessen gelöscht wurden, bleiben gelöscht.

Spalte löschen, auf die in einer Richtlinie für das Löschen von Zeilen verwiesen wird

Sie können in Spanner keine Spalte löschen, auf die von einer Richtlinie für das Löschen von Zeilen verwiesen wird. Sie müssen zuerst die Richtlinie für das Löschen von Zeilen löschen, bevor Sie die Spalte löschen.

Richtlinie zum Löschen von Zeilen einer Tabelle ansehen

Sie können die Richtlinien für das Löschen von Zeilen Ihrer Spanner-Tabellen ansehen.

GoogleSQL

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

Weitere Informationen finden Sie unter Informationsschema für GoogleSQL-Dialekt-Datenbanken.

PostgreSQL

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

Weitere Informationen finden Sie unter Informationsschema für PostgreSQL-Dialekt-Datenbanken.

Richtlinie zum Löschen von Zeilen ändern

Sie können die Spalte oder den Intervallausdruck einer vorhandenen Richtlinie zum Löschen von Zeilen ändern. Im folgenden Beispiel wird die Spalte von CreatedAt zu ModifiedAt gewechselt und das Intervall wird von 1 DAY auf 7 DAY erweitert. Wenn keine Richtlinie zum Löschen von Zeilen für die Tabelle vorhanden ist, wird ein Fehler zurückgegeben.

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;