Production Best Practicesintermediate

Data Retention

Expiring data by design rather than by emergency deletion, and moving cold data out of the hot path.

2 min readIntermediateUpdated 31 Jul 2026Edit this page

Retention is a design decision made at table creation, not a cleanup job invented when the disk fills. The mechanism you choose determines whether expiry is free or expensive.

Delete is the expensive option

A bulk DELETE is a write-heavy operation: it produces dead tuples in PostgreSQL, undo records and secondary index changes in InnoDB, tombstones in Cassandra, and part rewrites in ClickHouse. It generates replication traffic proportional to the rows removed, and it does not return space to the filesystem in most engines.

Never delete millions of rows in one statement

A single large DELETE holds a long transaction, blocks vacuum, inflates WAL and can lock more than you expect. If you must delete, do it in bounded batches with a pause between them, and stop if replication lag rises.

-- Bounded batch; repeat until zero rows are affected.
DELETE FROM events
WHERE id IN (
    SELECT id FROM events
    WHERE created_at < now() - interval '90 days'
    LIMIT 10000
);

Drop a partition instead

If a table has a time-based lifecycle, partition it by time and expire by dropping partitions. Dropping is a metadata operation: it is instant, frees space immediately, produces almost no WAL, and generates no dead rows.

This one decision changes retention from an ongoing operational burden into a scheduled no-op. See Partitioning.

Engine-native equivalents:

ClickHouse — TTL clauses drop or move parts automatically. See TTL.
Cassandra — per-row or per-column TTL, though expired data still becomes tombstones.
Redis — key expiry with EXPIRE; memory is reclaimed lazily and by the active expiry cycle.
Elasticsearch / OpenSearch — index lifecycle management deletes whole indices by age. See Index Lifecycle Management.
MongoDB — TTL indexes remove documents in a background pass.

Data Archiving

Archiving moves data out of the operational store while keeping it available. It is worth doing when data must be retained for years but is queried rarely.

A workable pattern:

Export the partition to columnar files (Parquet) in object storage, partitioned by the same time key.
Verify the export — row counts and a checksum against the source.
Detach and drop the partition.
Query the archive with a separate engine when it is needed.

The result is a small, fast operational database and cheap long-term storage, instead of a database that is 95% cold data raising the cost of every backup, restore and index rebuild.