AltSql DB 0.3 Adds Secondary Indexes and Statement Savepoints: A Lookup on a Million Rows Ran 198 Times Faster Than a Full Scan

AltSql DB 0.3 adds the two things a gateway database needs once it holds real data. Tables get secondary indexes, so a question about a column other than the key no longer reads the whole table. And a statement that fails inside your transaction now fails on its own, while the transaction goes on.

Both come in the v0.3.0-alpha release on GitHub, open source under the Apache License 2.0. As with 0.2, every number here was measured in simulation on a PC. Nothing has run on a gateway yet.

On a million rows, a question answered through an index took 0.7 ms. The same question with the index out of reach took 132.2 ms, reading every row. That’s 198 times faster, in one run on a shared cloud machine.

The altsql-db shell: inside a transaction, an INSERT is refused by a UNIQUE index, the transaction still commits the row before it, and EXPLAIN shows the next query reading an index A refused statement in altsql-db 0.3: taken back, and the transaction still commits.

Secondary Indexes

You make an index in SQL with CREATE INDEX, or from C with altsql_db_index_create. Both go to the same code. A table can have up to 8, each on up to 4 columns, and an index can be UNIQUE. The rows already in the table are indexed when you make it.

Every way of writing a row keeps the indexes up to date: the direct row calls, SQL’s INSERT, UPDATE and DELETE, DROP TABLE, and sync from devices. That’s the one write path 0.2 built, now doing the job it was built for. A table that devices fill through sync can have an index too, kept as each batch arrives.

The planner reads an index when it pins down more of the WHERE than the primary key does, and EXPLAIN says which one it picked:

EXPLAIN SELECT * FROM temps WHERE machine = 2 AND temp > 27.5;
index range|temps, index temps_machine, fixed: machine, range on temp

C code can walk an index without SQL too, with altsql_db_index_seek, and read each row as it would through the primary key.

A Failed Statement Fails on Its Own

In 0.2, if an UPDATE failed halfway through inside your transaction, say on a value its column couldn’t take, the whole transaction was spoiled. The only way out was a rollback. Now each statement inside a transaction runs under a savepoint. If it fails, everything it did is taken back, and the transaction carries on as if it had never run.

Copy on write makes this cheap. Pages from earlier commits never change in place, so the savepoint only keeps a copy of the pages this transaction had already written, the first time the statement touches one. When the statement succeeds, nothing is undone, and the file comes out exactly as it would have without a savepoint.

A test checks that last part to the byte. It runs the same statements on two files, and on one of them 761 more statements are made to fail and taken back. With a cache big enough to hold the run, the two files end up identical.

The cost lands on statements inside your own transactions. One-row INSERTs, a thousand to a transaction, ran 10% slower than on 0.2, and one-row UPDATEs 5% slower. A statement in a transaction of its own needs no savepoint at all.

How It Was Tested

The index test ran 72,000 random steps, with indexes made and dropped along the way. It asked 33,574 questions through the planner and again as full scans, and both gave the same rows every time. Along the way, a checker walked every index against its table 4,248 times.

The interface test from 0.2 now makes indexes both ways, in SQL and through direct calls, and the two files it writes still match byte for byte. Against SQLite, 100,000 random statements on tables with the same indexes gave the same answers. Power cuts at every write of a run with indexes and failing statements left the file at a commit every time, its indexes in step with its tables. And 56 deliberately planted bugs, 18 of them in the new code, were all caught.

What’s Next

That finishes the design AltSql DB started with. Next is its Beta: AltSql DB on a real Linux gateway, with devices running AltSql Core reporting to it for weeks, and the speed figures measured again on a quiet machine.

The code and Linux binaries are on GitHub. The live demo now keeps an index as the devices sync, and one of its preset questions reads through it. The AltSql DB page says more about the database.