Back to all articles
Platform at ScalePart 10
Data lifecycleMySQLPartitioningSystem designGCP

Platform at Scale, Part 10: The Table That Grew Forever

Janahan Thavendran7 min read
In this article

Platform at Scale, Part 10 · A spike you can plan for, a slope you have to manage. Archive and partition before you shard.

The figures in this series are illustrative. They're rounded and changed to protect confidential data, and they show the shape of each problem rather than the company's actual numbers.

A merchant opens their order history on a Monday morning and waits 8 seconds. Last year it was instant. Nothing about the code had changed. The table just had three more years of orders in it.

This is the part of scaling that doesn't show up on a Saturday. Peak load is a spike you can plan for. Data growth is a slope. It quietly turns fast queries into slow ones, short migrations into long maintenance windows, and backups into a nightly problem.

Why the table grew faster than we noticed

The orders tables held every order, every order line, every status change and every partner reference. They grew with the business, and the business jumped every festive season. Three things got worse with them:

  • Indexes stopped fitting in memory. Part 1's index on (outlet_id, status, created_at) was fast while its busy part fit in MySQL's buffer pool. Once the table held 400 million rows, it no longer did, and lookups that used to stay in memory started going to disk. Part 1's indexes bought us years, not forever.
  • Migrations got slower. Building an index in Part 1's Monday window took minutes when the table was small and hours when it wasn't. The window didn't grow with the table.
  • Restores got slower. The drill in Part 9 showed it. The recovery time we planned for assumed a smaller database.

Part 3's replicas moved the readers. They didn't stop the table growing. A report on a replica still scans the same rows.

What we fixed, in order

Stage 1: archive what nobody queries

Almost every query touched today's orders, this week's orders or the current menu. Old orders were read by finance, and now and then by a merchant or a diner looking up a past receipt.

So we moved orders older than 13 months out of the live tables: first into an archive table, and then into Cloud Storage as files organised by month and merchant. Old receipts were still available, through a slower path that took a few seconds instead of being instant. That was a deliberate trade, and we told merchants about it.

This was the cheapest fix, and it bought the most. Indexes fit in memory again, migrations fit back in the Monday window, and backups shrank. The archive was encrypted with our own keys, and archived orders could be put on legal hold when finance or a dispute needed them (Part 14).

Stage 2: partition by month

The live orders table was partitioned by month on created_at. Queries that filtered by date touched one partition instead of the whole table. Removing a month of old data became dropping a partition, instead of a huge DELETE that locked rows and pushed Part 3's replicas minutes behind.

MySQL added one catch. On a partitioned table, every unique key must include the partition column. The primary key became (id, created_at). And Part 1's unique index on (partner_id, partner_order_id), the one that stopped duplicate partner orders, couldn't stay as it was. We moved that check into a small, unpartitioned table of partner order references, written in the same transaction as the order. The duplicate protection stayed exactly as strong.

Retention became a written policy: 13 months live, 24 months in the archive table, everything older in Cloud Storage.

Stage 3: keep history and reporting off the live database

Part 3 moved dashboards and recent history to replicas. This part moved heavy reporting on old data into a warehouse built from the archive and the analytics store from Part 7. The primary and replicas served recent orders. The warehouse served everything older. Analysts had read-only access to the warehouse, never to raw orders, and merchant-facing history was served through views scoped to that merchant.

Stage 4: shard only when writes hit a measured ceiling

Part 3 turned sharding down because reads were the bottleneck, not writes or storage. After archiving and partitioning, that was still true. The primary was well within its write capacity.

So we wrote down the trigger instead of guessing: we would revisit sharding when write throughput, not reads, came within 70% of the primary's limit on two festive weekends in a row. Sharding is a months-long project. It should start from a measured ceiling, not fear of one.

The trade-off we made

Option
Keep one table, buy a bigger instance
Why it was tempting
No code change
Why we didn't choose it
Postpones the problem. Indexes, migrations and restores still grow with the table
Option
Delete old orders
Why it was tempting
Simplest
Why we didn't choose it
Finance and merchants need history. Deleting is a business policy, not a fix
Option
Archive, plus partition by month
Why it was tempting
Cheapest, keeps live data small, turns retention into a policy
Why we didn't choose it
Adds a lifecycle job, a slower path for old receipts, and MySQL's unique-key rule to work around
Option
Shard the orders table
Why it was tempting
Removes the single-machine limit for good
Why we didn't choose it
Months of work and harder cross-merchant reporting, for a ceiling we hadn't reached
Option
Archive, partition, warehouse for old data, shard only at a measured write ceiling
Why it was tempting
Each step buys time for the next, and sharding stays a decision, not a default
Why we didn't choose it
Our choice

What we measured

  • Rows in the live orders table: from 400 million to 60 million
  • Order history load time at p95: from 8 seconds to 0.8 seconds
  • Index build time in the Monday window: from 6 hours to 25 minutes
  • Restore time in the Part 9 drill: from 6 hours to 75 minutes
  • Peak write throughput as a share of the primary's limit: 35%

What it cost

The archive job, partition maintenance and the slower path for old orders added about $400 a month, and one more thing on-call had to understand. The warehouse added about $1,100 a month. The bigger cost was discipline: every new query had to respect the retention window, and every new large table had to be born partitioned or explain why not. That rule was later enforced by policy as code in the pipeline, not by review (Part 13).

What this part leaves for later

Gap left here
Sharding, if the write ceiling is reached
Where it's covered
Its own project. It changes how IDs are created, how cross-merchant reports are built and how Part 3's replicas are laid out
Gap left here
Where archived data is allowed to live
Where it's covered
Ties to Part 9's second region, and to data residency rules

What I took from it

Peak load is a spike. Data growth is a slope.

Archive on a policy, partition by time early, keep old data off the live database, and shard only when writes, not reads, hit a measured ceiling. The cost of a big table isn't storage. It's the index that no longer fits in memory, the maintenance window and the restore time.

Next in the series: Part 11: One tenant can't sink the ship