3 ms·
Be sure to check out http://sqlite.org/src4/doc/trunk/www/design.wiki http://sqlite.org/src4/doc/trunk/www/design.wiki ... SQLite4 splits the SQL engine from th
by _wmd 14y ago
Be sure to check out http://sqlite.org/src4/doc/trunk/www/design.wiki http://sqlite.org/src4/doc/trunk/www/design.wiki ... SQLite4 splits the SQL engine from the storage engine and provides public APIs for both, including the ability to run the SQL engine on top of any key/value store that provides an ordered-map interface.
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.
- xradionut 14y ago"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)." Isn't this basically the same concept as a clustered index in MS SQL Server?
- _wmd 14y agoYep, although I'm not sure if clustering is also a hand-triggered process in SQL Server (like it is in e.g. PostgreSQL). In any case SQLite 4.x will do it automatically.
- Shorel 14y agoIn SQL Server that's defined at table creation time. In Oracle it's the same: defined at creation time, but you call it an Indexed Ordered Table (IOT). In MySQL all InnoDB tables are clustered (in that sense) because that's the only storage strategy of InnoDB.
- vinkelhake 14y agoThose who are interested in SQLite4's LSM and LevelDB might also find OpenLDAP's MDB: http://symas.com/mdb/ http://symas.com/mdb/ interesting.
- _wmd 14y agoBeware MDB isn't quite as general purpose as the other options, specifically feeding it large records can cause it fall back to placing records in pages of their own, which can be wasteful on storage. It's easy to work around (by splitting your records up) but this needs to be considered as a factor of your design.
- rogerbinns 14y ago> ... including the ability to run the SQL engine on top of any key/value store ... Note that you can do that today with the virtual tables interface in SQLite 3. For example several people have written virtual tables where the underlying data is in a csv file, I wrote one that backends into CouchDB etc. It is a little ugly in that SQLite semantics bleed through the interface (eg BestIndex method) but does work. http://www.sqlite.org/vtab.html http://www.sqlite.org/vtab.html