A thousand devices reporting every few seconds is not a large system by anybody’s standards, but it has one property that changes how you design for it: the write rate never drops and the table never shrinks. There is no quiet period. Whatever you build has to work while ingestion is happening, because ingestion is always happening.
The three decisions that mattered were all about the table itself rather than the queries against it. I got the first one wrong initially, and it is the one I see most often.
Retention is a schema decision, not a scheduled job
The obvious way to keep a telemetry table from growing forever is to delete from it on a schedule. Every night, remove anything older than the retention window.
This is the most expensive query in the system, and it gets more expensive every day.
A DELETE has to find every matching row, mark each one, and leave the index entries behind for a later pass to clean up. In Postgres that later pass is vacuum, and vacuum is now competing with a write path that never pauses. Meanwhile the space is not returned, the table is more fragmented than it was, and the deletion window grows in proportion to how much you are deleting. You have added a heavy nightly job whose cost tracks your success at collecting data.
Partitioning inverts the whole thing. Partition by time, one child table per period, and retention becomes detaching a partition and dropping the table. No row is examined. The indexes go with it because they belong to that child. There is no tombstone, no vacuum debt, and the cost does not depend on how many rows were in there.
The catch is that you cannot retrofit this cheaply. Converting a large, live, unpartitioned table into a partitioned one means moving the data, and doing that without downtime is a genuine project. Partitioning is the decision you want to have made before the table got interesting, which is exactly when it feels like premature optimisation.
Keep several resolutions, and know which one answers which question
The second decision is that “keep the data” and “keep every reading” are not the same requirement, and conflating them is what makes retention windows a fight.
Two completely different questions get asked of telemetry. A control loop asks what the temperature is right now, over a window of seconds, and needs every reading. A person asks what the temperature did across the season, correlated against something else, and does not care about individual readings at all. Serving the second question from raw data means scanning millions of rows to produce a chart with a few hundred points on it.
So you keep tiers. Raw readings for a short window, because that is all the live path needs. One-minute aggregates for around a year. Hourly aggregates indefinitely, because they are small enough that “indefinitely” costs nothing. Each tier stores min, max, average and count rather than just the average, because an average alone cannot be re-aggregated correctly and cannot answer “did this ever spike”.
The rule that keeps this honest is that a query picks one tier. Never stitch raw and rolled-up data together in a single result to cover a window that straddles the boundary, because the two halves have different statistical properties and the join looks fine until someone notices the variance collapses halfway across the chart.
Late data is the part that breaks it
Everything above is standard and would apply to any time-series workload. This next part is specific to devices, and it is what made rollups harder than the textbook version.
Devices go offline. A device on a flaky link buffers its readings locally and replays them when it reconnects. So a reading that arrives at 15:00 may be timestamped 09:00, and if your one-minute rollup for the 09:00 window ran at 09:05, that rollup is now wrong.
This rules out the obvious implementation. You cannot compute a rollup once, append it, and treat it as finished, because the input to that window is not closed when the window closes. It is closed when you decide it is, and devices do not consult you.
What works is making rollups recomputable rather than append-only. Each rollup row is keyed on the tier plus the window plus the device, and writing it is an upsert. When late data lands, you mark the windows it touched as dirty and recompute exactly those. The rollup becomes a projection of the raw data for a window, derived on demand, rather than an event you emitted once and hoped was right.
That has a consequence for retention: you cannot drop raw data for a window until you are confident nothing more is coming for it. The raw retention window is not a storage decision, it is a bet on your worst-case device outage. Ours needed to be comfortably longer than the longest offline period we had actually observed, which is the sort of number you only learn by running the thing.
There is also a smaller trap in the same area. If your rollup job selects “everything since the last run”, late data that arrives with an older timestamp is never selected, because it is not new by the clock the job is watching. The job has to be driven by ingestion time to find the work, and by reading time to decide which windows to recompute. Using one clock for both loses data silently, which is the worst way to lose it.
What I would tell someone building this
Partition before you need to. Retention on an unpartitioned table is a nightly job that gets slower forever. Retention on a partitioned table is a metadata change. The conversion cost between those two states is the reason to decide early.
Store count and min and max, not just the average. An average cannot be re-aggregated into a coarser tier without weighting, and it cannot answer the question people actually ask about telemetry, which is whether something ever went out of range.
Assume every window will be reopened. If a device can buffer, then no window is final, and any rollup that cannot be recomputed for a specific window will eventually be quietly wrong.
Separate the clock that finds work from the clock that defines the window. Ingestion time tells you there is something to do. Reading time tells you where. Conflating them is how late data disappears without an error.