The Soft Delete Trap: Why Your is_deleted Flag Is Quietly Destroying Your Database

Trade-off #1: Unique Constraints Become a Nightmare

Here’s the scenario that breaks teams. You have a unique business requirement: email addresses must be unique.

CREATE TABLE users (
    id BIGINT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
    CONSTRAINT uq_users_email UNIQUE (email)
);

User alex@company.com deletes their account. Three days later, they try to sign up again with the same email. The database rejects the insert:

ERROR: duplicate key value violates unique constraint "uq_users_email"

Now what? Teams resort to fragile hacks:
– Appending timestamps to deleted emails: alex@company.com_deleted_1729001823 (destroys audit history and breaks downstream systems).
– Creating compound unique constraints on (email, is_deleted) (fails the second time the same email deletes an account, because (alex@company.com, true) can only exist once).
– Creating nullable columns like deleted_at and relying on vendor-specific NULL handling (in PostgreSQL, multiple NULLs are treated as distinct, in SQL Server, they are not).

The proper solution is a partial unique index:

CREATE UNIQUE INDEX uq_users_email_active 
ON users (email) 
WHERE deleted_at IS NULL;

But this only works in PostgreSQL and SQL Server, not MySQL. And it’s yet another database-specific detail your team has to remember.

Trade-off #2: Every Query Becomes a Landmine

Every single query must filter deleted rows. Miss one, and deleted data shows up in a report, an API response, or worse, a compliance audit.

This might seem manageable until you realize the scale of the problem. The team behind one open-source patient management system tracked this cost across their codebase. They found 69 production SQL statements naming their soft-delete flag across just eight tables. That’s 69 places where a developer could forget the filter and silently leak deleted records into results.

A common mitigation is database views that filter out deleted rows:

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

But this introduces its own complexity. Now you need to manage views for every table, keep them in sync with schema changes, and ensure developers actually query the views instead of the underlying tables. It’s a band-aid, not a solution.

Trade-off #3: Foreign Keys Become a Logic Puzzle

What happens to orders that belong to a soft-deleted customer? The foreign key still exists. The customer row is still there. Your JOIN still works. But semantically, you’re now dealing with an “invisible” customer whose data appears in reports, analytics, and API responses if any query misses the filter.

One team hit this exact wall: a soft-deleted row kept its foreign keys, which stopped a real delete with nothing on screen to explain it. Re-importing an Excel month failed after one of the fund payments was deleted because the ghost row was still referenced. The user saw an opaque error with no way to resolve it.

The opposite problem emerges with GDPR and similar regulations. A user exercises their “right to be forgotten”, and you need to actually erase their data. But their purchases, payment history, and activity logs are all tied to their account via foreign keys. What do you do?

One architecture decision I found particularly instructive involved financial records that needed to be retained for seven years while the user’s account was deleted. The solution: a tombstone account row with a deterministic UUID derived from sha256(player_id || pepper), with all PII columns blanked. Purchaser records get re-pointed to the tombstone before the real account is deleted. The FKs stay intact, but the personally identifiable data is gone.

The critical insight here was changing on delete cascade to on delete restrict on financial tables, so a bug in the deletion job fails loudly instead of silently destroying financial records.

Trade-off #4: Compliance Requirements Can Force Real Deletion

Soft delete gives you recoverability, but privacy regulations like GDPR, CCPA, and Australia’s APP 1.7 may require actual data erasure. You can’t just flip a flag and call it compliance.

The problem is that a system built entirely on soft delete becomes a compliance liability. One GitHub issue I reviewed outlined the tension beautifully: the hard-delete flow and the schema contradicted each other. Every financial table declared player_id uuid not null references accounts on delete cascade. But the hard-delete step deleted the accounts row, which cascaded to those tables. Step 3 then said purchaser rows should be “retained with player_id replaced by a one-way tombstone id.” After step 2, they were already gone.

The spec had been written without anyone thinking through the interaction between the delete flow and the schema.

A legal hold further complicates things. A “retain-until” date per version means the data can’t be deleted, expired, or pruned before that date, for example, invoices that must be kept seven years. A legal hold blocks deletion until the hold is lifted, whatever the dates say.

Both answer the same question as soft delete: may this version be deleted yet? So they belong in one design, with one check shared by delete, expiry, prune, container delete, and the purge job. As one maintainer put it: “The design proposal should cover all three before anything is built.”

The pragmatic middle ground is combining soft delete with a scheduled hard-delete job that permanently removes old soft-deleted rows in batches during low-traffic periods:

-- Delete 5,000 rows at a time
DELETE FROM logs
WHERE id IN (
  SELECT id
  FROM logs
  WHERE created_at < now() - interval '90 days'
  LIMIT 5000
);

This gives you the safety net of recoverability with a defined lifecycle that eventually cleans up dead data.

Trade-off #5: Restore Logic Opens Security Holes

Soft delete adds a trash page and a restore button. Do they get the same permission checks as the rest of your API? In my experience, this is where authorization gaps creep in.

The tenant filter typically lives in a default scope that also hides deleted rows. The trash page needs the deleted rows, so someone reaches for Document.unscoped (or whatever your ORM calls it). In Rails, unscoped drops every default scope. If the tenant filter lived in the same default_scope as deleted_at IS NULL, it’s gone too. The trash list now shows deleted documents from every tenant. Nobody notices because in development there’s only one tenant.

Restore is worse. It usually gets a role check like “editors can restore”, which says nothing about whether this editor could ever see this document. Per-record checks are awkward because the sharing or folder lookups often skip deleted rows, so the check fails and someone removes it to make restore work.

The security-conscious approach:
– Keep the tenant filter apart from the soft-delete filter, so dropping one can’t drop the other. If you use Postgres row-level security, the tenant policy shouldn’t mention deleted_at at all.
– Keep the owner, folder, and shares on the row when it’s soft-deleted, and run the same per-record check for viewing trash and for restore that you run for edit.
– Add the trash and restore endpoints to the test that walks every route as a user from another tenant and expects a 404.

Restore should be its own permission, rather than riding on delete. Deleting something is a reversible cleanup step, while restoring it can bring back data that a user should no longer see. The two actions carry different risk.

The Deeper Problem: Performance Degradation at the Storage Engine Level

Most discussions of soft delete focus on application-level concerns. But the real trap is what happens at the disk engine level.

A B-tree index stores values in sorted leaf pages. When you index a column alongside a soft-delete flag, the database must store an index leaf node for every single row in the table, regardless of whether the flag is true or false.

If 75% of your records are soft-deleted:
– 75% of your B-tree index pages contain dead pointers.
– Your index size grows from 4 GB to 16 GB.
– The index no longer fits inside your database instance’s RAM.

Every search for an active user’s order must traverse an index tree where four out of five pages contain data your query will immediately discard.

Worse, databases don’t fetch individual rows from disk. They read data in pages (typically 8KB in PostgreSQL, 16KB in MySQL InnoDB). If active rows and soft-deleted rows are physically co-mingled on disk:
– Page 4,102 contains 40 rows: 3 active, 37 deleted.
– Page 4,103 contains 40 rows: 1 active, 39 deleted.

To read 4 active rows, the database must pull 32KB of data into RAM. Your active working dataset can’t stay cached because dead rows are constantly crowding out live records. This induces continuous disk I/O thrashing.

And here’s the kicker: a boolean column has an absolute maximum cardinality of 2. When your ORM automatically appends AND is_deleted = false to every join and subquery, the query planner must estimate selectivity across compound predicates. Because statistical sampling on skewed tables can be imprecise, query planners (especially PostgreSQL and SQL Server) frequently miscalculate the cost. The planner estimates that an index scan will cost more than reading the entire table sequentially. It abandons your index, triggering a full table scan across millions of rows.

What Are Your Alternatives?

The naive boolean flag is broken. But simply switching to physical DELETE calls violates compliance, audit, and recovery requirements. The question becomes: what’s the right pattern for your use case?

The hardest part of their transition was untangling the flag from partial unique indexes, triggers, and queries. Their migration order became a multi-step program: delete flagged rows children-first, drop indexes and triggers that name the flag, then the column. As an operational rule: partial unique indexes become full ones, and every step needed a migration test with a flagged row that is still referenced to catch cascade violations.

The Verdict

Soft delete isn’t inherently wrong, it’s a tool with a specific job. The tragedy is that it’s deployed as a default without anyone thinking through the long-term costs. Like the trade-offs between simplicity and long-term maintainability in system design, the decision often comes down to YAGNI versus the reality that your system will outlive the team that built it.

Before you add that deleted_at column, answer these five questions:

1. What’s your unique constraint strategy? Partial indexes? Compound keys? Timestamp suffixes?
2. Who guarantees every query filters deleted rows? You need automated tests or view-layer enforcement.
3. What happens to related records? Foreign keys on soft-deleted rows create ghost data that breaks imports, exports, and user-facing messages.
4. Can you actually comply with privacy regulations? If not, you need a hybrid approach with a purge job.
5. Who can access the trash and restore endpoints? Authorization for deleted data needs the same per-record checks as your normal API, and a test that proves it.

Because here’s the uncomfortable truth: three years from now, your database will either be a well-tended garden or a bloat-ware attic. The choice isn’t whether to soft delete. It’s whether you’re prepared to handle the consequences if you do.