15 ms·
How the SQLite virtual machine works
- manimino 4y agoThese blog posts have been great. I'd love to see a deep dive on the query planner at some point. I've done lots of benchmarking SQLite while writing ducks [1], and found some interesting stuff. It seems like SQLite will only use one index, even in cases where two indexes would better suit the query. Or, in cases where the query is fetching most of the table, it will use an index even though a full scan would actually be much faster. Don't get me wrong, SQLite is awesome, and it's quite possible to work around these behaviors. Just interested in why they're not built in. [1] https://github.com/manimino/ducks https://github.com/manimino/ducks
- samatman 4y agoHave you brought any of that up in the SQLite forum? Not that you must, but the team loves that kind of feedback. They won't take all opportunities to make queries faster, if it's too complex for example, but they care a lot about speed of execution.
- chasil 4y agoThe author of SQLite recently published a paper on olap performance and duckdb that went into greater detail on the virtual machine. https://news.ycombinator.com/item?id=32684424 https://news.ycombinator.com/item?id=32684424 Also, there is a "hint" mechanism in SQLite to force the use of a specific index. A variant of it ("not indexed") can be used to force a full table scan, which is appropriate when more than 5% of a table's rows are involved. The docs strongly admonish that these are not "hints," and the optimizer must obey these clauses when they are used. https://www.sqlite.org/lang_indexedby.html https://www.sqlite.org/lang_indexedby.html
- manimino 4y agoExactly. So the workaround looks like: - Make one query with LIMIT of 5% - If it returns LIMIT rows, do a query with NOT INDEXED Which is surprising; why not build that into the query planning / execution in the first place?
- dllthomas 4y agoThe workaround is to understand how the questions you're asking the database relate to the structure of your data. What you describe is a brute force way to acquire that understanding on the fly on arbitrary queries. It might sometimes be the best you can do, and then it might be worth it (benchmark!), but in quite a few cases it'll be quite a bit slower.
- manimino 4y agoThat's a great point. The LIMIT workaround takes no knowledge of the data distribution, but an optimizer could do much better with some statistics such as estimated percentiles.
- lilyball 4y agoThe docs also say these aren't meant to be used for performance tuning but instead of protecting against regressions if the schema changes. I guess the idea is once you're done and happy with performance you check the query plan for time-sensitive queries and update the relevant statements with INDEXED BY to reference the indexes they're already using.
- chasil 4y agoIn Oracle, stats can change a CBO query plan in ways that adversely affect performance. To address this, Oracle 7 added hints, and then 8i introduced the ability to "pin" an existing optimizer plan to a query. Stats are run here and there in SQLite, so I can see how bad stats could throw the cost-based optimizer (CBO) into bad performance. Stats can be manually run with the ANALYZE command. This should likely be done (among other things) prior to distributing SQLite databases to an audience, or perhaps in weekly maintenance of active databases undergoing extensive DML. https://www.sqlite.org/lang_analyze.html https://www.sqlite.org/lang_analyze.html
- hinkley 4y ago> full table scan, which is appropriate when more than 5% of a table's rows are involved Am I wrong in thinking this ratio could be improved? Table scan being 20x faster than indexes seems like it might be leaving quite a bit on the table.
- HanyouHottie 4y agoI can believe it. Accessing sequential data that's being prefetched into cache is astonishingly faster than following references.
- whalesalad 4y agocool library, potentially have an immediate need for it will take it for a spin. currently maintaining multiple dict's in order to achieve concurrent (air quotes with my fingers here) writes but as more dimensions are added, more locks are needed etc. memory is cheap so I'd rather just store a big list of objects
- manimino 4y agoThanks! Hopefully ducks is everything you need it to be.
- derefr 4y ago> It seems like SQLite will only use one index, even in cases where two indexes would better suit the query. If you mean "one index per table per subquery", then AFAIK this is true of all RDBMSes. A query plan boils down to a bunch of "get [stream of tuples looking like X] out of [table Y]" leaf nodes; and those leaf nodes are then plan-optimized to extract those tuples using some particular strategy, which is always a very strict pipeline of "take a data source [table or index]; slice it to some range; then walk it either ascending or descending, filtering it, potentially with some offset+limit; and, if a non-covering index, dereference the index's file-offset-typed value to replace the index row-tuple with its backing table row-tuple." There's no room in that pipeline for having two indices work together to materialize one scan. If you want the effect of two indices working together, you either have to create a compound index (for a logical conjunction); or you have to use two subqueries and join them with UNION (for a logical disjunction.)
- mbaris 4y agoI really like this series of blog posts on SQLite, thanks if you are reading this. It was really informative for me since I did not know much about its internals before
- benbjohnson 4y agoAuthor here. Thank you for the kind words! I'm glad to hear it's been helpful.
- samatman 4y agoI'm having a great time with it. SQLite's documentation is so good that it's difficult to usefully add to it, but you've managed that. Some else said this, but I'd also love to see a deeper dive into the query plan, if you get to it.
- adamrezich 4y agohey, while you're here, the links to the other articles from this one are broken
- benbjohnson 4y agoOh shoot, thanks for letting me know. I'm getting them fixed up right now!
- tpetry 4y agoAre you still planning to write the article about cases when SQLite is not a good fit? Would be really interesting because we only hear stories when someone believes it is the best fit.
- orangepurple 4y agoI am under the impression that sqlite may struggle with many concurrent writers
- benbjohnson 4y ago
- skadamat 4y ago> SQL was originally designed for non-technical users to interact with the database, however, it's used almost exclusively by software developers peppering it throughout their applications. Interesting claim! In my experience, the # of data analysts / BI analysts / etc that write and have mastered SQL vastly exceeds the # of web developers (especially if we focus on developers actually writing SQL instead of using ORMs)
- benbjohnson 4y agoThat's a good point. My claim is probably too specific to software developers. Maybe I should have said "technical users" instead. I was paraphrasing from the Donald Chamberlin quote in the last section of the linked PDF[1]: "When Ray and I were designing Sequel in 1974, we thought that the predominant use of the language would be for ad-hoc queries by planners and other professionals whose domain of expertise was not primarily database management... Over the years, I have been surprised to see that SQL is more frequently used by trained database specialists to implement repetitive transactions such as bank deposits, credit card purchases, and online auctions. I am pleased to see the language used in a variety of environments, even though it has not proved to be as accessible to untrained users as Ray and I originally hoped." [1] https://ieeexplore.ieee.org/stamp/stamp.jsp?arnumber=6359709 https://ieeexplore.ieee.org/stamp/stamp.jsp?arnumber=6359709
- jgilias 4y agoI wonder what was the intended way of interacting with the database for technical users?
- benbjohnson 4y agoI don't have any evidence to back this up but my guess is that the underlying key/value store & cursor APIs were probably the intended way for programmers to interact with the database. SQL came out in the 1970s so every ounce of performance was important.
- ok_dad 4y ago
- avl999 4y agoI wonder how an alternate timeline might have played out if Richard Hipp had not named it "SQLite" and instead called it "SQLightning" or "SQLExpress" or something like that. For much of its lifetime, SQLite wasn't taken seriously despite being an extraordinary technology and part of me is convinced it was in large part due to "Lite" being in the name.
- tptacek 4y agoI don't think it has much to do with the name. I think the biggest perceptual impact on SQLite came from Rails, and its default for using SQLite as a test database while strongly discouraging people from using it in production.
- post-it 4y agoDjango too. > By default, the configuration uses SQLite. If you’re new to databases, or you’re just interested in trying Django, this is the easiest choice. SQLite is included in Python, so you won’t need to install anything else to support your database. When starting your first real project, however, you may want to use a more scalable database like PostgreSQL, to avoid database-switching headaches down the road. [0] [0] https://docs.djangoproject.com/en/4.1/intro/tutorial02/ https://docs.djangoproject.com/en/4.1/intro/tutorial02/
- tptacek 4y agoSure, Django too.
- nigma 4y agoThere are many reasons for using other databases like PostgreSQL or MySQL in production - feature set, (managed) hosting, backup and replication, etc. SQLite is primarily embedded/local database and cannot be easily separated and shared over network [1] between multiple disposable backend/worker instances. [1] https://12factor.net/backing-services https://12factor.net/backing-services
- 4y ago
- endisneigh 4y agoI see so many SQLite posts these days, but which companies with a lot (>1M) concurrent users are using SQLite in a non embedded fashion? It just seems so academic. I’d like to use a web service or app backed primarily by SQLite and see how it goes.
- jcwayne 4y agoI'm curious how many companies building for >1M concurrent users have even a tenth of that in reality.
- bob1029 4y ago> I’d like to use a web service or app backed primarily by SQLite and see how it goes. We've been doing this in production for over half a decade now. It goes so well it becomes boring very quickly.
- endisneigh 4y agoStats on your service/app and workload?
- bob1029 4y agoWe shard out to 1 SQLite database per user's working session, so there really isn't a meaningful figure if you are looking for apples-to-apples in a traditional sense. Our most heavily loaded SQLite instance probably sees 100 writes per second. In aggregate, we can handle tens of thousands of requests per second.
- Abishek_Muthian 4y ago> 1 SQLite database per user's working session I presume you've designed your application to accommodate only the data types offered by SQLite and without any concurrent writing?
- pjc50 4y agoThat's pretty much the anti-use-case for SQLite?
- purim 4y agoAny dates on supporting range of ports to my running sqlite/db instance on? ex) 5999-8999 edit: not sure why this is being downvoted? not being able to define a range of port seems like a huge oversight. the forums there are not very active so I am asking here.
- sgbeal 4y ago> edit: not sure why this is being downvoted? not being able to define a range of port seems like a huge oversight. It's likely been downvoted because your feature request demonstrates that you're not asking about sqlite. sqlite has no server element, and therefore no ports which need using. > the forums there are not very active so I am asking here. You're apparently not looking at the correct forums. As one of the sqlite forum moderators, i can say with some degree of authority that its forum is well-visited.
- purim 4y ago
- simonw 4y agoYour original comment didn't clarify that you were talking about ports on a Fly.io instance, which I imagine is why people didn't understand the relevance of your question.
- duskwuff 4y agoAnd even if the parent is asking about Fly.io, I don't see how that's relevant to a blog post about SQLite.
- sgbeal 4y ago> Do you work for fly.io? Horrible way to represent the company like this! Wha!?!?? The top post is very specifically about sqlite3, not fly.io (whatever that is). Your comment about it was taken entirely within the context of sqlite, not fly.io (whatever that is). > Why would I be on sqlite forums? Because this whole thread is about sqlite and your post said "the forums," implying sqlite's forums, not fly.io's (whatever that is). Edit: is see now that the article is hosted on fly.io (whatever that is), but the article is still entirely about sqlite, not fly.io (whatever that is).
- einpoklum 4y agoThe post does not describe a virtual machine, nor does it really describe how SQLite, specifically, works, as opposed to other DBMSes.
- d23 4y agoIt describes a virtual machine instruction set along with specific opcodes for the example query as well as a link to the full instruction set. It specifically talks about SQLite. Did you click the wrong link?
- ok_dad 4y agoThe "virtual machine" is not what you would probably consider a "VM" from the usual use of the term, like a "virtual x86/ARM computer" that you can do anything with. In this case, it's simply a set of variables used to simulate CPU registers, an instruction set that's specific to the underlying database's data format and desired operations, and then some code that "virtually" emulates what a CPU might do if it were designed with this memory architecture (the database file) and instruction set. The VM supports primitive operations via the instruction set; liken this to the machine code for your laptop's CPU, but very specific to the use-case of a SQLite database. The SQL you send into the SQLite db transaction is compiled into this VM's "machine code" and then run on the "virtual CPU" that implements that instruction set, and then it spits out your results. The article was very clear on how it worked.
- oxff 4y agoFor some reason, IME, SQLite feels the hardest to use of the popular databases. I did start my own database journey with Postgre and MSSQL so I feel kind of lost with SQLite whenever I use it.
- sgbeal 4y ago> For some reason, IME, SQLite feels the hardest to use of the popular databases. sqlite is, above all else, a C API for interacting with a database. Try using the C APIs for Postgres or MySQL or Oracle/OCI, and i _guaranty_ you you'll find sqlite's easier to work with _by leaps and bounds_.
- nijave 4y agoI'm guessing this varies a lot by language and library. In Python, it uses DB-API 2.0 which is a Python interface so it's basically the same as, say, Postgres (minus the sql dialect quirks). I'd think something like JDBC would do the same for Java, etc
- sgbeal 4y agoi can't speak to the high-level wrappers, but the native C API for MySQL is atrocious, the one for PostGres is meh, and the OCI is also (in my very limited experience) also meh. sqlite's C API (its core-most product) is a dream to use in comparison to its competitors.
- HelloNurse 4y agoWith a similar library API as the competition (according to other comments, a better API) and a good SQL dialect, what can make you feel "kind of lost" with SQLite? What are you missing? Obnoxious DBMS installations (e.g. DB2), colorful web management and monitoring consoles (e.g. Oracle), arcane semi-mandatory GUI tools?
- spullara 4y agoMost people probably don't know this but SQLite is used under the covers for most Mac OS applications like Photos and Mail. Been that way for a long time and lets you do some interesting data mining. e.g. https://javarants.com/build-your-own-mail-analyzer-for-mac-mail-app-747143e94ccc https://javarants.com/build-your-own-mail-analyzer-for-mac-m...
- pak9rabid 4y agoI believe it's because these applications likely use CoreData as their data persistence framework, which uses SQLite as the backend store. This has been nice because when I'm doing iOS app development that uses CD, I can simply pop the SQLite database open and query it directly to see what's going on as I'm testing.
- adamnemecek 4y agoIt's one of the possible backend stores.
- bob1029 4y agoThe EXPLAIN keyword is really awesome. It also allows you to quickly validate SQLite command text without actually executing it. We have found this to be useful for real-time validation of user inputs when working on SQL-based config tools. Note that EXPLAIN also seamlessly incorporates any application-defined functions, so you can validate everything is as expected. Useful when being clever with DSLs and such.
- flakiness 4y agoI hope SQLite's EXPLAIN were more visual / tree-like. Using bytecode internally is fine, but what users care is typically more high level. Decoding the that high level intention from the bytecode is so burdensome that I often give up and just fall back to try-n-error.
- ngcc_hk 4y agoSurprise a bit about the intent of sql. C J Dates of those days had no hint of that. Need to know set theory to understand what is going on I thought.