Gérer la conservation des données avec la valeur TTL

Cette page explique comment utiliser la valeur TTL (Time To Live) sur les tables Spanner. Pour en savoir plus, consultez la section À propos de la valeur TTL.

Avant de commencer

Avant de commencer, suivez ces bonnes pratiques.

Activer la sauvegarde et la récupération à un moment précis

Avant d'ajouter une valeur TTL à votre table, nous vous recommandons d'activer la fonctionnalité de sauvegarde et restauration de Spanner. Cela vous permet de restaurer complètement une base de données au cas où vous supprimeriez accidentellement vos données avec la règle TTL.

Si vous avez activé la récupération à un moment précis, vous pouvez afficher et restaurer les données supprimées (sans effectuer de restauration complète à partir d'une sauvegarde) si elles se trouvent dans la durée de conservation de version configurée. Pour en savoir plus sur la lecture de données dans le passé, consultez la section Effectuer une lecture non actualisée.

Nettoyer les données anciennes

Si c'est la première fois que vous utilisez TTL et que vous prévoyez que la première exécution supprimera de nombreuses lignes, envisagez d'abord de nettoyer manuellement les données anciennes à l'aide du langage LMD partitionné. Vous pouvez ainsi mieux contrôler l'utilisation des ressources, le processus en arrière-plan TTL. La valeur TTL a une priorité faible, idéale pour les opérations de nettoyage. Cependant, cela prolongera probablement la durée nécessaire pour supprimer l'ensemble initial de lignes d'une base de données très active, car le planificateur de tâches interne de Spanner donnera la priorité à d'autres tâches, telles que les requêtes utilisateur.

Vérifier les conditions

Pour les tables GoogleSQL, si vous souhaitez vérifier les données la règle de suppression des lignes affectera avant d'activer la valeur TTL, vous pouvez interroger votre table avec les mêmes conditions. Exemple :

GoogleSQL

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

Autorisations requises

Pour modifier le schéma de la base de données, vous devez disposer du l'autorisation spanner.databases.updateDdl. Pour en savoir plus, consultez Contrôle des accès pour Spanner

Créer une règle de suppression de lignes

GoogleSQL

Pour créer une règle de suppression de lignes à l'aide de GoogleSQL, vous pouvez définir une clause ROW DELETION POLICY lorsque vous créez une table ou ajouter une règle à une table existante. Cette clause contient l'expression d'une colonne et une l'intervalle.

Pour ajouter une règle au moment de la création de la table:

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

Où :

• timestamp_column doit être une colonne existante de type TIMESTAMP. Colonnes avec codes temporels de commit sont valides, tout comme colonnes générées. Cependant, vous ne pouvez pas spécifier une colonne générée qui référence une colonne d'horodatage de commit.

• num_days correspond au nombre de jours après l'horodatage dans la colonne timestamp_column définissant quand la ligne est marquée pour suppression. La valeur doit être un nombre entier non négatif, et DAY est le seul unité acceptée.

Pour ajouter une règle à une table existante, utilisez l'instruction ALTER TABLE. A la table ne peut pas comporter plus d'une règle de suppression de ligne. L'ajout d'une règle de suppression de lignes à une table comportant une règle existante échoue et génère une erreur. Consultez la section Valeur TTL sur des colonnes générées pour spécifier une logique de suppression de lignes plus sophistiquée.

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

PostgreSQL

Pour créer une règle de suppression de lignes à l'aide de PostgreSQL, vous pouvez définir TTL INTERVAL lorsque vous créez une table ou ajoutez une stratégie à une table existante.

Pour ajouter une règle au moment de la création de la table:

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

Où :

• timestamp_column_name doit être une colonne de type de données TIMESTAMPTZ. Vous devez créer cette colonne dans l'instruction CREATE TABLE. Les colonnes comportant des horodatages de commit sont valides, tout comme les colonnes générées. Cependant, vous ne pouvez pas spécifier une colonne générée qui référence une colonne d'horodatage de commit.

• interval_spec est le nombre de jours après la code temporel dans le timestamp_column_name pour lequel la ligne est marquée de suppression. La valeur doit être un entier non négatif et doit correspondre à un nombre entier de jours. Par exemple, '3 days' est autorisé, mais '3 days - 2 minutes' renvoie une erreur.

Pour ajouter une règle à une table existante, utilisez l'instruction ALTER TABLE. Une table ne peut comporter qu'une seule règle TTL. L'ajout d'une règle TTL à une table comportant une règle existante échoue et génère une erreur. Consultez la section Valeur TTL sur des colonnes générées pour spécifier une logique TTL plus sophistiquée.

Pour ajouter une règle à une table existante :

ALTER TABLE albums
ADD COLUMN timestampcolumn TIMESTAMPTZ;

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

Restrictions

Les règles de suppression de lignes sont soumises aux restrictions suivantes.

Valeur TTL des tables référencées par une clé étrangère

Vous ne pouvez pas créer de règle de suppression de lignes dans les cas suivants :

• Sur un tableau référencé par un clé étrangère qui n'inclut pas la clé ON DELETE CASCADE.
• Sur le parent d'un tableau référencé par une clé étrangère qui n'a pas inclure l’action référentielle ON DELETE CASCADE.

Dans l'exemple suivant, vous ne pouvez pas ajouter de règle de suppression de lignes à la table Customers, car elle est référencée par une clé étrangère dans la table Orders, qui ne comporte pas la contrainte ON DELETE CASCADE. La suppression de clients pourrait enfreindre cette contrainte de clé étrangère. Vous ne pouvez pas non plus ajouter de règle de suppression de lignes à la table Districts. Suppression d'une ligne dans Districts peut entraîner la cascade des suppressions dans la table Customers enfant, ce qui pourrait enfreindre la contrainte de clé étrangère sur la table 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)
);

Vous pouvez créer une règle de suppression de lignes sur une table référencée par une règle contrainte de clé utilisant ON DELETE CASCADE. Dans l'exemple suivant, vous pouvez créer une règle de suppression de lignes sur la table Customers, qui est référencée par la contrainte de clé étrangère CustomerOrder, définie sur la table Orders. Quand ? La valeur TTL supprime les lignes dans Customers, la suppression est appliquée en cascade jusqu'aux lignes correspondantes qui se trouvent dans la table 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
);

De même, vous pouvez créer une règle de suppression de lignes sur le parent d'une table référencée par une contrainte de clé étrangère ON DELETE CASCADE.

Valeur TTL sur les colonnes avec des valeurs par défaut

Une règle de suppression de lignes peut utiliser une colonne de code temporel avec une valeur par défaut. La valeur par défaut type est CURRENT_TIMESTAMP. Si aucune valeur n'est explicitement attribuée à la colonne ou si la colonne est définie sur sa valeur par défaut par une instruction INSERT ou UPDATE, la valeur par défaut est utilisée dans le calcul de la règle.

Dans l'exemple suivant, la valeur par défaut de la colonne CreatedAt dans la table Customers est l'horodatage au moment de la création de la ligne.

GoogleSQL

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

Pour en savoir plus, consultez DEFAULT (expression) dans le langage de définition des données GoogleSQL.

PostgreSQL

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

Pour en savoir plus, consultez CREATE TABLE dans le "langage de définition de données PostgreSQL".

Valeur TTL sur des colonnes générées

Les règles de suppression de lignes peuvent utiliser des colonnes générées pour exprimer des règles plus sophistiquées. Par exemple, vous pouvez définir la suppression d'une ligne sur le code temporel greatest (GoogleSQL ou PostgreSQL) de plusieurs colonnes ou mappe une autre valeur sur une code temporel.

Remarque : Vous ne pouvez pas spécifier une colonne générée faisant référence à une colonne d'horodatage de commit.

GoogleSQL

Le tableau suivant, intitulé Orders, suit les commandes client. Le propriétaire de la table souhaite configurer une règle de suppression de lignes qui supprime les commandes annulées au bout de 30 jours et les commandes non annulées au bout de 180 jours.

La valeur TTL de Spanner n'autorise qu'une seule règle de suppression de lignes par table. À exprimant les deux critères dans une seule colonne, vous pouvez utiliser une colonne générée avec une instruction 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'instruction crée une colonne nommée ExpiredDate qui ajoute 30 jours ou 180 jours à la colonne LastModifiedDate, suivant l'état de la commande. Ensuite, il définit la règle de suppression des lignes pour que les lignes expirent le jour où elles sont stockées dans Colonne ExpiredDate en spécifiant INTERVAL 0 day.

PostgreSQL

Le tableau suivant, intitulé Orders, suit les commandes client. Le propriétaire de la table souhaite configurer une règle de suppression de lignes qui supprime les lignes au bout de 30 jours d'inactivité.

La valeur TTL de Spanner n'autorise qu'une seule règle de suppression de lignes par table. À expriment les deux critères dans une seule colonne, vous pouvez créer colonne:

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'instruction crée une colonne générée nommée ExpiredDate qui évalue la plus récente des deux dates (LastModifiedDate ou CreateDate). Ensuite, elle définit la règle de suppression de lignes pour que les lignes expirent 30 jours après la création de la commande. Si la commande a été modifiée au cours de ces 30 jours, la suppression est prolongée de 30 jours supplémentaires.

Valeur TTL et tables entrelacées

Les tables entrelacées constituent une optimisation des performances qui associe les lignes liées d'une table enfant de type un à plusieurs à une ligne d'une table parente. Pour ajouter une règle de suppression de lignes à une table parente, toutes les tables enfants entrelacées doivent spécifier ON DELETE CASCADE, ce qui signifie que les lignes enfants seront supprimées de manière atomique avec la ligne parente. Cela garantit l'intégrité référentielle, de sorte que toute suppression sur la table parente supprime également les lignes enfants associées et ce, dans la même transaction. La valeur TTL de Spanner n'est pas compatible avec ON DELETE NO ACTION.

Taille maximale des transactions

Spanner a une limite de taille de transaction. Les suppressions en cascade sur des hiérarchies parents-enfants volumineuses avec colonnes indexées peuvent dépasser ces limites et entraîner l'échec d'une ou plusieurs opérations TTL. Pour les opérations ayant échoué, la valeur TTL conduit à effectuer de nouvelles tentatives avec des lots plus petits, jusqu'à atteindre une seule ligne parente. Toutefois, une hiérarchie d'enfants très volumineuse, même si elle correspond à une unique ligne parente, peut toujours dépasser la limite de mutations.

Les opérations ayant échoué sont signalées dans les métriques de valeur TTL.

Si une seule ligne et ses enfants entrelacés sont trop volumineux pour être supprimés, vous pouvez associer une règle de suppression de lignes directement sur les tables enfants, en plus de celle de la table parente. La règle sur les tables enfants doit être configurée que les lignes enfants sont supprimées avant les lignes parent.

Pensez à associer une règle de suppression de lignes aux tables enfants lorsque les deux instructions suivantes s'appliquent :

• La table enfant est associée à des index globaux.
• Vous prévoyez un grand nombre de lignes enfants (> 100) par ligne parente.

Supprimer une règle de suppression de lignes

Vous pouvez supprimer une règle de suppression de lignes existante d'une table. Cela renvoie une erreur s'il n'existe aucune règle de suppression de lignes sur la table.

GoogleSQL

ALTER TABLE MyTable
DROP ROW DELETION POLICY;

PostgreSQL

ALTER TABLE mytable
DROP TTL;

La suppression d'une règle de suppression de lignes interrompt immédiatement les processus TTL s'exécutant en arrière-plan. Toutes les lignes déjà supprimées par les processus en cours restent supprimées.

Supprimer une colonne référencée par une règle de suppression de lignes

Spanner ne vous permet pas de supprimer une colonne référencée par une règle de suppression de lignes. Vous devez d'abord supprimer la règle de suppression de lignes avant de supprimer la colonne.

Afficher la règle de suppression de lignes d'une table

Vous pouvez afficher les règles de suppression des lignes de vos tables Spanner.

GoogleSQL

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

Pour en savoir plus, consultez Schéma d'informations pour les bases de données de dialecte GoogleSQL.

PostgreSQL

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

Pour en savoir plus, consultez Schéma d'informations pour les bases de données de dialecte PostgreSQL.

Modifier une règle de suppression de lignes

Vous pouvez modifier la colonne ou l'expression d'intervalle d'une règle de suppression de lignes existante. L'exemple suivant remplace la colonne CreatedAt par ModifiedAt et étend l'intervalle de 1 DAY à 7 DAY. Cela renvoie une erreur s'il n'existe aucune règle de suppression de lignes sur la table.

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;