Gestionar la conservación de datos con TTL

En esta página se explica cómo usar el tiempo de vida (TTL) en tablas de Spanner en bases de datos con dialecto de GoogleSQL y con dialecto de PostgreSQL. Para obtener más información, consulta Información sobre TTL.

Antes de empezar

Antes de empezar, sigue estas prácticas recomendadas.

Habilitar la copia de seguridad y la recuperación a un momento dado

Antes de añadir TTL a tu tabla, te recomendamos que habilites copias de seguridad y restauración de Spanner. De esta forma, puedes restaurar por completo una base de datos si eliminas accidentalmente tus datos con la política de TTL.

Si has habilitado la recuperación a un momento dado, puedes ver y restaurar los datos eliminados (sin tener que restaurar una copia de seguridad completa) si se encuentran dentro del periodo de conservación de versiones configurado. Para obtener información sobre cómo leer datos antiguos, consulta Realizar una lectura obsoleta.

Limpiar datos antiguos

Si es la primera vez que usas TTL y esperas que la primera ejecución elimine muchas filas, te recomendamos que primero limpies los datos antiguos manualmente con DML particionado. De esta forma, tienes más control sobre el uso de los recursos en lugar de dejarlo en manos del proceso en segundo plano del TTL. El TTL se ejecuta con una prioridad baja, lo que resulta ideal para la limpieza incremental. Sin embargo, es probable que se tarde más en eliminar el conjunto inicial de filas de una base de datos con mucha actividad, ya que el programador de trabajo interno de Spanner prioriza otras tareas, como las consultas de los usuarios.

Verificar las condiciones

En el caso de las tablas de GoogleSQL, si quieres verificar los datos que afecta la política de eliminación de filas antes de habilitar el TTL, puedes consultar tu tabla con las mismas condiciones. Por ejemplo:

GoogleSQL 

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

Permisos obligatorios

Para cambiar el esquema de la base de datos, debes tener el permiso spanner.databases.updateDdl. Para obtener más información, consulta Control de acceso de Spanner.

Crear una política de eliminación de filas

GoogleSQL

Para crear una política de eliminación de filas con GoogleSQL, puedes definir una cláusula ROW DELETION POLICY al crear una tabla o añadir una política a una tabla que ya tengas. Esta cláusula contiene una expresión de una columna y un intervalo.

Para añadir una política al crear una tabla, sigue estos pasos:

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

Haz los cambios siguientes:

  • timestamp_column debe ser una columna que ya exista y que sea de tipo TIMESTAMP. Las columnas con marcas de tiempo de confirmación son válidas, al igual que las columnas generadas. Sin embargo, no puedes especificar una columna generada que haga referencia a una columna de marca de tiempo de confirmación.

  • num_days es el número de días transcurridos desde la marca de tiempo timestamp_column en la que se marca la fila para eliminarla. El valor debe ser un número entero no negativo y DAY es la única unidad admitida.

Para añadir una política a una tabla, usa la instrucción ALTER TABLE. Una tabla puede tener como máximo una política de eliminación de filas. Si se añade una política de eliminación de filas a una tabla que ya tiene una, se produce un error. Consulta TTL en columnas generadas para especificar una lógica de eliminación de filas más sofisticada.

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

PostgreSQL

Para crear una política de eliminación de filas con PostgreSQL, puedes definir una cláusula TTL INTERVAL al crear una tabla o añadir una política a una tabla que ya tengas.

Para añadir una política al crear una tabla, sigue estos pasos:

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

Haz los cambios siguientes:

  • timestamp_column_name debe ser una columna con el tipo de datos TIMESTAMPTZ. Debe crear esta columna en la instrucción CREATE TABLE. Las columnas con marcas de tiempo de confirmación son válidas, al igual que las columnas generadas. Sin embargo, no puedes especificar una columna generada que haga referencia a una columna de marca de tiempo de confirmación.

  • interval_spec es el número de días transcurridos desde la marca de tiempo de timestamp_column_name en la que se marca la fila para eliminarla. El valor debe ser un número entero no negativo y debe evaluarse como un número entero de días. Por ejemplo, '3 days' se permite, pero '3 days - 2 minutes' devuelve un error.

Para añadir una política a una tabla, usa la instrucción ALTER TABLE. Una tabla puede tener como máximo una política de TTL. Si añades una política de TTL a una tabla que ya tiene una, se produce un error. Consulte TTL en columnas generadas para especificar una lógica de TTL más sofisticada.

Para añadir una política a una tabla, sigue estos pasos:

ALTER TABLE albums
ADD COLUMN timestampcolumn TIMESTAMPTZ;

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

Restricciones

Las políticas de eliminación de filas tienen las siguientes restricciones.

TTL en tablas a las que hace referencia una clave externa

No puedes crear una política de eliminación de filas:

  • En una tabla a la que hace referencia una clave externa que no incluye la restricción ON DELETE CASCADE.
  • En el elemento superior de una tabla a la que hace referencia una clave externa que no incluye la acción referencial ON DELETE CASCADE.

En el ejemplo siguiente, no puedes añadir una política de eliminación de filas a la tabla Customers porque una clave externa de la tabla Orders hace referencia a ella, y esta tabla no tiene la restricción ON DELETE CASCADE. Si se eliminan clientes, se podría infringir esta restricción de clave externa. Tampoco puedes añadir una política de eliminación de filas a la tabla Districts. Eliminar una fila de Districts puede provocar que las eliminaciones se apliquen en cascada en la tabla secundaria Customers, lo que podría infringir la restricción de clave externa en la tabla 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)
);

Puedes crear una política de eliminación de filas en una tabla a la que haga referencia una restricción de clave externa que use ON DELETE CASCADE. En el siguiente ejemplo, puede crear una política de eliminación de filas en la tabla Customers a la que hace referencia la restricción de clave externa CustomerOrder, definida en la tabla Orders. Cuando el TTL elimina filas de Customers, la eliminación se aplica en cascada a las filas coincidentes de la tabla 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
);

Del mismo modo, puede crear una política de eliminación de filas en un elemento superior de una tabla a la que haga referencia una restricción de clave externa ON DELETE CASCADE.

TTL en columnas con valores predeterminados

Una política de eliminación de filas puede usar una columna de marca de tiempo con un valor predeterminado. Un valor predeterminado habitual es CURRENT_TIMESTAMP. Si no se asigna ningún valor explícitamente a la columna o si se le asigna su valor predeterminado mediante una instrucción INSERT o UPDATE, se usará el valor predeterminado en el cálculo de la regla.

En el ejemplo siguiente, el valor predeterminado de la columna CreatedAt de la tabla Customers es la marca de tiempo en la que se crea la fila.

GoogleSQL 

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

Para obtener más información, consulta DEFAULT (expresión).

PostgreSQL 

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

Para obtener más información, consulta CREATE TABLE.

TTL en columnas generadas

Las políticas de eliminación de filas pueden usar columnas generadas para expresar reglas más complejas. Por ejemplo, puedes definir una política de eliminación de filas en la greatest marca de tiempo (GoogleSQL o PostgreSQL) de varias columnas o asignar otro valor a una marca de tiempo.

GoogleSQL

La siguiente tabla, llamada Orders, registra los pedidos de venta. El propietario de la tabla quiere configurar una política de eliminación de filas que elimine los pedidos cancelados al cabo de 30 días y los pedidos no cancelados al cabo de 180 días.

Spanner TTL solo permite una política de eliminación de filas por tabla. Para expresar los dos criterios en una sola columna, puedes usar una columna generada con una instrucción 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));

La instrucción crea una columna llamada ExpiredDate que añade 30 o 180 días a LastModifiedDate en función del estado del pedido. A continuación, define la política de eliminación de filas para que las filas caduquen el día almacenado en la columna ExpiredDate especificando INTERVAL 0 day.

PostgreSQL

La siguiente tabla, llamada Orders, registra los pedidos de venta. El propietario de la tabla quiere configurar una política de eliminación de filas que elimine las filas tras 30 días de inactividad.

Spanner TTL solo permite una política de eliminación de filas por tabla. Para expresar los dos criterios en una sola columna, puedes crear una columna generada:

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;

La instrucción crea una columna generada llamada ExpiredDate que evalúa la más reciente de las dos fechas (LastModifiedDate o CreateDate). A continuación, define la política de eliminación de filas para que las filas caduquen 30 días después de que se haya creado el pedido. Si el pedido se modifica en esos 30 días, la eliminación se aplazará otros 30 días.

TTL y tablas intercaladas

Las tablas intercaladas son una optimización del rendimiento que asocia filas relacionadas de una tabla secundaria de uno a varios con una fila de una tabla principal. Para añadir una política de eliminación de filas a una tabla principal, todas las tablas secundarias intercaladas deben especificar ON DELETE CASCADE, lo que significa que las filas secundarias se eliminan de forma atómica con la fila principal. De esta forma, se asegura la integridad referencial, de modo que las eliminaciones en la tabla principal también eliminen las filas secundarias relacionadas en la misma transacción. TTL de Spanner no admite ON DELETE NO ACTION.

Tamaño máximo de la transacción

Spanner tiene un límite de tamaño de las transacciones. Las eliminaciones en cascada de grandes jerarquías de elementos principales y secundarios con columnas indexadas podrían superar estos límites y provocar que se produzca un error en una o varias operaciones de TTL. En el caso de las operaciones fallidas, el TTL vuelve a intentarlo con lotes más pequeños, hasta llegar a una sola fila principal. Sin embargo, las jerarquías secundarias grandes, incluso para una sola fila principal, podrían superar el límite de mutaciones.

Las operaciones fallidas se registran en las métricas de TTL.

Si una sola fila y sus elementos secundarios intercalados son demasiado grandes para eliminarlos, puedes adjuntar una política de eliminación de filas directamente a las tablas secundarias, además de a la tabla principal. La política de las tablas secundarias debe configurarse de forma que las filas secundarias se eliminen antes que las filas principales.

Considera la posibilidad de adjuntar una política de eliminación de filas a las tablas secundarias cuando se cumplan las dos condiciones siguientes:

  • La tabla secundaria tiene los índices globales asociados y
  • Esperas un gran número (>100) de filas secundarias por cada fila principal.

Eliminar una política de eliminación de filas

Puedes eliminar una política de eliminación de filas de una tabla. Devuelve un error si no hay ninguna política de eliminación de filas en la tabla.

GoogleSQL 

ALTER TABLE MyTable
DROP ROW DELETION POLICY;

PostgreSQL 

ALTER TABLE mytable
DROP TTL;

Si se elimina una política de eliminación de filas, se cancelarán inmediatamente todos los procesos de TTL que se estén ejecutando en segundo plano. Las filas que ya hayan eliminado los procesos en curso seguirán eliminadas.

Eliminar una columna a la que hace referencia una política de eliminación de filas

Spanner no te permite eliminar una columna a la que haga referencia una política de eliminación de filas. Antes de eliminar la columna, debes eliminar la política de eliminación de filas.

Ver la política de eliminación de filas de una tabla

Puede ver las políticas de eliminación de filas de sus tablas de Spanner.

GoogleSQL 

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

Para obtener más información, consulta Esquema de información de bases de datos con dialecto de GoogleSQL.

PostgreSQL 

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

Para obtener más información, consulta Esquema de información de las bases de datos con dialecto PostgreSQL.

Modificar una política de eliminación de filas

Puede modificar la columna o la expresión de intervalo de una política de eliminación de filas. En el siguiente ejemplo, se cambia la columna de CreatedAt a ModifiedAt y se amplía el intervalo de 1 DAY a 7 DAY. Devuelve un error si la tabla no tiene ninguna política de eliminación de filas.

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;