Section 5: Two Numbers, One Index - Data Modeling

Executive Summary: This section starts from the table every candidate draws first, rides(start_ts, end_ts) with an index on start_ts, and shows exactly why it fails: the overlap query has two range predicates on two columns, and a B-tree can bound only one of them. We then build the replacement: events_by_id as the source of truth, events_by_bucket holding one fat row per 15-minute bucket a closed event overlaps, and a Redis live tier holding the events that have no end yet. Maya's ride becomes four bucket rows. The partition key beats three alternatives, and its largest partition is about 15 MB against a 50 MB sub-shard trigger.

The interviewer points at the API you just wrote: "Fine. What's in the database?" Draw the obvious table first. Watching it fail is the fastest way to explain what the real model has to do.

The Table Everyone Draws First

CREATE TABLE rides (
  event_id  text PRIMARY KEY,
  city_id   text,
  start_ts  timestamptz NOT NULL,
  end_ts    timestamptz,          -- NULL while the ride is in progress
  status    text
);
CREATE INDEX rides_by_start ON rides (city_id, start_ts);

-- Section 4's overlap predicate, for the Market Street window:
SELECT * FROM rides
WHERE city_id = 'sf'
  AND start_ts <= '2026-10-30T17:40:00Z'
  AND (end_ts IS NULL OR end_ts > '2026-10-30T17:10:00Z');

The predicate is right. The index can't serve it. The B-tree on start_ts bounds only one side: "started before 17:40" means every SF ride in the last 90 days, which is about 27 million rows (300K/day × 90), to return about 25,000. Index end_ts instead and you scan everything still open plus everything that ended after 17:10. For a 'now' query that's tolerable. For a window two months back it's two months of rides.

Interviewer: "Put a composite index on (start_ts, end_ts). Done?"

Candidate: "That doesn't fix it. A composite B-tree sorts by start_ts first, so it can still only seek on the start range. The end_ts condition becomes a filter applied to every entry in that range, and that range is still all of history. The index can give me a strip of the data, but the query asks for a quadrant. An interval is two numbers, and a one-dimensional index can bound only one of them."

Key Insight: Interval overlap is a two-dimensional query wearing a one-dimensional disguise. Any model that has one sorted index on one timestamp scans unbounded history on one side. The fix is to make the start time bounded by construction: before a query runs, each closed event is copied into the small time slices it touches.

Quick check before moving on: