beginnerintermediateadvanced

System Design · Section 71

Data Lifecycle and Retention

Level: intermediate
Read: 12 min
Concepts: 2

Data has a temperature (hot, warm, cold) driven purely by how often it is accessed and how much latency a read can tolerate, and a retention length driven by business need and law — the two are independent axes, so moving data to cheap cold storage never answers the separate question of when it must be deleted. A legal hold is a third axis that overrides both: once data is relevant to litigation or an investigation it must be preserved exactly as-is, past its normal deletion date, until the hold is explicitly lifted, which means every automated deletion path has to check hold status on every run rather than relying on age alone. TTLs (per-record, enforced by the data store itself), object lifecycle rules (blob-storage policies that transition or delete by age), partition pruning (dropping a whole time partition instead of scanning and deleting rows one at a time) and archival pipelines (batch jobs that move aging data into a cheaper, queryable cold store) are the four mechanisms that carry a retention policy out automatically — and a schema designed around the retention window from the start (time-partitioned tables, TTL indexes) is what keeps that enforcement cheap as the data grows, versus bolting a cleanup job onto a table that was never shaped for it.

System Design overview

System Design for Beginners CoursefreeCodeCamp.org

What is true here

Hot/warm/cold is an access-frequency and cost decision; retention length is a separate business/legal decision — the two can be set independently for the same data.
A legal hold overrides both tier and retention for specifically named records until explicitly lifted — deletion jobs must check hold status on every run, not just record age.
TTLs (per-record), object lifecycle rules (blob storage), partition pruning (drop, not scan) and archival pipelines (batch to cold) are the four automated enforcement mechanisms.
Partition pruning turns a scan-and-delete over millions of rows into a near-instant metadata operation — but only if the table was partitioned by the same time window the retention policy uses.
TTL and lifecycle-rule expiry are best-effort, sweep-based, not instantaneous — code that assumes an expired record is physically gone the instant its TTL passes will be wrong some of the time.

What you will be able to do

Explain why storage tier and retention length are independent decisions, and why a legal hold overrides both
Design a retention job that checks legal-hold status before every deletion, not just record age
Choose between a TTL, an object lifecycle rule, partition pruning and an archival pipeline for a given retention need
Explain why time-partitioned tables make retention enforcement cheap, and why bolting deletion onto an unpartitioned table does not

Temperature, retention and legal holds

Why access-frequency tiering, how-long-to-keep-it policy and legal holds are three independent axes on the same data.

Hot vs warm vs cold data, and retention policies

coreintermediate

Not all data is accessed at the same rate for the same amount of time, and pricing it as if it were wastes money on one end and risks losing it on the other. "Hot" data is read or written constantly and needs to sit on fast, expensive storage — the last few days of orders, the active session table. "Warm" data is accessed occasionally — last quarter's reports, a user's older messages — and can live on cheaper storage with slightly higher latency. "Cold" data is rarely touched but still has to exist — seven-year-old tax records, an old audit log — and belongs on the cheapest, slowest tier, sometimes with a retrieval delay measured in hours. A retention policy is the separate decision of how long each category of data must be kept at all, driven by business need (a customer might dispute a charge from last year) and by law (many financial and healthcare regulations set a specific minimum retention period). A legal hold overrides both temperature and retention: when data becomes relevant to litigation or an investigation, it must be preserved exactly as it is, even past its normal deletion date, until the hold is lifted — deleting held data on schedule despite a hold is not a technical bug, it is a legal one.

Think of it as

Think of a filing system in an office: today's paperwork sits on the desk (hot — instant access, limited desk space), last year's files go in filing cabinets down the hall (warm — a short walk, more capacity), and anything older than that goes to an off-site archive warehouse (cold — a phone call and a wait, but nearly unlimited space at low cost). A retention policy is the office's shredding schedule taped to the wall — "tax records: 7 years, then shred." A legal hold is a manager walking over and saying "stop shredding anything related to the Smith account, no matter what the schedule says" — it suspends the normal schedule for a specific, named subset of files until someone explicitly lifts it.

text

# A retention policy, stated the way it actually
# needs to be enforced — per data category, not
# per table:
order records:        hot 30d -> warm 1y -> cold 7y -> delete
audit logs:            hot 7d -> cold 3y -> delete (never before 3y)
user account data:     hot while active -> delete within 30d of
                        account deletion request (subject to holds)
legal hold override:   any record tagged `hold: <case-id>`
                        is exempt from every rule above until the
                        hold is explicitly cleared

What we're doing: Read a retention policy table and predict what happens to one record over its lifetime.

order-retention-policy.txttext

Category: order records
Hot:      0-30 days     (fast storage, order-status API reads this)
Warm:     31 days-1 year (nightly reports read this)
Cold:     1-7 years      (only read for tax audits or disputes)
Delete:   after 7 years, UNLESS a legal hold is attached

Order #48213, placed 2026-01-15, currently under a
legal hold (case #LH-2231, opened 2026-06-01)

2: For its first 30 days the order sits in hot storage because the order-status API needs sub-second reads while a customer might still be checking on it.
3: After 30 days nothing reads it in real time anymore, so it moves to warm storage — cheaper, and a report job reading it once a night does not need millisecond latency.
7: The legal hold is what actually matters for this specific order: even once it ages into the cold tier and even after it crosses the 7-year mark, it must not be deleted while case #LH-2231 is open, because the hold overrides the retention schedule entirely for this record.

Why this works: The tier (hot/warm/cold) and the retention clock (7 years then delete) are two independent axes that most designs get right in isolation — the mistake that actually loses data or creates legal exposure is forgetting that a hold is a third, overriding axis that a scheduled deletion job must check before every delete, not something handled once at write time.

Running scheduled deletion against age alone, without checking hold status

Wrong

sql

-- Nightly cleanup job: delete anything past
-- its retention window, based on age only
DELETE FROM orders
WHERE created_at < NOW() - INTERVAL '7 years';

Better

sql

-- Check hold status before every delete, not
-- just age
DELETE FROM orders
WHERE created_at < NOW() - INTERVAL '7 years'
  AND id NOT IN (SELECT record_id FROM legal_holds
                 WHERE record_type = 'order'
                   AND status = 'active');

What you see: A record under active litigation is deleted by a routine nightly job because the job only ever checked age, and the deletion itself becomes evidence of spoliation in the case it was needed for.

Why: Age-based retention and legal holds are stored and reasoned about separately in most systems — the hold table gets added after the retention job already exists, so it is easy for the join back to the hold table to be left out of the one place (the actual delete statement) where it is safety-critical.

Three temperature tiers, one lifecycle

Hot

fast, expensive — accessed constantly

Warm

medium cost, occasional reads

Cold

cheapest, rare reads, minutes-hours retrieval

Legal hold

freezes deletion for named records regardless of tier

The three temperature tiers, by access pattern and typical cost

The three temperature tiers, by access pattern and typical cost
Tier	Access pattern	Typical latency	Relative cost
Hot	Read/written continuously (minutes to days old)	Milliseconds	Highest
Warm	Read occasionally (weeks to months old)	Tens to hundreds of ms	Medium
Cold	Rarely read, must still exist (months to years old)	Minutes to hours (some tiers)	Lowest

Remember: Hot/warm/cold is about access frequency and cost, not about whether data can be deleted. A retention policy is a separate, explicit answer to "how long must this exist," and a legal hold is a third axis that overrides both — deletion jobs must check hold status on every run, not just age.

Automating enforcement

The four mechanisms — TTLs, object lifecycle rules, partition pruning, archival pipelines — that carry out a retention policy without manual deletion.

TTLs, lifecycle rules and archival pipelines

coreintermediate

A retention policy is a decision; a TTL (time-to-live), an object lifecycle rule, partition pruning and an archival pipeline are the four mechanisms that actually carry it out automatically, without an engineer manually deleting or moving rows. A TTL is a per-record expiry attached at write time — many databases (Redis, DynamoDB, MongoDB) will delete a record on their own once its TTL passes, which is ideal for session data or caches where "expired" simply means "gone." An object lifecycle rule is the equivalent for blob storage — a policy attached to a bucket or prefix that says "move objects older than 30 days to a cheaper tier, delete after 7 years" and the storage system executes it in the background. Partition pruning is a database technique for tables partitioned by time (e.g. one partition per day or month): dropping an entire old partition is a near-instant metadata operation, versus a row-by-row DELETE that has to scan and remove millions of rows one at a time. An archival pipeline is the process — often a scheduled batch job — that reads data nearing the end of its hot/warm life, writes it out to cheaper long-term storage in a queryable format, and then removes it from the primary system, so the primary system's working set stays small even as total historical data grows without bound.

Think of it as

A TTL is a self-destructing note — it disappears on its own at a set time with no one having to remember to throw it away. A lifecycle rule is a standing instruction left with a moving company — "anything left in this room after 30 days, move it to the storage unit; after 7 years, junk it" — executed automatically without a person re-deciding each time. Partition pruning is the difference between throwing out an entire labeled box from an archive (instant) versus going through every item in the whole archive to find and remove the ones from a particular month (slow, and it disturbs everything else in the process). An archival pipeline is the conveyor belt that moves boxes from the busy front office (hot storage) to the warehouse (cold storage) on a schedule, so the front office never fills up.

sql

-- Time-partitioned table: dropping one partition removes
-- a whole month's rows in one metadata operation, not a
-- row-by-row scan
CREATE TABLE events (
  id BIGINT, occurred_at TIMESTAMPTZ, payload JSONB
) PARTITION BY RANGE (occurred_at);

CREATE TABLE events_2026_01 PARTITION OF events
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

-- Retention job: drop the whole partition once it ages out
DROP TABLE events_2026_01;

What we're doing: Compare deleting old rows by scanning a table versus dropping a time partition.

retention-two-ways.sqlsql

-- Approach A: row-by-row delete against a huge
-- unpartitioned table (50M rows, deleting ~2M old ones)
DELETE FROM events WHERE occurred_at < NOW() - INTERVAL '13 months';

-- Approach B: table is partitioned by month; retention
-- is "drop the oldest partition"
DROP TABLE events_2025_07;

3: This scans the table to find matching rows, deletes them (each a separate write-ahead-log entry), and leaves behind dead tuples for the vacuum/garbage collector to reclaim later — on a large table this can run for hours and compete with live traffic for I/O and locks.
7: This is a metadata-only operation: the partition is simply detached and the space reclaimed, in roughly constant time regardless of how many rows it held.

Why this works: The two approaches produce the same end state — old data gone — but one costs a few milliseconds and the other can degrade the whole table's performance for hours; the difference is entirely in whether the schema was designed around the retention policy up front (time-partitioned) or the policy was bolted on afterward against a table that was never shaped for it.

Adding a retention policy after the table is already large, without partitioning

Wrong

text

# Retention added late, as a cron job against
# an existing unpartitioned table:
0 3 * * * psql -c "DELETE FROM events WHERE
  occurred_at < now() - interval '13 months'"
# runs nightly, locks rows, competes with traffic

Better

text

# Retention designed in from the start: partition
# by the same time window the retention policy
# uses, so "enforce retention" is "drop a partition"
CREATE TABLE events (...) PARTITION BY RANGE
  (occurred_at);
# monthly job: DROP the oldest partition once it
# ages past the retention window

What you see: A nightly cleanup job that used to finish in seconds starts taking longer every month as the table grows, eventually running long enough to overlap with peak traffic and cause lock contention or replication lag.

Why: A row-by-row DELETE's cost scales with the number of matching rows and the size of the table it scans, both of which only grow over time if retention was never designed in — whereas a partition-drop's cost is independent of table size, because it never touches individual rows at all.

From hot table to cold archive, automatically

Record written

TTL or partition date set at write time

Ages past threshold

e.g. 30 days old

Archival pipeline

batch job reads + converts + writes cold copy

Cold archive

compressed, queryable, cheap

Partition dropped / TTL expiry

removed from primary store

Four automation mechanisms and what each one is actually good at

Four automation mechanisms and what each one is actually good at
Mechanism	Operates on	Typical use
TTL	Individual records in a database	Session data, caches, temporary tokens
Object lifecycle rule	Objects/prefixes in blob storage	Moving logs or backups to cheaper tiers, then deleting
Partition pruning	Whole partitions in a time-partitioned table	Dropping a full day/month of rows in one metadata op
Archival pipeline	Batches of aging rows or files	Moving old data out of the primary system into a queryable cold store

Remember: TTLs (per-record, database-enforced), object lifecycle rules (blob storage, prefix-based), partition pruning (drop-a-partition instead of scan-and-delete) and archival pipelines (batch move to cold storage) are the four mechanisms that carry out a retention policy automatically — design the schema (time-based partitioning) and storage layout around the policy from the start, because retaining that shape is what makes enforcement cheap later.