AltSql DB Keeps a Whole Fleet in One File and Reads by Key 5 Times Faster Than SQLite Through SQL

AltSql Core keeps a device’s readings and settings in its own flash and copies the same records to its gateway, byte for byte. A gateway that serves a whole fleet needs a database of its own, and AltSql DB is that database. When its design was written, this post set out what it would do and how it would be judged, before any numbers existed. The prototype, its live demo and its Alpha report are now done, and these are the numbers.

AltSql DB keeps every device a gateway serves in one file, a copy-on-write B-tree with two commit headers. A device’s payload is stored as the device wrote it, under the key (device, time, sequence number). Programs read it two ways: a direct path by key, with no SQL step at all, and SQL read with AltSql Core’s own expression parser and query engine, with plans that use the keys.

The live demo runs it in the browser: twelve devices running AltSql Core sync into one AltSql DB file, SQL shows the plan it chose, the direct path reads a device’s newest reading beside SQL, and a button cuts the power in the middle of a commit.

The AltSql DB demo: real code, simulated devices and file.

The Direct Path Against SQLite

The first test came before any SQL work: one million keys through AltSql DB’s direct path and through SQLite, set up at its best, each on a real file with real syncs. Keyed reads in the cache came out 5 times as fast as SQLite through SQL, 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 skips SQL as well: there AltSql DB reads 1.2 times as fast, short of the mark, and SQLite’s rowid table also scans faster.

Core’s Million Rows

The Alpha’s own test runs AltSql Core’s one-million-row benchmark on AltSql DB, on Core’s gateway copy and on SQLite. AltSql DB had to be no slower than Core’s gateway copy on any of the five queries, and it was faster on all five: grouping the million readings by hour took 161 ms, against 205 ms on Core and 304 ms on SQLite. SQLite was faster on three of them. A hundred devices running Core then synced a million readings into one file at 279,094 readings a second, 35.1 bytes of file per reading.

Tested Hard, in Simulation

The tests cut the power 3,354 times, three ways at each of 1,118 writes, and failed every file call in turn, 1,091 times: the file always opened at the last commit or the one under way. Three real Core devices synced through 623 power cuts, one at each write of the gateway’s sync session, with nothing lost and nothing applied twice. 75,093 random queries and 24,907 random writes gave the same answers on AltSql DB and on SQLite. The fuzzer found two bugs, both in reading damaged files, and both are fixed. 37 of 37 deliberately planted bugs were caught. With the AltSql Core gateway build it runs on, AltSql DB is 167 KB of code at -O2, about a sixth of SQLite’s 982 KB (computed from the measured sizes).

What Comes Next

Everything so far runs on a PC, and the speed figures come from a shared cloud machine. The Beta takes AltSql DB onto a Linux gateway with real devices reporting to it for weeks, brings one sync per commit where there are two today, and runs the speed tests again on a quiet machine. About AltSql DB