Soft deletes versus tabelas de arquivo

O que é

Soft delete: uma coluna deleted_at timestamptz (ou is_deleted boolean); excluir significa preenchê-la, e toda leitura passa a incluir WHERE deleted_at IS NULL. Arquivo: a linha é removida da tabela ativa e inserida numa tabela de arquivo com as mesmas colunas, mais archived_at, archived_by e um motivo, tudo em uma única transação (um CTE com modificação de dados, em que DELETE ... RETURNING alimenta um INSERT, faz as duas coisas em um único comando). Um terceiro desenho, uma tabela de histórico preenchida por trigger a cada alteração, registra a exclusão como mais um evento entre as atualizações.

Por que importa

A exclusão é um evento de negócio ("conta encerrada", "rascunho descartado"), e cada um dos dois desenhos responde a perguntas diferentes depois. Soft deletes tornam a restauração trivial e mantêm as chaves estrangeiras intactas, mas toda consulta, restrição de unicidade, contagem e join precisa lembrar do filtro; um único predicado esquecido mostra dados excluídos aos usuários. Tabelas de arquivo mantêm o esquema ativo fiel à realidade, mas transformam a restauração numa cópia de volta e complicam as referências vindas de outras tabelas.

Como aplicar

• Use soft delete quando as linhas excluídas são lidas com frequência (desfazer em poucos dias, telas de "mostrar excluídos", linhas ainda referenciadas em outros lugares). Exponha as linhas ativas por meio de uma view (CREATE VIEW customers AS SELECT ... FROM customers_all WHERE deleted_at IS NULL) ou de uma política de segurança em nível de linha, para que o filtro exista em um só lugar.
• Transforme as restrições de unicidade em índices únicos parciais: a documentação do PostgreSQL descreve que um índice único com predicado WHERE garante unicidade apenas entre as linhas que satisfazem o predicado, de modo que CREATE UNIQUE INDEX ... ON users (email) WHERE deleted_at IS NULL libera um endereço quando a conta é excluída.
• Use arquivo quando as linhas excluídas raramente são lidas (retenção, investigação ocasional). Defina o que acontece com as linhas filhas: arquive-as na mesma transação ou proíba a exclusão enquanto houver filhas.
• Defina uma rotina de purga nos dois desenhos: linhas com soft delete que passaram do período de retenção são excluídas fisicamente; tabelas de arquivo são particionadas ou podadas por data.
• Um marcador não é remoção: onde os registros realmente precisam desaparecer, planeje a exclusão física ou a anonimização.

Armadilhas

Linhas com soft delete continuam contando nas chaves estrangeiras e nos índices únicos não parciais. Filtros globais no nível do ORM são contornados por SQL puro e por ferramentas de relatório. Os índices de uma tabela com soft delete crescem com o histórico morto, a menos que sejam parciais. Tabelas de arquivo se afastam do esquema ativo quando as migrações se esquecem delas; um teste que insere uma linha ativa na tabela de arquivo detecta isso.