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 la valeur TTL à votre table, nous vous recommandons d'activer la sauvegarde et la restauration 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 anciennes données

Si vous utilisez la valeur TTL pour la première fois et que vous vous attendez à ce que la première exécution supprime de nombreuses lignes, envisagez d'abord de nettoyer manuellement les anciennes données à l'aide du LMD partitionné. Cela vous donne plus de contrôle sur l'utilisation des ressources, au lieu de laisser le processus d'arrière-plan TTL en arrière-plan. La valeur TTL s'exécute à faible priorité, ce qui est idéal pour un nettoyage incrémentiel. Toutefois, cela risque d'allonger le temps nécessaire pour supprimer l'ensemble initial de lignes dans une base de données occupée, car le planificateur de tâches interne de Spanner donnera la priorité aux autres tâches, telles que les requêtes des utilisateurs.

Vérifiez vos conditions

Pour les tables GoogleSQL, si vous souhaitez vérifier les données 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 l'expression d'une colonne et 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 contenant 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 qui fait référence à une colonne d'horodatage de commit.
num_days correspond au nombre de jours après l'horodatage dans le champ timestamp_column au cours duquel 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 ne peut comporter qu'une seule 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 renvoie 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 de type de données TIMESTAMPTZ. Vous devez créer cette colonne dans l'instruction CREATE TABLE. Les colonnes contenant 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 qui fait référence à une colonne d'horodatage de commit.
interval_spec correspond au nombre de jours après l'horodatage dans le champ timestamp_column_name au cours duquel la ligne est marquée pour suppression. La valeur doit être un nombre entier non négatif et 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 avoir qu'une seule règle TTL. L'ajout d'une règle TTL à une table avec une règle existante échoue et renvoie une erreur. Consultez la section Valeur TTL sur les colonnes générées pour spécifier une logique TTL plus sophistiquée.

Pour ajouter une stratégie à une table existante:

ALTER TABLE albums
ADD COLUMN timestampcolumn TIMESTAMPTZ;

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

Restrictions

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

figurant sur une table référencée par une clé étrangère ;
Au niveau du parent d'une table référencée par une clé étrangère.

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, ce qui peut entraîner la suppression de clients.

GoogleSQL

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

CREATE TABLE Orders (
  OrderID INT64,
  CustomerID INT64,
  CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID)
) PRIMARY KEY (OrderID)

PostgreSQL

CREATE TABLE customers (
  customerid   bigint NOT NULL,
  CreatedAt  timestamptz,
  PRIMARY KEY(customerid)
);

CREATE TABLE orders (
  orderid bigint NOT NULL,
  customerid bigint,
  PRIMARY KEY(orderid),
  CONSTRAINT fk_customerorder FOREIGN KEY (customerid) REFERENCES customers (customerid)
);

Valeur TTL sur les colonnes avec valeurs par défaut

Une règle de suppression de lignes peut utiliser une colonne d'horodatage avec une valeur par défaut. Généralement, la valeur par défaut est CURRENT_TIMESTAMP. Si aucune valeur n'est explicitement attribuée à la colonne ou si elle 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 correspond à l'horodatage auquel la ligne est créée.

GoogleSQL

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

Pour en savoir plus, consultez la section PAR DÉFAUT (expression) dans la section "Langage de définition de données GoogleSQL".

PostgreSQL

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

Pour en savoir plus, consultez la section CRÉER UNE TABLE dans le document "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 ligne sur le code temporel greatest (GoogleSQL ou PostgreSQL) de plusieurs colonnes ou mapper une autre valeur à un horodatage.

GoogleSQL

La table suivante, intitulée Orders, permet de suivre 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 ligne par table. Pour exprimer 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 de sorte que les lignes expirent le jour où elles sont stockées dans la colonne ExpiredDate en spécifiant INTERVAL 0 day.

PostgreSQL

La table suivante, intitulée Orders, permet de suivre les commandes client. Le propriétaire de la table souhaite configurer une règle de suppression des lignes qui supprime les lignes après 30 jours d'inactivité.

La valeur TTL de Spanner n'autorise qu'une seule règle de suppression de ligne par table. Pour exprimer les deux critères dans une seule 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 date la plus récente des deux (LastModifiedDate ou CreateDate). Elle définit ensuite la règle de suppression des 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, elle prolonge la suppression 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 impose une limite de taille des transactions. Les suppressions en cascade sur de grandes hiérarchies parent-enfant avec des 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 ligne directement sur les tables enfants, en plus de celle de la table parente. La règle appliquée aux tables enfants doit être configurée de sorte que les lignes enfants soient supprimées 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 afficher 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 utilisant le 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 la section Schéma d'informations pour les bases de données utilisant le 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 fait passer la colonne de CreatedAt à 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 dans 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;