technology

AltSql Is a Native Hybrid of Key-Value and SQL, From the Sensor to the Gateway

AltSql is a native hybrid of key-value and SQL, in the engine and in the database: one small engine and one set of records, from the sensor to the gateway, with nothing to translate in between. Everything else AltSql offers follows from that.

The engine is the software. The database is the data the software keeps. The engine is code: it writes records, finds them again, keeps them safe through a power cut and answers questions about them. The database is the records themselves, stored in order, usually in one file. In AltSql the hybrid lives in both. The engine is one piece of code with two ways in, and the database is one set of records that both ways reach.

The device keeps the key-value door and its own records in flash. The same records go byte for byte to the gateway, where get/put and SQL both reach one store with every record filed under a key: one set of transactions, the same rules through either door, one copy of the data. One store with two doors: the device keeps the key-value door, and the gateway opens both onto the same records.

Two Ways of Getting at Data

Key-value is the direct way. You know exactly what you want, say device 17’s latest reading, so you hand over the key and get the record back. There’s nothing to interpret. It’s fast and needs very little code.

SQL is the asking way. You describe what you want, say every device that ran hot last Tuesday, averaged by the hour, and the engine works out how to find it. It can answer questions nobody planned for. The price is extra machinery between you and the data, which has to read your question and plan how to answer it. Every request pays for that in time. The engine pays for it in code.

Most setups pick one, or run two systems side by side: a key-value store for speed and a SQL database for questions. Picture two warehouses, one set up for grabbing things fast and one for searching, with a truck driving between them all day to keep them the same. The truck is the expensive part. Data gets copied, the copies drift apart, a crash can leave them disagreeing, and someone has to write and look after the code that keeps them in step.

What Native Hybrid Means

AltSql is one warehouse with two doors. Get/put walks straight to the shelf. SQL goes through the front desk first. Both end up at the same records, under the same rules. Native means the two doors were designed together from the first line of code:

  • One store. Every record is filed under a key in one sorted store. A get finds its record in one walk down it, and SQL turns a question into the same walks and scans.
  • One set of transactions. A get/put and a SQL statement can sit in the same transaction, so related changes are saved together or not at all.
  • The same rules through either door. The same code checks types and keys whichever door a change comes through, and it will keep indexes the same way, so the doors can’t drift apart.
  • One copy of the data. Whatever goes in through one door is there for the other the moment it’s committed. Nothing is copied, so there’s nothing to keep in step.

The hybrid runs end to end. A small device keeps the key-value door, which is all a sensor needs and small enough for its chip. The gateway opens both doors onto the same records, and a record keeps its format the whole way.

What the Hybrid Gives You

  • No translator. A common setup today packs readings into a message on the device, unpacks the message at the gateway and inserts the fields into a database. Every one of those steps is code someone writes and a place for bugs to hide. With the hybrid, the device writes a record once, and the gateway files that same record under a key and answers questions on it where it lies. Nothing gets converted on the way.
  • More autonomy at the endpoint. A device keeps its own records safe through power cuts and dropped links, and works on them without asking anyone. In one simulated run the power was cut 400,000 times, and nothing saved was lost.
  • Smart functionality at the edge. A device’s records are already in a shape the engine can work with, so the device can act on its own data, such as comparing a reading with the last hour’s and raising an alarm, with no round trip to the cloud.
  • Less bandwidth. Records travel in the compact form they were written in: a reading takes 26 bytes as an AltSql record and 53 as JSON. The gateway confirms a whole batch with one number, and a question can go out to the devices so that only the answers come back.
  • Speed where it counts. Routine work, like reading a device’s latest value, goes straight to the record by key and skips the SQL machinery. In the Alpha, a read by key on the gateway ran 5 times as fast as SQLite answering the same read through SQL, measured in simulation. Questions still get SQL, on the live data, with nothing exported first.
  • Small. It’s one engine instead of two systems plus the code that ties them together. The key-value side of a sensor takes about 15 KB of code as compiled for a Cortex-M4, and the gateway side, with both doors, is about a sixth of SQLite’s code (computed from the measured sizes).

What It Isn’t

SQL on top of key-value isn’t new. Big systems like CockroachDB and TiDB are built that way, and SQLite’s own SQL runs on a tree of keys and values. AltSql doesn’t claim the idea.

It isn’t NewSQL either. NewSQL spreads one database across many servers so it can take huge loads. AltSql scales down instead, into the program on a device or a gateway, with no server at all. “Embedded database for IoT” is the right shelf, next to SQLite and LMDB.

Take the hybrid away and what’s left is a smaller SQLite, and SQLite is free and everywhere. What AltSql claims is the native hybrid made very small, both doors designed together from day one and working on the same records under the same rules. That part is hard, and it’s where the real value is.

The engine and the database pages go one level down, the modules take the hybrid to particular jobs, and the live demos run it in your browser.