Soft delete no PostgreSQL: quando usar, como implementar e os erros que vão te assombrar

O que é soft delete e por que as pessoas escolhem isso

Hard delete é simples: DELETE FROM orders WHERE id = $1. A linha some. Não tem volta.

Soft delete mantém a linha no banco e marca ela como deletada:

ALTER TABLE orders ADD COLUMN deleted_at TIMESTAMPTZ;
 
-- "deletar"
UPDATE orders SET deleted_at = NOW() WHERE id = $1;
 
-- buscar apenas ativos
SELECT * FROM orders WHERE deleted_at IS NULL;

Os motivos mais comuns para preferir soft delete:

• Auditoria: regulamentação exige histórico de todas as transações
• Recuperação de dados: clientes "deletam" e se arrependem
• Integridade referencial: outras tabelas referenciam o registro e um hard delete quebraria a FK ou exigiria CASCADE com efeitos colaterais
• Event sourcing lite: você quer saber quando algo foi removido sem manter um log separado

Mas a implementação tem armadilhas que aparecem só em produção.

O problema dos índices

O índice mais óbvio que qualquer dev vai criar:

CREATE INDEX idx_orders_user_id ON orders(user_id);

Esse índice serve para a query WHERE user_id = $1, mas ela também precisa filtrar deleted_at IS NULL. O banco vai usar o índice para encontrar os registros do usuário, depois vai filtrar os deletados na memória. Se 80% dos registros estiverem deletados, você está varrendo muito lixo.

A solução é incluir deleted_at no índice:

CREATE INDEX idx_orders_user_id_active ON orders(user_id)
  WHERE deleted_at IS NULL;

Isso é um partial index: só indexa as linhas onde deleted_at IS NULL. O índice fica menor, mais rápido, e a query WHERE user_id = $1 AND deleted_at IS NULL vai usar ele diretamente.

Para ORDER BY + paginação:

CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC)
  WHERE deleted_at IS NULL;

Unique constraints que quebram

Você tem uma tabela de usuários com email único:

CREATE UNIQUE INDEX idx_users_email ON users(email);

Usuário deletou a conta. Depois quer criar uma nova com o mesmo email. O soft delete impede porque o índice único ainda enxerga o email.

Solução com partial index:

DROP INDEX idx_users_email;
CREATE UNIQUE INDEX idx_users_email_active ON users(email)
  WHERE deleted_at IS NULL;

Agora emails de registros deletados não participam do unique constraint.

Mas atenção: se você tiver o registro deletado e criar um novo, o JOIN entre as duas tabelas vai trazer os dois. Isso nos leva ao próximo problema.

JOINs que vazam dados deletados

SELECT u.name, o.total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.id = $1
  AND u.deleted_at IS NULL;

Você filtrou usuários deletados, mas não filtrou ordens deletadas. Se o usuário tiver ordens deletadas, elas vão aparecer no resultado.

Em queries simples isso é óbvio. Em queries com 5 JOINs, uma view que esqueceu o filtro, ou um ORM que gerou o SQL automaticamente, isso vira vazamento de dados silencioso.

A mitigação mais comum é usar views:

CREATE VIEW active_orders AS
  SELECT * FROM orders WHERE deleted_at IS NULL;
 
CREATE VIEW active_users AS
  SELECT * FROM users WHERE deleted_at IS NULL;

E sempre fazer queries nessas views em vez das tabelas base. O problema é que views não são atualizáveis por padrão (sem triggers), e alguns ORMs não trabalham bem com elas.

Outra opção é Row Level Security (RLS):

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY orders_active ON orders
  USING (deleted_at IS NULL);

Com RLS, qualquer query na tabela automaticamente filtra deletados — para os roles que você configurar. É transparente para o ORM e para as queries. Mas adiciona complexidade na gestão de roles e em operações administrativas que precisam ver os deletados.

O bloat da tabela

Soft delete acumula linhas mortas na tabela. Com o tempo, você tem uma tabela com 10 milhões de linhas onde 8 milhões estão deletadas. Isso tem efeitos concretos:

• Table scans ficam mais lentos (mais páginas para varrer)
• Backups ficam maiores
• VACUUM demora mais
• Índices ficam mais fragmentados

Para tabelas de alto volume, considere purgar periodicamente os registros com soft delete antigo:

-- Mover para tabela de arquivo antes de deletar
INSERT INTO orders_archive
  SELECT * FROM orders
  WHERE deleted_at < NOW() - INTERVAL '1 year';
 
DELETE FROM orders
  WHERE deleted_at < NOW() - INTERVAL '1 year';

Isso pode ser um job agendado (pg_cron, cron externo, ou scheduled job na aplicação).

Soft delete com TypeORM

import { Entity, Column, DeleteDateColumn } from 'typeorm';
 
@Entity()
export class Order {
  @Column()
  userId: string;
 
  @DeleteDateColumn()
  deletedAt: Date | null;
}

O @DeleteDateColumn faz o TypeORM usar soft delete automaticamente quando você chama repository.softDelete(id) ou repository.softRemove(entity). As queries geradas automaticamente incluem WHERE deleted_at IS NULL.

Para buscar incluindo deletados:

const order = await repository.findOne({
  where: { id },
  withDeleted: true,
});

Para restaurar:

await repository.restore(id); // seta deleted_at = NULL

Soft delete com Prisma

O Prisma não tem suporte nativo a soft delete, mas tem um mecanismo de middleware/extension que resolve:

const prisma = new PrismaClient().$extends({
  query: {
    order: {
      async findMany({ args, query }) {
        args.where = { ...args.where, deletedAt: null };
        return query(args);
      },
      async findFirst({ args, query }) {
        args.where = { ...args.where, deletedAt: null };
        return query(args);
      },
      async delete({ args, query: _ }) {
        return prisma.order.update({
          where: args.where,
          data: { deletedAt: new Date() },
        });
      },
    },
  },
});

Isso intercepta as queries automaticamente. O ponto fraco é que você precisa manter isso para cada model, e queries que usam prisma.$queryRaw passam por fora.

Quando hard delete é a escolha certa

Soft delete não é sempre a resposta. Use hard delete quando:

• LGPD/GDPR: apagamento de dados pessoais a pedido do usuário exige deleção real. Soft delete não satisfaz o direito ao esquecimento — você precisa de um processo de purge mesmo assim.
• Tabelas de log/evento: eventos são imutáveis por natureza. Se precisar "cancelar" um evento, insira um novo evento de cancelamento — não delete o original.
• Performance crítica: se a tabela for consultada em alta frequência e o volume de deletados for grande, o custo dos partial indexes e do bloat pode ser proibitivo.
• Simplicidade: se você não tem um requisito real de auditoria ou recuperação, soft delete só adiciona complexidade.

A regra prática: soft delete para entidades de negócio com histórico importante (usuários, pedidos, contratos). Hard delete para dados operacionais descartáveis (sessões, cache, logs temporários).