Time: the nearly redundant index

Why does a timestamp barely need an index, and what breaks that?

Time: the case where the index is nearly redundant

Time is not a key-domain family. It is a totally ordered domain with one extra property that changes everything: the key is correlated with insertion order.

That correlation means the base data is already approximately sorted by the query key. That is the clustering of Index anatomy, obtained for free instead of purchased. And when clustering is free, the index barely needs to exist. Block min/max bounds (zone maps) prune almost perfectly. This is why time-series and log systems use lightweight range metadata rather than trees on their timestamp column, and why a full B-tree on a monotonically increasing timestamp is usually wasted money: it is a second copy of an ordering the data already has.

A second property: the write distribution is one-sided. Recent data is hot and mutable. Old data is cold and effectively read-only. That permits an index structure most families cannot have: per-chunk immutable indexes, built once when a chunk is sealed and never updated. The maintenance cost that dominates every other family is near zero here.

So the honest statement about "time-partitioned indexes" is that they are not an index type. They are the combination of partitioning and time semantics producing a case where indexing has little work to do. Where it does earn a mention is the negative case: when the query key is time but the insertion key is not. Late-arriving events, backfills, and event time diverging from ingestion time all break the correlation. The free clustering evaporates, and you are back to paying for an index. (This is Priya's fourth symptom: a batch of three-day-old scans lands in the newest blocks, so those blocks now have min and max timestamps spanning days.)