How the SQLite virtual machine works
fly.io
fly.io
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.
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.
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.
- 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?
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.
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.)
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)
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
Where the original Sequel authors took their Sequel spec and implemented it on real hardware + software, with interfaces for both "non-technical" ad-hoc queries as well as integration with PL/1 and COBOL for more technical users.
I have "non-technical" in quotes since in that 1970/80's timeframe, you had to be pretty technical to even have that label.
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.
e.g. https://javarants.com/build-your-own-mail-analyzer-for-mac-m...
You have to look where SQLite isn't rather than where it is these days.
cough _Billions_ cough with a capital "B". Very nearly every non-trivial electronic device built over the past 10-15 years. (Non-trivial being very roughly: "anything with a UI or having the potential to run one.")
completely off topic at this point, but Jim Cornette reading paid advertisements for codecademy was very funny during the pandemic. He insisted on pronouncing it code-cademy while his co-host tried to correct him.
Um, actually this[1] suggests that it originally wasn't about anything other than seeming like a good name and that any deeper explanation that has been given was made up. Of course, by the same token, "it seemed like a good name" could have also been made up. But it appears that the team eventually embraced it in the oxidization[2] sense.
[1] https://www.reddit.com/r/rust/comments/27jvdt/internet_archa...
ad nauseam
Case in point: This discussion was originally about SQLite.
This was pre-WAL, presumably enabling WAL would help a lot (but is still not the default, so beware). But the caveats were real, it's not like people just took one look at the name and though "'SQLite?' I better put a big warning in our documentation to not use this in production."
Indeed it would!
"WAL provides more concurrency as readers do not block writers and a writer does not block readers. Reading and writing can proceed concurrently."
https://www.sqlite.org/wal.html
I think the people advocating for SQLite to be used in more places are all assuming write ahead logging is enabled.
The chief problem that I see with WAL is that it breaks ACID with databases that are ATTACHed, as the documentation shows:
...I forgot how significant these problems are. These are quite serious.
"However, WAL mode has notable disadvantages. To accelerate searching the WAL, SQLite creates a WAL index in shared memory. This improves the performance of read transactions, but the use of shared memory requires that all readers must be on the same machine. Thus, WAL mode does not work on a network filesystem. It is not possible to change the page size after entering WAL mode. In addition, WAL mode comes with the added complexity of checkpoint operations and additional files to store the WAL and the WAL index."
https://vldb.org/pvldb/volumes/15/paper/SQLite%3A%20Past%2C%...
All the time. I suspect this is similar.
> 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]
Then most hosting for rails were stateless, so you had no way of storing SQLite on disk.
And finally, for serious production you need high availability and SQLite couldn't offer that.
Edit: I looked into a common Go driver for SQLite[1] and the FAQ reads,
> Can I use this in multiple routines concurrently?
> Yes for readonly. But not for writable. See #50, #51, #209, #274.
Every time I see a blog post from fly.io reg SQLite I'm tempted to use it for my next project, But the need to rewrite my framework for limited data types and the doubts regarding concurrency keeps me away.
When SQLite arrived it got lumped in with file storage that can't scale. Rails only perpetuated what everyone was already thinking.
SQLite is primarily embedded/local database and cannot be easily separated and shared over network [1] between multiple disposable backend/worker instances.
A friend of mine made a similar point about GIMP. I'd never thought about it that way. What a shame to be hindered by such a terrible name choice (in GIMP's case).
I think there has been a lot of recognition in the last 10 years that sqlite is actually quite robust, but it still hasn't been considered suitable for serious use is based on how software and database servers have traditionally operated.
It seems like what's changing that now is the recognition that other approaches may make more sense given modern software architecture.
As far as I can recall, it was always well regarded as an embeddable database. What makes you think that it wasn't taken seriously "for much of its lifetime"?
Been in missiles for a while.
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.
Some else said this, but I'd also love to see a deeper dive into the query plan, if you get to it.
It just seems so academic. I’d like to use a web service or app backed primarily by SQLite and see how it goes.
That might sound confining, but it's what people already do with Postgres: read replicas, single write master.
And the benefits aren't academic: having your database in-process not only simplifies your database code (because N+1 queries are no longer perf emergencies), but can also drastically speed up response times, because you're not dealing with compounding intra-DC latency responding to an HTTP request that incurs multiple database queries.
And, finally: SQLite wants a single writer per database. But there's no rule that says your entire app has to land in a single .db file. That's what you'd naturally do in a Postgres app, but it's not necessarily the natural answer for a SQLite-backed app, where it's pretty trivial to pull from multiple databases with a single SQL statement.
Don't get me wrong, I think it's still pretty niche-y as an alternative to standard n-tier app databases. You'd use it deliberately; it's unlikely to be a full-stack default. But people have definitely been sleeping on SQLite, and I think what you're seeing is some of those people beginning to wake up.
This is what I'm saying - I keep hearing this, which popular website is using SQLite as their main database?
I don't have any doubt you can serve 1000 users, or even 10,000 current users using SQLite. There's a power law distribution with the popularity of sites. The most popular sites have multiple orders of magnitude.
Can SQLite serve 500,000 current users with 75% reads and writes with the consistency you'd expect?
> And, finally: SQLite wants a single writer per database. But there's no rule that says your entire app has to land in a single .db file. That's what you'd naturally do in a Postgres app, but it's not necessarily the natural answer for a SQLite-backed app, where it's pretty trivial to pull from multiple databases with a single SQL statement.
Sounds reasonable in theory. How do you maintain consistency between all of these databases? Schema migrations? Backups and restores and maintaining referential consistency?
By the time you implement all of this, and you certainly can, why not just use Postgres?
First, I don't think this is a good metric for software quality or use-case analysis. Just because people don't use X doesn't mean X wouldn't be a superior tool for their use-case.
> How do you maintain consistency between all of these databases?
You don't, in the GP's example, you would shard the database schema. Sharding is easy to figure about, because many database services do this already to spread data and analysis across several servers. You might use Litestream to then send those thousands/millions of database files to some S3 bucket somewhere. Then, you could analyze the shards together in that bucket using one of the many available solutions for such things, like Apache Hive or whatever the BigCo clouds provide.
> By the time you implement all of this, and you certainly can, why not just use Postgres?
Because you analyzed the pros and cons to SQLite vs. Postgres for your specific use-case and determined SQLite will be better in the long run. Perhaps your particular use-case will turn up "Postgres" and you won't use SQLite, or maybe you'll use both of them, but for different data stores.
No one technology is "the chosen one", sometimes you need several different options.
I'm not sure if you're being serious. Sharding isn't easy at all. Especially if you're doing it across different files that need to be synced in tandem with schema migrations that also need to be applied in tandem.
> Because you analyzed the pros and cons to SQLite vs. Postgres for your specific use-case and determined SQLite will be better in the long run. Perhaps your particular use-case will turn up "Postgres" and you won't use SQLite, or maybe you'll use both of them, but for different data stores.
The use case is already defined in my original post. A popular app with a lot of concurrent users. People claim SQLite is superior to Postgres for such a scenario, I'm curious to read about and use a service using SQLite for an app with a lot of users (>1M 75% reads)
From looking at the issues for Litestream on GitHub I'm not really convinced it's production ready. For some small app I'm sure it's fine, I'm talking about an app with a lot of users generating a lot of money and you need high availability.
All of this makes it sound like I dislike SQLite, I really don't - I've used it successfully for local apps on the web, Android and iOS. However I feel this popularity for SQLite on the web may be misplaced.
Only idiots would claim some technology is superior without doing some use-case analysis and experimentation.
> I'm not sure if you're being serious...
I am, but still it depends on use case. If you have an application that can support it for the use-case, then you can do simple things like making one DB per user and then doing aggregate functions on Apache Hive or something. If you have a schema that will absolutely NOT support sharding, then I guess it might not work in that case. I would argue I could design a SQLite solution for 95% of the applications that need a SQL database, but there are some which, for whatever reason, may require other solutions. I am also NOT saying that all of those designs would be the best for that use-case, but it would work.
Unequivocally: Your use cases define the tools you need to use.
> The use case is already defined in my original post. A popular app with a lot of concurrent users. People claim SQLite is superior to Postgres for such a scenario, I'm curious to read about and use a service using SQLite for an app with a lot of users (>1M 75% reads).
Still, you didn't define it. What's the data schema? What is the application? I don't know what to say, if your app is a TODO list app that's wildly popular, then I guarantee I can do what you want with SQLite easily. If it's Spotify-level stuff with special aggregation and data analysis, maybe not?
sigh. any app based on SQLite with more than 1 million concurrent users, 75% reads.
Here are a few other sources I googled:
https://www.sqlite.org/famous.html
This is the best I can do, because I have no idea what else you want. If I had the time, I could throw up a simulated million users doing a TODO app with SQLite as the backing DB engine, but I don't have time, I have a 2 year old. Do your own research from here, or don't and just use Postgres until you retire, I don't really care that much.
I don’t think your example is a good fit for SQLite as it’s not a good fit for any sql store that I know of.
https://www.uber.com/blog/building-ubers-fulfillment-platfor...
The parent asked about use cases where there wasn’t an _external_ (which I interpret to mean “handled explicitly by the app, not the database”) sharding / caching layer.
Spanner internally handles splits based on load / size.
That having been said, more careful reading of the Uber post shows that they still do employ additional app-level explicit sharding to resolve hot-spots, so it’s probably a fair point that this doesn’t qualify as an example the parent was looking for.
Legitimately interested as this is something I have more than a little professional interest.
That said, “show me an app doing 5k concurrent writes with SQLite” seems like a much more reasonable standard.
Definitely not the use case for SQLite unless you can easily do sharding but as you said, why bother?
I like finding hints of our architecture scattered across these SQLite threads :D
It's very sketchy trying to slam all users into a single physical database. If you can slice up the users along unit of work, session, customer, account, etc., you can go horizontal very quickly.
Look no further than sqlite's own source control system: <https://fossil-scm.org>
i've used that site almost daily since the end of 2007 and have encountered maybe (maybe) three "db is locked" errors.
SQLite has been deployed on billions of devices, so "academic" is an odd term here.
We've been doing this in production for over half a decade now. It goes so well it becomes boring very quickly.
Our most heavily loaded SQLite instance probably sees 100 writes per second. In aggregate, we can handle tens of thousands of requests per second.
I presume you've designed your application to accommodate only the data types offered by SQLite and without any concurrent writing?
Using SQLite for any serious webservices is bad thing because you can only have a single app. Then you have other tools on top of SQLite for those shortcoming.
Perhaps more appropriately, sqlite's own forum is sqlite-backed: <https://sqlite.org/forum> (running the Fossil SCM's forum interface, which itself exclusively uses sqlite for storage: <https://fossil-scm.org> (that whole site is an instance of fossil, serving all content from an sqlite db))
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_.
I'd think something like JDBC would do the same for Java, etc
What are you missing? Obnoxious DBMS installations (e.g. DB2), colorful web management and monitoring consoles (e.g. Oracle), arcane semi-mandatory GUI tools?
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.
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.
This is answered in the forums, too: https://community.fly.io/t/new-feature-every-public-port-now...