Many Small Queries Are Efficient in SQLite

(I am just asking: are you sure WAL is on?)

I'm seeing these numbers on my current scratch benchmark:

- Events append to a 10M+ record table (~4+ GiB database).

- Reads are fetched from a separate computed table, which is trigger-updated from the append-only table.

- WAL-mode ON, Auto-vacuum ON

  {:dbtype "sqlite",
   :auto_vacuum "INCREMENTAL",
   :connectionTestQuery "PRAGMA journal_mode;",
   :preferredTestQuery "PRAGMA journal_mode;",
   :dataSourceProperties
   {:journal_mode "WAL",
    :limit_worker_threads 4,
    :page_size 4096,
    :busy_timeout 5000,
    :enable_load_extension true,
    :foreign_keys "ON",
    :journal_size_limit 0,
    :cache_size 15625,
    :maximumPoolSize 1,
    :synchronous "NORMAL"}},

- With a separate writer process (sequential), and concurrently, two reader processes, I'm seeing 400+ append transactions/second (into the append-only table, no batching), and a total of 41,000 reads per second, doing `select *` on the trigger-updated table.

My schema is (deliberately) poor --- most of it is TEXT.

(edit: add clarifying text)

Yes, you can get by with one box for probably quite a while. But eventually a service of any significant size is going to need multiple boxes. Hell, even just having near-zero downtime deployments essentially requires it. Vertically scaling is generally a whole lot less cost effective than horizontal scaling (for rented servers), especially if your peak usage is much higher than off-hours use.

Or going from ~1ms over a local wired network to ~10ms over a wireless network.

Had a customer performance complaint that boiled down to that, something that should take minutes took hours. Could not reproduce it internally.

After a lot of back abd forth I asked if the user machine was wired. Nope, wireless laptop. Got them to plug in like their colleagues and it was fast again.

This isn’t an sqlite-specific point, although sqlite often runs faster on a single machine because local sockets have some overhead.

They call it the n+1 problem. 200 queries is the theoretically correct approach, but due to high network latency of networked DMBSes you have to hack around it. But if the overhead is low, like when using SQLite, then you would not introduce hacks in the first place.

The parent is saying that if you correctly design your application, but then move to system that requires hacks to deal with its real-world shortcomings, that you won't be prepared. Although I think that's a major overstatement. If you have correctly designed the rest of your application too, introducing the necessary hacks into a couple of isolated places is really not a big deal at all.

Maybe this has been solved though? Anybody here running a serious backend-heavy app with SQLite in production and can share? How do you remotely edit data, do analytics queries etc on production data?

(Long-time Fossil dev here.)

The @ syntax is pre-processed, transformed to printf()-like calls, the destination of which depends on whether fossil is currently running (to simplify only slightly) from the CLI or as a CGI/server process.

That is: @ itself has no runtime costs, but does transform into calls which do have runtime costs. (printf() and its ilk aren't cheap!)

I know it’s not and never suggested it was.

I was making the point that writes contain more overhead than reads (which should be obvious) so people should bear that in mind when reading this blog post.

Edit: is it “bear” or “bare”? I’m never sure with that phrase haha

Let's also not forget that db servers can have a memory, in that they can tweak query optimization based on previous queries or scans or whatever state is relevant. SQLite has no memory, in that sense. All query optimizations it makes are based solely upon the single query being processed.

Each query needs to navigate the index then read. The two queries do that twice.

Is it faster to read pages 30-50 of a book by:

a) Go to page 30, read until 50

b) Go to page 30, read that page, close book, open book, go to page 31 and so on.

Each page open you get to binary search to find the page.

Unless you have toy amounts data... or doing batch operations which is not typical (and can be problematic for other transactions due to locking, etc...)

We just flip into an app-side maintenance mode before we run the backup so we know there’s no writes, scp the file and then flip it back. We only do nightlies so it’s not a problem. The shell script is super simple and we’ve only needed to do nightly backups so far so we run it in a cron at midnight when no one is working. Ezpz. Literally took us an hour to implement and been chugging along without issues for nearly 2 years now without fail.

If we ever need more than that I’d probably just setup litestream replication.