Gerenciar a retenção de dados com o TTL

Nesta página, discutimos como usar o time to live (TTL) nas tabelas do Spanner. Para saber mais, consulte Sobre o TTL.

Antes de começar

Antes de começar, siga estas práticas recomendadas.

Ativar backup e recuperação pontual

Antes de adicionar o TTL à sua tabela, recomendamos ativar o backup e a restauração do Spanner. Isso permite restaurar totalmente um banco de dados caso você exclua acidentalmente seus dados com a política de TTL.

Se você ativou a recuperação pontual, poderá ver e restaurar dados excluídos (sem uma restauração completa do backup) se eles estiverem dentro da versão configurada. período de armazenamento. Para informações sobre como ler dados no passado, consulte Executar uma leitura desatualizada.

Limpe dados antigos

Se esta for a primeira vez que você está usando o TTL e você espera que a primeira execução exclua muitas linhas, primeiro recomendamos limpar os dados antigos manualmente usando DML particionada. Isso proporciona mais controle sobre o uso de recursos, em vez de deixar isso para o processo em segundo plano do TTL. O TTL é executado em prioridade baixa, ideal para limpeza incremental. No entanto, isso provavelmente aumentará o tempo necessário para excluir o conjunto inicial de linhas em um banco de dados ocupado, porque o programador de trabalho interno do Spanner priorizará outro trabalho, como consultas do usuário.

Verifique suas condições

Para tabelas do GoogleSQL, se você quiser verificar os dados que a política de exclusão de linhas afetará antes de ativar o TTL, consulte sua tabela usando as mesmas condições. Exemplo:

GoogleSQL

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

Permissões necessárias

Para alterar o esquema do banco de dados, é necessário ter a permissão spanner.databases.updateDdl. Para saber mais detalhes, consulte Controle de acesso para o Spanner.

Criar uma política de exclusão de linhas

GoogleSQL

Para criar uma política de exclusão de linhas usando o GoogleSQL, é possível definir uma cláusula ROW DELETION POLICY ao criar uma nova tabela ou adicionar uma política a uma tabela existente. Essa cláusula contém uma expressão de uma coluna e um intervalo.

Para adicionar uma política no momento da criação da tabela:

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

Em que:

• timestamp_column precisa ser uma coluna existente com o tipo TIMESTAMP. As colunas com carimbos de data/hora de confirmação são válidas, assim como as colunas geradas. No entanto, não é possível especificar uma coluna gerada que faz referência a uma coluna de carimbo de data/hora de confirmação.

• num_days é o número de dias após o carimbo de data/hora em timestamp_column em que a linha está marcada para exclusão. O valor precisa ser um número inteiro não negativo, e DAY é a única unidade aceita.

Para adicionar uma política a uma tabela atual, use a instrução ALTER TABLE. Uma tabela pode ter no máximo uma política de exclusão de linhas. Adicionar uma política de exclusão de linhas a uma tabela com uma política existente falha com um erro. Consulte TTL nas colunas geradas para especificar uma lógica mais sofisticada de exclusão de linha.

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

PostgreSQL

Para criar uma política de exclusão de linhas usando o PostgreSQL, defina uma cláusula TTL INTERVAL ao criar uma nova tabela ou adicione uma política a uma tabela atual.

Para adicionar uma política no momento da criação da tabela:

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

Em que:

• timestamp_column_name precisa ser uma coluna com o tipo de dados TIMESTAMPTZ. É necessário criar essa coluna na instrução CREATE TABLE. As colunas com carimbos de data/hora de confirmação são válidas, assim como as colunas geradas. No entanto, não é possível especificar uma coluna gerada que faz referência a uma coluna de carimbo de data/hora de confirmação.

• interval_spec é o número de dias após o carimbo de data/hora em timestamp_column_name em que a linha está marcada para exclusão. O valor precisa ser um número inteiro não negativo e avaliar como um número inteiro de dias. Por exemplo, '3 days' é permitido, mas '3 days - 2 minutes' retorna um erro.

Para adicionar uma política a uma tabela atual, use a instrução ALTER TABLE. Uma tabela pode ter no máximo uma política de TTL. Adicionar uma política de TTL a uma tabela com uma política atual falha com um erro. Consulte TTL em colunas geradas para especificar uma lógica de TTL mais sofisticada.

Para adicionar uma política a uma tabela existente:

ALTER TABLE albums
ADD COLUMN timestampcolumn TIMESTAMPTZ;

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

Restrições

Não é possível criar uma política de exclusão de linha:

• em uma tabela referenciada por uma chave estrangeira.
• No pai de uma tabela referenciada por uma chave externa.

No exemplo a seguir, não é possível adicionar uma política de exclusão de linhas à tabela Customers, porque ela é referenciada por uma chave externa na tabela Orders e a exclusão de clientes pode violar essa restrição.

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 em colunas com valores padrão

Uma política de exclusão de linhas pode usar uma coluna de carimbo de data/hora com um valor padrão. Um valor padrão típico é CURRENT_TIMESTAMP. Se nenhum valor for atribuído explicitamente à coluna ou se a coluna for definida como o valor padrão por uma instrução INSERT ou UPDATE, o valor padrão será usado no cálculo da regra.

No exemplo a seguir, o valor padrão da coluna CreatedAt na tabela Customers é o carimbo de data/hora em que a linha é criada.

GoogleSQL

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

Para mais informações, consulte PADRÃO (expressão) em "Linguagem de definição de dados do GoogleSQL".

PostgreSQL

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

Para saber mais, consulte CREATE TABLE em "Linguagem de definição de dados do PostgreSQL".

TTL em colunas geradas

As políticas de exclusão de linhas podem usar colunas geradas para expressar regras mais sofisticadas. Por exemplo, é possível definir uma política de exclusão de linhas no carimbo de data/hora greatest (GoogleSQL ou PostgreSQL) de várias colunas ou mapear outro valor para um carimbo de data/hora.

Observação: não é possível especificar uma coluna gerada que faça referência a uma coluna de carimbo de data/hora de confirmação.

GoogleSQL

A tabela a seguir, chamada Orders, acompanha os pedidos de venda. O proprietário da tabela quer configurar uma política de exclusão de linha que exclua pedidos cancelados após 30 dias e pedidos não cancelados após 180 dias.

O TTL do Spanner permite apenas uma política de exclusão de linha por tabela. Para expressar os dois critérios em uma única coluna, use uma coluna gerada com uma instrução 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));

A instrução cria uma coluna chamada ExpiredDate que adiciona 30 dias ou 180 dias ao LastModifiedDate, dependendo do status do pedido. Em seguida, ele define a política de exclusão de linhas para expirar as linhas no dia armazenado na coluna ExpiredDate especificando INTERVAL 0 day.

PostgreSQL

A tabela a seguir, chamada Orders, acompanha os pedidos de venda. O proprietário da tabela quer configurar uma política de exclusão de linhas após 30 dias de inatividade.

O TTL do Spanner permite apenas uma política de exclusão de linha por tabela. Para expressar os dois critérios em uma única coluna, crie uma coluna gerada:

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;

A instrução cria uma coluna gerada chamada ExpiredDate, que avalia a mais recente das duas datas (LastModifiedDate ou CreateDate). Em seguida, ela define a política de exclusão de linhas para expirar as linhas 30 dias após a criação do pedido. Se o pedido for modificado dentro desses 30 dias, ela estenderá a exclusão por mais 30 dias.

TTL e tabelas intercaladas

Tabelas intercaladas são uma otimização de desempenho que associa linhas relacionadas em uma tabela filha de um para muitos a uma linha em uma tabela pai. Para adicionar uma política de exclusão de linha em uma tabela pai, todas as tabelas filhas intercaladas precisam especificar ON DELETE CASCADE. Isso significa que as linhas filhas serão excluídas atomicamente com a linha pai. Isso garante a integridade referencial para que a exclusão na tabela pai também exclua as linhas filhas relacionadas na mesma transação. O TTL do Spanner não é compatível com ON DELETE NO ACTION.

Tamanho máximo da transação

O Spanner tem um limite de tamanho de transação. As exclusões em cascata em hierarquias pai-filho grandes com colunas indexadas podem exceder esses limites e causar falha em uma ou mais operações de TTL. Para operações com falha, o TTL tentará novamente com lotes menores, até uma única linha mãe. No entanto, hierarquias filhas grandes até mesmo em uma única linha mãe ainda podem exceder o limite de mutação.

As operações com falha são informadas em Métricas de TTL.

Se uma única linha e os filhos intercalados forem muito grandes para serem excluídos, anexe uma política de exclusão de linhas diretamente nas tabelas filhas, além daquela na tabela mãe. A política nas tabelas filhas precisa ser configurada de modo que as linhas filhas sejam excluídas antes das linhas mãe.

Considere anexar uma política de exclusão de linha a tabelas filhas quando as duas seguintes instruções se aplicarem:

• A tabela filha tem índices globais associados a ela. e
• Você espera um grande número de mais de (100) linhas filhas por linha pai.

Excluir uma política de exclusão de linhas

É possível descartar uma política de exclusão de linha existente de uma tabela. Isso retornará um erro se não houver uma política de exclusão de linha na tabela.

GoogleSQL

ALTER TABLE MyTable
DROP ROW DELETION POLICY;

PostgreSQL

ALTER TABLE mytable
DROP TTL;

A exclusão de uma política de exclusão de linha cancela imediatamente todos os processos de TTL em execução em segundo plano. Todas as linhas já excluídas pelos processos em andamento permanecem excluídas.

Excluir uma coluna referenciada por uma política de exclusão de linhas

O Spanner não permite excluir uma coluna referenciada por uma política de exclusão de linhas. Primeiro, é preciso excluir a política de exclusão de linha antes de excluir a coluna.

Exibir a política de exclusão de linhas de uma tabela

É possível visualizar as políticas de exclusão de linhas das suas tabelas do Spanner.

GoogleSQL

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

Para mais informações, consulte Esquema de informações para bancos de dados de dialetos GoogleSQL.

PostgreSQL

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

Para mais informações, consulte Esquema de informações para bancos de dados de dialetos PostgreSQL.

Modificar uma política de exclusão de linhas

É possível alterar a coluna ou a expressão de intervalo de uma política de exclusão de linha existente. O exemplo a seguir muda a coluna de CreatedAt para ModifiedAt e estende o intervalo de 1 DAY para 7 DAY. Se não houver uma política de exclusão de linhas na tabela, será retornado um erro.

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;