Administra la retención de datos con TTL

En esta página, se analiza cómo usar el tiempo de actividad (TTL) en las tablas de Spanner. Para para obtener más información, consulta 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 que habilites Copia de seguridad y restablecimiento de Spanner. Esto te permite restablecer una base de datos por completo en caso de que borres datos de forma accidental 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.

Limpia datos antiguos

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

Verifica tus condiciones

Para las tablas de GoogleSQL, si deseas 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. Para obtener más información, 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 ROW DELETION POLICY cuando crees una tabla nueva o agregues una política a en una tabla existente. Esta cláusula contiene una expresión de una columna y un durante un intervalo de tiempo determinado.

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. Columnas con Las marcas de tiempo de confirmación son válidas, al igual que 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 que pasaron la la marca de tiempo en el timestamp_column en la que la fila se marca como o la eliminación del contenido. El valor debe ser un número entero no negativo y DAY es el único unidad admitida.

Para agregar una política a una tabla existente, usa el estado ALTER TABLE. R la tabla puede tener como máximo una política de eliminación de filas. Agrega una política de eliminación de filas a una tabla con una política existente falla con 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 agregar 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 sentencia 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 posteriores a la marca de tiempo en la timestamp_column_name en la que se marca la fila para su eliminación. 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' está permitido, pero '3 days - 2 minutes' muestra un error.

Para agregar una política a una tabla existente, usa el estado ALTER TABLE. R 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 con un error. Consulta TTL en las columnas generadas para especificar más lógica de TTL 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

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 haga referencia un externa que no incluya el botó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 siguiente ejemplo, no puedes agregar una política de eliminación de filas a la tabla Customers, ya que una clave externa en la tabla Orders hace referencia a ella y no tiene la restricción ON DELETE CASCADE. Si borras clientes, es posible que se incumpla esta restricción de clave externa. Tampoco puedes agregar una política de eliminación de filas a la tabla Districts. Si borras una fila de Districts, es posible que las eliminaciones se propaguen en cascada en la tabla secundaria Customers, lo que podría incumplir 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 entidad restricción de clave que usa ON DELETE CASCADE. En el siguiente ejemplo, puedes Crea 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. Cuándo El TTL borra las filas de la Customers, y la eliminación se reduce en cascada a las filas coincidentes. que están en 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
);

De forma similar, puedes crear una política de eliminación de filas en un elemento superior de una tabla que sea se hace referencia a través de 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 el valor predeterminado es CURRENT_TIMESTAMP. Si no se asigna ningún valor de forma explícita a la columna o si una sentencia INSERT o UPDATE establece la columna en su valor predeterminado, se usa el valor predeterminado en el cálculo de la regla.

En el siguiente ejemplo, el valor predeterminado de 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 CREATE TABLE 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 el proceso de eliminación de filas política sobre la marca de tiempo greatest (GoogleSQL o PostgreSQL) de varias columnas o asigna otro valor a una y marca de tiempo.

Nota: No puedes especificar una columna generada que haga referencia a una columna de marca de tiempo de confirmación.

GoogleSQL

La siguiente tabla, llamada Orders, realiza un seguimiento de los pedidos de ventas. 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 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 agrega 30 días o 180 días al LastModifiedDate según el estado del pedido. Luego, define la política de eliminación de filas para que venza las filas el día almacenado en la ExpiredDate especificando INTERVAL 0 day.

PostgreSQL

En la siguiente tabla denominada Orders, se realiza un seguimiento de los pedidos de venta. El propietario de la tabla quiere configurar una política de eliminación de filas que borre filas. tras 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 la política de eliminación de filas para que venza las filas 30 días después del pedido se creó o si el pedido se modificó dentro de los 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 es compatible con 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 grandes jerarquías superiores y secundarias con columnas indexadas podrían exceder 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 de la tabla superior. La política sobre las tablas secundarias debe configurarse de modo que las filas secundarias se borren antes que las 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.

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

Para obtener más información, consulta Esquema de información para bases de datos de 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 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 extiende el intervalo de 1 DAY a 7 DAY. Esto muestra un 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;