Databases & System Design•Part 2: Relational Databases (RDBMS / PostgreSQL / MySQL) (6 - 25)
Soft Deletes and Partial Indexes:
In Simple Words:
Setting deleted_at = NOW() instead of deleting rows causes index bloat.
Fix: Create a Partial Index that indexes only active non-deleted rows!
sql
-- Partial index: only indexes active records (small, fast, ignores deleted rows):
CREATE UNIQUE INDEX uq_active_user_email ON users (email)
WHERE deleted_at IS NULL;