Section 7: Two Numbers at Once - Deep Dive: Indexing Intervals (Candidate-Driven)

Executive Summary: Handed the wheel, the candidate drives to where this system bears weight: indexing intervals. We reframe the overlap query geometrically, walk five indexing options with a number and a verdict each, choose 15-minute buckets with arithmetic, and prove the emit rule on Maya's ride. That rule lets a query read three partitions in parallel, return each ride exactly once, and paginate without server state.

"Where do you want to go deeper?"

"Indexing intervals," the candidate says. "Writes peak at 1,750 a second; any store absorbs that. The design stands or falls on whether a 30-minute San Francisco window reads about 25,000 rows or 27 million."

The Query Is a Quadrant, Not a Strip

"Plot every ride as a point: start on the x-axis, end on the y-axis. Every point sits above the diagonal, since end is after start. start <= qe AND end > qs keeps points left of a vertical line and above a horizontal one. That's a quadrant.

"A B-tree on start_ts gives one side: a vertical strip of everything that started before 17:40, running back to the first ride ever recorded. An index on end_ts gives the other strip, which includes every ride after the window. A B-tree orders one dimension. This problem has two."

Key Insight: An interval is two numbers, and the query constrains both in opposite directions. Index one and the other becomes a scan. Every option below is a different answer to "how do I bound the second dimension?"