Datenaufbewahrung mit TTL verwalten

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

Hinweise

Beachten Sie vor Beginn die folgenden Best Practices.

Sicherung und Wiederherstellung zu einem bestimmten Zeitpunkt aktivieren

Bevor Sie Ihrer Tabelle eine 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 zum ersten Mal TTL verwenden und bei der ersten Ausführung viele Zeilen gelöscht werden, sollten Sie zuerst alte Daten manuell mit partitionierter DML bereinigen. 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 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 GoogleSQL-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 eine Richtlinie zum Löschen von Zeilen mit GoogleSQL erstellen möchten, 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 beim Erstellen einer Tabelle eine Richtlinie 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 und generierte Spalten sind zulässig. 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 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 zum Löschen von Zeilen haben. Wenn Sie einer Tabelle mit einer vorhandenen Richtlinie eine Richtlinie zum Löschen von Zeilen hinzufügen, wird ein Fehler ausgegeben. Unter TTL für generierte Spalten finden Sie Informationen zum Festlegen einer ausgefeilteren 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 eine Richtlinie zum Löschen von Zeilen mit PostgreSQL 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 beim Erstellen einer Tabelle eine Richtlinie 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. Gültig sind sowohl Spalten mit Commit-Zeitstempeln als auch generierte Spalten. 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 der timestamp_column_name, nach denen die Zeile zum Löschen markiert wird. Der Wert muss eine nicht negative Ganzzahl sein und auf eine ganze Anzahl von Tagen lauten. 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, schlägt das Hinzufügen fehl. Unter TTL für generierte Spalten erfahren Sie, wie 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 über 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 nicht die Einschränkung „ON DELETE CASCADE“ enthält.
• Für das übergeordnete Element einer Tabelle, auf die von einem 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 in der Tabelle Orders auf einen Fremdschlüssel verwiesen wird, der nicht die Einschränkung „ON DELETE CASCADE“ hat. Das Löschen von Kunden kann gegen diese Fremdschlüsseleinschränkung verstoßen. Außerdem können Sie der Tabelle Districts keine Richtlinie zum Löschen von Zeilen hinzufügen. Wenn Sie eine Zeile aus Districts löschen, kann es zu einem kaskadierenden Löschen in der untergeordneten Tabelle Customers kommen, was gegen die Fremdschlüsseleinschränkung in der Tabelle Orders verstoßen kann.

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 eine Fremdschlüsseleinschränkung mit ON DELETE CASCADE verweist. Im folgenden Beispiel wird eine Richtlinie zum Löschen von Zeilen für die Tabelle Customers erstellt, auf die in der Tabelle Orders definierten Fremdschlüsseleinschränkung CustomerOrder verwiesen wird. Wenn TTL Zeilen in Customers löscht, werden die übereinstimmenden Zeilen in der Tabelle Orders kaskadisch gelöscht.

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 das übergeordnete Element einer Tabelle erstellen, auf die von einer ON DELETE CASCADE-Fremdschlüsseleinschränkung verwiesen wird.

TTL für Spalten mit Standardwerten

Für eine Richtlinie zum Löschen von Zeilen kann eine Zeitstempelspalte mit einem Standardwert verwendet werden. Ein typischer Standardwert ist CURRENT_TIMESTAMP. Wenn der Spalte kein Wert explizit zugewiesen ist oder die Spalte durch eine INSERT- oder UPDATE-Anweisung auf den Standardwert gesetzt wird, wird der Standardwert bei der Regelberechnung verwendet.

Im folgenden Beispiel ist der Standardwert für die Spalte CreatedAt in 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) in der 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 der PostgreSQL-Datendefinitionssprache.

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 Commit-Zeitstempelspalte verweist.

GoogleSQL

In der folgenden Tabelle mit dem Namen Orders werden Kundenaufträ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.

Bei der Spanner-TTL ist nur eine Richtlinie zum Löschen von Zeilen pro Tabelle zulässig. Wenn Sie die beiden Kriterien in einer einzigen Spalte ausdrücken möchten, 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

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

Bei der Spanner-TTL ist nur eine Richtlinie zum Löschen von Zeilen pro Tabelle zulässig. Wenn Sie die beiden Kriterien in einer einzigen Spalte ausdrücken möchten, 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, in der das jüngste der beiden Datumsangaben (LastModifiedDate oder CreateDate) ausgewertet wird. Anschließend wird die Richtlinie zum Löschen von Zeilen so definiert, dass Zeilen 30 Tage nach dem Erstellen der Bestellung ablaufen. Wenn die Bestellung innerhalb dieser 30 Tage geändert wurde, wird das Löschen um weitere 30 Tage verlängert.

TTL und verschränkte Tabellen

Verschachtelte Tabellen sind eine Leistungsoptimierung, bei der zugehörige Zeilen in einer untergeordneten Tabelle vom Typ „Eins-zu-viele“ mit einer Zeile in einer übergeordneten Tabelle verknüpft werden. Wenn Sie einer übergeordneten Tabelle eine Richtlinie zum 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 zusammen mit der übergeordneten Zeile untrennbar gelöscht werden. So 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. Die 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 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 erfasst.

Wenn eine einzelne Zeile und ihre verschränkten untergeordneten Zeilen zu groß zum Löschen sind, können Sie zusätzlich zur Richtlinie in der übergeordneten Tabelle eine Richtlinie zum 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 aufrufen

Sie können sich die Richtlinien zum 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 mit 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 PostgreSQL-Datenbanken.

Richtlinie für das 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 von 1 DAY auf 7 DAY erweitert. 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;