Time-Series Indexes, Sort/Match Patterns, Window Analytics, and Bucket-Aware Performance

Learning objectives

1. Index the query shape, not the word “time-series”

AtlasMart's operations dashboard asks: “for tenant A, sensor 07, show the last six hours and a rolling 20-minute average.” The useful predicate contains stable metadata equality plus an event-time range, then requires chronological order. MongoDB 6.3+ creates a compound metaField/timeField index for new time-series collections, but that default index indexes the entire metadata subdocument. Queries on scalar metadata subfields often benefit from an explicit compound index matching those subfields and time.

Time-series secondary indexes are implemented over buckets, not one B-tree key per public measurement. MongoDB can index bucket min/max information and metadata to prune work efficiently, but several index families and operations have special restrictions. Always verify with explain() on the actual query shape.

2. Create a queryable fixture and inspect index definitions

docker rm -f atlasmart-ch19-l3 2>/dev/null || truedocker volume rm atlasmart-ch19-l3-data 2>/dev/null || truedocker run -d --name atlasmart-ch19-l3 \  -p 127.0.0.1:27162:27017 \  -v atlasmart-ch19-l3-data:/data/db \  mongodb/mongodb-community-server:8.3.8-ubuntu2204-slim \  --bind_ip_alldocker exec atlasmart-ch19-l3 mongosh --quiet --eval 'printjson(db.adminCommand({buildInfo:1}).version);printjson(db.adminCommand({getParameter:1,featureCompatibilityVersion:1}).featureCompatibilityVersion);' 

const d=db.getSiblingDB("atlasmart");d.telemetry_ch19_l3.drop();d.createCollection("telemetry_ch19_l3",{timeseries:{timeField:"eventAt",metaField:"meta",granularity:"seconds"}});d.telemetry_ch19_l3.createIndex({"meta.tenantId":1,"meta.sensorId":1,eventAt:1},{name:"tenant_sensor_time"});const base=ISODate("2026-09-02T00:00:00Z");const docs=[];for (let sensor=0;sensor<4;sensor++) {  for (let i=0;i<360;i++) {    docs.push({eventAt:new Date(base.getTime()+i*2*60*1000),meta:{tenantId:"tenant-a",storeId:"baku-01",sensorId:`sensor-${sensor.toString().padStart(2,"0")}`},temperatureC:16+sensor+(i%20)/10,powerW:70+(i%40)});  }}d.telemetry_ch19_l3.insertMany(docs);print("measurements",d.telemetry_ch19_l3.countDocuments({}));printjson(d.telemetry_ch19_l3.getIndexes());

The fixture count is deterministically 1,440. Inspect both the automatically created time-series index and the explicit tenant_sensor_time index. Do not assume an _id_ index exists on a time-series collection: unlike regular collections it is not created automatically, and MongoDB 8.3 rejects creating or hinting an index named exactly _id_ on a time-series collection.

3. Compare selective and broad access with executionStats

const d=db.getSiblingDB("atlasmart");const from=ISODate("2026-09-02T06:00:00Z"), to=ISODate("2026-09-02T10:00:00Z");const targeted=d.telemetry_ch19_l3.find({"meta.tenantId":"tenant-a","meta.sensorId":"sensor-01",eventAt:{$gte:from,$lt:to}}).sort({eventAt:1}).explain("executionStats");const broad=d.telemetry_ch19_l3.find({temperatureC:{$gte:17.5}}).sort({eventAt:1}).explain("executionStats");function stages(x,out=[]){if(!x||typeof x!=="object")return out;if(x.stage)out.push(x.stage);for(const v of Object.values(x))stages(v,out);return [...new Set(out)];}for (const [name,e] of [["targeted",targeted],["broad",broad]]) {  printjson({name,nReturned:e.executionStats?.nReturned,totalKeysExamined:e.executionStats?.totalKeysExamined,totalDocsExamined:e.executionStats?.totalDocsExamined,stages:stages(e.queryPlanner?.winningPlan)});}

The exact plan tree is version- and optimizer-dependent. The evidence to compare is returned rows, keys/documents examined, presence of index-oriented versus blocking-sort stages, and whether the intended predicate is pushed into bucket pruning. A correct result with broad bucket examination can still be operationally expensive.

4. Window analytics require partition, time order, and an explicit final sort

$setWindowFields lets AtlasMart compute moving statistics without exporting the series. A time-range window requires one ascending date sort field. The stage defines the order used for the window, but MongoDB does not guarantee the final returned order, so add a final $sort when consumers depend on output order.

const d=db.getSiblingDB("atlasmart");const from=ISODate("2026-09-02T06:00:00Z"), to=ISODate("2026-09-02T08:00:00Z");const pipeline=[  {$match:{"meta.tenantId":"tenant-a","meta.sensorId":"sensor-01",eventAt:{$gte:from,$lt:to}}},  {$setWindowFields:{    partitionBy:"$meta.sensorId",    sortBy:{eventAt:1},    output:{      avgTemp20m:{$avg:"$temperatureC",window:{range:[-20,"current"],unit:"minute"}},      maxPower20m:{$max:"$powerW",window:{range:[-20,"current"],unit:"minute"}}    }  }},  {$project:{_id:0,eventAt:1,"meta.sensorId":1,temperatureC:1,powerW:1,avgTemp20m:1,maxPower20m:1}},  {$sort:{eventAt:1}}];printjson(d.telemetry_ch19_l3.aggregate(pipeline,{allowDiskUse:true}).limit(8).toArray());printjson(d.telemetry_ch19_l3.explain("executionStats").aggregate(pipeline));

Do not invent a specific spill result. This dataset is intentionally small and may remain in memory. In representative environments inspect explain/profiler/log memory and disk-use evidence. allowDiskUse:true permits eligible stages to spill; it is not proof that spilling occurred or that the pipeline is efficient.

5. Deliberately wrong: compute first, filter later

If a pipeline first derives a value for every measurement and only later filters on that computed value, the original metadata/time index cannot directly satisfy that computed predicate. A semantics-preserving optimizer may move ordinary matches earlier, so the bad example intentionally matches on a value created by $set.

const d=db.getSiblingDB("atlasmart");const from=ISODate("2026-09-02T06:00:00Z"), to=ISODate("2026-09-02T10:00:00Z");const bad=[  {$set:{wanted:{$and:[{$eq:["$meta.sensorId","sensor-01"]},{$gte:["$eventAt",from]},{$lt:["$eventAt",to]}]}}},  {$match:{wanted:true}},  {$sort:{eventAt:1}}];const good=[  {$match:{"meta.sensorId":"sensor-01",eventAt:{$gte:from,$lt:to}}},  {$sort:{eventAt:1}}];print("same logical count",d.telemetry_ch19_l3.aggregate(bad).itcount()===d.telemetry_ch19_l3.aggregate(good).itcount());printjson({bad:d.telemetry_ch19_l3.explain("executionStats").aggregate(bad),good:d.telemetry_ch19_l3.explain("executionStats").aggregate(good)});

The repair expresses selective conditions directly on stored fields before the expensive work. Compare explain evidence rather than assuming stage text alone determines execution. This is the same evidence-first habit developed in Chapters 8 and 12.

6. Production judgment

Indexes improve time-series queries when they align with stable metadata filters, time ranges, and required sort order. Every extra index adds storage and write maintenance, so use workload evidence before proliferating indexes. Watch index size, keys/docs examined, sort/window memory, bucket pruning, and tail latency across representative windows. Avoid distinct() on time-series collections for large distinct-value work; an indexed $match plus $group is the documented alternative.

Bridge. Lesson 4 adds expiration and shows why the collection's timeField determines what “old” means—an especially important distinction for late-arriving data.

Authoritative references

• Time Series Collections — time-series model, writable non-materialized view, automatic index, and sharding boundaries.
• Create and Query a Time Series Collection — timeField/metaField, granularity, custom bucketing, and collection options.
• About Time Series Data and Bucketing — system.buckets organization, bucket catalog, bucket creation/closure, and out-of-order timestamps.
• Time Series Collection Considerations — metadata cardinality, bucket density, granularity, compression, and zone-sharding boundary.
• Set Granularity for Time Series Data — granularity and custom bucket span/rounding behavior.
• Add Secondary Indexes to Time Series Collections — automatic and additional indexes plus sort/query support.
• Time Series Collection Limitations — bucket limits and index/query/update restrictions.
• Automatic Removal for Time Series Collections — expireAfterSeconds, bucket-level TTL timing, and collMod.
• Time Series Compression — zstd and column-compression mechanisms and tradeoffs.
• $collStats — time-series storageStats and bucket diagnostic fields.
• serverStatus — bucket catalog counters and server-level time-series diagnostics.
• $setWindowFields — time-range windows, ordering requirements, and analytics behavior.
• Explain Results — queryPlanner/executionStats interpretation and plan-format caveats.
• MongoDB 8.3 Release Notes — current stable 8.3 series and time-series compatibility changes.
• mongosh Release Notes — mongosh 2.10.0 baseline.
• PyMongo Release Notes — PyMongo 4.17.x driver baseline where client measurement is used.