Datenaufbewahrung mit TTL verwalten

Auf dieser Seite wird beschrieben, wie Sie die Gültigkeitsdauer (Time to Live, TTL) für Spanner-Tabellen in Datenbanken im GoogleSQL- und PostgreSQL-Dialekt verwenden. Weitere Informationen finden Sie unter Über TTL.

Hinweis

Beachten Sie vor Beginn die folgenden Best Practices.

Sicherung und Wiederherstellung zu einem bestimmten Zeitpunkt aktivieren

Bevor Sie Ihrer Tabelle TTL hinzufügen, sollten Sie Spanner-Sicherungen und ‑Wiederherstellungen aktivieren. So können Sie eine Datenbank vollständig wiederherstellen, wenn Sie Ihre Daten versehentlich mit der TTL-Richtlinie 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 TTL zum ersten Mal verwenden und bei der ersten Ausführung das Löschen vieler Zeilen erwarten, sollten Sie zuerst alte Daten manuell mit partitionierter DMLbereinigen. Auf diese Weise haben Sie mehr Kontrolle über die Ressourcennutzung, anstatt sie dem TTL-Hintergrundprozess zu überlassen. TTL wird mit einer niedrigen Priorität ausgeführt, ideal für eine inkrementelle Bereinigung. Hierdurch dauert es jedoch wahrscheinlich länger, bis die ersten Zeilen in einer ausgelasteten Datenbank gelöscht werden, da der interne Arbeitsplaner von Spanner andere Aufgaben wie Nutzerabfragen priorisiert.

Bedingungen prüfen

Wenn Sie bei GoogleSQL-Tabellen die Daten überprüfen möchten, auf die sich die Richtlinie für das Löschen von Zeilen auswirkt, bevor Sie die TTL aktivieren, 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 Schemas der Datenbank benötigen Sie die Berechtigung spanner.databases.updateDdl. Weitere Informationen finden Sie unter Zugriffssteuerung für Spanner.

Richtlinie für das Löschen von Zeilen erstellen

GoogleSQL

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

So fügen Sie eine Richtlinie beim Erstellen einer Tabelle hinzu:

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

Ersetzen Sie Folgendes:

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

• num_days ist die Anzahl der Tage nach dem Zeitstempel in der timestamp_column, nach 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 höchstens eine Richtlinie für das Löschen von Zeilen haben. Wenn Sie einer Tabelle mit einer vorhandenen Richtlinie eine Richtlinie für das Löschen von Zeilen hinzufügen, tritt ein Fehler auf. Unter TTL für generierte Spalten können Sie eine komplexere Logik für das Löschen von Zeilen angeben.

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 eine Richtlinie zu einer vorhandenen Tabelle hinzufügen.

So fügen Sie eine Richtlinie beim Erstellen einer Tabelle hinzu:

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

Ersetzen Sie Folgendes:

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

• interval_spec ist die Anzahl der Tage nach dem Zeitstempel in der timestamp_column_name, nach denen die Zeile zum Löschen markiert wird. Der Wert muss eine nicht negative Ganzzahl sein und zu einer ganzen Anzahl von Tagen ausgewertet werden. Beispiel: '3 days' ist 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 höchstens eine TTL-Richtlinie haben. Wenn Sie einer Tabelle mit einer vorhandenen Richtlinie eine TTL-Richtlinie hinzufügen, tritt ein Fehler auf. Unter TTL für generierte Spalten können Sie eine komplexere TTL-Logik angeben.

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;

Beschrä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:

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

Im folgenden Beispiel können Sie der Tabelle Customers keine Richtlinie für das Löschen von Zeilen hinzufügen, da in der Tabelle Orders auf einen Fremdschlüssel verwiesen wird, der die Einschränkung ON DELETE CASCADE nicht enthält. Das Löschen von Kunden könnte gegen diese Fremdschlüsseleinschränkung verstoßen. Sie können der Tabelle Districts auch keine Richtlinie für das Löschen von Zeilen hinzufügen. Wenn Sie eine Zeile aus Districts löschen, kann dies dazu führen, dass Löschvorgänge in der untergeordneten Tabelle Customers kaskadieren, was gegen die Fremdschlüsseleinschränkung für die Tabelle Orders verstoßen könnte.

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 zur Datenlöschung für eine Tabelle erstellen, auf die durch eine Fremdschlüsseleinschränkung verwiesen wird, die ON DELETE CASCADE verwendet. Im folgenden Beispiel können Sie eine Richtlinie für das Löschen von Zeilen für die Tabelle Customers erstellen, auf die durch die Fremdschlüsseleinschränkung CustomerOrder verwiesen wird, die für die Tabelle Orders definiert ist. Wenn TTL Zeilen in Customers löscht, wird der Löschvorgang auf übereinstimmende Zeilen in der Tabelle Orders kaskadiert.

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 für das Löschen von Zeilen für ein übergeordnetes Element einer Tabelle erstellen, auf die durch eine Fremdschlüsseleinschränkung mit 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 wird oder wenn die Spalte mit einer INSERT- oder UPDATE-Anweisung auf ihren Standardwert gesetzt wird, wird der Standardwert in der Regelberechnung verwendet.

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

GoogleSQL

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

Weitere Informationen finden Sie unter DEFAULT (Ausdruck).

PostgreSQL

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

Weitere Informationen finden Sie unter CREATE TABLE.

TTL für generierte Spalten

Richtlinien für das Löschen von Zeilen können generierte Spalten verwenden, um ausgefeiltere Regeln auszudrücken. Beispielsweise können Sie eine Richtlinie für das Löschen von Zeilen für den greatest Zeitstempel (GoogleSQL oder PostgreSQL) mehrerer Spalten definieren oder einem Zeitstempel einen anderen Wert zuordnen.

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

GoogleSQL

Die folgende Tabelle mit dem Namen Orders erfasst Verkaufsbestellungen. 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.

Spanner-TTL lässt nur eine Richtlinie zur Datenlöschung pro Tabelle zu. Um die beiden Kriterien in einer einzelnen 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 wird die Richtlinie zum Löschen von Zeilen so definiert, dass Zeilen an dem Tag ablaufen, der in der Spalte ExpiredDate gespeichert ist. Dazu wird INTERVAL 0 day angegeben.

PostgreSQL

Die folgende Tabelle mit dem Namen Orders erfasst Verkaufsbestellungen. Der Tabelleninhaber möchte eine Richtlinie zum Löschen von Zeilen einrichten, mit der Zeilen nach 30 Tagen Inaktivität gelöscht werden.

Spanner-TTL lässt nur eine Richtlinie für das Löschen von Zeilen pro Tabelle zu. Um die beiden Kriterien in einer einzelnen 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 namens ExpiredDate erstellt, die das letzte der beiden Datumsangaben (LastModifiedDate oder CreateDate) auswertet. Anschließend wird die Richtlinie zum Löschen von Zeilen so definiert, dass Zeilen 30 Tage nach der Erstellung der Bestellung ablaufen. Wenn die Bestellung innerhalb dieser 30 Tage geändert wurde, wird der Löschvorgang um weitere 30 Tage verlängert.

TTL und verschränkte Tabellen

Verschränkte Tabellen sind eine Leistungsoptimierung, die verknüpfte Zeilen in einer untergeordneten 1:n-Tabelle mit einer Zeile in einer übergeordneten Tabelle verknüpft. Wenn Sie einer übergeordneten Tabelle eine Richtlinie für das Löschen von Zeilen hinzufügen möchten, muss für alle verschränkten untergeordneten Tabellen ON DELETE CASCADE angegeben werden. Das bedeutet, dass die untergeordneten Zeilen atomar mit der übergeordneten Zeile gelöscht werden. So wird die referenzielle Integrität sichergestellt, sodass bei Löschvorgängen in der übergeordneten Tabelle auch die verknüpften untergeordneten Zeilen in derselben Transaktion gelöscht werden. Spanner-TTL unterstützt ON DELETE NO ACTION nicht.

Maximale Transaktionsgröße

Für Spanner gilt eine Beschränkung der Transaktionsgröße. Kaskadierende Löschvorgänge für große über- und untergeordnete Hierarchien mit indexierten Spalten können diese Limits überschreiten und dazu führen, dass ein oder mehrere TTL-Vorgänge fehlschlagen. Bei fehlgeschlagenen Vorgängen wiederholt TTL den Vorgang mit kleineren Batches bis hin 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 Elemente zu groß sind, um gelöscht zu werden, können Sie zusätzlich zur Richtlinie für die übergeordnete Tabelle eine Richtlinie für das Löschen von Zeilen direkt an die untergeordneten Tabellen anhängen. 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 für das 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 eine Richtlinie für das Löschen von Zeilen verweist

Mit Spanner können Sie eine Spalte nicht löschen, auf die eine Richtlinie für das Löschen von Zeilen verweist. Sie müssen zuerst die Richtlinie für das Löschen von Zeilen löschen, bevor Sie die Spalte löschen.

Richtlinie für das 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 Datenbanken im GoogleSQL-Dialekt.

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 Datenbanken im PostgreSQL-Dialekt.

Richtlinie für das Löschen von Zeilen ändern

Sie können die Spalte oder den Intervallausdruck einer vorhandenen Richtlinie für das Löschen von Zeilen ändern. Im folgenden Beispiel wird die Spalte von CreatedAt zu ModifiedAt geändert und das Intervall von 1 DAY zu 7 DAY verlängert. 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
REPLACE ROW DELETION POLICY (OLDER_THAN(ModifiedAt, INTERVAL 7 DAY));

PostgreSQL

ALTER TABLE mytable
ALTER TTL INTERVAL '7 days' ON timestampcolumn;