Many small queries are efficient in SQLite
sqlite.org
sqlite.org
You can run an unbelievable # of select statements per unit time against a SQLite database. I think of it like a direct method invocation to members of some List<T> within the application itself.
Developers who still protest SQLite are really sleeping on the latency advantages. 2-3 orders of magnitude are really hard to fight against. This opens up entirely new use cases that hosted solutions cannot consider.
Databases that are built into the application by default are the future. Our computers are definitely big enough. There are many options for log replication (sync & async), as well as the obvious hypervisor snapshot approach (WAL can make this much less dangerous).
Interesting point. It's easy to forget that in the past local storage was small and expensive so that it was necessary to have a separate database machine.
No they're not, because for web servers, you have many (tens? hundreds? thousands?) of web servers that all need to talk to a single database. And you sure don't want to replicate and sync a gigantic database across each web server -- that would be a disaster.
While for local apps on your phone or computer, usage of SQLite is already widespread -- it's not the future, it's here. And cloud-connected apps that work offline already do some sort of sync for that.
All this depends on your data being somewhat shardable of course.
And syncing databases is just waaay harder than having a dedicated one. It really seriously is a pain to configure and debug and manage storage and performance requirements around and dealing with scaling and managing failure modes and so forth. If you can even find someone who has the skills to do it properly so you never lose data.
It's far, far easier to just write normal SQL queries that don't have to make hundreds of requests, than to deal with syncing a database across all your webservers.
Why do you need 2 webservers (assuming that the cpu / network / disk throughput of a single server is sufficient for your application)?
And because designing for 2 is really different from designing for 1, but not much harder if you do it from the beginning. But expanding from 1 to 2 when you unexpectedly need to because of sudden traffic, and having to re-architect it, can be really hard.
I'm not talking about hobby sites here, I'm talking about anything designed for a business, where downtime of a day is unacceptable.
Databases are astonishingly more optimized and predictable and reliable than web servers.
Maybe if you're in the top 1000 or so largest websites.
Back when the alexa 10k was a thing, $work was on it - and we're serving that level of traffic with a rails app running on 30 CPU cores. It would fit _easily_ onto a single machine.
There are a lot of good reasons that we use a fleet of cheap scalable web servers in front of a beefy database server that has a live failover.
"You can't fit on one big machine" is virtually never one of them - and at that point you're firmly in the realm of 'months of bespoke engineering to handle the load'.
Similarly, "beefy database server that has a live failover" is not inherently better than "beefy web+database server that has a live failover".
I agree there are a lot of good reasons. There used to be a _lot more_ good reasons, and - while I haven't actually tried it yet - I suspect that it's far more tractable than it used to be.
If I _were_ to try it, my design would be multiple failover targets (at least 3, geographically dispersed, probably 4), plus a separate control plane to configure BGP.
That's just not true. There are a lot of web servers where their operations are CPU- or memory-intensive, depending on what they do. Are they working with images? Mapping? Video? Large data throughput? Especially when so much server code is written in slow interpreted scripted languages.
Well-architected databases often can fit on one machine, unless you're at Facebook scale. Webservers however -- absolutely not. The front-end for a simple CRUD interface, or for a blog with static pages, sure. But for a lot of interactive sites, definitely not.
So the "traditional scalable" way of doing things right now assumes your "app" layer (sorry if I'm not using the right term -- the thing between the HTTP request and the database) is inherently going to be CPU intensive in a way that the database is not; and so assumes you're going to need to make a distributed system with load balancing, network connection to a database, etc.
But it seems to me that making that distributed system 1) requires a huge amount of "complexity budget" 2) actually increases aggregate CPU utilization, since one way or another everything has to be marshaled across the network, usually involving interpretation, copies, verification, maybe encryption, and so on.
So the question is, could you instead spend your "complexity budget" on actually optimizing the "app" layer? Use a performant compiled language, like Go or Rust, use profiling and optimization, etc. Sure, you'll have to spend more of your development time optimizing inner loops, but you also spend less time designing and debugging distributed systems.
I remember hearing somewhere that the entirety of StackOverflow ran on two physical boxes. Given how much traffic they must have gotten, I'm betting that I can do the same for the website I'm currently developing.
And connected to SQLite: Combine the fact that databases are just files, and that you can easily run "ATTACH <file>" to connect multiple database files on the same "connection", you should in many cases be able to design your overall structure to effectively "pre-shard" data. Right now I've got a separate .sqlite file for the per-user data, but all users share common site data; so if I ever reach the point where I really can't optimize further, I should be able to break my app into multiple servers without having to do further work sharding the database.
If everything goes up in smoke, I'll write a blog post admitting I was wrong and DM you. :-D
You definitely could, and I know people who have ported things from e.g. PHP or Python or Java to Go, so that a service that required 100 servers then only required 1. But it's just expensive -- the time it takes, and hiring people who can do that and maintain that.
And I only know of that having been done for services that were already in an extremely stable state, and not expected to change -- API endpoints, not HTML generators.
Generally speaking, the "app" layer is just changing a lot as features get tested and added and changed and swapped out, and dev cost is the priority, not server cost. And load balancing web servers is very easy.
> And connected to SQLite: Combine the fact that databases are just files, and that you can easily run "ATTACH <file>" to connect multiple database files on the same "connection", you should in many cases be able to design your overall structure to effectively "pre-shard" data.
Distributing a database that is read-only is easy, sure. It's the writes that are hard, and when you shard it's the joins between shards that give you headaches. If you work on a site where data from one user never has to be joined to data from another, then lucky you. ;)
That’s a bit overzealous. SQLite is great, but it’s not a replacement for a hosted database. Not all data can live on the client. Use SQLite when you have the right use case for it (offline desktop app, etc.)
I personally haven't used it.
What I am curious about is if "Many Small Queries Are Efficient in SQLite" when using the various hosted flavors of SQLite.
When you say "client" are you referring to the end user's machine, or the server hosting the application they are talking to?
I only advocate for using SQLite on the server, integrated with the hosting application (i.e. your back-end systems).
Redundant SQLite is not impossible [0] but it is definitely less mature and well-known than replicated MySQL or Postgres.
Not sure why people are still trying to shoe-horn it into a role that it's not meant to be in, and not even really supported to be.
[1] https://www.sqlite.org/whentouse.html (see Server Side Database)
This brings strict types that people expect from the other server-based databases.
Just saying that it's "worth using it" gives the impression that it's a good choice for all levels of traffic, all security requirements, all isolation requirements (yeah it's serialisable, but this comes back to levels of traffic), the kind of use case where you need SKIP LOCKED instead of just a single writer, etc.
So it seems like it _tries_ to do the things that people want, but only half-heartedly, and just fails silently if it can't. Was the column actually NULL? Or just a wrong string format? Who knows?
The point is, SQLite is very "flimsy". And that's perfectly fine for what it is (or what it intends to be). But if I try to insert or retrieve a date() or do strftime() on a string that isn't a valid date/time, don't just return a NULL. That's where the "flimsiness" makes it a deal-breaker for anything where integrity is important and the database itself is supposed to be the authority.
Size is no real concern, if the user of a client side application has many gigabytes of data a sqlite database is still well suited for the role. There's no shoehorning, it just works.
I'm not sold on client side, but a lot of great work is being done on putting the db and application on the same server, between SQLite replication and other approaches like SpacetimeDB. I'm interested to see where it goes.
My concern is over-reliance on client side storage and the complexity it adds to have records of state on both client and server.
That assumes local applications themselves are the future, and that assumption has grown ever weaker and weaker with everyone and their dog going cloud-only (or starting as a SaaS in the first place) to grab all the sweet sweet recurring subscription revenue.
Knowing this, I wonder if there will be another wave of object databases at some point.
It still depends a lot. If your system is high-load enough, you may want sharding, for example.
The biggest problem with GraphQL is how easy it becomes to accidentally trigger N+1 queries. As this article explains, if you're using SQLite you don't need to worry about pages that accidentally run 100s of queries, provided those queries are fast - and it's not too hard to build a GraphQL API where all of the edge resolutions are indexed lookups with a limit to the number of rows they'll consider.
I had a lot of fun building a Datasette GraphQL plugin a while ago: https://datasette.io/plugins/datasette-graphql
But if you do a ton of small queries instead of one big one, you could be depriving the database of the opportunity to choose an efficient execution plan.
For example, if you do a join by querying one table for a bunch of IDs and then looking up each key with an individual select, you're forcing the database into doing a nested loop join. Maybe a merge join or hash join would have been faster.
Or maybe not. Sometimes the way you write the queries corresponds to what the database would have done anyway. Just not necessarily, so it's something to keep in mind.
As a pedant, I've been referring to it as a "1+n" problem, but haven't managed to make it catch on yet!
Also "n+1" looks more like a reference to concurrency/synchronization throughput - like, n concurrent threads or requests where the results are collected and used together. I was really confused the first few times I saw "n+1" because of that.
It looks like this page in the SQLite docs has had the "n+1" terminology for as long as it's been on the internet archive (2016): https://web.archive.org/web/20161112021608/https://www.sqlit...
Usually it's doing one query that returns n results, then doing one more query for each result. Therefore, you end up having done 1+n queries. If you'd used a join you could potentially have done only 1 query.
It's interesting to consider the question of what this would look like if you put Postgres, rather than SQLite, in process? With PGlite we can actually look at it.
I'm hesitant to post a link to benchmarks after the last 24 hours on Twitter... but I have some basic micro-benchmarks comparing WASM SQLite to PGlite (WASM Postgres that runs in-process): https://pglite.dev/benchmarks#round-trip-time-benchmarks
It's very much in the same ballpark, Postgres is a heavier databases and is understandably slower, but not by much. There is a lot of nuance to these benchmarks though as the underlying VFSs are a little different to each other, and PGlite has a WAL rather then SQLite which is in its rollback journal mode (I believe this is why PGlite is faster for some inserts/updates).
But essentially I think Postgres when in-process would be able to perform similarly to SQLite with many small queries and embracing n+1. But having said that I think the other comments here about query planning are important to consider, if you can minimise your queries, you minimise the scans of indexes and tables, which is surely better.
What this does show, particularly when you look at the "native" comparison at the end, is that removing the network (or at least a local socket) from the stack being the two closer together.
The network round trip time can also add up if you run into resource constraints doing this.
On a remote database, you also have to contend with multiple users and so complicated locking techniques can come into play depending upon the complexity of all database activity.
Many databases have options to return multiple result sets from one connection which helps control the overhead caused by this usage pattern.
EDIT: This also brings back horrible memories where developers would do this in a db client server architecture. Then they would often not close the DB connections when done. So you could have thousands of active connections basically doing nothing. Luckily, this problem was solved with better database connection handling.
Here's an example PostgreSQL query that returns 10 rows from one table and 20 rows from another table in a single network round-trip, using JSON serialization to return the different shaped rows in one go: https://simonwillison.net/dashboard/union-json-demo/
Related trick: https://til.simonwillison.net/sqlite/related-rows-single-que...
the problem is that it only works with Postgres, not mysql or sqlite or pretty much anything else (at least not as conveniently) and the bigger problem is that the queries become more complex
The one JSON PostgeSQL feature that SQLite doesn't have yet and I miss is row_to_json(record) which serializes an entire row without you needing to specifically list the column names.
I've not tried this stuff in MySQL myself yet but it looks like JSON_ARRAYAGG() might be the equivalent there: https://dev.mysql.com/doc/refman/8.4/en/aggregate-functions....
If none of this makes sense, don't worry, that just means you don't work in enterprise...
1. How are they managing the concurrent writes? I know about lock or queue, but if my stack needs multiple server, then what?
2. How do they manage durable data store? Since sqlite doesn't have a replication mechanism, how do people handle losing transactions?
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
> How do they manage durable data store? Since sqlite doesn't have a replication mechanism, how do people handle losing transactions?
I've never run one of my SQLite-embedded services on a machine that wasn't virtualized and also known as "production". The simplest recovery strategy is to snapshot the VM. There are other libraries that augment SQLite and provide [a]sync replication/clustering/etc.
See:
https://www.sqlite.org/pragma.html#pragma_synchronous
https://www.sqlite.org/pragma.html#pragma_journal_mode
#2. Snapshot is backup strategy. AFAIK, from reading some reddit comments by Ben? (author of litestream) that it's not a replication strategy.
I've used sqlite in ETL pipeline very successfully, as well as as a read-only caching. I just can't figure out how people use it as server without doing a whole bunch of hacks to deal with its limitations.
I would never try to have 2 different processes (such as a web server) talking to the same SQLite database. That is a recipe for performance disaster. All of the projects I build this way are single process designs. It is not really a good fit for legacy solutions.
If you absolutely require transactional integrity against the same datastore with multiple processes, you could consider selecting one process as the DB owner and then IPC/RPC the SQL commands from the other processes to that one. Named pipes (shared memory) are nearly free in many ecosystems. You will find tradeoffs with a proxied approach, such as all application defined functions need to be defined on the db owning process.
> that it's not a replication strategy
Correct, but many scenarios can survive with 15-60 minutes (whatever the snapshot interval is) of data loss. The complexity trade off is often worth it if the business can tolerate. If you do require replication, then a 3rd party library or something application-level is what you need.
I feel like I missed something here.
The only concern I have is it isn't portable. Move to Postgres and your app will choke. This can be acceptable while scaling because SQLite can go much farther than people realize. But it is good to keep in mind.
> Such criticism would be well-founded for a traditional client/server database engine, such as MySQL, PostgreSQL, or SQL Server. In a client/server database, each SQL statement requires a message round-trip from the application to the database server and back to the application. Doing over 200 round-trip messages, sequentially, can be a serious performance drag. This is sometimes called the "N+1 Query Problem" or the "N+1 Select Problem" and it is an anti-pattern.
But if we're considering running SQLite, the apt comparison would be against other DBs running on the same machine, because we've already decided local data storage is acceptable.
I assume Postgres/MySQL/etc would have higher latency than SQLite due to IPC overhead — but how significant is it?
I ran a quick, non-scientific Python script on my Macbook M2 using local Postgres and SQLite installs with no tuning.
It does 200 simple selects, after inserting random data, to match the article's "200 queries to build one web page".
(We're mostly worrying about real-world latency here, so I don't think the exact details of the simple queries matter. I ran it a few times to check that the values don't change drastically between runs.)
SQLite:
Query Times:
Min: 0.007 ms
Max: 0.031 ms
Mean: 0.007 ms
Median: 0.007 ms
Total for 200 queries: 1.126 ms
PostgreSQL:
Query Times:
Min: 0.023 ms
Max: 0.170 ms
Mean: 0.028 ms
Median: 0.026 ms
Total for 200 queries: 4.361 ms
I've seen typical individual query latency of 4ms in the past when running DBs on separate machines, hence 200 queries would add almost an entire second to the page load.
But this is 4.3ms total, which sounds reasonable enough for most systems I've built.
A single non-trivial query required for page load could add more time than this entire latency overhead. So I'd probably pick DBs based on factors other than latency in most cases.