A tiny database for sensors: key-value and time-series on the device, SQL on the gateway, and the same records on both, copied byte for byte.

AltSql is a project to build a very small database engine for connected devices. A sensor keeps its readings and settings in its own flash memory, reads them back to decide on the spot, and sends only what matters. Its gateway keeps the very same records and answers questions about them in SQL.

The idea. Put a very small database on the device that reads the sensors, and store its data so that the gateway’s database can use it as it is, byte for byte, with no translation step.

Before and After

Today a device and its gateway keep data in two different formats. A converter has to sit between them and turn the device’s key-value data into rows for the gateway’s SQL database. AltSql uses one format on both sides and takes the converter out:

Status: planned for the Advanced Beta.

The Alpha answers every query the same way, whatever state the device is in. A device in the field is rarely in the same state twice: its battery runs down, its link comes and goes, its flash fills and wears, and its data turns from routine to alarming. The Learned Query Optimizer (LQO) is a planned, optional layer that lets AltSql take its situation into account.

A temperature sensor on a machine takes one reading a second. When the machine runs hot, the fan has to come on right away, whether or not the plant’s Wi-Fi is working. In the Alpha demo this device runs the AltSql engine on 64 KB of flash with 1,048 bytes of engine RAM, and it makes that call by itself.

The run covers one simulated hour. Wi-Fi drops out from minute 20 to minute 35. Two gateways listen: gateway A receives every record, and gateway B, which stands in for a constrained link, receives only the minute summaries and the fan state.

Medicines and fresh food travel in refrigerated trucks, and many loads must stay between 2 and 8 °C for the whole trip. When the cooling fails, somebody has to know how long the load was warm and how warm it got, even if the truck is nowhere near a mobile signal at the time. In the Alpha demo a logger inside the load keeps that record itself. It runs the AltSql engine on 512 KB of flash with 920 bytes of engine RAM.

A soil sensor in a field usually runs on a battery and talks over a radio that carries a few dozen bytes a day. It cannot wait for a server to tell it when to water. In the Alpha demo the sensor decides for itself when to open the irrigation valve, and it reports to the farm office in one small radio message a day. It runs the AltSql engine on 32 KB of flash with 672 bytes of engine RAM.

The three case studies share one pattern. A device keeps its own data, decides for itself and talks over a link that is slow, costly or often down. Its gateway receives the same records the device wrote, so nobody has to maintain code that translates between the two. Machine monitoring, cold chain and agriculture came first because the Alpha demo covers them.

The pattern turns up in many more industries. None of the eight below has been tested, and none has a device in the Beta. This post sets out what each would ask of the engine, what already carries over from the demo, and what would be new work. It is a map for choosing what to try after the Beta, ideally together with people who build these devices.