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 bénéficiez ainsi d'un meilleur contrôle sur l'utilisation des ressources, au lieu de vous reposer sur le processus TTL d'arrière-plan. Le TTL s'exécute avec une faible priorité, ce qui est idéal pour le nettoyage incrémentiel. 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 qui seront affectées par la règle de suppression de lignes avant d'activer la valeur TTL, vous pouvez interroger votre table en utilisant 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 de l'autorisation spanner.databases.updateDdl. Pour en savoir plus, consultez la page 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 une expression d'une colonne et d'un 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. Les colonnes comportant des horodatages de commit sont valides, tout comme les colonnes générées. Toutefois, vous ne pouvez pas spécifier une colonne générée faisant 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 entier non négatif et DAY est la seule unité acceptée.

Pour ajouter une règle à une table existante, utilisez l'instruction ALTER TABLE. Une table peut comporter au maximum une règle de suppression de lignes. 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 une clause TTL INTERVAL lorsque vous créez une table ou ajouter une règle à 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 avec le 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. Toutefois, vous ne pouvez pas spécifier une colonne générée faisant référence à une colonne d'horodatage de commit.

• interval_spec correspond au nombre de jours après l'horodatage dans la colonne timestamp_column_name définissant quand la ligne est marquée pour 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.

TTL sur les 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 une table référencée par une clé étrangère qui n'inclut pas la contrainte ON DELETE CASCADE.
• Sur le parent d'une table référencée par une clé étrangère qui n'inclut pas 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 peut enfreindre cette contrainte de clé étrangère. Vous ne pouvez pas non plus ajouter de règle de suppression de lignes à la table Districts. La suppression d'une ligne de Districts peut entraîner des suppressions en cascade dans la table enfant Customers, ce qui peut 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 contrainte de clé étrangère qui utilise 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. Lorsque le TTL supprime des lignes dans Customers, la suppression se propage en cascade aux lignes correspondantes de 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 des colonnes avec des valeurs par défaut

Une règle de suppression de lignes peut utiliser une colonne d'horodatage 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 une règle de suppression de lignes sur l'horodatage greatest (GoogleSQL ou PostgreSQL) de plusieurs colonnes ou mapper une autre valeur à un horodatage.

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

GoogleSQL

La table suivante, nommée Orders, suit les commandes commerciales. 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. Pour exprimer les deux critères dans une même 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, elle définit la règle de suppression de lignes pour que les lignes expirent à la date stockée dans la colonne ExpiredDate en spécifiant INTERVAL 0 day.

PostgreSQL

La table suivante, nommée Orders, suit les commandes commerciales. 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. Pour exprimer les deux critères dans une même colonne, vous pouvez créer une colonne générée:

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 possède 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 de manière à supprimer les lignes enfants avant les lignes parentes.

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 consulter les règles de suppression de 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 la section Schéma d'informations pour les bases de données en 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 le schéma d'informations pour les bases de données en 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;