Advanced Schema PatternsLesson 4.3

Soft deletes: how to keep deleted records in the database

soft delete pattern, deleted_at timestamp, is_deleted flag, filtered views, partial index on deleted_at, hard delete vs soft delete, audit implications

Why Soft Deletes

A soft delete marks a row as deleted without removing it. The record stays in the database for audit trails, undo operations, analytics, and compliance. Only the application's query logic changes to filter out deleted rows.

Implementation

ALTER TABLE orders
  ADD COLUMN deleted_at TIMESTAMP NULL DEFAULT NULL;

-- Soft delete
UPDATE orders SET deleted_at = CURRENT_TIMESTAMP WHERE order_id = 101;

-- Standard query (exclude deleted)
SELECT * FROM orders WHERE deleted_at IS NULL;

-- Restore
UPDATE orders SET deleted_at = NULL WHERE order_id = 101;

-- View all including deleted (admin panel)
SELECT * FROM orders;

Partial Index for Performance

-- Index only active (non-deleted) rows — keeps index small
CREATE INDEX idx_orders_active
  ON orders(customer_id)
  WHERE deleted_at IS NULL;

Tradeoffs

UNIQUE constraints now need to account for deleted rows — a re-registered email may conflict with a soft-deleted account.
Tables grow indefinitely. Archive old soft-deleted rows to a separate archive table on a schedule.
Joins with soft-deletable tables must always include the WHERE deleted_at IS NULL filter or risk including ghost rows.