Datenaufbewahrung mit TTL verwalten

Auf dieser Seite wird erläutert, wie Sie die Gültigkeitsdauer (Time to Live, TTL) für Spanner-Tabellen in Datenbanken mit Google SQL- 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 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 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 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(<var>timestamp_column</var>, INTERVAL <var>num_days</var> DAY));

Ersetzen Sie Folgendes:

• timestamp_column muss eine vorhandene Spalte vom Typ TIMESTAMP sein. 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.

• 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(<var>timestamp_column</var>, INTERVAL <var>num_days</var> 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,
  <var>timestamp_column_name</var> TIMESTAMPTZ,
  PRIMARY KEY(key)
) TTL INTERVAL <var>interval_spec</var> ON <var>timestamp_column_name</var>;

Ersetzen Sie Folgendes:

• 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 und es wird eine Fehlermeldung angezeigt. 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 die Einschränkung ON DELETE CASCADE nicht enthält.
• Für das übergeordnete Element einer Tabelle, auf die von einem 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 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 ein übergeordnetes 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).

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

In Spanner können Sie keine Spalte 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;