Telemetry Storage & Retention

This page explains how telemetry keeps years of sensor readings without the database growing without bound. It is written to be readable without knowing Timescale — every mechanism is shown on a concrete row first.

Applies to: telemetry (Rust / Postgres + TimescaleDB). Supersedes the MongoDB time-series collections used by the Node sensor-service.

The Problem, In Numbers

One building, 100 sensors, one reading every 30 seconds:

At ~170 bytes a row that is roughly 180 GB a year, before indexes. Telemetry is written once and almost never read individually, so paying full price for every row is waste.

Three mechanisms cut it. They are independent — each helps on its own, and they multiply.

Lever 1 — Store Each Fact Once

A temperature reading arrives at POST /ingest:

{
  "buildingId": "b1",
  "roomId": "r1",
  "timestamp": 1700000000000,
  "temperature": 21.5
}

The naive row stores all of it twice — once in typed columns, once again in the payload JSON blob:

JSON stores key names as text on every row. "buildingId" is 12 bytes, repeated 288 000 times a day, for a value that is already in the column next to it.

Rule: payload holds only what no column holds.

The stored row becomes:

Nothing is lost. building_id, room_id and ts are the columns; value is the measurement the plugin designated. GET /{metric}/latest reassembles the original envelope from the columns before answering.

The list is not hand-maintained: MetricDescriptor.fields names every field a metric declares, and the plugin names the one that became value. Everything else is what gets kept.

Lever 2 — Squeeze Rows That Repeat Each Other

Room r1 writes 2 880 rows a day. Look at what they have in common:

b1 | r1 | temperature | 22:13:20 | 21.5 | {}
b1 | r1 | temperature | 22:13:50 | 21.6 | {}
b1 | r1 | temperature | 22:14:20 | 21.6 | {}
...2877 more, all starting the same way

b1, r1 and temperature are identical on every line. The timestamps rise by exactly 30 000 ms. The values barely move.

Timescale can rewrite a batch of up to 1 000 such rows into one compressed row:

alter table readings set (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'building_id, room_id, metric',
  timescaledb.compress_orderby   = 'ts desc'
);
select add_compression_policy('readings', interval '7 days');

• compress_segmentby — these columns are stored once per batch instead of 1 000 times.
• compress_orderby — timestamps are stored as first-value-plus-differences (+30s, +30s, …) rather than 1 000 full timestamps.
• Floating-point values are stored as differences between neighbours, which are tiny when a room’s temperature drifts slowly.

Typical result on telemetry: 10–20× smaller.

Only data older than 7 days is compressed. Recent data stays in ordinary row form, so ingest stays fast and GET /{metric}/latest is unaffected. Compression runs in the background as chunks age past the threshold.

Lever 3 — Keep Summaries, Not Every Reading

Nobody asks what a room measured at 03:41:30 last November. They ask for a chart.

After 14 days, individual readings are deleted and replaced by one row per hour:

create materialized view readings_hourly
with (timescaledb.continuous) as
select time_bucket('1 hour', ts) as bucket,
       building_id, room_id, metric,
       avg(value) as avg, min(value) as min, max(value) as max,
       sum(value) as sum, count(*) as samples
from readings
group by 1, 2, 3, 4;

120 raw readings per hour collapse to a single row carrying average, minimum, maximum, sum and sample count. That is 120× fewer rows, kept indefinitely.

readings itself is dropped after 14 days:

select add_retention_policy('readings', interval '14 days');

The view refreshes incrementally on a schedule and tolerates late-arriving readings within a 3-day window, so a sensor that reconnects after an outage still lands in the right bucket.

Which table a query reads

Decided entirely by the requested time range, in the adapter:

TimeRange::bucket_interval() in the kernel already returns "1 hour" for 1D and "1 day" otherwise, so the routing is one branch in the persistence adapter. No kernel code knows either table exists.

Combined Effect

10 buildings, 100 sensors each, one reading every 30 seconds:

Estimates, not measurements. payload width dominates and depends on the real airQuality mix. Verify with:

select pg_size_pretty(hypertable_size('readings'));
select * from hypertable_compression_stats('readings');

A compression ratio below ~8× means payload is carrying more than it should.

Compared With MongoDB Time-Series

The Node sensor-service used Mongo time-series collections. The underlying idea is the same — batch measurements, store repeated identifiers once, delta-encode time — so this is not a change of strategy, but the details differ.

Three practical differences:

• roomId was not part of metaField. Only building was. Every measurement therefore carried its own copy of the room name. Under segmentby it is stored once per batch.
• Buckets were keyed on the wrong clock. timeField was createdAt (ingest time) while every query filtered and sorted on timestamp (sensor event time), so Mongo could not skip buckets by time. Postgres has one ts column serving as both the partition key and the query key, so chunk exclusion works.
• No rollup equivalent. Mongo deleted everything at 90 days via expireAfterSeconds. Here data older than 14 days survives as hourly summaries, indefinitely.

Where Mongo was better: compression was immediate and unconditional, and typed schema fields meant no JSON key repetition — which is precisely the gap lever 1 closes.

Where This Lives

See also Communication & Data Flow.