Soft deletes sound simple: instead of deleting a record, you mark it as deleted.
UPDATE users SET deleted = true WHERE id = 123;
Done, right?
Not quite.
At small scale, this works fine. But as your data grows, soft deletes can quietly wreck query performance, bloat indexes, and complicate logic.
Let’s break down how to do it properly—without turning your database into a slow-moving legacy monster.
🧠 Why Soft Deletes Exist
We use soft deletes when:
- You need auditability
- You want undo/restore capability
- Data must be retained for compliance or business logic
Instead of deleting:
DELETE FROM orders WHERE id = 10;
You do:
UPDATE orders SET deleted_at = NOW() WHERE id = 10;
⚠️ The Hidden Problems
1. Query Performance Degrades
Every query now needs:
WHERE deleted_at IS NULL;
If you forget it? 👀 Congrats, you just exposed deleted data.
If you include it everywhere? Your indexes might not help anymore.
2. Indexes Become Less Effective
A typical index:
CREATE INDEX idx_users_email ON users(email);
Now your query:
SELECT * FROM users
WHERE email = 'a@b.com'
AND deleted_at IS NULL;
👉 That index is no longer optimal.
3. Table Bloat
Soft-deleted rows still sit there:
- Increasing table size
- Slowing scans
- Affecting cache efficiency
✅ The Right Way to Implement Soft Deletes
1. Use deleted_at Instead of Boolean
Avoid:
deleted = true
Use:
deleted_at TIMESTAMP NULL
Why?
- Stores when deletion happened
- Helps in cleanup jobs
- Enables auditing
2. Use Partial Indexes (Game Changer)
If you're using PostgreSQL:
CREATE INDEX idx_users_active_email
ON users(email)
WHERE deleted_at IS NULL;
Now this query is fast again:
SELECT * FROM users
WHERE email = 'a@b.com'
AND deleted_at IS NULL;
👉 Only active rows are indexed = smaller, faster index.
3. Default Scope / Global Filters (Use Carefully)
In ORMs like Spring Data / Hibernate:
@Where(clause = "deleted_at IS NULL")
This ensures deleted records are automatically excluded.
⚠️ But:
- Can confuse developers
- Harder to debug when data "disappears"
Use it, but document it clearly.
4. Archive Old Data
Soft delete ≠ keep forever.
Move old deleted data:
INSERT INTO users_archive
SELECT * FROM users
WHERE deleted_at < NOW() - INTERVAL '90 days';
DELETE FROM users
WHERE deleted_at < NOW() - INTERVAL '90 days';
👉 Keeps main table lean and fast.
5. Be Smart with Unique Constraints
Problem:
UNIQUE(email)
Soft delete a user → can't reuse email ❌
Fix with partial unique index:
CREATE UNIQUE INDEX uniq_active_email
ON users(email)
WHERE deleted_at IS NULL;
Now:
- Active users must be unique ✅
- Deleted users don’t block reuse ✅
6. Batch Your Deletes
Avoid:
UPDATE users SET deleted_at = NOW();
Instead:
- Process in batches (e.g., 1000 rows at a time)
- Prevent locks and performance spikes
7. Don’t Overuse Soft Deletes
Not everything needs it.
Avoid soft deletes for:
- High-frequency log tables
- Temporary/session data
- Event streams
Sometimes, hard delete + backup is cleaner.
🧪 Real-World Pattern (Recommended Setup)
deleted_at TIMESTAMP NULL- Partial indexes on active data
- Background job for cleanup/archival
- Explicit queries (not overly magical ORM behavior)
⚡ TL;DR
Soft deletes are not free.
If you:
- Add
deleted_at - Use partial indexes
- Archive old data
- Handle uniqueness correctly
👉 You get the benefits without killing performance.
If you don’t…
👉 You’re slowly building a performance bug that shows up at scale (usually at the worst possible time 😄).
💬 Final Thought
Soft deletes are like adding a feature flag to your data layer—powerful, but easy to misuse.
Design it right early, or you’ll end up debugging “why is this query suddenly slow?” six months later.
Top comments (0)