What I Learned Partitioning a Billion-Row Table in Production

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. This is what we did about it, and more usefully, what nobody told us beforehand. First: are you sure you need this? Partitioning is not a performance trick you reach for when a query feels sluggish. It carries real operational cost, and most tables that people want to partition should just be indexed properly. Some honest signals that you're actually at the boundary: Your indexes no longer fit comfortably in memory, so index reads hit disk Index maintenance — REINDEX, VACUUM, ANALYZE — takes so long you can't schedule it Deleting old data is impossible in practice, because a DELETE of a hundred million rows will destroy your write throughput for hours Your queries almost always filter on a single obvious dimension, usually time That last one matters more than the rest. Partitioning only helps if your access pattern lines up with how you split the data. If your queries hit every partition anyway, you have added complexity and gained nothing. For us the alignment was clean: nearly every query on the events table was scoped to a date range. Operations dashboards loo