The events table nobody deletes from

There is an events table. Every click, view and state change writes a row: about five million rows a day, roughly half a kilobyte each once the index entry is counted.

Nobody has ever deleted from it. The database volume is 2 TB and there are three copies of everything.

When does it stop fitting? Commit to a figure now.

Predict

Five million rows a day at about 512 bytes each, three copies, on a 2 TB volume. When does it stop fitting?

Choose one answer

In about three years, which is somebody else's problemIn about nine monthsWithin a few weeksUnanswerable without knowing the compression ratio

Lock in answer

Storage growth

≈ Hypothetical model, not a forecast

Accumulates stored bytes month by month from a record size and a write rate, then applies retention, compression and replication. A period is one month and period 0 is an empty system.

It assumes

• Five million rows arrive every day, at a constant rate, with no seasonality.
• Each row is 512 bytes including its index entry, measured before compression.
• Ten years of retention stands in for a table nobody has ever deleted from.
• Three full copies are kept, which is one primary and two replicas.
• No compression, which is the honest default for a row-store heap of small rows.
• The storage budget is a 2 TB volume, with nothing else sharing it.

It ignores

• Indexes, which routinely add half again on top of the rows themselves
• Backups and snapshots, which are copies the replication factor does not count
• Deleted-but-not-yet-reclaimed space and filesystem overhead
• That compression ratios vary enormously by data type and degrade over time

Leave the write rate and the row size alone. Set a retention period that keeps this table inside the 2 TB volume for the whole three-year horizon, and find the longest one that does.

Bytes per record512 B

Records written per day5,000,000

Months of data kept120

Counted in months. Data older than this is gone.

Storage budget1.8 TiB

Bytes written per day

2.4 GiB

Stored bytes added each month

218 GiB

Steady-state size once retention bites

25.5 TiB

Steady-state size before copies and compression

8.5 TiB

Disk per logical byte

3

Month the budget is exceeded

9

Budget exceeded inside the horizon

1

Records held at steady state

18,250,000,000

Times the steady-state size the budget covers

0.071

• Disk used
• Volume size

◷ Not there yet — keep moving the controls.

2.56 gigabytes of rows a day. 234 gigabytes of disk a month once the three copies are counted. The volume is gone in month nine, and at ten years of retention the steady state would be 28 TB — fourteen times the volume you have.

The challenge's answer is eight months: eight times 234 gigabytes is 1.87 TB, which fits, and nine months is 2.10 TB, which does not. Notice how little room that leaves — the budget covers the steady state 1.07 times over, so one busy quarter or one new event type spends the margin. I would set six months and keep a 1.4 times margin, or ask for a larger volume and set twelve. Eight is the arithmetic answer and a bad operational one.

What I would actually do

Measure the row size instead of estimating it. pg_total_relation_size('events') divided by the row count gives you bytes per row including indexes and TOAST, which is where the surprise lives: a table with four indexes on it can be more than twice the size of its rows. Doing this before anything else has changed more than one of these estimates by a factor of two.

Partition by time, which is what makes retention cheap. Declarative range partitioning on the timestamp, one partition per month, with pg_partman creating them ahead of time. Then retention is ALTER TABLE events DETACH PARTITION events_2026_01 followed by a copy out and a DROP TABLE — metadata operations that complete in moments and return the disk immediately.

Do not implement retention as a delete. DELETE FROM events WHERE created_at < now() - interval '8 months' on a few hundred million rows writes about as much write-ahead log as the inserts did, holds the dead rows until VACUUM reclaims them, and returns no disk to the filesystem at all without a VACUUM FULL that takes an exclusive lock. On a table at this scale that is how a cleanup job becomes an outage. Partition detach is not a nicer version of delete; it is a different operation.

Then ask what the events are for, because it changes the answer entirely. If product features read recent events, Postgres with monthly partitions and a few months of retention is the right home and this is finished. If the reads are analytical — counts, funnels, aggregates over a year — then the table is in the wrong engine. Columnar storage over the same events, ClickHouse or Parquet files in object storage queried with DuckDB, realistically compresses structured event data by somewhere between five and fifteen times, and that one change moves the arithmetic further than any retention decision could.

The tiering that follows is the usual one and it is worth naming because it sounds more complicated than it is: hot months in the partitioned table, older months as Parquet in object storage, and a documented way to query the old ones that somebody has actually run once.

Check

A colleague suggests keeping everything but setting the replication factor to one, since the events are not critical. What does that buy, and what does it cost?

Choose one answer

It triples the runway, which is the cheapest fix availableIt delays the problem by about two years and removes the failover for that table, which is a strange trade for a table that is still unboundedNothing, because replication is not optionalIt saves disk and makes writes faster, so it is worth doing anyway

Check answer

Where the ramp stops being a ramp

The condition is the whole point of the lesson: once old records are deleted on a fixed schedule, none of the arithmetic above applies.

A retention policy converts an unbounded ramp into a flat line at a known height. The chart stops being a question about when you run out and becomes a single multiplication — monthly volume times months kept times copies — that you can check against the volume once and then leave alone. Growth in the write rate still moves the line, but it moves a line rather than a slope, and a line you can see.

Which means the real deliverable from this lesson is not a number. It is a sentence in a document somewhere that says how long events are kept and who agreed to it, because the ramp exists precisely where that sentence does not. Every table that ate a volume was a table whose retention nobody was asked about.

Worth remembering

• Rows a day times bytes a row times days kept times copies, divided by compression — and in a row store, assume compression divides by about one.
• Five million rows a day at 512 bytes is 2.56 gigabytes raw, which is 234 gigabytes of disk a month once three copies are counted.
• A 2 TB volume is full in month nine, and at ten years of retention the steady state would be 28 TB.
• Days kept is the only term that is a policy rather than an observation, and the only one that turns an unbounded ramp into a flat steady state.
• The arithmetic answer here is eight months of retention and the operational answer is six, because eight leaves only 7% headroom against one busy quarter.
• Measure bytes per row from total relation size divided by row count — indexes and TOAST routinely double the figure people estimate from the columns.
• Implement retention as partition detach and drop, never as a delete: a delete on this table writes as much log as the inserts did and returns no disk without an exclusive-lock rewrite.
• If the reads are analytical rather than product features, columnar storage compresses event data five to fifteen times and moves the arithmetic further than any retention decision.