SQLite 3.7.16 Released

In the new design a row's primary key directly contributes to its physical key, meaning lookups and iterations on the primary key involve only one scan (rather than an index scan followed by random lookups in SQLite 3.x/most DBMS). Basically the old "OID" feature on steroids, except now you don't have to design for it at all. This also means data is inherently clustered by primary key, providing further performance benefits.

The new storage engine in 4.x is exciting by itself, it's a log structured merge tree, similar to LevelDB but using the old SQLite 3.x B+tree instead of SSTables for the disk segments. Additionally it is possible to incrementally compact older generations inline as part of each write, rather than mandatorily having some unpredictable background compactor thread like LevelDB. Similar to 3.x and unlike LevelDB, the storage engine also supports interprocess locking, meaning it's trivial to write software with concurrent readers across multiple processes and a single writer.

The possibility of mixing a key/value store and SQL store, including having single transactions affecting both stores, is really exciting. The SQL engine is still a work in progress, but it should be awesome when it's ready.

I run with foreign key support disabled, because the referencing ("child") rows might get inserted before the referencing ("parent") rows (I don't control the order of inserts).

Now I can just run a periodic scrub job with this command to clean out rows which didn't successfully transition from "violating foreign keys" to "satisfying foreign keys", instead of writing my own code.

Neat. SQLite is an awesome database library.