You own a system with several very large tables, an undo requirement from product, and a retention policy from legal. How would you decide between soft delete, hard delete with an archive table, and time-partitioned retention — and what makes each choice go wrong at scale?

## Do not answer "soft or hard" — answer "which requirement" "Delete" is four different requirements wearing one word, and choosing a mechanism before separating them is why systems end up with a `deleted_at` on every table and a purge job nobody trusts. 1. **Reversibility.** A human pressed a button and may regret it. Needs a window in which the data is recoverable by a normal operation, not by a DBA with a backup. 2. **Referential integrity of history.** An invoice from 2023 must still name its customer. Needs the referenced row (or a copy of the fields it needs) to survive. 3. **Retention and erasure.** Storage costs money and law limits how long you may keep things. Needs data to actually leave, on a schedule, provably. 4. **Auditability.** "Who removed this and when?" Needs a change record — which is a history/audit concern with its own design, not something a single flag column provides. Each requirement maps to different machinery, and a table usually has only one or two of them. Applying all four everywhere is how the schema gets expensive. ## The three mechanisms and their failure modes **Soft delete (flag in the live table).** Cheapest to implement, and genuinely right when "deleted" is a user-visible, reversible state on a modest-sized entity — a project, a saved view, a contact. It fails when volume grows: the table and every index carry rows no query wants, plan estimates drift, unique constraints must be reshaped into partial indexes, cascades and restores become hand-written transaction logic that must be tested, and one forgotten predicate is a silent correctness bug. The tell that you chose wrong: the dead fraction climbs past roughly half and nobody has a purge running. **Hard delete plus archive table.** The row moves to `orders_archive` (often with a `JSONB`/`JSON` payload rather than a mirrored schema, so it does not need migrating in lockstep) in the same transaction as the delete. The live table stays small, its constraints and indexes go back to meaning exactly what they say, and queries need no extra predicate. Restore is an explicit, auditable operation. Costs: cross-state queries need a `UNION` or a separate read path; the archive schema drifts from the live one unless you use a payload column; and the write path is heavier at delete time. This is the most under-used option and usually the right one for large child tables. **Time-partitioned retention.** Partition by created/occurred time and drop whole partitions when they age out. Deletion becomes a metadata operation: instant, no dead rows, no bloat, no long transaction, no replication storm. It is the only approach that stays cheap at billions of rows. Its constraint is that the retention key must be time, and per-row erasure (one specific user) still needs a different mechanism — usually anonymization in place or crypto-shredding, because you cannot drop a partition to satisfy one subject. ## The scale arithmetic worth saying out loud - **Growth.** Soft delete makes storage a function of all data ever created, not of live data. Estimate the dead fraction at 12 and 36 months before choosing it. - **Write amplification.** A soft delete is an update; in MVCC engines that creates a new row version and touches every index containing the updated column — sometimes more expensive than the delete would have been. - **Purge cost.** Mass deletes create dead versions, bloat indexes, and generate write-ahead log and replication lag. Partition drops do none of that. If you plan to purge, design the partitioning first. - **Query cost.** A universal predicate is fine when supported by partial indexes and terrible when it is not, because it turns index scans into filtered scans over mostly-dead data. ## A workable default policy - Small, user-facing aggregates: **soft delete with a 30-day window**, cascaded within the aggregate, plus a purge job that hard-deletes after the window. Undo is satisfied and growth is bounded. - Large child/detail tables: **hard delete, archive if anyone might ask**, cascaded by real foreign keys so the engine does the work. - Append-only event/log/telemetry tables: **partition by time, drop partitions** on the retention schedule; never soft delete these. - Anything with personal data: an **erasure path** independent of the delete mechanism — hard delete or irreversible anonymization, applied to every copy. - Anything needing "who changed what": a **history table or change capture**, decided separately from deletion. ## How to talk about it The strong answer is not a preference; it is a decision procedure. Name the requirements, name the mechanism each one implies, put a number on growth and purge cost, and state what would make you revisit the choice — dead fraction crossing a threshold, a purge exceeding its window, or a new regulatory obligation. Also be willing to say that a mechanism was right when the table was small and is wrong now; migrating a table from soft delete to archive-plus-partitions is ordinary work, and treating the original choice as permanent is the actual mistake.