Administra la retención de datos con el TTL

En esta página, se explica cómo usar el tiempo de actividad (TTL) en las tablas de Spanner. Para obtener más información, consulta la sección Acerca del TTL.

Antes de comenzar

Antes de comenzar, sigue estas prácticas recomendadas.

Habilita la copia de seguridad y la recuperación de un momento determinado

Antes de agregar el TTL a tu tabla, te recomendamos habilitar Copia de seguridad y restablecimiento de Spanner. Esto te permite restablecer por completo una base de datos en caso de que borres tus datos por accidente con la política de TTL.

Si habilitaste la recuperación de un momento determinado, puedes ver y restablecer los datos borrados (sin un restablecimiento completo de la copia de seguridad) si se encuentran en el período de retención de la versión configurada. Si deseas obtener información para leer datos en el pasado, consulta Cómo realizar una lectura inactiva.

Limpiar datos antiguos

Si es la primera vez que usas el TTL y esperas que la primera ejecución borre muchas filas, considera primero limpiar los datos antiguos de forma manual mediante DML particionado. Esto te brinda más control sobre el uso de recursos, en lugar de dejarlo para el proceso en segundo plano del TTL. El TTL se ejecuta con una prioridad baja, ideal para una limpieza incremental. Sin embargo, es probable que esto alargue el tiempo que lleva borrar el conjunto inicial de filas en una base de datos ocupada porque el programador de trabajo interno de Spanner priorizará otros trabajos, como las consultas de los usuarios.

Verifica tus condiciones

En las tablas de GoogleSQL, si quieres verificar los datos que afectará 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 necesarios

Para cambiar el esquema de la base de datos, debes tener el permiso spanner.databases.updateDdl. Si deseas obtener más detalles, consulta Control de acceso para Spanner.

Crea 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 cuando creas una tabla nueva o agregas una política a una tabla existente. Esta cláusula contiene una expresión de una columna y un intervalo.

Para agregar una política durante la creación de la 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));

Aquí:

timestamp_column debe ser una columna existente con el 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 marcas de tiempo de confirmación.
num_days es la cantidad de días desde la marca de tiempo del timestamp_column en los que se marca la fila para su eliminación. El valor debe ser un número entero no negativo y DAY es la única unidad compatible.

Para agregar una política a una tabla existente, usa el estado ALTER TABLE. Una tabla puede tener como máximo una política de eliminación de filas. Cuando se agrega una política de eliminación de filas a una tabla con una política existente, 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 cuando creas una tabla nueva o agregas una política a una tabla existente.

Para agregar una política durante la creación de la tabla, sigue estos pasos:

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

Aquí:

timestamp_column_name debe ser una columna con el tipo de datos TIMESTAMPTZ. Debes 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 marcas de tiempo de confirmación.
interval_spec es la cantidad de días desde la marca de tiempo del timestamp_column_name en los que se marca la fila para su eliminación. El valor debe ser un número entero no negativo y debe evaluarse como una cantidad de días entero. Por ejemplo, se permite '3 days', pero '3 days - 2 minutes' muestra un error.

Para agregar una política a una tabla existente, usa el estado ALTER TABLE. Una tabla puede tener como máximo una política de TTL. Agregar una política de TTL a una tabla con una política existente falla y muestra un error. Consulta TTL en las columnas generadas para especificar una lógica de TTL más sofisticada.

Para agregar una política a una tabla existente, sigue estos pasos:

ALTER TABLE albums
ADD COLUMN timestampcolumn TIMESTAMPTZ;

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

Restricciones

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

En una tabla a la que hace referencia una clave externa
En el elemento superior de una tabla a la que hace referencia una clave externa

En el siguiente ejemplo, no puedes agregar una política de eliminación de filas a la tabla Customers porque hace referencia a una clave externa en la tabla Orders y borrar clientes podría infringir esta restricción.

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)
);

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 típico es CURRENT_TIMESTAMP. Si no se asigna ningún valor de forma explícita a la columna, o si esta se establece en su valor predeterminado mediante una declaración INSERT o UPDATE, se usa el valor predeterminado en el cálculo de la regla.

En el siguiente ejemplo, el valor predeterminado para la columna CreatedAt en 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) en "Lenguaje de definición de datos de GoogleSQL".

PostgreSQL

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

Para obtener más información, consulta CREAR TABLA en “Lenguaje de definición de datos de PostgreSQL”.

TTL en columnas generadas

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

GoogleSQL

La siguiente tabla denominada Orders hace un seguimiento de los pedidos de venta. El propietario de la tabla desea configurar una política de eliminación de filas que borre los pedidos cancelados después de 30 días y los pedidos no cancelados después de 180 días.

El TTL de Spanner 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 declaració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 agrega 30 días o 180 días al LastModifiedDate según el estado del pedido. Luego, especifica INTERVAL 0 day para definir la política de eliminación de filas a fin de que venzan las filas en el día almacenadas en la columna ExpiredDate.

PostgreSQL

La siguiente tabla denominada Orders hace un seguimiento de los pedidos de venta. El propietario de la tabla quiere configurar una política de eliminación de filas que borre las filas después de 30 días de inactividad.

El TTL de Spanner 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). Luego, define que la política de eliminación de filas venza 30 días después de la creación del pedido o, si el pedido se modificó dentro de esos 30 días, se extenderá la eliminación por otros 30 días.

TTL y tablas intercaladas

Las tablas intercaladas son una optimización de rendimiento que asocia las filas relacionadas en una tabla secundaria de uno a varios con una fila en una tabla superior. Para agregar una política de eliminación de filas en una tabla superior, todas las tablas secundarias intercaladas deben especificar ON DELETE CASCADE, lo que significa que las filas secundarias se borrarán de forma atómica con la fila superior. Esto garantiza la integridad referencial de modo que las eliminaciones en la tabla superior también borren las filas secundarias relacionadas en la misma transacción. El TTL de Spanner no admite ON DELETE NO ACTION.

Tamaño máximo de transacción

Spanner tiene un límite de tamaño de transacción. Las eliminaciones en cascada en jerarquías superiores y secundarias grandes con columnas indexadas podrían superar estos límites y hacer que una o más operaciones de TTL fallen. En el caso de las operaciones fallidas, TTL volverá a intentarlo con lotes más pequeños, hasta una sola fila superior. Sin embargo, las jerarquías secundarias grandes para incluso una sola fila superior pueden superar el límite de mutación.

Las operaciones con errores se informan en las métricas de TTL.

Si una sola fila y sus elementos secundarios intercalados son demasiado grandes para borrarlos, puedes adjuntar una política de eliminación de filas directamente en las tablas secundarias, además de la que está en la tabla superior. La política de las tablas secundarias debe configurarse de modo que las filas secundarias se borren antes que las filas superiores.

Considera adjuntar una política de eliminación de filas a las tablas secundarias cuando se apliquen las siguientes dos declaraciones:

La tabla secundaria tiene todos los índices globales asociados; y
Esperas una gran cantidad de filas secundarias (más de 100) por fila superior.

Borrar una política de eliminación de filas

Puedes descartar una política de eliminación de filas existente de una tabla. Esto muestra un error si no hay una política de eliminación de fila existente en la tabla.

GoogleSQL

ALTER TABLE MyTable
DROP ROW DELETION POLICY;

PostgreSQL

ALTER TABLE mytable
DROP TTL;

Si borras una política de eliminación de filas, se anulan de inmediato todos los procesos de TTL que se ejecuten en segundo plano. Las filas que ya se borraron en los procesos en curso permanecen borradas.

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

Spanner no te permite borrar una columna a la que hace referencia una política de eliminación de filas. Debes borrar la política de eliminación de filas antes de borrar la columna.

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

Puedes ver las políticas de eliminación de filas de tus tablas de Spanner.

GoogleSQL

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

Si deseas obtener más información, consulta Esquema de información para bases de datos con dialectos de GoogleSQL.

PostgreSQL

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

Si deseas obtener más información, consulta Esquema de información para bases de datos de dialecto de PostgreSQL.

Modifica una política de eliminación de filas

Puedes modificar la columna o la expresión de intervalo de una política de eliminación de filas existente. En el siguiente ejemplo, se cambia la columna de CreatedAt a ModifiedAt y se extiende el intervalo de 1 DAY a 7 DAY. Esto muestra un error si no hay una política de eliminación de filas existente en la tabla.

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;