Gestire la conservazione dei dati con TTL

Questa pagina spiega come utilizzare durata (TTL) nelle tabelle Spanner nei database con dialetto GoogleSQL e nei database con dialetto PostgreSQL. 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 il backup e il ripristino di Spanner. In questo modo puoi ripristinare completamente un database nel caso in cui elimini accidentalmente i dati con la policy TTL.

Se hai abilitato il recupero point-in-time, puoi visualizzare e ripristinare i dati eliminati, senza un ripristino completo dal backup, se rientrano nel periodo di conservazione delle versioni configurato. Per informazioni sulla lettura dei dati nel passato, consulta Eseguire una lettura dati inattivi.

Libera spazio dai dati obsoleti

Se è la prima volta che utilizzi il TTL e prevedi che la prima esecuzione eliminerà molte righe, valuta la possibilità di eliminare manualmente i dati obsoleti utilizzando DML partizionato. In questo modo hai un maggiore controllo sull'utilizzo delle risorse, anziché lasciarlo al processo in background del TTL. Il TTL viene eseguito con una priorità bassa, ideale per la pulizia incrementale. Tuttavia, è probabile che il tempo necessario per eliminare il set iniziale di righe in un database occupato aumenti perché lo scheduler di lavoro interno di Spanner assegna la priorità ad altri lavori, come le query utente.

Verifica le condizioni

Per le tabelle GoogleSQL, se vuoi verificare i dati interessati dalla politica di cancellazione delle righe prima di abilitare il TTL, puoi eseguire una 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 disporre dell'autorizzazione spanner.databases.updateDdl. Per maggiori dettagli, consulta Controllo dell'accesso per Spanner.

Crea una politica di cancellazione delle righe

GoogleSQL

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

Per aggiungere una policy 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));

Sostituisci quanto segue:

• timestamp_column deve essere una colonna esistente di tipo TIMESTAMP. Le colonne con timestamp di commit sono valide, così come le colonne generate. Tuttavia, non puoi specificare una colonna generata che fa riferimento a una colonna con timestamp di commit.

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

Per aggiungere una policy a una tabella esistente, utilizza l'istruzione ALTER TABLE. Una tabella può avere al massimo una policy di eliminazione delle righe. L'aggiunta di una policy di eliminazione delle righe a una tabella con una policy esistente non riesce e genera un errore. Consulta TTL nelle colonne generate per specificare una logica di eliminazione delle righe più sofisticata.

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

PostgreSQL

Per creare una politica di cancellazione delle righe utilizzando PostgreSQL, puoi definire una clausola TTL INTERVAL quando crei una nuova tabella o aggiungere una politica a una tabella esistente.

Per aggiungere una policy al momento della creazione della tabella:

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

Sostituisci quanto segue:

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

• interval_spec è il numero di giorni successivi al timestamp in timestamp_column_name in cui la riga viene contrassegnata per l'eliminazione. Il valore deve essere un numero intero non negativo e deve restituire un numero intero di giorni. Ad esempio, '3 days' è consentito, ma '3 days - 2 minutes' restituisce un errore.

Per aggiungere una policy a una tabella esistente, utilizza l'istruzione ALTER TABLE. Una tabella può avere al massimo una policy TTL. L'aggiunta di una policy TTL a una tabella con una policy esistente non riesce e genera un errore. Consulta TTL nelle colonne generate per specificare una logica TTL più sofisticata.

Per aggiungere una policy a una tabella esistente:

ALTER TABLE albums
ADD COLUMN timestampcolumn TIMESTAMPTZ;

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

Limitazioni

Le policy di eliminazione delle righe presentano le seguenti limitazioni.

TTL nelle tabelle a cui fa riferimento una chiave esterna

Non puoi creare una policy di eliminazione delle righe:

• In una tabella a cui fa riferimento una chiave esterna che non include il vincolo ON DELETE CASCADE.
• Nel padre di una tabella a cui fa riferimento una chiave esterna che non include l'azione referenziale ON DELETE CASCADE.

Nell'esempio seguente, non puoi aggiungere una policy di eliminazione delle righe alla tabella Customers, perché a questa fa riferimento una chiave esterna nella tabella Orders, che non ha il vincolo ON DELETE CASCADE. L'eliminazione dei clienti potrebbe violare questo vincolo di chiave esterna. Non puoi nemmeno aggiungere una policy di eliminazione delle righe alla tabella Districts. L'eliminazione di una riga da Districts potrebbe causare l'eliminazione a cascata nella tabella figlio Customers, 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 una policy di eliminazione delle righe in una tabella a cui fa riferimento un vincolo di chiave esterna che utilizza ON DELETE CASCADE. Nell'esempio seguente, puoi creare una policy 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 viene eseguita a cascata sulle righe corrispondenti 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 una policy di eliminazione delle righe in un padre di una tabella a cui fa riferimento un vincolo di chiave esterna ON DELETE CASCADE.

TTL nelle colonne con valori predefiniti

Una politica di cancellazione delle righe può utilizzare una colonna con timestamp con un valore predefinito. Un valore predefinito tipico è CURRENT_TIMESTAMP. Se non viene assegnato esplicitamente un valore alla colonna o se la colonna viene impostata sul valore predefinito da un'istruzione 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 saperne di più, consulta DEFAULT (espressione).

PostgreSQL

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

Per saperne di più, consulta CREATE TABLE.

TTL nelle colonne generate

Le policy di eliminazione delle righe possono utilizzare le colonne generate per esprimere regole più sofisticate. Ad esempio, puoi definire una policy di eliminazione delle righe sul timestamp greatest (GoogleSQL o PostgreSQL) di più colonne o mappare un altro valore a un timestamp.

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

GoogleSQL

La seguente tabella denominata Orders tiene traccia degli ordini di vendita. Il proprietario della tabella vuole configurare una policy di eliminazione delle righe che elimini gli ordini annullati dopo 30 giorni e gli ordini non annullati dopo 180 giorni.

Il TTL di Spanner consente una sola politica di cancellazione per tabella. Per 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 o 180 giorni a LastModifiedDate a seconda dello stato dell'ordine. Quindi, definisce la policy di eliminazione delle righe in modo che le righe scadano il giorno memorizzato nella colonna ExpiredDate specificando INTERVAL 0 day.

PostgreSQL

La seguente tabella denominata Orders tiene traccia degli ordini di vendita. Il proprietario della tabella vuole configurare una politica di cancellazione delle righe che elimini le righe dopo 30 giorni di inattività.

Il TTL di Spanner consente una sola policy di eliminazione delle righe per tabella. Per esprimere i due criteri in una singola colonna, puoi creare una colonna generata:

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 la politica di cancellazione delle righe in modo che le righe scadano 30 giorni dopo la creazione dell'ordine oppure, se l'ordine è stato modificato entro 30 giorni, estenderà la cancellazione di altri 30 giorni.

TTL e tabelle con interfoliazione

Le tabelle con interfoliazione sono un'ottimizzazione delle prestazioni che associa le righe correlate in una tabella figlio uno-a-molti a una riga in una tabella padre. Per aggiungere una politica di cancellazione delle righe a una tabella padre, tutte le tabelle figlio con interfoliazione devono specificare ON DELETE CASCADE, il che significa che le righe figlio vengono eliminate in modo atomico con la riga padre. In questo modo si garantisce l'integrità referenziale, in modo che le eliminazioni nella tabella padre eliminino anche le righe figlio correlate nella stessa transazione. Il TTL di Spanner non supporta ON DELETE NO ACTION.

Dimensione massima della transazione

Spanner ha un limite di dimensioni delle transazioni. Le eliminazioni a cascata su gerarchie padre-figlio di grandi dimensioni con colonne indicizzate potrebbero superare questi limiti e causare il mancato funzionamento di una o più operazioni TTL. Per le operazioni non riuscite, il TTL riprova con batch più piccoli, fino a una singola riga padre. Tuttavia, le gerarchie figlio di grandi dimensioni anche per una singola riga padre potrebbero comunque superare il limite di mutazione.

Le operazioni non riuscite vengono segnalate nelle metriche TTL.

Se una singola riga e i relativi figli con interfoliazione sono troppo grandi per essere eliminati, puoi collegare una politica di cancellazione delle righe direttamente alle tabelle figlio, oltre a quella nella tabella padre. La policy nelle tabelle figlio deve essere configurata in modo che le righe figlio vengano eliminate prima delle righe padre.

Valuta la possibilità di collegare una policy di eliminazione delle righe alle tabelle figlio quando si applicano le seguenti due istruzioni:

• La tabella figlio ha indici globali associati; e
• Prevedi un numero elevato di (>100) righe figlio per riga padre.

Elimina una politica di cancellazione delle righe

Puoi eliminare una politica di cancellazione delle righe esistente da una tabella. Viene restituito un errore se non esiste una politica di cancellazione delle righe nella tabella.

GoogleSQL

ALTER TABLE MyTable
DROP ROW DELETION POLICY;

PostgreSQL

ALTER TABLE mytable
DROP TTL;

L'eliminazione di una policy di eliminazione delle righe interrompe immediatamente tutti i processi TTL in esecuzione in background. Le righe già eliminate dai processi in corso rimangono eliminate.

Elimina una colonna a cui fa riferimento una policy di eliminazione delle righe

Spanner non consente di eliminare una colonna a cui fa riferimento una policy di eliminazione delle righe. Prima di eliminare la colonna, devi prima eliminare la policy di eliminazione delle righe.

Visualizza la policy di eliminazione delle righe di una tabella

Puoi visualizzare le policy 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 saperne di più, consulta Schema informativo per i database con dialetto GoogleSQL.

PostgreSQL

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

Per saperne di più, consulta Schema informativo per i database con dialetto PostgreSQL.

Modifica una politica di cancellazione delle righe

Puoi modificare la colonna o l'espressione dell'intervallo di una policy di eliminazione delle righe esistente. L'esempio seguente cambia la colonna da CreatedAt a ModifiedAt ed estende l'intervallo da 1 DAY a 7 DAY. Viene restituito un errore se non esiste una policy 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;