Storing sensor time series: retention, downsampling, and rollups

Reference article · 6 min read

A meter that reports every fifteen minutes produces a manageable number of readings per year. The same meter reporting every second produces a far larger number, and an agency with tens of thousands of endpoints multiplies that again. Most sensor data programs start modestly, run on whatever database is already licensed, and work well until queries slow down, storage costs climb, or the historian reaches a tag limit nobody budgeted to raise.

The decisions that determine whether that happens are made early, and they are mostly about aging. How long do you keep full resolution readings, what summaries replace them, how are series labeled, and which engine holds the data. None of this requires exotic technology. It requires deciding in writing what questions the data must answer one, five, and twenty years from now.

Set retention from the questions, not from the disk

Sensor data usually serves four distinct purposes, and each justifies a different window.

• Operational troubleshooting. Needs raw resolution but only a short window, often days or weeks. Staff are looking at a specific transient, alarm, or trip.
• Billing, settlement, and regulatory reporting. Needs exact interval values, retained for as long as the record itself must be retained, with a clear audit trail of any corrections.
• Engineering and capacity planning. Needs hourly or daily aggregates across many years, not raw samples.
• Public reporting and long-term trends. Needs monthly or annual summaries.

Write a retention schedule for each class and have the agency records officer sign it. State records retention schedules, utility commission rules, and grant conditions all touch interval data, and the requirements differ by state and program and change over time. Confirm the governing schedule with your records officer or counsel rather than inferring it from what the system happens to do today.

Rollups: keep more than the average

A rollup is a precomputed aggregate at a coarser interval, written once and read many times. The common mistake is storing only the mean. If raw pressure samples are replaced by hourly averages, the excursion that mattered disappears. For each rollup interval, store several functions together: minimum, maximum, sum, count, and often first and last. Peak demand, pressure spikes, and short outages remain visible, and the count column tells you how much data the bucket actually contained.

A few details cause most of the rework:

• Bucket alignment and time zone. Align buckets to a fixed reference and store timestamps in UTC. Daylight saving transitions produce short and long local days, which is exactly when billing disputes appear.
• Gaps versus zeros. A missing reading is not a reading of zero. Preserve the distinction in both raw and rolled-up data.
• Late and corrected data. Meters backfill. If rollups are computed once and never revisited, they will silently disagree with the raw record. Define a recomputation window.
• Quality flags. Carry estimated, substituted, and validated markers forward into the aggregates.

Cardinality is the failure mode that surprises people

In most time series systems, a series is the combination of a measurement name and its tag values, and each unique combination carries its own index entry. Cardinality grows by multiplication, not addition. Systems usually fail here before they fail on raw volume.

The practical rule is that tags should be things you filter or group by, and should have a bounded set of values: site, asset class, feeder, device identifier. Anything with unbounded variety belongs in a field value or in a relational table joined at query time. Work order numbers, event identifiers, firmware build strings, and anything containing a timestamp are frequent offenders. Gateways and middleware sometimes attach their own tags automatically, so review what an integration actually sends before onboarding a new device fleet, not after.

Compression and storage layout

Time series engines compress well because timestamps are regular and consecutive values are similar. Delta encoding on timestamps and specialized float encoding on values do most of the work, and columnar layout makes long range scans cheap. You improve on that mainly by not fighting it: report on a regular interval where you can, avoid storing floating point precision the instrument does not produce, and encode repeated status strings as small integers with a lookup table. Expect vendors to quote compression figures from their own benchmarks. Ask instead for a test against a sample of your data.

Cold data does not need to sit on the same tier as hot data. Many designs keep recent raw readings on fast storage and move older raw data and long-horizon rollups to object storage, queried less often and more slowly.

Choosing between a historian, a relational database, a time series store, and a warehouse

• Control system historian. Strong at control context, alarm history, and plant displays. Licensing is often per tag, cross-system analytics are limited, and it is usually best treated as the system of record for operations with a one-way replica feeding analytics.
• Relational database. Reasonable at modest volumes, especially when readings are constantly joined to asset, customer, or work management records and staff already administer the platform. Use native time partitioning. It strains under high sustained ingest and multi-year scans.
• Purpose-built time series store. Earns its place when ingest is continuous, retention policies and downsampling should run automatically, and queries are mostly range scans over a few series.
• Warehouse or lakehouse. Best for multi-year analysis joined to other agency data. Watch per-query and scan-based pricing, and do not put alarm latency requirements on it.

Running two stores is normal: a hot operational store and a cold analytical one, with a documented boundary and a scheduled job moving data across it.

What to ask vendors and what to require in the contract

• Ask what the license actually meters: tags, series, devices, ingest rate, stored bytes, queries, or scanned bytes. Ask what happens when you exceed it mid-year.
• Ask for the documented cardinality ceiling and what the system does when it is reached, including whether writes are rejected or degraded.
• Ask how retention and rollup rules are configured, whether agency staff can change them without vendor involvement, and how late-arriving data is handled.
• Require full export of raw and derived data in an open, documented format at a stated rate, available on demand and at termination, with schema and rollup definitions included.
• Require that rollup function definitions and any estimation or substitution logic be documented, since those numbers may appear in regulatory filings.
• Require notice before any change to pricing units, and confirm that deletion on schedule is supported and logged.

Start by inventorying every stream with its interval, expected device count, and the purpose it serves. Write the retention schedule against those purposes, fix a tag naming convention before the first large integration, then load a representative year of real data and run your actual queries against it. That test settles most architecture arguments faster than a comparison matrix will.

HQT works with state and local agencies on data platform architecture, including time series storage design, retention and rollup strategy, and integration between operational systems and analytics environments.

Frequently asked questions