Why we rewrote our query planner in a single weekend — and what the document model taught us about restraint.
Three engineers, eighty-four collections, and one stubborn aggregation pipeline that kept timing out at 3:14 a.m. on the analytics dashboard nobody wanted to admit they depended on.
Last winter, somewhere between the third on-call rotation and a particularly grim Tuesday standup, our analytics service started returning empty arrays for tenants with more than ninety thousand orders. The aggregation pipeline — six stages, a $lookup, two $unwinds, and a regrettable $facet — was hitting the planner's collection-scan fallback and quietly giving up at the 60-second mark. The fix wasn't a faster index. The fix was admitting that the planner we had written in 2022 had never been asked the question we were now asking it.
The shape of the data was always the answer
We had spent two years modeling around relational instincts — one collection per noun, joins reconstituted at read time, foreign keys with prayer. The document model rewards a different habit: store the shape you read. When we collapsed the order, the line items, and the merchant snapshot into a single nested document, the planner went from a six-stage gymnastics routine to a single covered query.