Storing device telemetry without regretting it
Telemetry from a fleet arrives forever, and mostly gets read once. That combination breaks the schema instincts that serve you well everywhere else.
A thousand devices reporting every thirty seconds is about a billion rows a year. The schema that felt fine at the pilot stage becomes the reason queries time out, and the fix is disruptive once the data is live.
Partition from day one
Time-based partitioning is not premature optimisation here, it is the design. It makes retention a matter of dropping a partition rather than a `DELETE` that runs for hours and bloats the table.
Native Postgres partitioning is enough for a lot of fleets. TimescaleDB is worth it when you want automatic chunking and compression without building it yourself.
Decide retention before you collect
Nobody wants to answer this at the start, so it defaults to forever, and forever is expensive.
A tiered answer usually works:
- Raw readings at full resolution: 30 to 90 days
- Hourly aggregates: a year or two
- Daily aggregates: indefinitely
Almost every real query is against aggregates. Raw data matters for incident forensics, which has a short useful window.
Write in batches
One insert per reading will saturate a database far earlier than you expect. Buffer at the edge or in the ingest service and write in batches. It is dramatically cheaper, and it gives you somewhere to absorb a database blip without dropping data.
Separate hot from cold
Recent data is queried constantly by dashboards; old data is queried rarely and in bulk. They deserve different storage. Keeping thirteen months of raw readings on fast storage because nobody chose a boundary is a common and avoidable cost.
Model the device, not the reading
The row is the cheap part. What makes telemetry useful later is knowing which firmware version, which hardware revision, which site produced it. Capture that context at ingest - reconstructing it afterwards ranges from painful to impossible.