Borrado lógico frente a tablas de archivo

Qué es

Borrado lógico (soft delete): una columna deleted_at timestamptz (o is_deleted boolean); al borrar se rellena, y cada lectura añade WHERE deleted_at IS NULL. Archivo: la fila se elimina de la tabla activa y se inserta en una tabla de archivo con las mismas columnas más archived_at, archived_by y un motivo, todo en una misma transacción (una CTE que modifica datos, con DELETE ... RETURNING alimentando un INSERT, hace ambas cosas en una sola sentencia). Un tercer diseño, una tabla de historial rellenada por un trigger en cada cambio, registra el borrado como un evento más entre las actualizaciones.

Por qué importa

Borrar es un evento de negocio ("cuenta cerrada", "borrador descartado"), y cada diseño responde después a preguntas distintas. El borrado lógico hace trivial deshacer el borrado y mantiene intactas las claves foráneas, pero cada consulta, restricción de unicidad, recuento y join debe acordarse del filtro; un solo predicado olvidado muestra datos borrados a los usuarios. Las tablas de archivo mantienen el esquema activo fiel a la realidad, pero convierten la restauración en una copia de vuelta y complican las referencias desde otras tablas.

Cómo aplicarlo

• Usa borrado lógico cuando las filas borradas se leen con frecuencia (deshacer en cuestión de días, vistas de "mostrar eliminados", filas aún referenciadas desde otras tablas). Expón las filas vigentes mediante una vista (CREATE VIEW customers AS SELECT ... FROM customers_all WHERE deleted_at IS NULL) o una política de seguridad a nivel de fila, de modo que el filtro exista en un único lugar.
• Convierte las restricciones de unicidad en índices únicos parciales: la documentación de PostgreSQL describe que un índice único con un predicado WHERE garantiza la unicidad solo entre las filas que cumplen el predicado, de modo que CREATE UNIQUE INDEX ... ON users (email) WHERE deleted_at IS NULL libera una dirección cuando se borra la cuenta.
• Usa archivo cuando las filas borradas se leen raramente (retención, investigaciones ocasionales). Decide qué ocurre con las filas hijas: archívalas en la misma transacción o prohíbe el borrado mientras existan hijas.
• Define una purga en ambos diseños: las filas con borrado lógico que superen el periodo de retención se eliminan físicamente; las tablas de archivo se particionan o se depuran por fecha.
• Una marca no es una eliminación: cuando los registros deban desaparecer de verdad, planifica el borrado físico o la anonimización.

Trampas

Las filas con borrado lógico siguen contando para las claves foráneas y los índices únicos no parciales. Los filtros globales a nivel de ORM se eluden con SQL directo y con herramientas de informes. Los índices de una tabla con borrado lógico crecen con historial muerto, salvo que sean parciales. Las tablas de archivo se desalinean del esquema activo cuando las migraciones se olvidan de ellas; una prueba que inserte una fila activa en la tabla de archivo lo detecta.