Part 47: Soft Deletes vs Hard Deletes: Design Patterns

In modern software, accidentally deleting data can cause catastrophic customer issues.

1. Hard Delete vs Soft Delete

• Hard Delete: Physically removes row via DELETE FROM. Gone forever.
• Soft Delete: Retains row, marking it with deleted_at TIMESTAMPTZ or is_deleted BOOLEAN.

-- Soft delete:
UPDATE users SET deleted_at = CURRENT_TIMESTAMP WHERE id = 42;

-- Query active users:
SELECT * FROM users WHERE deleted_at IS NULL;

Key Takeaways

• Soft deletes preserve audit trails and allow instant undo/recovery.
• Index deleted_at or create a partial index on active records for high performance.

In Part 48, we explore The Upsert Pattern: ON DUPLICATE KEY UPDATE in MySQL!