AltSql DB

AltSql DB is the next step of AltSql Core’s idea, taken to the gateway. Core keeps a device’s records in a log in its flash and copies them to the gateway byte for byte. AltSql DB gives a gateway that serves a whole fleet a database built for those records: every device in one file, values read and written by key without going through SQL, and SQL that uses the keys.

Where it stands. The Alpha is done: a working prototype, its tests and a live demo, with every figure measured on the code. Like the other modules, it runs on a PC so far, and the gateway’s file in the demo is simulated. The next step is a real gateway with real devices. AltSql Core stays as it is, and devices keep running it.

Try the live demo

Why the Gateway Needs It

Today a gateway keeps a copy of one device’s log in a file, and answers SQL by reading every record. That suits a gateway that mirrors one device. A gateway that serves a fleet needs more: one place for every device, lookups that go straight to a row, updates and deletes, and changes that land together or not at all. Without them, product teams fall back on SQLite and glue code that turns device data into rows, the converter AltSql exists to remove.

Devices running AltSql Core send their records, byte for byte, to a gateway running AltSql DB. In the gateway, one file holds one tree: readings keyed by device, time and sequence number, settings keyed by device and key, the gateway’s own data, and the sync state. Programs reach it by a direct path, by key, or with SQL that uses the keys. Each batch is one transaction. AltSql DB in the Alpha: one file for the whole fleet, a direct path and SQL.

What It Does

  • A fleet in one file. Every device a gateway serves, in one copy-on-write B-tree. Two commit headers, written in order, mean a power cut leaves the file as it was before a change or after it, with no journal and no write-ahead log. The tests cut the power 3,354 times, three ways at each of 1,118 writes, and the file always opened at a commit.
  • Records as they were sent. A device’s payload is stored byte for byte, under a key built from it: the device, the time and the record’s sequence number. A batch from a device is saved in one transaction, and a batch sent twice is applied once.
  • A direct path. Get, put, delete and ordered scans by key, with no SQL parsing or planning in between: one walk down the tree.
  • SQL that uses the keys. Everything AltSql Core’s gateway SQL does, plus primary keys, point lookups, lists and range scans by key, a time range across the whole fleet, UPDATE, DELETE, DROP TABLE, prepared statements and transactions across statements. On 75,093 random queries and 24,907 random writes, it gave the same answers as SQLite.

How It Relates to AltSql Core

AltSql Core stays the core, with its name and its code unchanged. AltSql DB is built on it, the way AltSql Ask is: it checks Core’s records with Core’s own checks, and reads SQL with Core’s own lexer and expression parser and runs it through Core’s query engine. Devices keep running Core’s sensor build, and a gateway that mirrors one device can keep using Core’s gateway copy. AltSql DB is for the gateway that serves a fleet. It doesn’t run on microcontrollers: on raw flash, Core’s log is the right structure.

How It Was Judged

  • First, the direct path. Keyed reads in the cache came out 5 times as fast as SQLite through SQL, set up at its best for the job, against a mark of twice. With the same keys, ordered scans ran at 0.9 times SQLite’s rate and transactions of 10,000 writes at 3.3 times, against a mark of 0.8. One gap stays on record: with whole-number keys, SQLite’s blob interface reads nearly as fast, and AltSql DB is 1.2 times its rate there; SQLite’s rowid table also scans faster.
  • Then, the Alpha. AltSql Core’s one-million-row benchmark ran on AltSql DB, on Core’s gateway copy and on SQLite. AltSql DB was faster than Core’s gateway copy on all five of its queries, the mark it had to meet; SQLite was faster on three of them. A hundred devices running Core synced a million readings into one file at 279,094 readings a second.
  • And the tests. Model tests, a crash matrix, every file call failed in turn, real Core devices syncing through power cuts, 45 minutes of fuzzing, and 37 of 37 deliberately planted bugs caught.

What Comes Next

The Beta takes AltSql DB onto a Linux gateway such as a Raspberry Pi, with devices running AltSql Core reporting to it for weeks and the power pulled at random. It also brings one sync per commit where there are two today, keys read backwards for the newest rows first, and the speed tests run again on a quiet machine. Like every AltSql module, the prototype is marked all rights reserved until a license is chosen. The roadmap shows where it sits, and device makers with a gateway in mind can get in touch.