MongoDB Document Modelling for Sensor Data

My first pass at storing the StopFire sensor data went into a relational shape without me consciously deciding it should. stations table, readings table, weather_forecasts table, alerts table, foreign keys everywhere, third normal form because that is what I had been taught a well-designed schema looks like. It was correct. It was also the wrong shape for what I actually needed to do with the data, and it took building the thing to see why.

The access pattern that mattered most was “give me everything known about this sensor reading, right now, for this station.” Station metadata (location, install date, sensor model), the reading itself (temperature, humidity, wind speed, timestamp), and the matching weather forecast for that hour. Every time the scoring job needed a reading, it needed all three, joined. In a relational store that is at minimum a three-table join per lookup. Multiply by however many readings arrive per minute across a few hundred stations and the joins start showing up as real latency, not a rounding error.

The relational instinct, and why it stops fitting

Normalisation exists to prevent update anomalies. Store a station’s location once in a stations table, reference it by station_id everywhere else, and moving a station means updating one row instead of hunting down every place its location got copied. That reasoning is sound, and it is the correct default for data that gets updated often and read in many different shapes.

Sensor readings are the opposite of that. A reading, once it lands, never changes. Its associated station metadata changes rarely (a sensor gets serviced, a station gets recalibrated) compared to how often the reading gets read. Optimising the storage shape for update safety, when updates are rare and reads dominate by a wide margin, is optimising for the wrong thing.

MongoDB’s document model let me store the shape I actually query, not the shape that protects against edits I was not making.

Embedding: one document per reading

{
  "_id": ObjectId("5ad4f2c1e4b0..."),
  "station_id": "STN-0142",
  "timestamp": ISODate("2018-04-18T14:32:00Z"),
  "location": {
    "lat": -37.8136,
    "lon": 144.9631,
    "region": "Yarra Ranges"
  },
  "reading": {
    "temperature_c": 34.2,
    "humidity_pct": 18,
    "wind_speed_kph": 42
  },
  "weather_forecast": {
    "source": "BOM",
    "rainfall_mm": 0,
    "fire_danger_rating": "Severe"
  },
  "sensor_model": "TempHum-X200"
}

Every field that a scoring run needs for this reading sits inside one document. No join. find_one({"station_id": "STN-0142", "timestamp": {"$gte": start, "$lt": end}}) returns everything at once, and MongoDB’s storage engine reads it off disk as one contiguous unit rather than assembling it from three index lookups.

The cost is duplication. location and sensor_model repeat on every single reading document for that station, thousands of times over the station’s lifetime. Relational design would call this an anomaly waiting to happen. In practice, station metadata changes maybe twice a year per station, and I was willing to trade that rare, batchable update cost for every single read being one document fetch instead of a join. That trade only makes sense because I know the update frequency and the read frequency for this specific dataset. It would be the wrong trade for data that mutates constantly.

Basic CRUD with pymongo

from pymongo import MongoClient

client = MongoClient("mongodb://localhost:27017/")
db = client.stopfire
readings = db.readings

# Create
readings.insert_one({
    "station_id": "STN-0142",
    "timestamp": datetime.utcnow(),
    "reading": {"temperature_c": 34.2, "humidity_pct": 18, "wind_speed_kph": 42}
})

# Read
doc = readings.find_one({"station_id": "STN-0142"}, sort=[("timestamp", -1)])

# Update
readings.update_one(
    {"station_id": "STN-0142"},
    {"$set": {"location.region": "Yarra Ranges North"}}
)

# Delete
readings.delete_many({"timestamp": {"$lt": cutoff_date}})

update_one with dot notation ("location.region") updates a nested field without touching the rest of the document, which matters because rewriting the whole document on every metadata change would undo most of the read benefit I was chasing. delete_many against a timestamp range is how I purged readings past the retention window, one operation instead of a cascade of foreign-key-respecting deletes across three tables.

The aggregation pipeline

Reads that need a shape other than “one full document” go through MongoDB’s aggregation pipeline, which chains stages the way a Unix pipe chains commands. Computing average risk indicators per station over the last 24 hours looked like this:

pipeline = [
    {"$match": {
        "timestamp": {"$gte": start_time},
        "weather_forecast.fire_danger_rating": {"$in": ["Severe", "Extreme"]}
    }},
    {"$group": {
        "_id": "$station_id",
        "avg_temp": {"$avg": "$reading.temperature_c"},
        "max_wind": {"$max": "$reading.wind_speed_kph"},
        "reading_count": {"$sum": 1}
    }},
    {"$sort": {"max_wind": -1}},
    {"$project": {
        "_id": 0,
        "station_id": "$_id",
        "avg_temp": 1,
        "max_wind": 1,
        "reading_count": 1
    }}
]

results = list(readings.aggregate(pipeline))

$match filters down to readings under severe or extreme fire danger, using the same dot notation to reach into the nested weather_forecast field, before anything else runs. This matters for performance: putting $match first means the pipeline discards irrelevant documents immediately rather than grouping the whole collection and filtering afterward. $group then rolls the matched readings up per station, computing averages and a max in the same pass. $sort orders by wind speed, worst first. $project reshapes the output and drops the raw _id.

It reads like SQL’s WHERE / GROUP BY / ORDER BY / SELECT, in a different order and a different syntax, and once I stopped fighting the syntax and started thinking of each stage as a filter on the stream of documents flowing through it, writing pipelines got a lot faster.

Indexing: the compound index that actually mattered

Without an index, find_one({"station_id": "STN-0142", "timestamp": {"$gte": start, "$lt": end}}) scans every document in the collection. Against a collection accumulating readings every sixty seconds across a few hundred stations, that scan gets expensive within weeks.

readings.create_index([("station_id", 1), ("timestamp", -1)])

A compound index on station_id ascending and timestamp descending matches the exact shape of the query: find this station, then get its most recent readings first. MongoDB uses the leftmost fields of a compound index for equality matches and the remaining fields for range or sort operations, so this one index served both the station_id equality filter and the timestamp ordering in the same lookup. I checked the difference with .explain("executionStats") before and after creating it. Without the index, a query against roughly two million documents scanned all two million to find matches. With it, the same query touched only the documents for that station, an order of magnitude fewer.

I got the field order wrong on my first attempt, indexing timestamp first and station_id second. It still worked, but .explain() showed a wider scan than the correctly-ordered version, because a query starting with an equality filter on station_id cannot use a leading timestamp index efficiently. The rule I now keep in mind: equality fields go first in a compound index, range and sort fields go after.

Where the document model would have hurt me

None of this means normalisation was wrong, only that it was wrong for this specific access pattern. If the project had needed to update station metadata frequently, across thousands of historical readings retroactively, the denormalised shape would have meant rewriting every affected document instead of one row in a stations table. If the queries had needed arbitrary joins across unrelated entities (readings by sensor model across every region, readings by technician who last serviced the station), the document model would have fought back just as hard as the relational one fought back against my read pattern.

The document decided itself, once I stopped asking “how do I model this data correctly” and started asking “what does the code that reads this data actually ask for, most often.” Those turned out to be different questions with different right answers.