2
pedroven
6 min de leitura ·

A forma mais rápida de deletar 95% de um banco de dados é não deletar | Recorte de dados de produção

Como descrito aqui, a primeira tentativa de criar um recorte dos dados de produção foi feita usando o ORM do Django, e não funcionou: mesmo deletando em lotes, o job ainda estava rodando depois de mais de 2 horas, e o pipeline estourou o tempo limite antes de terminar. Olhando mais de perto, deletar é a direção cara tanto no Django, onde a cascata é resolvida em Python pelo Collector (como descrito no primeiro post), quanto no Postgres (tudo passa pelo WAL, e as linhas deletadas continuam lá até um VACUUM), e, além disso, o custo cresce com as linhas que jogamos fora, que são a maioria da tabela. Então, para tentar extrair o máximo de performance, decidimos ir para a estratégia completamente oposta: em vez de deletar uma porcentagem dos dados, copiar o que precisamos.

Copiar, truncar, reinserir

A estratégia que usamos consiste nestes 4 passos, mais um passo zero:

  1. SET CONSTRAINTS ALL IMMEDIATE, para que as chaves estrangeiras sejam verificadas a cada comando, em vez de no COMMIT.
  2. Copiar as linhas que mantemos para tabelas auxiliares, percorrendo da folha até a raiz.
  3. Rodar ANALYZE em cada uma das tabelas auxiliares criadas.
  4. Truncar as tabelas originais em um único comando.
  5. Inserir de volta as linhas mantidas, agora da raiz até a folha.

Mas, antes de entrar nisso, é importante mencionar que, desde o primeiro post, decidimos focar em performance e usar o ORM o mínimo possível, então o foco da estratégia aqui é usar mais SQL puro e usar os models do Django como fonte de metadados. Dito isso, a estratégia consiste no seguinte: em vez de iterar sobre as linhas que queremos remover e deletar cada uma (o que dispararia os deletes em cascata), vamos na direção oposta da árvore, da folha até a raiz, copiando os dados para uma tabela auxiliar, basicamente seguindo este exemplo:

-- blog_comment é a folha
CREATE TEMP TABLE _keep__blog_comment AS
SELECT * FROM blog_comment
WHERE id % 10 = 0;              -- uma regra de exemplo para o recorte, neste caso 10% das linhas

Aqui estamos usando a mesma cascata fictícia Blog → Post → Comment do primeiro post. Mas escrever esses nomes de tabelas e colunas à mão para cada tabela da árvore é exatamente o tipo de coisa propensa a erros e, para evitar isso, usamos os models do Django, não para rodar a query, mas só para nos dar os metadados de que ela precisa:

from django.db import connection

def copy_to_keep_table(model, where):
    table = model._meta.db_table                     # "blog_comment"
    with connection.cursor() as cursor:
        cursor.execute(f"DROP TABLE IF EXISTS _keep__{table}")
        cursor.execute(f"CREATE TEMP TABLE _keep__{table} AS SELECT * FROM {table} WHERE {where}")
        cursor.execute(f"ANALYZE _keep__{table}")

def copy_parent_to_keep_table(child, field_name):
    field = child._meta.get_field(field_name)        # Comment.post
    parent = field.related_model                     # o model Post
    # field.column é a coluna da chave estrangeira ("post_id"), então nunca a escrevemos à mão
    copy_to_keep_table(
        parent,
        f"{parent._meta.pk.column} IN "
        f"(SELECT DISTINCT {field.column} FROM _keep__{child._meta.db_table})",
    )

copy_to_keep_table(Comment, "id % 10 = 0")   # a folha, onde está o volume
copy_parent_to_keep_table(Comment, "post")   # _keep__blog_post
copy_parent_to_keep_table(Post, "blog")      # _keep__blog_blog

Assim, os nomes das tabelas, as chaves primárias e as colunas de chave estrangeira vêm todos de Model._meta e, subindo na árvore, blog_post mantém só os posts para os quais os comentários copiados apontam, e blog_blog mantém só os blogs desses posts, de forma que nenhuma chave estrangeira fica apontando para o nada depois da reinserção.

Além disso, você pode estar se perguntando: por que não usar simplesmente CREATE TABLE ... AS SELECT (CTAS) e terminar o trabalho ali, só renomeando a tabela auxiliar para o lugar da original? A cópia acima já é um CTAS e, embora esse comando seja muito performático, a tabela que ele cria tem só os nomes das colunas e os tipos da original; todo o resto se perde, como as chaves primárias, as chaves estrangeiras, as constraints de unique e check, o NOT NULL, os valores default das colunas, os índices e as sequences que geram os ids. Então, se substituirmos a tabela original por essa, vai parecer certo, mas vai quebrar na primeira escrita real nessa nova tabela. Por isso, mantemos as tabelas originais e substituímos só o conteúdo delas, exatamente o que o truncate e a reinserção da próxima seção fazem.

Os detalhes do mecanismo

Rodar o SQL acima não é a única coisa necessária para concluir essa tarefa; também precisamos cuidar das ações antes e depois dessa query. Então, antes de criar as tabelas auxiliares, devemos chamar SET CONSTRAINTS ALL IMMEDIATE, que faz com que as constraints sejam verificadas a cada comando, em vez de serem adiadas para o COMMIT. Isso é necessário porque o Django cria toda chave estrangeira no Postgres como DEFERRABLE INITIALLY DEFERRED, então, por padrão, uma chave estrangeira quebrada só falha no COMMIT, longe do comando que a causou. Com isso, pegamos o comando exato que causa a falha, o que facilita encontrar o culpado.

Depois de copiar os dados para as tabelas auxiliares, é importante rodar ANALYZE (ANALYZE _keep__blog_comment;) na tabela recém-criada, principalmente para atualizar as estatísticas do banco (número de linhas, número de páginas da tabela), já que são elas que o planejador de consultas (query planner) usa para escolher o melhor caminho para buscar os dados, evitando o uso errado de nested loops ou joins ineficientes.

E então um único TRUNCATE TABLE blog_blog, blog_post, blog_comment, em um só comando e sem CASCADE, e isso é de propósito. O Postgres se recusa a truncar uma tabela que algo fora do comando referencia por chave estrangeira, e essa recusa funciona como uma rede de segurança de graça: se alguém adicionar um novo model apontando para essas tabelas e esquecermos de incluí-lo, a execução falha em segundos, em vez de esvaziar silenciosamente uma tabela que queríamos manter.

Por último, mas não menos importante, a etapa de reinserção:

-- aqui a ordem é a inversa, da raiz para a folha, para que toda chave estrangeira encontre o pai já inserido
INSERT INTO blog_blog SELECT * FROM _keep__blog_blog;
INSERT INTO blog_post SELECT * FROM _keep__blog_post;
INSERT INTO blog_comment SELECT * FROM _keep__blog_comment;

que copia de volta todos os dados selecionados anteriormente das tabelas keep para as tabelas originais correspondentes.

Também vale dizer que nada disso roda contra produção: o pipeline restaura um snapshot de produção em um banco temporário e faz toda a transformação lá e, como todos os passos estão dentro da mesma transação, garantimos que não deixamos para trás um banco truncado pela metade.

Conclusão

Depois de algumas dificuldades com a ideia original, essa abordagem produziu resultados muito melhores, mas não sem custo: ela exige muito mais cuidado de quem desenvolve e testes melhores para garantir que todos os cenários estão cobertos, já que deixamos para trás os benefícios do ORM. Também é necessário seguir todas as relações por completo, para garantir que nenhuma tabela importante seja esquecida e truncada por engano. A performance é superior: no cenário do final do primeiro post, o pipeline foi interrompido no limite de 2 horas enquanto deletava em lotes, e nem chegou à parte final, enquanto o novo termina o recorte inteiro em cerca de 1 hora e 45 minutos. Então, para esse tipo de tarefa, essa provavelmente é a melhor escolha.

Carregando publicação patrocinada...
1

1h45 contra um limite de 2 horas ainda deixa pouca folga, e eu olharia primeiro pra reinserção, não pra cópia. Com SET CONSTRAINTS ALL IMMEDIATE, cada linha que volta pra blog_comment dispara o trigger de FK e faz um lookup em blog_post, e cada índice da tabela é atualizado linha a linha durante o INSERT. Como o banco é um snapshot restaurado e descartável, dá pra testar duas coisas. Dropar os índices que não são PK antes da reinserção e recriar no fim, porque CREATE INDEX em cima da tabela cheia sai bem mais barato que manter índice durante a carga. E rodar o INSERT com session_replication_role = replica, que desliga os triggers de FK (precisa de superuser, mas no banco temporário isso não é problema). Esse segundo tira a rede de segurança que vocês queriam, então eu passaria a checagem pro final, com um SELECT de órfãos por FK gerado pelo mesmo _meta que já monta as tabelas keep. Vocês chegaram a medir quanto desse 1h45 é cópia e quanto é volta?

1

Tem uma armadilha no DROP TABLE IF EXISTS do copy_to_keep_table quando um pai tem mais de um filho no recorte. Se Post e Comment tiverem FK pra auth_user, a segunda chamada recria o _keep__auth_user só com os autores do segundo filho, e a reinserção do outro filho cai em 23503. O keep de cada pai tem que ser montado de uma vez só, com UNION das FKs de todos os filhos (o _meta.get_fields() do pai lista as relações reversas).

E tem a FK pra própria tabela, tipo Comment.parent: o id % 10 = 0 guarda resposta sem o comentário pai. Dá pra puxar os ancestrais antes de subir a árvore:

WITH RECURSIVE anc(id) AS (
  SELECT parent_id FROM _keep__blog_comment WHERE parent_id IS NOT NULL
  UNION
  SELECT c.parent_id FROM blog_comment c JOIN anc ON c.id = anc.id
  WHERE c.parent_id IS NOT NULL
)
INSERT INTO _keep__blog_comment
SELECT * FROM blog_comment
WHERE id IN (SELECT id FROM anc)
  AND id NOT IN (SELECT id FROM _keep__blog_comment);

Como a checagem IMMEDIATE roda no fim do comando, a ordem das linhas no INSERT de volta não importa.