What is 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.
Related terms
Backup & Point-in-Time Recovery
Scheduled full snapshots plus a write-ahead log enabling restore to any second within retention. Point-in-time recovery turns “we dropped the wrong table” from catastrophe into a support ticket.
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.
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.
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.
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.