Archiving old data

In the last lesson we found that most of Tiffin's data is cold. Orders from two years ago sit in the same table, on the same expensive SSDs, behind the same indexes, as the order a customer placed five minutes ago. They make backups slower, migrations slower and the working set bigger, and almost nobody reads them.

The cheapest growth step there is: move that data somewhere cheaper, and take it out of the main database.

What archiving means

Archiving is moving data that is rarely read out of your main database into cheaper storage, while keeping a way to get it back when someone does need it.

The important part is the second half. Archiving is not deleting. Tiffin must keep orders for years: tax law in most countries requires invoices and payment records for somewhere between 5 and 10 years, and customers will occasionally dispute a charge from long ago. You're not throwing the data away. You're moving it to a place that suits how it's actually used.

Pick the line between hot and cold

Before moving anything, decide what "old" means. Ask each reader of the data how far back they go:

For Tiffin, a reasonable line is orders older than 12 months. Everything newer stays in Postgres. Everything older moves out. Some apps pick 90 days, some 3 years. The right number comes from this table, not from a rule.

Rider location history is different. Nobody needs the exact path of a delivery from last year except in a rare dispute. A line of 30 days makes sense there, and that one decision removes most of the 550 GB to 700 GB of rider history from the last lesson.

Where cold data goes

You have three common options.

Object storage

Write old data as files to an object store like Amazon S3 or Google Cloud Storage, one file per day or per month. Use a columnar format like Parquet, which compresses well (often 5 to 10 times smaller than the same rows in Postgres) and can be queried directly by tools like Athena, BigQuery or Trino.

Cost is the big win. Fast SSD storage attached to a database costs roughly $0.10 to $0.20 per GB per month, and you pay for it on the primary and every replica. Object storage costs around $0.02 per GB per month, and colder tiers (like S3 Glacier) a fraction of that.

An archive database

A separate, cheaper database (smaller machine, slower disks, no replicas) that holds old orders in the same schema. This is handy when support or the app still needs to look up an old order by ID with plain SQL.

The data warehouse

Most companies of Tiffin's size already copy their data into a warehouse (BigQuery, Snowflake, Redshift) for analytics. If finance already reads everything from there, old orders may only need to live in the warehouse plus an object store backup.

Tiffin uses two of these:

Moving the data without hurting production

This is where archiving goes wrong. The naive version:

-- Don't do this on a 200-million-row table at dinner time.
INSERT INTO archive.orders SELECT * FROM orders WHERE created_at < now() - interval '12 months';
DELETE FROM orders WHERE created_at < now() - interval '12 months';

That DELETE may touch 120 million rows in a single transaction that runs for hours. It writes gigabytes of write-ahead log, makes every replica fall behind (Module 5's replication lag, but measured in minutes), stops VACUUM cleaning up anywhere else while it's open, and leaves 120 million dead rows behind when it's done. If it fails near the end, all of that work rolls back.

The safe version does the same work in small batches, during quiet hours, and checks each batch before deleting anything:

// Runs nightly at 3am as a scheduled job (Module 7).
const cutoff = monthsAgo(12)
while (true) {
  const rows = await db.query(
    `SELECT * FROM orders WHERE created_at < $1 ORDER BY id LIMIT 5000`, [cutoff])
  if (rows.length === 0) break

  await archive.write(rows)               // e.g. append to this month's Parquet file
  await archive.verify(rows.map(r => r.id)) // confirm they really landed

  await db.query(`DELETE FROM orders WHERE id = ANY($1)`, [rows.map(r => r.id)])
  await sleep(200) // give replicas time to keep up
}

A few rules this follows:

• Copy, verify, then delete. If the job crashes after copying but before deleting, the next run copies the same rows again. So writing to the archive must be safe to repeat, which is the same idea as Module 7's jobs that can run twice. Overwriting by order ID, or de-duplicating on read, both work.
• Small batches. Each batch is a short transaction that touches 5,000 rows and finishes in well under a second. A crash loses one batch of work, not hours.
• Throttle. The sleep keeps replication lag small. Watch it while the job runs and slow down if it grows.
• Move related rows together. An order's items and events must move with the order, or you end up with items pointing at an order that isn't there.

Reading archived data

Archived data is still data your product promised to keep. Decide up front how each reader gets to it.

The customer app. "Your orders" shows the last 12 months from Postgres. At the bottom, a "Load older orders" button calls a separate endpoint that reads the archive. It may take two seconds instead of 50 milliseconds, and that's fine, because people press it rarely and expect it to be a bit slower.

Support tools. An order lookup by ID checks Postgres first, then the archive. Order IDs that carry their creation time (we'll see these in Module 19) make this easy: you can tell from the ID alone which store to ask.

Foreign keys. If a refunds table in Postgres points at an order that has been archived, the foreign key breaks. Either archive the refunds with the order, or make sure nothing still live points at cold rows. Check this before the first run, not after.

Deletion is a different job

Archiving keeps data. Some data must eventually go away for good: privacy laws like GDPR give people the right to have their personal data erased, and you shouldn't keep data forever just because storage is cheap.

So write a retention policy per kind of data:

Then remember that "deleted" must include the archive too, and that backups need a plan: either they expire on a schedule shorter than your promise, or you re-apply erasures after any restore. A customer whose account is erased should not reappear in a Parquet file three years later. Deleting data everywhere covers how.

The two rules can collide: the tax office wants the invoice kept for years, and the customer wants their data gone. Erasure laws usually allow for records you are legally required to keep, so the common answer is to keep the financial record and strip or anonymise the personal details around it.

What you get back

After archiving one year's worth of orders and 30 days of rider pings, Tiffin's main database drops from about 1.5 TB to around 400 GB. Backups and restores are nearly four times faster. New replicas are ready in an hour instead of an afternoon. Most of the indexes fit in memory again.

No new architecture, no new team, no change to how the app writes orders. Just one scheduled job and a policy. That's why it comes first.