Adding an index stops working eventually. Here's what we did when a nationwide logistics platform's core table crossed a billion rows — and the parts nobody warns you about.
There's a specific moment in a backend engineer's life when the usual advice stops working.
A query gets slow. You check the execution plan, you add an index, it gets fast again. This works for years. It works so reliably that it starts to feel like a law of nature.
Then one day you add the index and nothing happens. Or worse — the index takes six hours to build, locks the table while it does, and the query is still slow at the end of it.
That's roughly where we were on a nationwide logistics platform processing tens of thousands of orders a day. The tracking events table — one row per scan, per parcel, per status change — had crossed a billion rows. Every parcel generated a dozen or more events on its journey. The table only ever grew.
