Data

The Service Nobody Meant to Build

An Undocumented Dependency

Most IoT systems I inherit contain a small service that nobody set out to build. It subscribes to nothing, publishes nothing, and does one job: it queries the time-series database for measurements, queries the relational database for what those measurements are about, and glues the two together in memory so a dashboard can display them.

Nobody scoped it. It was written in an afternoon to unblock a demonstration, and it is now the single point of failure for every screen in the organisation. It has no tests, its author has moved on, and it is the reason a new report takes three weeks instead of an hour.

That service is not a mistake anyone made. It is the inevitable consequence of a storage decision taken in the second week of the project, when somebody reasonably observed that sensor readings are timestamped measurements and put them in a database built for timestamped measurements. Everything that followed was forced.

Read the Questions Off the Whiteboard

The way to avoid it is to look at what people will actually ask, rather than at what shape the incoming data has. Those two things point in different directions and only one of them matters.

The incoming data is uniform and boring. Device, time, value, some transport metadata. Every candidate engine stores it and none of them struggle.

The questions are not uniform at all. Which sites went over budget this quarter. Whose meter is this. Was this sensor recalibrated before or after the anomaly. What did consumption look like either side of the plant upgrade. Which of these units are still under warranty and which are due for a battery. What do we invoice this tenant.

Look at the verbs in those. Every single one puts a measurement next to something that is not a measurement: a tenant record, a site hierarchy, a maintenance log, a tariff table, an installation date. The workload is not time-series data with some reference data attached. It is a relational workload where one of the tables happens to be enormous and ordered by time.

Choose an engine that cannot express a join and you have not avoided the join. You have relocated it into code you now maintain.

The join does not disappear when the database cannot do it. It reappears as a service, with a deployment pipeline, an on-call owner and a bug backlog, and it is the least documented thing in the system.

Throughput Is Not Your Problem

The obvious objection is that a general-purpose database will not keep up. It is worth doing the arithmetic on that, because the numbers are not close and almost nobody checks.

Take a device reporting every quarter of an hour, which is a fairly chatty configuration for anything on a battery. That is 96 messages a day.

Devices	Messages per day	Sustained write rate	Rows after 3 years
200	19,200	0.2 per second	21 million
1,000	96,000	1.1 per second	105 million
5,000	480,000	5.6 per second	525 million
25,000	2.4 million	28 per second	2.6 billion

Twenty-five thousand devices is a genuinely large private deployment, and it is twenty-eight writes a second. A modest server handles that while mostly idle. The benchmark arguments that decide these purchasing conversations are fought at rates four orders of magnitude higher, against workloads that have nothing in common with yours.

There is a second-order effect worth knowing: storing one row per decoded field rather than one per message multiplies these figures by five or six. Even then the largest line stays comfortably inside what one well-tuned relational server absorbs without special handling.

And unlike an application log, this workload cannot surprise you. A battery device that transmits ten times more often is no longer a battery device, so the energy budget puts a hard ceiling on the write rate that no amount of commercial success can breach. Capacity planning here is arithmetic rather than forecasting.

What the Time-Series Extension Buys

Adding a time-series extension to a relational database closes the gap that used to justify a separate engine, and it does so with four mechanisms that each replace something you would otherwise write and then own.

The first is transparent partitioning by time. The table behaves as one table to anyone querying it, and underneath it is split into chunks, each carrying its own indexes. A query constrained to last week touches one chunk instead of four years of rows, and index maintenance stays cheap because no single index ever grows to the size of the whole dataset.

The second is tiered storage. Recent data sits in a row-oriented form, which is what you want for fast inserts and for late-arriving messages that need to slot into the past. Once it cools past an age you set, it converts to a column-oriented form, which compresses hard and makes aggregate queries markedly faster, because averaging one field stops requiring a read of twenty. This is a declared policy rather than a scheduled job somebody has to keep alive.

The third is incrementally maintained aggregates. Every deployment eventually wants raw data for recent troubleshooting, hourly buckets for the year and daily ones for the decade. Doing that by hand means a downsampling job, a backfill script and a class of bug where the aggregate silently disagrees with the source. Continuous aggregates recompute only the buckets that received new rows, which makes the tier a definition rather than a pipeline.

The fourth is retention as a policy. Chunks older than a set age are dropped on a schedule, so disk usage is flat and predictable instead of climbing quietly until it becomes an incident at an inconvenient hour.

None of that helps if the tables are the wrong shape, which is the part vendors do not cover.

One Table Cannot Be Both Shapes

The instinct is a single readings table with a column per measurement. It works beautifully for one device model and degrades from there, because the second model brings fields the first does not have and the columns that do not apply fill with nulls. By the fifth model the table has drifted into something with a hundred and forty columns, most of them empty for most rows, and every addition is a schema migration on a live table in production.

The shape that survives splits on one question: does this field exist on every message regardless of what sent it?

Fields that always exist go in a wide table with real typed columns. Time, device, signal strength, link quality, data rate, sequence number, frequency, how many gateways heard it, the raw payload, and a derived count of messages missed since the last one. These are properties of the transport rather than of the sensor, they are the same for a water meter and a door contact, and they benefit from being columns because you filter and aggregate on them constantly.

Fields that depend on the device go in a narrow table with four columns: time, device, field name, value. A message carrying six measurements becomes six rows. A device model nobody has seen before needs no schema change whatsoever, because its decoder simply emits field names that have not appeared before and they become rows like any other.

The trade is honest and worth stating. Narrow costs you at query time: reconstructing a message as an object means pivoting, and a chart wanting three measurements together does more work than it would against columns. In exchange you never migrate a live table to onboard a device type, and after the third device model that trade has already paid for itself several times over.

One failure mode here is nasty enough to design against explicitly. Something decides which fields are columns and which become rows, and if the write path and the query path disagree about that even slightly, a field written to one table and looked for in the other yields no error, no exception and no empty result. It yields a panel that has always been blank, which nobody investigates because it has never worked and everybody assumes it is waiting on something. Keep that mapping in one place, apply it at write time, and have the query layer read the same definition rather than a developer's recollection of it.

The Argument That Has Nothing to Do With Features

Set capability aside for a moment, because the second half of this decision is about time.

IoT infrastructure gets specified once and then runs, frequently on sites nobody enjoys revisiting, for longer than anyone plans. Over that life, the data is the only irreplaceable component. Hardware is replaceable, dashboards can be rebuilt in a fortnight, the ingest can be rewritten. Four years of readings cannot be recreated by any amount of effort, and any migration that has to move them carries a risk that a dashboard migration does not.

That asymmetry argues for boring, portable, widely understood storage, and it argues against anything with a history of breaking compatibility between major versions. Vendors who have done that have generally had good technical reasons, and the reasons do not refund the month their users spent migrating. Choosing a component that has been stable for two decades is not conservatism, it is declining to pay that cost on a schedule somebody else controls.

The operational side is the same argument in a different form. Whoever inherits this system already knows how to back up, restore, replicate and tune a mainstream relational database, and so does any contractor they hire. A specialist engine adds a body of operational knowledge that exactly one person has, and that person will eventually leave.

When a Dedicated Engine Is the Right Call

None of this generalises to every workload, and there are three situations where I recommend against the approach above.

Genuinely high-rate data is a different discipline. Vibration or acoustic sampling in kilohertz, or per-second telemetry from thousands of mains-powered machines, is where purpose-built engines earn their reputation and a general-purpose one starts to hurt. The useful test is whether your devices are on batteries: if they are, you are not in this case, because the energy budget will not permit it.

Infrastructure metrics belong in a monitoring stack rather than in the sensor store. Server load, gateway memory and queue depths have their own tooling that already solves this, and mixing them into the readings table buys nothing and complicates retention.

And where the organisation is already committed to a cloud provider's managed services, running storage yourself may be the longer road rather than the shorter one. The cloud integration route is often more sensible than fighting the platform the rest of the business runs on.

There is a fourth case that is not technical. If something is already in production, the team understands it, and it answers the questions people actually ask, leave it alone. Migrating a working system to gain a join you have been living without is a project, not an improvement, and it will consume the quarter that something else needed.

Two Decisions That Get Deferred and Should Not

Storage is not only an engine choice, and the two questions that get postponed are the ones that cost most later.

Retention has to be a decision rather than a default. Most deployments want full resolution for weeks, hourly aggregates for a year or two and daily beyond, with exceptions for anything feeding a bill or a compliance obligation, which is kept intact. Deciding this while designing is a policy. Deciding it when the volume becomes a problem is an incident, and the pressure to just delete something is exactly when mistakes happen.

The other is recording what a reading means. A value of 21.4 with no unit, no scale factor and no record of which calibration was in force is worth substantially less in year three than it was in week one, and it cannot be reconstructed afterwards. Capturing the sensor's configuration, its calibration history and its installation record at the time of ingest costs almost nothing and is the difference between a dataset you can defend and one you can only look at. It also happens to be the strongest practical argument for keeping the relational half in the same database as the measurements, because that is what makes the metadata a join rather than an archaeology exercise.

What I Provide

I build the storage layer and the ingest path into it: the schema, including the split above and the field mapping that keeps it honest, the aggregate tiers sized against the queries people actually run, retention that matches the obligations rather than the disk, and dashboards reading the database directly instead of through a service somebody has to keep alive.

Where an existing store is doing its job, my advice is to keep it and spend the budget on something that is actually hurting. Where it has been outgrown, or is heading into a version migration nobody has planned for, I run the move, historical data included, which is the frightening part and is mostly a matter of doing it in a rehearsed order with a rollback that has been tested rather than described.

The schema documentation, the ingest source and the migration runbook are handed over, because the value of this layer is measured in years and it will outlast the engagement.