Many Small Queries Are Efficient in SQLite

The pattern I like to advocate for now is to do customer sharding with SQLite. Cloudflare makes this easy with D1, you can tie Durable Objects to a user as an afterthought.

The nice thing about this pattern is that you can create foreign data wrappers for your customer SQLite databases and query them as if they were in postgres, cross customer aggregations are slow but individual customer analytics are quite fast, and this gives you near infinite scalability.

You hit those write limits surprisingly early if you use background workers though. I had a project with very little user traffic that choked on SQLite simply because a few Celery workers were updating job statuses concurrently. It wasn't the volume of data, just the contention from the workers that forced the switch to Postgres.

Most of us, majority of the time, don’t need that level of optimization, because not every project is destined to grow 10x quickly.

LLM also has this tendency of premature optimization where they start to write very complex classes for users who only want to extract some information just to resolve a quick problem.

The thing is sqlite can scale further vertically than most network databases. In some context's like writes and interactive transactions it outright scales further. [1]

That's before you even get into sharding sqlite.

[1] - https://andersmurphy.com/2025/12/02/100000-tps-over-a-billio...

I don't see how anyone would design a system that executes 200 queries per page. I understand having a system that is ín use for many many years and accumulates a lot of legacy code eventually ends up there, but designing? Never. That's not design, that's doing a bad job at design.

There's also the alternative of having a cluster with one local DB in each node

The same is true for regular databases though, isn't it?

Network adds latency and while it might be fine to run 500 queries with the database being on the same machine, adding 1-5ms per query makes it feel not okay.

Yes, it's saying that the string concatenation and other outside-of-SQL business logic took 22ms, running in their custom TH1 scripting language. In 2016.

Update: Actually it looks like I was wrong about TH1: https://fossil-scm.org/home/doc/tip/www/th1.md

The timeline appears to be constructed by C code instead: https://www.fossil-scm.org/home/file?name=src/timeline.c&ci=...

Update 2: Here's the timeline code from September 2016: https://www.fossil-scm.org/home/file?name=src/timeline.c&ci=...

Back then it had some kind of special syntax for outputting HTML:

    sqlite3_snprintf(sizeof(zNm),zNm,"b%d",i);
    zBr = P(zNm);
    if( zBr && zBr[0] ){
      @ <p style='border:1px solid;background-color:%s(hash_color(zBr));'>
      @ %h(zBr) - %s(hash_color(zBr)) -
      @ Omnes nos quasi oves erravimus unusquisque in viam
      @ suam declinavit.</p>
      cnt++;
    }
  }

That @ syntax is used in modern day Fossil too. Maybe that adds some extra overhead?

As another example, a SQL Server optimization per https://learn.microsoft.com/en-us/sql/t-sql/statements/set-n...:

> For stored procedures that contain several statements that don't return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

Rather I think their point is that since O(N) is really X * N, it's not the N that gets you, it's the X.

It being so obvious, why is sqlite not the de facto standard?

IMO the page is concise and well written. I wouldn’t call it very elaborate.

Maybe the page could have been shorter, but not my much.

It's not the cost of protecting one transaction from another transaction so much as the cost of flushing a transaction to storage to survive a crash.

In the bad old days you had to wait for a lever to move and for the disk to rotate at least once!

Probably even more so if it can fully fit into CPU cache.

Even if you're on the same local server, you're still going over a socket to a different service, whereas with sqlite you remain in the same application / address space / insert words I don't fully understand here. So while client/server SQL servers are faster locally than on a remote server, they can (theoretically) never be as fast as SQLite in the same process.

Of course, SQLite and client/server database servers have different use cases, so it is kind of an apples and oranges comparison.

I think they're trying to not shame other services, but yes the comparison is vs networked whether that's local on loopback or not. For a small query, which is what they're talking about, it's not inconceivable that formatting into a network packet, passing through the userspace networking functions, into and through kernel, all back out the other side, then again for the response, is indeed meaningfully slower than a simple function call within the program.

Connecting to localhost still involves the network stack and a fair bit of overhead.

SQLite is embedded in your program's address space. You call its functions directly like any other function. Depending on your language, there is probably some FFI overhead but it's a lot less than than an external localhost connection

I think the most common set up is to have your application server and DB on different hosts. That way you can scale each independently.

Yeah, en/decoding results and parameters from and to JS types is also quite the timewaster

sqlite+litestream [1] is fantastic, i highly recommend it.

I use it with pocketbase and it is a delightful and very productive setup.

This guide [2] is for an older version of pocketbase and litestream, but i can update it if would be helpful/interesting for anyone.

[1] https://github.com/benbjohnson/litestream/

[2] https://notes.danielgk.com/Pocketbase/Pocketbase+on+Fly.io

as an in memory database, I got around 40,000,000 reads per second. Using WAL and a file rather than in memory, around 900,000 reads per second. This is single threaded, for a simple integer ID to string Value query, and a few years old at this point, and only minor config optimizations eg not even using memory mapped io and a ~3gb database with a million or so rows on a Windows machine. The performance really is amazing.

Orders of magnitude I would imagine. Very significantly faster.

Yes. Git is the same way: it uses the Linux kernel for storage, and the Linux kernel is managed with Git. :P

scp works as long as the app is not making changes at the same time.

If there's a chance someone is writing to the database during the copy, you should "sqlite3 database.sqlite .backup" (or ".dump") first; Or, alternatively, on a new enough sqlite3, you have a builtin sqlite3_rsync that is like rsync except it interacts with the sqlite3 updates to guarantee a good copy at the other end.

For analytic queries, yes, a single SQL query often beats many small ones. The query optimizer is allowed to see more opportunities to optimize and avoid unnecessary work.

Most SQLite queries however, are not analytic queries. They're more like record retrievals.

So hitting a SQLite table with 200 "queries" is similar hitting a webserver with 200 "GET" commands.

In terms of ergonomics, SQLite feels more like a application file-format with a SQL interface. (though it is an embedded relational database)

https://www.sqlite.org/appfileformat.html

Depends. Throughput is probably higher, but the latency of a big scan might be larger than a small one, so many small lookups might feel more responsive if they’re each rendered independently. The example on the page doesn’t look like it can be merged into a single scan. I’m not a SQL expert but at a glance it does look like it could maybe be compressed into one or two dozen larger lookups.

One query isn't cheaper than two queries that do the same amount of IO and processing and operate in the same memory space