Work on SQLite4 has concluded
sqlite.org
sqlite.org
> This directory contains source code to an experimental "version 4" of SQLite that was being developed between 2012 and 2014.
> All development work on SQLite4 has ended. The experiment has concluded.
> Lessons learned from SQLite4 have been folded into SQLite3 which continues to be actively maintained and developed. This repository exists as an historical record. There are no plans at this time to resume development of SQLite4.
I'd be interested to hear why they stopped. Presumably reimplementing SQL on a KV store was seen as not worth it, when applications that are satisfied with an embedded KV store backend (which is much faster and simpler to write!) already have many options.
I think part of this is because of a fundamental limitation of sqlite that it's an embedded database that has to persist data on disk at all times: The design of LSM trees works well with databases with a resident in-memory component because it's an approximation of just dumping every new thing you see at the end of an unordered in-memory array. This is as opposed to a data structure like a b-tree where you have to /find/ exactly where to put the data first, and then put it there. This finding bit means you're doing a lot of random access in memory, which is thrashing all of your caches (CPU / disk etc). LSM trees avoid this thrashing by just dumping stuff at the end of an array. However this means you have to scan that array to do lookups (as opposed to something easier like binary search). Then as your array gets big, you merge and flush it down to a lower "layer" of the lsm tree which is slightly bigger and sorted. And when that one fills, you flush further. And these merge-flushes are nice big sequential writes so that's nice too.
Anyway, with SQLite, the highest layer of your LSM tree would probably (this is conjecture) have to be on disk because of the way that there is no server component, versus in an in-memory system it'd probably be in your L2/L3 cache or at least your main memory. So this could be one reason why that model didn't work out as well for them.
Regarding the LSM engine, you can find all the relevant implementation details here: https://sqlite.org/src4/doc/trunk/www/lsm.wiki#summary
> The in-memory tree is an append-only red-black tree structure used to stage user data that has not yet flushed into the database file by the system. Under normal circumstances, the in-memory tree is not allowed to grow very large.
SQLite: The Database at the Edge of the Network with Dr. Richard Hipp
> The in-memory tree is an append-only red-black tree structure used to stage user data that has not yet flushed into the database file by the system.
Hmm, ok, so this contradicts my assumption. Actually, now that I think about it, other LSMs like rocksdb / leveldb work like this too (library-like model with some in-memory component when you "open" the database).
Anyway, without diving into the details of the code, there's other technical decisions that would affect this stuff.
One thing is how big your in-memory structure is (in relation to available memory and insertion workload) and how often you flush to disk is a key thing. Another thing is what your LSM tree looks like - aside from the data structure, how many tiers/levels you have is a big thing. I assume some of these are configurable parameters. E.g. rocksdb has an enormous set of parameters that handles this stuff. It's also annoying to tune.
I found this benchmark here that is illustrative: https://sqlite.org/src4/doc/trunk/www/lsmperf.wiki
The first graph is underwhelming, but when you adjust the buffers (look at the last graph) ~250k writes / second constant regardless of database size (this is why you want an LSM tree) is darned good! And this is on a spinny drive, not an SSD. Their "large" buffer sizes aren't even that large IMHO.
So maybe his mention that the LSM storage was underwhelming was overblown :-) I don't know.
Another difference is with other LSM-based systems that aren't just key-value, it's usually in the context of column stores: you keep a separate LSM for each column family (could be 1-n columns). But I can't think off the top of my head how this would cause a difference. Perhaps in how reads happen - the query engines work quite differently.
Anyway, my talk is cheap, I'm just guessing here, actually doing the analysis is the hard work :-) Also, I'm something of an amateur currently, so take my words with a grain of salt. Anyone else have any ideas re: this?
[0] https://www.fossil-scm.org/xfer/doc/trunk/www/fossil-v-git.w...
One big difference is that fossil includes wiki and ticketing.
Philosophy differs: Fossil intentionally limits "distributedness". For example, fossil push/pull always transfers all branches with their name. Private branches are considered an anti-feature.
Minor differences are the licence (GPL vs BSD) and the data store (files vs sqlite). Under some circumstances these details matter, but not for the majority of developers.
The rest is not significant, imho. For example, "Lots of little tools vs stand-alone executable". Who cares? In both cases you type "$VCS $SUBCOMMAND $PARAMETERS" commands.
Stand-alone executable is pretty significant. Git is available on most servers -- fossil is not. If it's packaged in your OS, it's often outdated. Stand-alone kinda makes up for this as you can easily get the latest version with a wget & chmod on any computer, on all 3 platforms.
As for sqlite, it is an astoundingly solid rdbms that is well battle-tested. I consider that a big difference.
He refers to himself as D. Richard Hipp.
The weird error may have been mine.
I also hoped for big win on the insertion-heavy loads, and I also haven't succeed in that. The problem is that every insert statement must read back something from DB to verify DB state against schema for correctness. As reads in LSM are slower, the net win is either absent or negligible. I have to say I wrote "must" in sentence above because you sometimes can get away without reading back, but not always. In the end, worst case scenario is always "read and write", not just "write".
But!
I devised a scheme to lay out layers' data so that they are as contiguous as they can be. Or get a very good approximation to that contiguousness, basically (O(1) "pages" per level). Thus contiguous reads got very high performance and beat old storage on read scheme, despite the need of level merging, etc.
https://www.sqlite.org/inmemorydb.html
Perhaps the key (ha!, pun) is that you're talking about using RAM _and_ disk with the RAM being for caching/fast access that eventually hits the disk. Whereas, I think, in this case sqlite is either on the disk, or in RAM. There is no multiple tiers.
Correct me if I'm off here. Thanks.
This is closer to what I mean but not quite. Specifically, in-memory (or main-memory) is a technical term that talks about a specific type of database that focuses on doing operations /mostly/ in memory, with some spillover to disk as necessary, but only as an edge case. You usually then handle persistence with a sequential transaction log, perhaps in NVDIMM storage if you have some. This is in contrast to other systems where the memory is a buffer, but the "actual stuff" happens on disk. There is of course many hybrid schemes - it's really more of a spectrum. Some examples: SAP HANA, ArangoDB, MapD, MemSQL, MS SQL Server Hekaton.
There's also a lot of techniques that come with this style of thinking - often these are column stores, and often since these databases don't really hit disk, the bottleneck is moving data in and out of memory to the CPU caches, and sometimes just CPU speed. Often these use lightweight compression and SIMD instructions to tackle those problems.
So SQLite's "in-memory" database scheme doesn't quite count as something like this, it's more of a disposable database. But that's ok - it's not bad, it's just a different thing.
> Whereas, I think, in this case sqlite is either on the disk, or in RAM. There is no multiple tiers.
See the thread above - I kind of assumed this was the case, but like you mentioned, it looks like with the LSM implementation it does kind of sort of have a memory component - it lasts until you close the database "connection", and gets flushed often.
Which, while a totally alien concept in the modern software world, is actually a pretty cool thought.
(I'm sure under the hood bugs are getting fixed and all)
Look how Open Office development charged after the introduction of Go OO, or MySQL after MariaDB, or more generally the entire smartphone market after the introduction of the iPhone. Competition is good.
That said, Dr. Hipp is known to be passionate about SQLite. In my previous examples the software was pretty much neglected before the competition came, which is certainly not the case with SQLite.
Rewriting SQLite in Rust, or some other trendy “safe” language, would not help. In fact it might hurt.
Prof. Regehr did not find problems with SQLite. He found constructs in the SQLite source code which under a strict reading of the C standards have “undefined behaviour”, which means that the compiler can generate whatever machine code it wants without it being called a compiler bug. That’s an important finding. But as it happens, no modern compilers that we know of actually interpret any of the SQLite source code in an unexpected or harmful way. We know this, because we have tested the SQLite machine code – every single instruction – using many different compilers, on many different CPU architectures and operating systems and with many different compile-time options. So there is nothing wrong with the sqlite3.so or sqlite3.dylib or winsqlite3.dll library that is happily running on your computer. Those files contain no source code, and hence no UB.
The point of Prof. Regehr’s post (as I understand it) is the the C programming language as evolved to contain such byzantine rules that even experts find it difficult to write complex programs that do not contain UB.
The rules of rust are less byzantine (so far – give it time :-)) and so in theory it should be easier to write programs in rust that do not contain UB. That’s all well and good. But it does not relieve the programmer of the responsibility of testing the machine code to make sure it really does work as intended. The rust compiler contains bugs. (I don’t know what they are but I feel sure there must be some.) Some well-formed rust programs will generate machine code that behaves differently from what the programmer expected. In the case of rust we get to call these “compiler bugs” whereas in the C-language world such occurrences are more often labeled “undefined behavior”. But whatever you call it, the outcome is the same: the program does not work. And the only way to find these problems is to thoroughly test the actual machine code.
And that is where rust falls down. Because it is a newer language, it does not have (afaik) tools like gcov that are so helpful for doing machine-code testing. Nor are there multiple independently-developed rust compilers for diversity testing. Perhaps that situation will change as rust becomes more popular, but that is the situation for now.
You can just use C tooling with Rust, generally; I know kcov has worked for a long time, but it looks like easy gcov support does exist: https://github.com/kennytm/cov
https://www.infoq.com/news/2009/08/Porting-SQLite-to-.NET-in...
I honestly hope against hope that you’re being sarcastic.
[0] https://www.cockroachlabs.com/blog/sql-in-cockroachdb-mappin...
Correction: CockroachDB is based on a KV store, it's a full SQL RDBMS on top of this KV store.
Presto is a distributed SQL query engine for big data, so basically the complete opposite of SQLite, though it often gets used in federation scenarios, as does SQLite.
An interesting anecdote is that the team working on what would become osquery (https://osquery.io/) asked if they could reuse the SQL parser from Presto. We get that question a lot, and after explaining that the parser is the easy part (semantic analysis and execution is the real work), I determined that what they really wanted were SQLite virtual tables: https://sqlite.org/vtab.html (and those worked out great for them)
I created a logsql.py plugin for skybot, it just logs to the DB instead of to text files. Is that something you'd be interested in merging back in?
To internalize it better I invented a "project" for myself - http://thredis.org/ which was (and is, but I'm not maintaining it) a Redis/SQLite hybrid. It was fun to hack on.
Another invaluable source of DB internals information is PostgreSQL. Both projects have amazingly well written and detailed comments.
The number of oddball, often critical, places where I've found SQLite being used would defy belief. As far as I can tell, the "expected" place for SQLite to work seems to be almost anything that's not your normal dB driving some web-based CRUD app...all kinds of embedded systems, easy to manipulate in-memory scratch pads for bioinformatics, lots of data analysis tools in mobile communications.
It's so good, and so obvious, that I think sometimes it makes other tools that might be simpler fits for many use-cases less likely to be used, like leveldb.
That can totally be handled with SQLite.
For bigger load, have a worker that does the writes with a queue.
I'm French too and I read your blog sometimes ;-)
Is the following what you suggest:
You create an index, and the index creation takes 30 seconds. Then instead of writing directly to SQLite (which won't work since the index creation blocks other writers) you suggest to store the write in a queue (for example another SQLite database for durability), and have a worker apply the write to the main database when the index creation is done?
I am beginning to suspect that MySQL, PostgreSQL, DB2, Oracle, BigTable, and others allow one to get so far with the wrong architecture, that maybe some even very experienced programmers believe that to go faster they have no choice but more threads.
From the link you shared: "However, since there is only one WAL file, there can only be one writer at a time".
https://www.hwaci.com/sw/sqlite/prosupport.html
> Paid support options and products are provided by Hipp, Wyrick & Company, Inc., (Hwaci), a Georgia corporation with headquarters in Charlotte, North Carolina and has been in business since 1992. Hwaci has an international team of employees and associates representing the best available talent. We are a 100% engineering company. There is no sales staff. Our goal is to provide outstanding service and honest advice without spin or sales-talk.
> Hwaci is a small company but it is also closely held and debt-free and has low fixed costs, which means that it is largely immune to buy-outs, take-overs, and market down-turns. Hwaci intends to continue operating in its current form, and at roughly its current size until at least the year 2050. We expect to be here when you need us, even if that need is many years in the future.
It is not as shiny, but in the long run, you still get all the goodness. Nevermind the name / version number.
So I'm pretty sure he did mean perl5, and as a happy user of both perl5 and sqlite the comparison seems apt.
Whilst SQLite3 is at the very top of its class, with lots of new features, and very well maintained.
He's also, OTOH, a technically brilliant developer whose positive contributions will be missed.
(also if you ever run into him at a conference, I'd recommend grabbing a beer with him, I've always enjoyed doing so in spite of our spirited disagreements over various things)
This is only partially sarcastic.
All running in kubernetes.
$ sudo service port-flaps restartExample: industrial equipment, military stuff, bridges, aircraft, etc.
[1] For pretty much complete nonsense NIH and standards-lawyering reasons.
You consider the fact that there is no spec other than "how the current implementation works", so no way to know if the code than works on version n will continue to work on version n+1, and no way to produce an alternate implementation, is a "complete nonsense reason"?
I also believe that it wasn't just Mozilla, but also Microsoft that scuttled this. Mozilla was the louder voice, but Microsoft never supported it either.
Yes. Sqlite is among the most stable and mature software in existence. I challenge you to name a single browser technology or API that has been anywhere remotely as stable as Sqlite, especially as implemented. As mentioned elsewhere ITT, Hwaci has contractual commitments to support backwards-compatible Sqlite3 for at least the next 35 years.
At that point, if I had to guess, "web browsers" won't be around any more, the web itself will be a dying or dead legacy technology, and companies yet to be founded will lead the tech sector, while Apple, Google, Microsoft and most likely of all Mozilla will be IBM-style dead letters or Digital[1]-style dead.
Sqlite was primarily written by one person and is maintained by three people.[2] If for some reason Mozilla had to fork or take over maintenance of Sqlite, it wouldn't even be worth noticing in their budget. It would, in fact, likely cost considerably less developer time than they spent designing, implementing and lobbying for IndexedDB.
More likely, if Sqlite3 were no longer being suitably maintained, all the browser vendors, plus the hundreds of other major tech companies that use it, would jointly endow a Sqlite Foundation to take over maintainence, which might cost Mozilla 5 grand or so a year, ye gods.
Even if Mozilla felt compelled for some reason (avoiding dependencies?) to rewrite Sqlite from scratch, Sqlite is 125k lines[3] of ANSI C compared to Firefox's 18 million lines[4] of C++, so Sqlite represents roughly one month of Firefox development time.
Firefox, Chrome, IE/Edge and Safari all make very extensive use of Sqlite internally, including to implement IndexedDB, so, obviously, Mozilla does not actually consider dependency on Sqlite and its API to present a meaningful risk.
Further, as things stand (and as they stood in 2010), Sqlite's massive footprint in the Firefox codebase means Mozilla already must rely on Sqlite remaining maintained, just the same as if WebSQL had been standardized.
> I also believe that it wasn't just Mozilla, but also Microsoft that scuttled this. Mozilla was the louder voice, but Microsoft never supported it either.
MS, especially at the time, ordinarily tried to slow or stall the standardization process for web technologies in general, so IE wouldn't fall too far behind. I expect better of Mozilla.
Mozilla was also by far the loudest (really the only) voice against WebSQL; MS barely participated in the conversation and was mostly noncommittal when it did. It's unlikely MS would've objected at all if Mozilla hadn't given them the opening.
[1] https://en.wikipedia.org/wiki/Digital_Equipment_Corporation
[2] https://sqlite.org/crew.html
[3] https://sqlite.org/testing.html
[4] https://www.openhub.net/p/firefox/analyses/latest/languages_...
Possible problems:
* Nearly half an MB of library to add to your project which might be a concern on mobile (~2.1MB uncompressed.
* It handles the whole DB in memory rather than trying to use any sort of local storage as a block store, which pumps up memory use (again, mobile user may particularly find this an issue) and to persist data you have to pickle the whole DB as a single array (which could be a significant performance issue if the data changes regularly and is not very small) and reload it upon new visit.
* Concurrency between multiple tabs/windows is going to be an issue for the same reason.
I'd love it if someone takes the time and creates a SQL engine based on IndexedDB so one can do serious SQL work instead of working around the many limitations in IndexedDB.
Doesn't look like this is fixed in sqlite4 though...
Other DBMSes often have to do complete table rewrites for certain types of ALTER anyway - so while, yes, it's faintly annoying to have to do that in userspace rather than having the db engine do it for you, it's not nearly as big a disadvantage as it first appears.
Where possible I much prefer to spin up a version of my target database in a tempdir but "faster test cycles" is sometimes worth accepting the trade-offs.
Since you were nominally optimizing for migration, a zoom-out perspective may be to note that upgrading SQLite3 versions vs. upgrading major RDBMS versions is trivial/fast, relatively rarely required, also cohabitation of multiple versions works a lot easier, any kind of CI/CD process is going to be orders of magnitude faster and use much less CPU/memory/disk space, which means smaller build artifacts and thus faster transfer/download.
There are some things that are inconvenient to do without window functions, but my primary use for them is simple optimization. Most of the cases where I've used window function in postgres, the query would be trivial to rewrite to use a subquery instead. Window functions always led to a much faster query that scaled much better over large query sets.
This was a partially right assumption, but only for writes.
If you write something in a DB you check some constraints and those checks are reads.
So most DB writes come with a bunch of reads.
The reads were slower with the LSMs, so the B-Trees performed better in "real world" writes (which come with reads) and LSMs only performed better in "artificial" writes (without reads).
Shameless plug https://github.com/maxpert/lsm-windows (I did port the LSM storage to windows).
https://www.sqlite.org/testing.html
"The TH3 test harness is a set of proprietary tests, written in C that provide 100% branch test coverage (and 100% MC/DC test coverage) to the core SQLite library. The TH3 tests are designed to run on embedded and specialized platforms that would not easily support TCL or other workstation services. TH3 tests use only the published SQLite interfaces. TH3 consists of about 57.3 MB or 782.3 KSLOC of C code implementing 42213 distinct test cases. TH3 tests are heavily parameterized, though, so a full-coverage test runs about 1.7 million different test instances. The cases that provide 100% branch test coverage constitute a subset of the total TH3 test suite. A soak test prior to release does hundreds of millions of tests. Additional information on TH3 is available separately."
https://www.sqlite.org/th3.html
"TH3 License
SQLite itself is in the public domain and can be used for any purpose. But TH3 is proprietary and requires a license.
Even though open-source users do not have direct access to TH3, all users of SQLite benefit from TH3 indirectly since each version of SQLite is validated running TH3 on multiple platforms (Linux, Windows, WinRT, Mac, OpenBSD) prior to release. So anyone using an official release of SQLite can deploy their application with the confidence of knowing that it has been tested using TH3. They simply cannot rerun those tests themselves without purchasing a TH3 license."
<from more info>
Obtaining A License To Use SQLite
Even though SQLite is in the public domain and does not require a license, some users want to obtain a license anyway. Some reasons for obtaining a license include:
Your company desires warranty of title and/or indemnity against claims of copyright infringement.
You are using SQLite in a jurisdiction that does not recognize the public domain.
You are using SQLite in a jurisdiction that does not recognize the right of an author to dedicate their work to the public domain.
You want to hold a tangible legal document as evidence that you have the legal right to use and distribute SQLite.
Your legal department tells you that you have to purchase a license.
</from more info>
How is it possible that they can sell licenses to the code that was put into the public domain by other contributors?
A contributor must attach the following declaration[1] to contribute. So now their contributions are in public domain. Now in a place where the law doesn't recognize public domain, doesn't the code belong to the original authors? How can an unaffiliated company license it as if they wrote the code?
[1]: "The author or authors of this code dedicate any and all copyright interest in this code to the public domain. We make this dedication for the benefit of the public at large and to the detriment of our heirs and successors. We intend this dedication to be an overt act of relinquishment in perpetuity of all present and future rights to this code under copyright law."
"Public domain" does not mean: this has to be free, nobody can ask for money.
"Public domain" means: noone has the right to stop anyone else from doing what they want.
The same is true for things like the Mona Lisa. You can snap a photo of it (that photo may or may not be copyrightable, depends on the country, meaning you may also "steal" someone elses photo if applicable), print posters and sell them. This is possible because the intellectual property is in the public domain. It doesn't mean you have to give your posters away for free. You can even charge people to look at your photo -- disregarding the fact that noone probably would pay.
Couldn't the person who put in into public domain go to the country and sue them saying the company is licensing their code? How can the company defend itself? We all know how it should go but that might not be what actually might happen.
The company doesn't need any authority because anyone can "license" public domain software, just like I can sell you a star in the sky. It's a worthless piece of paper, its a gimmick and a way of making a donation to a cause you appreciate.
>Couldn't the person who put in into public domain go to the country and sue them saying the company is licensing their code?
Again, you're misunderstanding what public domain means. "The person" you mention has no right to sue anyone because the thing being discussed has been released into the public domain. Nobody owns it.
To illustrate: when you say (paraphrased); "The author could sue you for misusing what he/she put in the public domain" the logic doesn't hold, because if the author could to that it wouldn't be released into the public domain.
Because you're required to sign a piece of paper that says: Yes, I agree to place this code in the public domain, if you want to contribute a patch to SQLite. What you're paying Hipps company for, when you buy a license, is keep records that says that all code is in fact in the public domain.
I assume we'll never find out because contributors obviously don't intend to sue anyone if they release their code to the public domain, but it's strange that a company would be comfortable buying a possibly illegitimate license when they're uncomfortable with a public domain declaration.
Besides, most countries in the developed world have some notion of giving away one's copyright (not moral rights, which can't be given away) so it might not be as much of a problem.
SQLite is developed and maintained by the SQLite Consortium, who's members include Mozilla, Adobe, Oracle and Adobe. The embedded SQL database is used in a number of well-known applications, such as Adobe's Lightroom, Apple's Mac OS X operating system and Mozilla's Firefox and Thunderbird.
http://www.h-online.com/open/news/item/SQLite-3-7-0-adds-Wri...
And since the link above is fairly old, I suspect since then the list of big companies supporting the project has probably grown.
https://github.com/LMDB/sqlightning
The performance there shows either little to no performance difference, up to substantial speed increases.
http://charlesleifer.com/blog/using-sqlite4-s-lsm-storage-en...
In peewee 3.0a I've also added built-in support for using the lsm1 virtual table if you're interested.
EDIT Seeing as I am getting slammed by downvotes, my comment here was simply pointing out that the headline I saw on HN could be read in multiple ways. As a long time user of SQLite3, I was initially excited when I read the title as I had thought it meant something good coming from the SQLite team. Turns out not to be. That, to me, still entails doubt.
Right there on the web page. Highlighted in green even.
> This repository exists as an historical record. There are no plans at this time to resume development of SQLite4.
(To clarify, this is directed at all the downvoters, not the commentator I'm replying to.)
I don' think it's too much to ask for commenters to have a little charity.