Nesta página, abordamos como usar o time to live (TTL) nas tabelas do Spanner. Para Para saber mais, consulte Sobre 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 à tabela, recomendamos ativar o Backup e restauração do Spanner. Isso permite restaurar completamente um banco de dados caso você exclua acidentalmente seus dados com o 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.
Limpar dados antigos
Se você estiver usando o TTL pela primeira vez e espera que a primeira execução exclua muitas linhas, primeiro limpe os dados antigos manualmente usando DML particionada. Isso dá a você mais controle sobre o uso de recursos, em vez de deixá-lo para o processo de TTL em segundo plano. O TTL é executado com baixa prioridade, ideal para limpeza. 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 vai priorizar outro trabalho, como consultas de usuários.
Verificar as condições
Para tabelas do GoogleSQL, se você quiser verificar os dados que a política de exclusão de linha vai afetar antes de ativar o TTL, consulte a 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 mais detalhes, consulte Controle de acesso do Spanner.
Criar uma política de exclusão de linha
GoogleSQL
Para criar uma política de exclusão de linha usando o GoogleSQL, defina uma cláusula ROW DELETION POLICY
ao criar uma nova tabela ou adicione 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 tipoTIMESTAMP
. Colunas com carimbos de data/hora de confirmação são válidas, assim como colunas geradas. No entanto, não é possível especifique uma coluna gerada que faça 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 emtimestamp_column
em que a linha está marcada para exclusão. O valor precisa ser um número inteiro não negativo, eDAY
é a única unidade compatível.
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 linha. Como adicionar uma política de exclusão de linhas
a uma tabela com uma política atual falhará 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 um
cláusula TTL INTERVAL
ao criar uma nova tabela ou adicionar 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 tipo de dadosTIMESTAMPTZ
. É preciso criar essa coluna na instruçãoCREATE TABLE
. Colunas com carimbos de data/hora de confirmação são válidas, assim como colunas geradas. No entanto, não é possível especifique uma coluna gerada que faça 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 emtimestamp_column_name
em que a linha está marcada para exclusão. O valor precisa ser um número inteiro não negativo e precisa ser avaliado 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
. Um
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 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:
ALTER TABLE albums
ADD COLUMN timestampcolumn TIMESTAMPTZ;
ALTER TABLE albums
ADD TTL INTERVAL '5 days' ON timestampcolumn;
Restrições
As políticas de exclusão de linha têm as seguintes restrições.
TTL em tabelas referenciadas por uma chave externa
Não é possível criar uma política de exclusão de linha:
- Em uma tabela referenciada por um chave estrangeira que não inclui ON DELETE CASCADE.
- Na tabela pai referenciada por uma chave estrangeira que não inclui a ação referencial ON DELETE CASCADE.
No exemplo a seguir, não é possível adicionar uma política de exclusão de linha à tabela Customers
porque ela é referenciada por uma chave estrangeira na tabela Orders
, que não tem a restrição ON DELETE CASCADE.
Excluir clientes pode violar essa restrição de chave externa. Também não é possível
adicionar uma política de exclusão de linha à tabela Districts
. Excluindo uma linha de
Districts
pode fazer com que as exclusões sejam em cascata na tabela filha Customers
, 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)
);
É possível criar uma política de exclusão de linhas em uma tabela referenciada por uma entidade externa
Restrição de chave que usa ON DELETE CASCADE
. No exemplo abaixo, é possível
criar uma política de exclusão de linha na tabela Customers
, que é referenciada pela
restrição de chave estrangeira CustomerOrder
, definida na tabela Orders
. Quando
o TTL exclui linhas em Customers
, a exclusão é feita em cascata para linhas correspondentes
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, é possível criar uma política de exclusão de linha em um pai de uma tabela que é
referenciada por uma restrição de chave estrangeira ON DELETE CASCADE
.
TTL em colunas com valores padrão
Uma política de exclusão de linha 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 explicitamente atribuído
coluna, ou se a coluna for definida com o valor padrão por um INSERT
ou
UPDATE
, o valor padrão será usado no cálculo da regra.
No exemplo abaixo, o valor padrão da coluna CreatedAt
na tabela
Customers
é o carimbo de data/hora em que a linha foi criada.
GoogleSQL
CREATE TABLE Customers (
CustomerID INT64,
CreatedAt TIMESTAMP DEFAULT (CURRENT_TIMESTAMP())
) PRIMARY KEY (CustomerID);
Para mais informações, consulte DEFAULT (expression) em "Linguagem de definição de dados GoogleSQL".
PostgreSQL
CREATE TABLE customers (
customerid bigint NOT NULL,
createdat timestamptz DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY(customerid)
);
Para mais informações, 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, defina uma exclusão de linha
política no carimbo de data/hora greatest
(GoogleSQL ou PostgreSQL) de várias colunas, ou associe outro valor a uma
carimbo de data/hora.
GoogleSQL
A tabela Orders
a seguir rastreia 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, é possível usar 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. Depois,
define a política de exclusão de linhas para expirar linhas no dia armazenado no
coluna ExpiredDate
especificando INTERVAL 0 day
.
PostgreSQL
A tabela a seguir, chamada Orders
, rastreia 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 linha para que as linhas expirem 30 dias após a
criação do pedido. Se o pedido for modificado nesses 30 dias,
a exclusão será estendida 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. A exclusão em cascata em grandes hierarquias pai-filho com colunas indexadas pode exceder esses limites e causar falha em uma ou mais operações 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 seus filhos intercalados forem muito grandes para serem excluídos, será possível anexar uma política de exclusão de linhas diretamente nas tabelas filhas, além das uma na tabela pai. A política em tabelas filhas precisa ser configurada para que 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 linha
O Spanner não permite excluir uma coluna referenciada por uma política de exclusão de linha. Primeiro, é preciso excluir a política de exclusão de linha antes de excluir a coluna.
Conferir a política de exclusão de linha de uma tabela
É possível consultar as políticas de exclusão de linhas das 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 com dialeto do 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 dialeto PostgreSQL.
Modificar uma política de exclusão de linha
É 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
a 7 DAY
. Isso retorna uma
se não houver uma política de exclusã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;