Faça a gestão da retenção de dados com o TTL

Esta página aborda a utilização do tempo de vida (TTL) em tabelas do Spanner em bases de dados com dialeto GoogleSQL e bases de dados com dialeto PostgreSQL. Para mais informações, consulte o artigo Acerca da TTL.

Antes de começar

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

Ative a cópia de segurança e a recuperação num ponto específico no tempo

Antes de adicionar o TTL à sua tabela, recomendamos que ative a cópia de segurança e o restauro do Spanner. Isto permite-lhe restaurar totalmente uma base de dados caso elimine acidentalmente os seus dados com a política de TTL.

Se tiver ativado a recuperação num ponto específico no tempo, pode ver e restaurar dados eliminados, sem uma restauração completa a partir da cópia de segurança, se estiver dentro do período de retenção de versões configurado. Para ver informações sobre como ler dados no passado, consulte o artigo Efetue uma leitura desatualizada.

Limpe dados antigos

Se estiver a usar o TTL pela primeira vez e esperar que a primeira execução elimine muitas linhas, considere limpar primeiro os dados antigos manualmente através do DML particionado. Isto dá-lhe mais controlo sobre a utilização de recursos, em vez de a deixar ao processo em segundo plano do TTL. O TTL é executado com uma prioridade baixa, o que é ideal para a limpeza incremental. No entanto, é provável que isto aumente o tempo necessário para eliminar o conjunto inicial de linhas numa base de dados ocupada, porque o programador de tarefas interno do Spanner dá prioridade a outras tarefas, como as consultas dos utilizadores.

Valide as suas condições

Para tabelas GoogleSQL, se quiser validar os dados que a política de eliminação de linhas afeta antes de ativar o TTL, pode consultar a sua tabela usando as mesmas condições. Por exemplo:

GoogleSQL

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

Autorizações necessárias

Para alterar o esquema da base de dados, tem de ter a autorização spanner.databases.updateDdl. Para obter detalhes, consulte o artigo Controlo de acesso para o Spanner.

Crie uma política de eliminação de linhas

GoogleSQL

Para criar uma política de eliminação de linhas com o GoogleSQL, pode definir uma cláusula ROW DELETION POLICY quando cria uma nova tabela ou adicionar uma política a uma tabela existente. Esta 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, faça o seguinte:

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

Substitua o seguinte:

• timestamp_column tem de ser uma coluna existente com o tipo TIMESTAMP. As colunas com datas/horas de confirmação são válidas, tal como as colunas geradas. No entanto, não pode especificar uma coluna gerada que faça referência a uma coluna de data/hora de confirmação.

• num_days é o número de dias após a data/hora no elemento timestamp_column em que a linha é marcada para eliminação. O valor tem de ser um número inteiro não negativo e DAY é a única unidade suportada.

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

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

PostgreSQL

Para criar uma política de eliminação de linhas através do PostgreSQL, pode definir uma cláusula TTL INTERVAL quando cria uma nova tabela ou adicionar uma política a uma tabela existente.

Para adicionar uma política no momento da criação da tabela, faça o seguinte:

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

Substitua o seguinte:

• timestamp_column_name tem de ser uma coluna com o tipo de dados TIMESTAMPTZ. Tem de criar esta coluna na declaração CREATE TABLE. As colunas com datas/horas de confirmação são válidas, tal como as colunas geradas. No entanto, não pode especificar uma coluna gerada que faça referência a uma coluna de data/hora de confirmação.

• interval_spec é o número de dias após a data/hora no elemento timestamp_column_name em que a linha é marcada para eliminação. O valor tem de ser um número inteiro não negativo e tem de ser avaliado como um número inteiro de dias. Por exemplo, '3 days' é permitido, mas '3 days - 2 minutes' devolve um erro.

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

Para adicionar uma política a uma tabela existente, faça o seguinte:

ALTER TABLE albums
ADD COLUMN timestampcolumn TIMESTAMPTZ;

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

Restrições

As políticas de eliminação de linhas têm as seguintes restrições.

TTL em tabelas referenciadas por uma chave externa

Não pode criar uma política de eliminação de linhas:

• Numa tabela referenciada por uma chave externa que não inclui a restrição ON DELETE CASCADE.
• No elemento principal de uma tabela referenciada por uma chave externa que não inclui a ação referencial ON DELETE CASCADE.

No exemplo seguinte, não pode adicionar uma política de eliminação de linhas à tabela Customers, porque é referenciada por uma chave externa na tabela Orders, que não tem a restrição ON DELETE CASCADE. A eliminação de clientes pode violar esta restrição de chave externa. Também não pode adicionar uma política de eliminação de linhas à tabela Districts. A eliminação de uma linha da tabela Districts pode fazer com que as eliminações se propaguem em cascata na tabela secundária Customers, o que pode violar a restrição de chave externa na tabela 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)
);

Pode criar uma política de eliminação de linhas numa tabela referenciada por uma restrição de chave externa que usa ON DELETE CASCADE. No exemplo seguinte, pode criar uma política de eliminação de linhas na tabela Customers, que é referenciada pela restrição de chave externa CustomerOrder, definida na tabela Orders. Quando o TTL elimina linhas em Customers, a eliminação é aplicada em cascata às linhas correspondentes que se encontram na tabela 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
);

Da mesma forma, pode criar uma política de eliminação de linhas num elemento principal de uma tabela que é referenciada por uma restrição de chave externa ON DELETE CASCADE.

TTL em colunas com valores predefinidos

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

No exemplo seguinte, o valor predefinido para a coluna CreatedAt na tabela Customers é a 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 DEFAULT (expressão).

PostgreSQL

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

Para mais informações, consulte CREATE TABLE.

TTL em colunas geradas

As políticas de eliminação de linhas podem usar colunas geradas para expressar regras mais sofisticadas. Por exemplo, pode definir uma política de eliminação de linhas na data/hora greatest (GoogleSQL ou PostgreSQL) de várias colunas ou mapear outro valor para uma data/hora.

Nota: não pode especificar uma coluna gerada que faça referência a uma coluna de data/hora de confirmação.

GoogleSQL

A tabela seguinte, denominada Orders, acompanha as encomendas de vendas. O proprietário da tabela quer configurar uma política de eliminação de linhas que elimine as encomendas canceladas após 30 dias e as encomendas não canceladas após 180 dias.

O TTL do Spanner só permite uma política de eliminação de linhas por tabela. Para expressar os dois critérios numa única coluna, pode usar uma coluna gerada com uma declaraçã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 declaração cria uma coluna denominada ExpiredDate que adiciona 30 dias ou 180 dias à coluna LastModifiedDate, consoante o estado da encomenda. Em seguida, define a política de eliminação de linhas para que as linhas expirem no dia armazenado na coluna ExpiredDate, especificando INTERVAL 0 day.

PostgreSQL

A tabela seguinte, denominada Orders, acompanha as encomendas de vendas. O proprietário da tabela quer configurar uma política de eliminação de linhas que elimine linhas após 30 dias de inatividade.

O TTL do Spanner só permite uma política de eliminação de linhas por tabela. Para expressar os dois critérios numa única coluna, pode criar 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 declaração cria uma coluna gerada denominada ExpiredDate que avalia a data mais recente das duas datas (LastModifiedDate ou CreateDate). Em seguida, define a política de eliminação de linhas para expirar as linhas 30 dias após a criação da encomenda ou, se a encomenda tiver sido modificada nesses 30 dias, prolonga a eliminação por mais 30 dias.

TTL e tabelas intercaladas

As tabelas intercaladas são uma otimização do desempenho que associa linhas relacionadas numa tabela secundária de um para muitos a uma linha numa tabela principal. Para adicionar uma política de eliminação de linhas a uma tabela principal, todas as tabelas secundárias intercaladas têm de especificar ON DELETE CASCADE, o que significa que as linhas secundárias são eliminadas de forma atómica com a linha principal. Isto garante a integridade referencial de modo que as eliminações na tabela principal também eliminem as linhas secundárias relacionadas na mesma transação. O TTL do Spanner não suporta ON DELETE NO ACTION.

Tamanho máximo da transação

O Spanner tem um limite de tamanho das transações. As eliminações em cascata em grandes hierarquias principais-secundárias com colunas indexadas podem exceder estes limites e fazer com que uma ou mais operações de TTL falhem. Para operações com falhas, o TTL tenta novamente com lotes mais pequenos, até uma única linha principal. No entanto, as hierarquias secundárias grandes, mesmo para uma única linha principal, podem exceder o limite de mutação.

As operações com falhas são comunicadas nas métricas de TTL.

Se uma única linha e os respetivos elementos secundários intercalados forem demasiado grandes para eliminar, pode anexar uma política de eliminação de linhas diretamente às tabelas secundárias, além da política na tabela principal. A política em tabelas secundárias deve ser configurada de forma que as linhas secundárias sejam eliminadas antes das linhas principais.

Considere anexar uma política de eliminação de linhas a tabelas secundárias quando se aplicarem as seguintes duas declarações:

• A tabela secundária tem quaisquer índices globais associados; e
• Espera um grande número de linhas secundárias (>100) por linha principal.

Elimine uma política de eliminação de linhas

Pode remover uma política de eliminação de linhas existente de uma tabela. Esta função devolve um erro se não existir uma política de eliminação de linhas na tabela.

GoogleSQL

ALTER TABLE MyTable
DROP ROW DELETION POLICY;

PostgreSQL

ALTER TABLE mytable
DROP TTL;

A eliminação de uma política de eliminação de linhas anula imediatamente todos os processos de TTL em execução em segundo plano. Todas as linhas já eliminadas pelos processos em curso permanecem eliminadas.

Elimine uma coluna referenciada por uma política de eliminação de linhas

O Spanner não permite eliminar uma coluna referenciada por uma política de eliminação de linhas. Primeiro, tem de eliminar a política de eliminação de linhas antes de eliminar a coluna.

Veja a política de eliminação de linhas de uma tabela

Pode ver as políticas de eliminaçã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 o artigo Esquema de informações para bases de dados de dialeto 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 o artigo Esquema de informações para bases de dados com dialeto PostgreSQL.

Modifique uma política de eliminação de linhas

Pode alterar a coluna ou a expressão de intervalo de uma política de eliminação de linhas existente. O exemplo seguinte muda a coluna de CreatedAt para ModifiedAt e expande o intervalo de 1 DAY para 7 DAY. Esta função devolve um erro se não existir uma política de eliminação de linhas na tabela.

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;