Gestisci la conservazione dei dati con TTL

Questa pagina illustra come utilizzare la durata (TTL) nelle tabelle Spanner. A Per saperne di più, consulta Informazioni sul TTL.

Prima di iniziare

Prima di iniziare, segui queste best practice.

Abilita backup e recupero point-in-time

Prima di aggiungere il TTL alla tabella, ti consigliamo di abilitare Backup e ripristino di Spanner. Ciò consente ripristinare completamente un database in caso di eliminazione accidentale dei dati con il criterio TTL.

Se hai abilitato il recupero point-in-time, puoi visualizzare e ripristinare i dati eliminati, senza un ripristino completo dal backup, se presenti entro il periodo di conservazione della versione configurato. Per informazioni sulla lettura dei dati passati, consulta Esegui una lettura inattiva.

Pulisci i vecchi dati

Se è la prima volta che utilizzi TTL e ti aspetti che la prima esecuzione elimina molte righe, valuta prima di eseguire la pulizia manuale dei vecchi dati DML partizionato. Questo ti offre un maggiore controllo sull'utilizzo delle risorse, invece di lasciare che il processo in background TTL. Il TTL viene eseguito a una priorità bassa, ideale per pulizia. Tuttavia, il tempo necessario per eliminare l'elemento probabilmente insieme iniziale di righe in un database occupato perché l'interfaccia work scheduler darà priorità ad altre attività, come le query degli utenti.

Verifica le condizioni

Per le tabelle GoogleSQL, se vuoi verificare i dati che il criterio di eliminazione delle righe verrà applicato prima di abilitare il TTL. Puoi eseguire query sulla tabella utilizzando le stesse condizioni. Ad esempio:

GoogleSQL

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

Autorizzazioni obbligatorie

Per modificare lo schema del database, devi avere il Autorizzazione spanner.databases.updateDdl. Per maggiori dettagli, vedi Controllo dell'accesso per Spanner.

Crea un criterio di eliminazione delle righe

GoogleSQL

Per creare un criterio di eliminazione delle righe utilizzando GoogleSQL, puoi definire un ROW DELETION POLICY quando crei una nuova tabella o aggiungi un criterio alla una tabella esistente. Questa clausola contiene un'espressione di una colonna e un intervallo di tempo.

Per aggiungere un criterio al momento della creazione della tabella:

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

Dove:

• timestamp_column deve essere una colonna esistente con il tipo TIMESTAMP. Colonne con i timestamp di commit sono validi, così come lo sono colonne generate. Tuttavia, non puoi specificare una colonna generata che fa riferimento a una colonna del timestamp di commit.

• num_days è il numero di giorni dopo il timestamp nella timestamp_column in cui la riga è contrassegnata per l'eliminazione dei dati. Il valore deve essere un numero intero non negativo e DAY è l'unico unità supportata.

Per aggiungere un criterio a una tabella esistente, utilizza l'istruzione ALTER TABLE. R può avere al massimo un criterio di eliminazione delle righe. Aggiunta di un criterio di eliminazione delle righe a una tabella con un criterio esistente non riesce e si verifica un errore. Consulta TTL sulle colonne generate per specificare ulteriori informazioni di una sofisticata logica di eliminazione delle righe.

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

PostgreSQL

Per creare un criterio di eliminazione delle righe utilizzando PostgreSQL, puoi definire TTL INTERVAL quando crei una nuova tabella o aggiungi un criterio a una una tabella esistente.

Per aggiungere un criterio al momento della creazione della tabella:

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

Dove:

• timestamp_column_name deve essere una colonna con tipo di dati TIMESTAMPTZ. Devi creare questa colonna nell'istruzione CREATE TABLE. Colonne con i timestamp di commit sono validi, così come lo sono colonne generate. Tuttavia, non puoi specificare una colonna generata che fa riferimento a una colonna del timestamp di commit.

• interval_spec è il numero di giorni dopo il timestamp nel timestamp_column_name in cui la riga è contrassegnata l'eliminazione dei dati. Il valore deve essere un numero intero non negativo e deve essere valutato a un numero intero di giorni. Ad esempio, '3 days' è consentito, ma '3 days - 2 minutes' restituisce un errore.

Per aggiungere un criterio a una tabella esistente, utilizza l'istruzione ALTER TABLE. R può avere al massimo un criterio TTL. Aggiunta di un criterio TTL a una tabella con un criterio esistente non funziona e genera un errore. Consulta TTL sulle colonne generate per specificare ulteriori informazioni una sofisticata logica TTL.

Per aggiungere un criterio a una tabella esistente:

ALTER TABLE albums
ADD COLUMN timestampcolumn TIMESTAMPTZ;

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

Limitazioni

I criteri di eliminazione delle righe hanno le seguenti restrizioni.

TTL nelle tabelle a cui fa riferimento una chiave esterna

Non puoi creare un criterio di eliminazione delle righe:

• In una tabella a cui fa riferimento un chiave esterna che non includa l'opzione ON Vincolo DELETE CASCADE.
• Nel padre di una tabella a cui fa riferimento una chiave esterna che non includi l'azione referenziale ON DELETE CASCADE.

Nell'esempio seguente, non puoi aggiungere un criterio di eliminazione di righe alla Tabella Customers, perché fa riferimento a una chiave esterna in Orders che non ha il vincolo ON DELETE CASCADE. L'eliminazione dei clienti potrebbe violare questo vincolo di chiave esterna. Inoltre, non puoi aggiungi un criterio di eliminazione delle righe alla tabella Districts. Eliminazione di una riga da Districts potrebbe causare la propagazione delle eliminazioni nella tabella secondaria Customers, il che potrebbe violare il vincolo di chiave esterna nella tabella 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)
);

Puoi creare un criterio di eliminazione di riga in una tabella a cui fa riferimento un vincolo chiave che utilizza ON DELETE CASCADE. Nell'esempio seguente, crea un criterio di eliminazione delle righe nella tabella Customers a cui fa riferimento il vincolo di chiave esterna CustomerOrder definito nella tabella Orders. Quando Il TTL elimina le righe in Customers, l'eliminazione passa alle righe corrispondenti presenti nella tabella 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
);

Allo stesso modo, puoi creare un criterio di eliminazione delle righe su un elemento padre di una tabella a cui fa riferimento un vincolo di chiave esterna ON DELETE CASCADE.

TTL su colonne con valori predefiniti

Un criterio di eliminazione delle righe può utilizzare una colonna timestamp con un valore predefinito. Un tipico il valore predefinito è CURRENT_TIMESTAMP. Se nessun valore è assegnato esplicitamente a la colonna oppure se la colonna è impostata sul valore predefinito da un INSERT o UPDATE, il valore predefinito viene utilizzato nel calcolo della regola.

Nell'esempio seguente, il valore predefinito per la colonna CreatedAt nella tabella Customers è il timestamp in cui viene creata la riga.

GoogleSQL

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

Per ulteriori informazioni, consulta la sezione PREDEFINITA (espressione) in "GoogleSQL Data Definition Language".

PostgreSQL

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

Per ulteriori informazioni, consulta CREARE TABELLA in "PostgreSQL Data Definition Language".

TTL nelle colonne generate

I criteri di eliminazione delle righe possono utilizzare le colonne generate ed esprimere regole più sofisticate. Ad esempio, puoi definire l'eliminazione di una riga sul timestamp greatest (GoogleSQL o PostgreSQL) di più colonne oppure mappa un altro valore a un timestamp.

Nota: non puoi specificare una colonna generata che fa riferimento a una colonna timestamp di un timestamp.

GoogleSQL

La seguente tabella denominata Orders monitora gli ordini di vendita. Il proprietario della tabella vuole configurare un criterio di eliminazione delle righe che elimina i file annullati gli ordini dopo 30 giorni e gli ordini non annullati dopo 180 giorni.

Il TTL di Spanner consente un solo criterio di eliminazione delle righe per tabella. A esprimere i due criteri in una singola colonna, puoi utilizzare una colonna generata con un'istruzione 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));

L'istruzione crea una colonna denominata ExpiredDate che aggiunge 30 giorni o 180 giorni per LastModifiedDate a seconda dello stato dell'ordine. Poi, definisce il criterio di eliminazione delle righe in modo che le righe scadano il giorno di archiviazione ExpiredDate specificando INTERVAL 0 day.

PostgreSQL

La seguente tabella denominata Orders monitora gli ordini di vendita. Il proprietario della tabella vuole configurare un criterio di eliminazione delle righe che elimini le righe dopo 30 giorni di inattività.

Il TTL di Spanner consente un solo criterio di eliminazione delle righe per tabella. A i due criteri in una singola colonna, puoi creare una generazione colonna:

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;

L'istruzione crea una colonna generata denominata ExpiredDate che valuta la più recente delle due date (LastModifiedDate o CreateDate). Quindi, definisce il criterio di eliminazione delle righe in modo che le righe scadano 30 giorni dopo oppure, se l'ordine è stato modificato entro questi 30 giorni, ne verrà estesa l'eliminazione di altri 30 giorni.

Tabelle TTL e con interleaving

Tabelle con interleaving sono un'ottimizzazione delle prestazioni che associa le righe correlate in un modello tabella secondaria con una riga in una tabella padre. Per aggiungere un criterio di eliminazione delle righe su una tabella padre, tutte le tabelle figlio con interleaving devono specificare ON DELETE CASCADE, il che significa che le righe figlio verranno eliminate atomicamente con la riga padre. Questo garantisce l'integrità referenziale in modo che le eliminazioni sulla tabella padre eliminino anche le relative righe figlio nella stessa transazione. Il TTL di Spanner non supporta ON DELETE NO ACTION.

Dimensione massima della transazione

Spanner ha un limite per le dimensioni delle transazioni. Le eliminazioni a cascata su gerarchie padre-figlio di grandi dimensioni con colonne indicizzate potrebbero superare questi limiti e causare l'esito negativo di una o più operazioni TTL. Per non riusciti operazioni, il TTL proverà nuovamente con batch più piccoli, fino a una singola riga padre. Tuttavia, anche per una singola riga padre le gerarchie figlio grandi possono superare il limite di mutazioni.

Le operazioni non riuscite sono riportate in Metriche TTL.

Se una singola riga e i relativi elementi secondari con interleaving sono troppo grandi per essere eliminati, puoi collegare un criterio di eliminazione delle righe direttamente sulle tabelle figlio, oltre al una nella tabella padre. Il criterio nelle tabelle figlio deve essere configurato in questo modo: le righe figlio vengano eliminate prima delle righe padre.

Valuta la possibilità di collegare un criterio di eliminazione delle righe alle tabelle figlio quando: si applicano le seguenti dichiarazioni:

• Alla tabella figlio sono associati eventuali indici globali. e
• Prevedi un numero elevato di (>100) righe secondarie per riga principale.

Elimina un criterio di eliminazione delle righe

Puoi rilasciare un criterio di eliminazione delle righe esistente da una tabella. Viene restituito un errore se non è presente alcun criterio di eliminazione delle righe nella tabella.

GoogleSQL

ALTER TABLE MyTable
DROP ROW DELETION POLICY;

PostgreSQL

ALTER TABLE mytable
DROP TTL;

L'eliminazione di un criterio di eliminazione delle righe interrompe immediatamente tutti i processi TTL in esecuzione in background. Le eventuali righe già eliminate dai processi in corso rimangono eliminati.

Elimina una colonna a cui fa riferimento un criterio di eliminazione delle righe

Spanner non consente di eliminare una colonna a cui viene fatto riferimento da un criterio di eliminazione delle righe. Devi prima elimina il criterio di eliminazione delle righe prima l'eliminazione della colonna.

Visualizzare il criterio di eliminazione delle righe di una tabella

Puoi visualizzare i criteri di eliminazione delle righe delle tabelle Spanner.

GoogleSQL

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

Per ulteriori informazioni, vedi Schema di informazioni per i database di dialetti GoogleSQL.

PostgreSQL

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

Per ulteriori informazioni, vedi Schema di informazioni per i database di dialetti PostgreSQL.

Modificare un criterio di eliminazione delle righe

Puoi modificare la colonna o l'espressione dell'intervallo di un'eliminazione di riga esistente . L'esempio seguente cambia la colonna da CreatedAt a ModifiedAt ed estende l'intervallo da 1 DAY a 7 DAY. Questo restituisce un se non è presente alcun criterio di eliminazione delle righe nella tabella.

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;