Hacker News Reader

In my experience most SQLite writes take less than 1ms. So if your app is handling less than a thousand writes per second you will probably be OK!

A common pattern (which I've implemented myself in Datasette) is to put your writes in an in-memory queue and apply them using a single dedicated connection.

If you open multiple connections to an sqlite db from a single app you are a bad developer, full stop.

We have server app on a single beefy server servicing ~100,000 simultaneous users with avg user action per second of 0.2 (~20k actions per second) requiring some ~60k sqlite writes per second.

A single thread handles that with message passing with little load. We estimate we can handle 5-10 times as much without changing anything. That's with SSD's and standard OS caching. With either an in-memory db or using zstd compressed ramdisks the limits are ridiculous.

Do you happen to have a blog post laying out the details? Would love to learn more about what you're doing!

With either an in-memory db or using zstd compressed ramdisks the limits are ridiculous.

Who needs durability anyway, right?

Litestream can ensure a pretty decent level of durability, for a ramdisk anyway.

Suitability depends on what the requirements are.

There are workloads where some data loss is acceptable, like analytics.

But more seriously if you want the best of both worlds (in-memory speeds and durability) there are NVDIMMs.

SQL Server can take advantage of them: https://docs.microsoft.com/en-us/sql/relational-databases/pe...

I imagine with some effort SQLite could as well.

The problem is (I've seen it) staff turning off or disabling WAL[1] because it makes things faster, or as an unintended consequence of doing something else. Great if 'some data loss' being possible is understood in advance. Often, it isn't.

[1] example: not quite WAL, but disabling full logging, so it was then on simple logging, on MSSQL as part of a log shrink process. On a cient site. Who did 3-hourly log backups.

This is pretty much how we've been running in production with several different banks over the last half decade.

1 connection per database is critical. WAL is important. Not double-locking helps (most builds of SQLite serialize all writes internally).

You do all of these things correctly at the same time, then you can easily handle tens of thousands of transactions per second.

Additionally, we also do a per session database concept where data is scoped as specifically as possible. Using just 1 sqlite database for the whole application would be a mistake imo.

Synchronous replication is our next step, but we might build something in-house for this.

Are you aware of litestream? It's become quite useful in the last year or two, and recently provides synchronous replication. Worth checking it out (as well as rqlite and dqlite and bedrock) before starting your own project.

Hi, thanks for sharing your experiences. What's "Not double-locking"? Thanks.

> If you open multiple connections to an sqlite db from a single app you are a bad developer, full stop.

Even if you are taking advantage of the built in WAL feature?

Can you elaborate on why you think it's bad to open multiple connections? How would you allow read access while a writer is active without multiple connections?

By build a c/s architecture on top of SQLite, we can get almost 100K writes per second, including the object to JSON, JSON to SQL parsing process:

https://blog.synopse.info/?post/2022/02/15/mORMot-2-ORM-Perf...

Quick queuing theory recap: independent requests arriving at ~1000/s and each taking ~1 ms will, in the presence of any variability at all, be guaranteed to lead to an infinite backlog of unserved requests.

I’m not an expert on queue theory but this little “tidbit” has always bothered me. As far as I can tell, it is true IFF your variability causes the average wpos to go up, which negates the entire premise of “requests arriving at 1000/s”

If you’re averaging 1000/s and you get 1010 req in one sec, yes this leads to an infinite queue - but your average is now above 1000/s! Then if a few seconds later you get 990 requests, the queue catches up (because your avg is back to 1000)

IOW - it assumes only positive variability and no negative variability which does not mimic real world systems in my experience.

It’s an academic exercise that’s interesting to think about, but has no practical application. Else we would see infinite queue backups every time a system received a burst of traffic beyond their maximum throughput in any given time period.

What am I missing?

Just code up a 5-line simulation and see what happens?

You do actually expect the server to eventually recover from all load spikes, but that dead time means cumulatively you'll typically have queued more requests than you've handled at long enough time scales. On average that surplus is infinite, despite it sometimes being as low as 0.

It's not just academic, but it's not surprising that doesn't echo your real-world experience either. We don't usually run systems anywhere near capacity, and even when you do you'll still expect the queue to be able to handle the load eventually.

I tried sqlite once and had a lot of errors with just 2 writers. Instantly no errors after switching to postgres.

Bloomberg [1] uses SQLite in a custom distributed RDBMS engine of theirs in a way I lack the knowledge to completely understand its design.

I remember Richard Hipp mentioning Bloomberg in one of his interviews (don't ask me which one, I don't remember), that they use SQLite to serve billions of bank transaction on a daily basis without a problem.

  [1] https://github.com/bloomberg/comdb2

Update: I have found the following paper that describes the engine in greater detail: http://www.vldb.org/pvldb/vol9/p1377-scotti.pdf

SQLite has been used on top of a lot of things, including FoundationDB (https://apple.github.io/foundationdb/architecture.html).

That's different than using the DB directly though.

That is what "foundational" means.

After reading the paper, I see that it is not built on top of SQLite, but shares some components with SQLite.

It's like asking whether it's possible to write an OS kernel module in Python.

No, not really possible, and attempts to do so would be misguided. But it does not prevent Python from being highly successful in other areas.

Same with SQLite: it's a fine tool for a large spectrum of applications, but high-throughput OLTP is not it; SQLite can't reasonably replace Cassandra or even Postgres.

The point of much of the "SQLite hype" is that there are many applications where read load is high and queries complicated, but the write load is low to non-existent. In situations like these, you can make do with a smaller DB than Postgres.

> The point of much of the "SQLite hype" is that there are many applications where read load is high and queries complicated, but the write load is low to non-existent. In situations like these, you can make do with a smaller DB than Postgres.

Sure, but my point is that unless you know for sure your app will stay the same forever, why not just use Postgres or something more flexible to begin with? With things like Supabase, Hasura, and Managed Postgres why even bother with SQLite? Just seems like you're causing your tech team inevitable pain when you have more writes than expected and have to migrate off of it.

Both Fly.io and Cloudflare's solutions are a good example of this. If you're going to use another service, why even bother with SQLite? I totally get the SQLite use case when you're using it directly like with X-Range-Requests (https://phiresky.github.io/blog/2021/hosting-sqlite-database...) or SQLite.js and a WASM app (https://blog.ouseful.info/2022/02/11/sql-databases-in-the-br...), but idk.

tldr: if you're using a service, why bother with one backed by SQLite instead of Postgres? The underlying data store will be abstracted away anyways. One scales decently, and the other, not so much.

These are services that replicate flat files and key-value stores. SQLite seems like a step up, particularly if it's similarly priced to flat file replication.

Why don't you solve a bunch of problems you don't have with tools you don't need? You might want them in the future. Thats good engineering

You say that on one of the top over engineering forums in the world. People on HN are running their startups like they will soon be larger than Google or Meta, building their architecture and software like that; not like you suggest.

10K users isn’t much, but 10k concurrent writer’s is a different proposition - I’d be surprised if 100k users had 10k concurrent writers

That's a fair point. AFAIK SQLite uses a queue (in WAL mode) in order to handle concurrent writes. I imagine it basically couldn't handle 10K concurrent writes to begin with in a practical application since newly read data would be out of date pretty quickly.

I’m not sure as I’ve never used it but if the writes were fast enough perhaps it wouldn’t be a problem

idk, the author themselves don't recommend it:

>If many threads and/or processes need to write the database at the same instant (and they cannot queue up and take turns) then it is best to select a database engine that supports that capability, which always means a client/server database engine.

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

That reads to me like a somewhat-sarcastic way to make fun of people who think SQLite can't handle their write load.

Not a lot of applications have many processes that need to write at the same instant in time and cannot be put in a queue instead.

Why build a queue before you really need it when you can just use postgres?

To be explicit, SQLite doesn't support concurrent writers. In WAL mode it can handle a single writer with concurrent readers. In "vanilla" mode, a writer requires exclusive access to the database.

I have a controversial opinion.

We got to the canonical "LAMP" stack incrementally, shaped by the computing environment at the time. The servers that you had available to your web app 15 years ago were maybe 2 cores running at 1.5GHz and you had a couple gigs of RAM, and you wrote the app in a very slow dynamic language. So in order to scale out to the Internet, you had to split your application into replicas that ran on different servers; the traffic you were getting and the architecture of your application meant that you needed more than 1 CPU second per second, and that was the only way to get that at the time. All of these replicas needed some coordination, so you had a database server to handle that coordination, and it ran on the biggest computer possible because it was the single point of failure. (And when one computer wasn't big enough, it was time for ugly hacks like sharding.)

Fast forward 15 years and the landscape has changed dramatically. You can get 448vCPU machines from AWS. You're writing your application that is compiled or JIT'd into native code. With the same hardware, your application of today might be 10-100x faster than your application 15 years ago on the same hardware. But the hardware is even faster, and computers are bigger (you can go get a 64 core processor off the shelf at an electronics store!). So one node might now be 1000-10000x more powerful than where this "I must have a hundred replicas of my application" mindset came from.

With that in mind, if you start right now, you can build a web application that scales beyond your wildest dreams with a single computer. If you only have one computer, why not build the database into the application? You will have made so much money by the time the 448 core computer is inadequate for your application that it doesn't even matter. Hire 100 developers to move the thing to FoundationDB or Spanner or Postgres or whatever "real" database you think you need to scale to the next 10 billion users. (Interestingly, the top article on HN right now is "What if it changes?", a sarcastic reminder that maybe today you should build the system you need for today. If it sucks in a year, fix it in a year.)

I am throwing some pretty important things out the window. You probably update your application, so you want some rolling deployment that aborts if the new version fails to start up or whatever. A tornado blowing up your datacenter on your biggest use day of the year would be bad. Your users are all over the world, so you probably want to serve as much content as you can from a computer near them. But honestly, the state of the art for these concepts are pretty new. Globally consistent ("planet scale") transactions are hard, and not that fast. Automated canarying is also not trivial. So you might build a really complicated application to support those needs, and not even achieve them, because nobody has achieved perfection there yet.

Anyway, my controversial opinion is: don't build a distributed system unless you are absolutely sure you must have a distributed system. Every time you split up your state storage, your work as a programmer becomes harder. A single thread; no need for transactions, everything happens in order. A single computer with multiple threads; you'll need some locks, write barriers, or atomic instructions. Multiple computers with multiple threads? Now you're writing a computer science paper. That's a super fun activity, but check that discovering new classes of computer science problems is what your company's business is before you go out of your way to start doing it. You might be able to make a lot of money by being pretty boring. When you're relaxing on your private island, you might find a recreational activity even more fun than finding bugs in distributed transaction protocols. Who knows.

The currently-accepted simplification is to use a single database server to coordinate your stateless application replicas. That doesn't protect you from tornadoes, give users in Antarctica sub-millisecond page loads, or let you upgrade the database without downtime. If you're OK with that, it's totally reasonable to just build the database into your application as SQLite does. It's simpler, and no worse to an outside observer.

(That said, I tend to reach for Postgres first because it has a lot of polish that I don't think SQLite has. But I don't think anyone is unreasonable or stupid for picking SQLite. Especially if the SQLite instance streams its data to S3 and you have point-in-time recovery options for a disaster or bad release. You're going to want those with Postgres too, and they aren't enabled out of the box.)

Brilliant idea. Most startups or side projects won't reach the day when a single 448vCPU machine is not enough to handle their traffic.

> I have a controversial opinion.

How is this controversial. This should be mainstream thinking. Solve the problems that you have, when you're having them, instead of spending time preparing for a future that may never come.

"This caching ideally would be enforced by the browser, and not the server"

I don't think we'd need a UI served by that central server at that point. A sqlite file and some local program that communicates with other (de)centralized hosts for whatever services or data isn't available locally sounds like a neat departure from the approach the Solid project is taking with plug-able data backends.

Instead whatever ecosystem springs up around really making use of sqlite at "the edge" (here I mean individual's computing devices - not what they mostly references to fly.io and cloudflare from TFA) could invert how web apps are delivered and consumed for the 1% of people who care about this kind of thing. By invert I just mean that the user hosts their own frontends and interfaces with DNS named hosts / ports providing functionality, CPU, disk, network a la plan9.

> CouchDB tried the whole DB-per-user thing and it didn't end super well.

Can anyone give further context here? Haven't looked at CouchDB for several years, remember it as being good for certain use cases (intermittently offline devices etc).

CouchDB doesn't support document ACLs so inevitably you will have to use a DB per user and map reduce in order to implement anything that has more than one user.

Yeah it stuck me as a weird comment. As if there was some public case of a company doing this and then going under or something.

So I'm wondering as well who it didn't end too well for.

Concurrent writers isn't a thing people directly need. What they need is high write throughput. One way to improve write throughput is with concurrency, but the other way is by just being fast.

Making the database fast has the advantage of not requiring extra concurrency complications throughout the rest of your application.

If I understand your idea correctly, wouldn't that require connecting to the browser of every single person who has posted to load the content?

Yes - hypothetically you could allow for some notion of a remote TTL where the server could cache as opposed to the browser, but when you follow this to its logical conclusion you're just reinvented the sort of thing we already have.

My only experience with sqlite is having to spend time ripping it out and replacing with something that actually works.

Sqlite is almost never the right solution.