From early 2023 until the end of 2025 I built the backend for a smart indoor farming platform. Sensors, pumps, lights, climate controllers, north of a thousand devices across grow rooms, all of them talking MQTT, all of them expecting an answer. I handed it over at the end of last year, and these are the notes I wish I’d had at the start of it.
The thing that shaped every decision was not the device count. It was that a single reading has to serve two completely different consumers. A control loop wants to know what the temperature is right now, in the next few hundred milliseconds, and it wants a straight answer. An agronomist wants to know what the temperature did across the whole season, correlated against yield, and is happy to wait ten seconds for it.
Those are not the same query. They are barely the same product. Trying to serve both from one store is the mistake that makes everything afterwards harder.
MQTT is not a queue, and Pub/Sub is not a broker
The devices speak MQTT because that is what devices speak. The rest of the platform runs on Google Cloud, so the readings need to land in Pub/Sub. In an architecture diagram that is one arrow. In practice it is the part of the system I spent the most time on, because the two halves disagree about three things.
Delivery. MQTT QoS 1 means at-least-once. Pub/Sub also means at-least-once. Compose two at-least-once systems and you have not got at-least-once. You have got a number of copies nobody has bounded. A device on a flaky cellular link republishes, the bridge redelivers, the subscription redelivers, and the same temperature reading lands four times. If your ingest path is INSERT, you have just invented four different temperatures.
Ordering. MQTT preserves order within a single connection. Pub/Sub preserves no order at all unless you set an ordering key, and ordering keys are not free: they pin the publish to a region and they serialise delivery per key. The instinct is to order everything. The correct answer is to order per device and never across the fleet, because there is no such thing as the fleet’s order. A thousand devices with a thousand clocks do not have a shared timeline, and pretending otherwise buys you a bottleneck in exchange for a fiction.
Liveness. MQTT has retained messages and last-will testaments. A broker knows the difference between a device that said nothing and a device that vanished. Pub/Sub has neither concept. Once you cross that bridge, a silent device is indistinguishable from a healthy one that had nothing to report, and in a grow room, a pump that stopped reporting is the single most urgent thing in the building.
That last one is worth sitting with. Most pipelines treat the absence of a message as the absence of information. For device fleets it is the opposite: absence is the highest-value signal you have, and it is the only one your message bus will never send you. It has to be manufactured, on a timer, by something that knows what should have arrived.
Idempotency is a schema decision, not a code decision
The fix for duplicate delivery is not deduplication logic. It is admitting that every reading has a natural identity and putting that identity in the primary key.
device_id + reading_tsThat pair is what the device meant. Everything else (the Pub/Sub message ID, the publish timestamp, the delivery attempt count) is an artefact of transport and tells you nothing about the world. Key on the device’s own idea of what and when, write with an upsert, and the fourth copy of a reading becomes a no-op instead of a data-quality incident. No dedupe cache, no bloom filter, no TTL to tune.
It also means the pipeline is safe to replay. When something downstream is wrong and you need to re-run a day, you re-run a day. You do not first have to reason about what re-running will duplicate.
And then the table stops scaling
Here is the part that cost me the most, and the part I would tell anyone building this to read first.
Spanner distributes a table by splitting it into contiguous key ranges. Each split is served by one replica set. That single implementation detail decides your write throughput, and it interacts badly with the most natural key you could possibly choose for time-series data.
If your primary key starts with the timestamp, then every write in the system, from every one of a thousand devices, targets the end of the key space. One split. One server. Every insert.
The failure mode is unpleasant because it does not look like a failure. Nothing errors. Latency creeps up under load and recovers when load drops. And the obvious remedy makes no difference at all. You add nodes, the hot split does not move, and throughput stays exactly where it was. You are paying for parallelism you have made it impossible to use.
The fix is to stop the key from being monotonic:
shard_id + device_id + reading_ts
where shard_id = hash(device_id) % NNow writes distribute across N ranges, adding capacity does something, and reads for one device are still a single seek because the shard is derived from the device. You always know which one to look in. What you give up is cheap global range scans over time: “every reading in the last hour, all devices” now touches every shard. That query belongs in the analytics store anyway, which brings us back to where we started.
Two stores, two jobs
The split at the end of that first diagram is the whole design.
Spanner holds current state. One row per device. The most recent reading, the last command acknowledged, whether the thing is considered alive. It is small, it is bounded by the number of devices rather than the passage of time, and it is strongly consistent, which matters, because a control loop deciding whether to run a pump cannot be reading a stale replica.
BigQuery holds history. Append-only, never read by a device, never in the path of a control decision. This is where the “what did it do all season” questions go, and it is fine for them to take seconds, because nothing physical is waiting on the answer.
Keeping those two stores strictly separated is what stops one from ruining the other. The write path never grows unbounded. The analytics path never contends with a decision that has a deadline. The temptation, always, is to answer one dashboard query from the operational store because it is right there and the data is already correct. Doing it once is free. Doing it habitually is how the control loop starts timing out during reporting hours.
What I would tell someone starting this
Write down which store answers which question before you write any schema. Not after. Almost every scaling problem I hit downstream traced back to a query that was being served by the wrong half of the system.
Treat the bridge as a component, not a config. MQTT-to-Pub/Sub is not plumbing. It is where delivery semantics, ordering and liveness all get decided, and those decisions are extremely expensive to revisit once a thousand devices depend on them.
Assume every message arrives more than once, and make that boring. Put the device’s own identity in the key and stop thinking about it.
Look up how your database distributes writes before you choose a primary key. For Spanner it is key ranges, so monotonic prefixes are a trap. Every distributed store has an equivalent detail. It is never in the getting-started guide and it always decides your ceiling.
Build the timeout that notices silence on day one. Every other alert in the system fires because something sent you bad news. That one fires because nothing sent you anything, and on a fleet it is the alert that matters most.