Skip to content
C Codeloom
Backend

Soft Deletes vs Hard Deletes: A Practical Guide

Compare soft and hard deletes across recovery, compliance, indexing, and query complexity to pick the right deletion strategy.

·3 min read · By Codeloom
Intermediate 9 min read

What you'll learn

  • ✓Why deletes are harder than they look
  • ✓When soft deletes pay off
  • ✓Hidden costs of soft deletes
  • ✓Compliance and right-to-erasure
  • ✓A hybrid strategy that works in practice

Prerequisites

  • •Familiar with HTTP and databases

What and Why

Deleting data sounds simple until you realize how much of your product depends on history. Audit trails, undo, billing, analytics, and shared references all care about deleted rows. Choosing how you delete has long-term consequences.

Two main approaches: hard delete (DELETE FROM ...) and soft delete (UPDATE ... SET deleted_at = now()).

Mental Model

Hard delete removes the row. The data is gone unless backups recover it. Cheap, simple, complete.

Soft delete keeps the row but marks it as deleted. Every query has to remember to filter on deleted_at IS NULL or the user will see ghosts.

Architecture

Application
  |
  v
+---------------+
| active_users  |  <-- SELECT * FROM users WHERE deleted_at IS NULL
+---------------+
  |
  v
+---------------+
|   users       |   <-- physical table with all rows
+---------------+
Soft delete view layer

A common pattern: a database view exposes only active rows. Most code reads from the view; admin code reads the table.

For compliance, soft-deleted rows still need to be erased on a schedule. A background job hard-deletes anything older than the retention window.

Trade-offs

Soft deletes preserve referential integrity. Foreign keys still resolve, audit logs still make sense, and the undo button is free.

Soft deletes complicate every query. Forget the filter once and you leak deleted data. Indexes get larger because they include rows users cannot see. Uniqueness constraints break (a user can re-register with the same email after delete).

Hard deletes are clean but lose context. A canceled order disappears; revenue reports get harder. Foreign-keyed children fail to delete unless you cascade carefully.

GDPR-style erasure rights require true deletion of personal data within a deadline. Soft delete alone does not satisfy this; pair it with hard cleanup.

Practical Tips

Add a partial index on WHERE deleted_at IS NULL so queries on active rows stay fast even when the table has many tombstones.

Drop unique constraints from columns that users can re-use after delete (like email). Replace with a unique index that includes a non-deleted condition.

Choose a clear data model: a single deleted_at timestamp beats a boolean plus a date. Many ORMs (Django, ActiveRecord, Sequelize) have well-tested soft-delete extensions; use them rather than rolling your own.

Plan for purge jobs from day one. A “we will clean up later” comment becomes “we have eight years of dead rows” remarkably fast.

For high-write tables, consider hard deletes plus an append-only audit table. You get small live tables and a permanent record without paying both costs everywhere.

Wrap-up

Soft delete vs hard delete is rarely all-or-nothing. Soft delete for entities users care about and can undo. Hard delete for ephemeral or high-volume data. Pair with purge jobs to meet compliance. Decide early, document the choice, and apply it consistently across the schema. Future you will be grateful.

Related articles