What is Soft Delete & Tombstones?
Marking rows deleted with a timestamp flag instead of removing them, preserving history and undo. BYOB keeps a soft-delete grace window on projects before hard purge, so accidents stay recoverable.
Example
BYOB’s project soft-delete flow stamps deleted_at instead of dropping rows; recovery jobs restore within the grace window while purge jobs later hard-delete expired tombstones.
What people get wrong
Soft-deleting without filtering or purging. Every query must exclude tombstones — ideally via partial indexes — or dead rows silently pollute results and growth.
Frequently asked questions
When should rows hard-delete instead?
When retention expires, privacy law requires erasure, or storage costs bite. Soft delete is a grace window with a purge job, not permanent limbo.
How do queries ignore tombstones?
Filter deleted_at IS NULL on every read, or hide tombstones behind views and partial indexes so application code rarely thinks about them.
Related terms
Retention & Purge Policy
A documented policy deleting or archiving rows after their useful life, enforced by scheduled jobs. Retention bounds table growth, honors privacy promises, and keeps backups and queries fast.
Audit Trail
An append-only log recording who changed what and when, written by triggers or application events. Audits answer incident questions no snapshot can: intent, actor, and exact sequence of changes.
Partial Index
An index covering only rows matching a predicate, such as live subscriptions where status is active. Smaller, faster, and cheaper to maintain than indexing rows queries never touch.
B-Tree Index
The default balanced-tree structure making equality and range lookups fast without scanning every row. Add one where queries filter, join, or sort — then confirm with EXPLAIN before assuming victory.
Composite Index
One index spanning several columns for queries that always filter them together, ordered most-selective first. A three-column composite beats three single-column indexes on the same query shape.
Unique Constraint vs Index
A uniqueness guarantee enforced by the database versus a lookup accelerator with no such promise. Postgres implements unique constraints with an index, but only the constraint rejects duplicates.