Data keeps growing

For the first year, Tiffin's database was the least interesting part of the system. One Postgres primary, a couple of read replicas, a connection pool, a cache in front of the menus. Every problem in Modules 3 to 8 was about traffic: too many requests at 8pm.

Traffic comes and goes. Data doesn't. Every order Tiffin has ever taken is still in the orders table, and the table never shrinks. This lesson is about what happens when the size of your data, not the number of requests, becomes the problem.

Three years later

Tiffin now runs in a handful of cities and takes around 250,000 orders a day. Add up three years of growth and the orders table has about 200 million rows.

Let's estimate how big that really is. Rough sizes per row, including some overhead:

Then add indexes. On a busy table, indexes are often 50% to 100% of the table size again. So the order tables alone are roughly 500 GB to 700 GB, and the rider location history is another 550 GB to 700 GB on its own, as much as all of them together. Add customers, payments, menus, reviews and the rest, and the whole database is about 1.5 TB.

That last row is the surprise. Nobody thinks of rider pings as "the data". Riders send their location every 4 seconds, and even though the history keeps only one point every 30 seconds while an order is on the road (Module 6), a 15-minute ride is 30 rows per order. Small rows, huge count. This is common: the table you think about least often grows the fastest.

Why big hurts, even on a big disk

A modern server can hold 10 TB or more of SSD. So why worry? Because almost everything about running a database gets slower as the data grows, and most of it has nothing to do with disk space.

The working set stops fitting in memory

A database is fast when the data it needs is already in RAM. Say the primary has 256 GB of memory. When the database was 100 GB, every index and most rows lived in memory. At 1.5 TB, only a small slice does.

That's fine as long as queries only touch recent data. A query that wanders into old orders now reads from disk, and each one pushes useful pages out of the cache for everyone else.

The working set is the data your queries touch often enough that it needs to stay in memory. When it no longer fits, latency jumps.

Indexes get deeper and wider

A B-tree index on 200 million rows is still fast for a single lookup: four or five levels deep. But the index itself is now several gigabytes, and every insert has to update it. Five indexes on orders means five index writes per order.

Maintenance becomes a project

These are the jobs that quietly turn from "minutes" into "a weekend":

• Backups. Copying 1.5 TB at 200 MB/s takes about 2 hours. Restoring takes at least as long, and that's your recovery time (RTO) from Module 5.
• New replicas. A new read replica has to copy the whole database first. Hours again.
• Schema changes. Changing a column's type, or building a new index on a 200-million-row table, can take hours and lock or slow writes. (Modern Postgres and MySQL add a plain new column instantly; it's the changes that rewrite or scan every row that hurt.)
• Cleanup. Postgres's VACUUM cleans up dead rows, and it has to scan the table's changed pages and every one of its indexes to do it. On a huge, busy table it runs for hours and competes with real traffic.
• Deleting. DELETE FROM orders WHERE created_at < '2023-01-01' on 50 million rows creates 50 million dead rows, a huge write-ahead log and replica lag. It can be worse than doing nothing.

Most data is cold

Look at who actually reads Tiffin's orders:

• The live order screens read orders from the last few hours.
• "Your orders" in the customer app reads the last few weeks. Very few people scroll back two years.
• Restaurant payouts read the last week or month.
• Support reads orders from the last 30 to 60 days, when complaints come in.
• Finance and tax need everything, but once a month or once a year, and they're happy to wait.

Put numbers on it and you get a pattern almost every app has: something like 95% of reads touch data from the last 30 days, which is only a few percent of the rows.

Data that is read constantly is hot. Data that is kept but rarely read is cold. Most of the pain of a big database comes from keeping cold data in the same place, on the same expensive hardware, with the same indexes, as hot data.

Not every growth problem is the same

Before you reach for a fix, name the problem. "The database is big" can mean very different things:

Each row of that table is a lesson in this module, and they're in that order on purpose. The early ones are cheap: a batch job, a different table layout. The last two, sharding and splitting the app, change how the whole system and the whole team work. You put those off as long as you honestly can.

The order to try things

A useful habit: for every step, write down the trigger that tells you it's time. "We'll shard when the primary's write load passes 70% of what we measured it can do" is a plan. "We'll shard because we're getting big" is a guess.

Remember also what you already have. Scaling up (Module 4) still works: a database server with 1 TB of RAM and 64 cores is a normal thing to rent. Read replicas (Module 5) and caching (Module 3) still take most reads away. Many companies much bigger than Tiffin run their main database on one primary for years. The goal is not to get to sharding. The goal is to avoid needing it.