5 ms·
Many small queries are efficient in SQLite
- bob1029 2y agoI built a rules engine around SQLite queries for the last product I was working on. This article was the reason. 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).
- norir 2y ago> Databases that are built into the application by default are the future. Our computers are definitely big enough. 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.
- nevdka 2y agoIt wasn't just storage. In the 90s, most servers were single core. Databases already had network interfaces so they could be shared by non-web applications running on desktops, and so were the natural place to split a website over multiple machines. This turned into the received wisdom of always putting your database on a separate machine, despite processing power and storage latency improving so much since the 486 and pentium days that network overhead dominated response times.
- crazygringo 2y ago> Databases that are built into the application by default are the future. 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.
- secondcoming 2y agoWe have a giant Postgres DB, but instead of having every machine connect directly to it, we have a job that creates a smaller SQLite cache of relevant data and that’s pushed out to the machines who then reload that on the fly. All this depends on your data being somewhat shardable of course.
- aynyc 2y agoYou can’t really sync the data in real time tho. Transactions still need to hit your Postgres, and then you reload. You might as well do write thru cache.
- binary132 2y agoYeah, this sounds pretty not great if you need ACID consensus or locking between multiple machines. If what you need is to read a somewhat static state, fine, great.
- Nathanba 2y agoMost people don't have tens or thousands of webservers and could be running with a more efficient inprocess database that syncs to another networked readonly replica for a very long time. I'm very surprised that MySQL or Postgres still don't have an inprocess mode because it's such an obvious win and on a technical coding level it should (naively) be very easy to switch from network calls to direct calls.
- crazygringo 2y agoBut it's standard to have at least 2 webservers. 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.
- corytheboyd 2y ago> Databases that are built into the application by default are the future. 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.)
- gwbas1c 2y agoThere are ways to use a hosted SQL, or from a cloud application. 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.
- claytongulick 2y agoNope. You get back to the roundtrip problem.
- infamia 2y agoThe SQLite db can easily live on the same machine as the app server.
- deleted 2y ago[deleted]
- danielheath 2y ago> using the various hosted flavors of SQLite
- closeparen 2y agoI think the "hosted flavors of SQLite" would be doing the type of trick Litestream does: streaming the WAL to other copies of the database. The copy you interact with is still local.
- bob1029 2y ago
- VWWHFSfQ 2y agoI'm still not understanding this push toward using SQLite as a production backend database. It's great for what it is, a tiny embeddable client-side application database. Like an address book on your phone. But even the developers themselves have steadfastly refused to allow it to expand beyond that scope. For instance, they won't add native types for any useful things like dates/times, or uuids. Because that would bloat the code and the size of the embedded object. So you're stuck with "everything is a string". Ref integrity can be enabled, but even those constraint options are very limited. 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.
- claytongulick 2y agoIt's absolutely supported for that use case if you can get away with single-writer multi-reader architecture, which IMHO most medium sized applications can. [1] [1] https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html (see Server Side Database)
- blopker 2y agoMaybe it's not for you, but the "everything is a string" thing is just the default. SQLite has STRICT table option since 2021 that people really should be using if possible: https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html This brings strict types that people expect from the other server-based databases.
- dudus 2y agoAnd even being this simple and having clear challenges it's worth using it for the latency gains.
- strken 2y agoWhen is it a good choice, and when is it not? 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.
- emseetech 2y ago| Databases that are built into the application by default are the future. 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.
- vlovich123 2y agoSQLite underpins nearly every on-disk storage mechanism at Apple and Google as the OS level. You may not be sold but systems programmers elsewhere are.
- emseetech 2y agoWhat works for Apple and Google is rarely the best approach for the rest of us. My concern is over-reliance on client side storage and the complexity it adds to have records of state on both client and server.
- vsuperpower2021 2y agoSqlite isn't something that works for only apple or google. Point is it's sufficient for them, simple, and more than sufficient for you. He's explaining to you that this is something that has already happened as you're unaware.
- emseetech 2y agoI use SQLite all the time, I said in another comment it's my go-to for server-side green field projects. But there's a lot of push to create data solutions that are fully bidirectional between client and server, or to even make them client-driven in an awkward peer-to-peer model, and I'm not sold on either pattern.
- lupusreal 2y agoSqlite is at it's best in "pure client" situations where there is no server and there are no P2P things going on. Just the application, the user, and their data on their own drive with sqlite.
- mschuster91 2y ago> Databases that are built into the application by default are the future. Our computers are definitely big enough. 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.
- timClicks 2y ago> Databases that are built into the application by default are the future. Our computers are definitely big enough. Knowing this, I wonder if there will be another wave of object databases at some point.
- grishka 2y ago> Databases that are built into the application by default are the future. It still depends a lot. If your system is high-load enough, you may want sharding, for example.
- kllrnohj 2y agoWouldn't a hosted database really only "make sense" if you don't want sharding? It's a lot easier to shard at the front end before it makes it to any business logic than to do that and also shard the database. It's when you don't want to deal with database sharding at all that you'd want all application instances to hit the same hosted database.
- grishka 2y agoSorry I forgot that people do "front end" these days. I was thinking of the old-school server-side-rendered web apps that load almost instantly, not the modern spinner-fest ones.
- binary132 2y agoI often find myself thinking about what a database like SQLite that might look like if it had a native API instead of a query language. I guess it wouldn’t be so different from a dataframe API, but with persistence, locking, and relational features, and I guess an mmap under the hood.
- adrianmonk 2y agoIt's true that RPC latency doesn't exist, and that opens up other possibilities that are worth considering. 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.
- simonw 2y agoAn interesting result of this is that SQLite is a really good database for implementing GraphQL. 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 https://datasette.io/plugins/datasette-graphql
- crabmusket 2y agoPet peeve: why is the common term "n+1 query problem" when it's the 1 that happens first and the n that is the problem? As a pedant, I've been referring to it as a "1+n" problem, but haven't managed to make it catch on yet!
- Izkata 2y agoI swear "n+1" only popped up in the past 5 or so years with this meaning. I learned it over a decade ago as "1+n". 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.
- crabmusket 2y agoIt reminds me of mathematical induction, where the k=n and k=n+1 cases are compared. The emphasis is on the +1, the next case to be proved. 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.sqlite.org/np1queryprob.html https://web.archive.org/web/20161112021608/https://www.sqlit...
- dullcrisp 2y agoCommutativity of addition
- crabmusket 2y ago-is irrelevant when we're not dealing with algebra, but a sequence of dependent RPC calls!
- nodoodles 2y agoI have thought of that as ‘n’ being the manageable threshold and the (uncontrolled) ‘+1’ the overflow creates the problems. Typically in terms of additional layers or iterations.. but i like your point and perhaps’n+1’ and ‘1+n’ mean different problem shapes
- samwillis 2y agoI love this explanation. 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 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.
- treebeard901 2y agoThe article doesn't explain why this is a bad idea for database servers hosted on a remote machine. The first reason is obvious... Network connections take memory and processing power on both client and server. Each additional query causes more resource usage. It is unnecessary overhead which is why things like multiple active result sets were created for SQL Server. 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.
- simonw 2y agoThese days there are other tricks you can use to turn several SQL queries into a single round-trip too, with things like JSON aggregates. 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/ https://simonwillison.net/dashboard/union-json-demo/ Related trick: https://til.simonwillison.net/sqlite/related-rows-single-query https://til.simonwillison.net/sqlite/related-rows-single-que...
- Nathanba 2y agoit is very interesting, I have a similar blog post in my bookmarks as well: https://www.crunchydata.com/blog/generating-json-directly-from-postgres https://www.crunchydata.com/blog/generating-json-directly-fr... 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
- 2y ago
- emseetech 2y agoEffectively (not completely but in general real world use) solving the N+1 queries problem is one of many reasons why SQLite is my first choice for greenfield projects. 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.
- slaymaker1907 2y agoOh, I've definitely seen N+1 problems with a SQLite database before. It's more resilient than a traditional DB server, but it can still run into performance issues when doing too many select queries.
- eyelidlessness 2y agoOne thing I don’t see mentioned yet is that efficiency isn’t the only reason multiple queries can be problematic. There’s also consistency. This might not matter for many SQLite use cases, or in general for blocking single-reader calls. But if your database might be handling concurrent writes that could occur between any two reads, reducing the overhead to zero still might not be worth the tradeoff.
- nh2 2y agoAnybody have a benchmark that shows that postgres is actually slower when on the same machine with same durability settings?
- olalonde 2y agoThe issue isn't that "postgres is actually slower" but the network round trips: > 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.
- jomohke 2y agoI think that's why the GP said "on the same machine" — I read the article as comparing to the typical DB that is located on a separate machine in the data center, accessed over a network, as is currently the common setup. 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?
- jomohke 2y ago(replying to myself) 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 (Again, this is very quick and non-optimised, so I wouldn't take the measured differences too seriously) 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.
- aynyc 2y agoI still can't figure out how people are using sqlite in server context. 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?
- bob1029 2y ago> How are they managing the concurrent writes? PRAGMA journal_mode = WAL; PRAGMA synchronous = NORMAL; Use a single SqliteConnection instance for all access - most builds of the SQLite provider serialize threads inside. Opening multiple connections will incur unnecessary F/S operations. > 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_synchronous https://www.sqlite.org/pragma.html#pragma_journal_mode https://www.sqlite.org/pragma.html#pragma_journal_mode https://www.sqlite.org/threadsafe.html https://www.sqlite.org/threadsafe.html https://rqlite.io/docs/faq/ https://rqlite.io/docs/faq/ https://litestream.io https://litestream.io
- aynyc 2y ago#1. They are pretty much my default settings now. But how do you serialize concurrent write with multiple processes? Let's say you run your API service in separate stack as your webapp service. You can't really do that. I know some folks just make sure the transactions are so fast, collision will be rare. But why take the chance? #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.
- 2y ago