8 ms·

I've yet to see how you can use SQLite for a multi-user multi-write app effectively. Unless we're all going back to single tenant, single user applications SQLi

I've yet to see how you can use SQLite for a multi-user multi-write app effectively. Unless we're all going back to single tenant, single user applications SQLite seems overhyped for the new usecases. Don't get me wrong, there are plenty of uses for SQLite, but I think the hype is getting out of hand IMHO. If you are making an app that only a single person is going to use at a time, then there are plenty of options, including SQLite. Heck, IndexedDB is sufficient. CouchDB tried the whole DB-per-user thing and it didn't end super well. Happy to be proven wrong though. If anyone has an example of a site with more than say, 10K concurrent writers (edit: changed from users) running on a single SQLite DB I'd probably change my mind. --- Now, what would be interesting is a way to architect an app such that you can have a central entity, like this site, for instance, but all of your posts are actually referring to your own SQLite, or equivalent store. In that sense you can own your data completely. For performance you can specify a TTL for your data and the consumer (this site) could cache* it accordingly. Though this would probably end up being a lot less performant than simply using something like Postgres, but at least you'd have more control over your own data. * - This caching ideally would be enforced by the browser, and not the server, that way the server actually never touches or knows what data you have.

catmanjan 4y ago
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
- endisneigh 4y ago
  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.
  - catmanjan 4y ago
    I’m not sure as I’ve never used it but if the writes were fast enough perhaps it wouldn’t be a problem
    - endisneigh 4y ago
      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 https://www.sqlite.org/whentouse.html
      - simonw 4y ago
        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.
        antifa 4y ago
        Why build a queue before you really need it when you can just use postgres?
  - deleted 4y ago
    [deleted]
  - pdw 4y ago
    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.
anamexis 4y ago
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?
- endisneigh 4y ago
  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.
stefanos82 4y ago
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 http://www.vldb.org/pvldb/vol9/p1377-scotti.pdf
- endisneigh 4y ago
  SQLite has been used on top of a lot of things, including FoundationDB (https://apple.github.io/foundationdb/architecture.html https://apple.github.io/foundationdb/architecture.html). That's different than using the DB directly though.
  - jdright 4y ago
    That is what "foundational" means.
- sally1620 4y ago
  After reading the paper, I see that it is not built on top of SQLite, but shares some components with SQLite.
nine_k 4y ago
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.
- endisneigh 4y ago
  > 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-databases-on-github-pages/ 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-browser-via-wasm-sqlite-and-duckdb/ 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.
  - skybrian 4y ago
    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.
  - deleted 4y ago
alatkins 4y ago
> 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).
- endisneigh 4y ago
  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.
- LAC-Tech 4y ago
  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.
simonw 4y ago
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.
- rogers18445 4y ago
  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.
  - codethief 4y ago
    Do you happen to have a blog post laying out the details? Would love to learn more about what you're doing!
  - tremon 4y ago
    With either an in-memory db or using zstd compressed ramdisks the limits are ridiculous. Who needs durability anyway, right?
    - jaytaylor 4y ago
      Litestream can ensure a pretty decent level of durability, for a ramdisk anyway. Suitability depends on what the requirements are.
    - taspeotis 4y ago
      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/performance/configuring-storage-spaces-with-a-nvdimm-n-write-back-cache?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/pe... I imagine with some effort SQLite could as well.
jrockway 4y ago
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.)
deleted 4y ago
[deleted]
tepitoperrito 4y ago
"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.
otabdeveloper4 4y ago
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.
tlb 4y ago
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.

catmanjan 4y ago

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

endisneigh 4y ago
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.
- catmanjan 4y ago
  I’m not sure as I’ve never used it but if the writes were fast enough perhaps it wouldn’t be a problem
  - endisneigh 4y ago
    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 https://www.sqlite.org/whentouse.html
    - simonw 4y ago
      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.
      - antifa 4y ago
        Why build a queue before you really need it when you can just use postgres?
- deleted 4y ago
  [deleted]
- pdw 4y ago
  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.

anamexis 4y ago

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?

endisneigh 4y ago
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.

stefanos82 4y ago

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 http://www.vldb.org/pvldb/vol9/p1377-scotti.pdf

endisneigh 4y ago
SQLite has been used on top of a lot of things, including FoundationDB (https://apple.github.io/foundationdb/architecture.html https://apple.github.io/foundationdb/architecture.html). That's different than using the DB directly though.
- jdright 4y ago
  That is what "foundational" means.
sally1620 4y ago
After reading the paper, I see that it is not built on top of SQLite, but shares some components with SQLite.

nine_k 4y ago

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.

endisneigh 4y ago
> 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-databases-on-github-pages/ 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-browser-via-wasm-sqlite-and-duckdb/ 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.
- skybrian 4y ago
  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.
- deleted 4y ago

alatkins 4y ago

> 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).

endisneigh 4y ago
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.
LAC-Tech 4y ago
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.

simonw 4y ago

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.

rogers18445 4y ago
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.
- codethief 4y ago
  Do you happen to have a blog post laying out the details? Would love to learn more about what you're doing!
- tremon 4y ago
  With either an in-memory db or using zstd compressed ramdisks the limits are ridiculous. Who needs durability anyway, right?
  - jaytaylor 4y ago
    Litestream can ensure a pretty decent level of durability, for a ramdisk anyway. Suitability depends on what the requirements are.
  - taspeotis 4y ago
    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/performance/configuring-storage-spaces-with-a-nvdimm-n-write-back-cache?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/pe... I imagine with some effort SQLite could as well.

jrockway 4y ago

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.)

deleted 4y ago

[deleted]

tepitoperrito 4y ago

"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.

otabdeveloper4 4y ago

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.

tlb 4y ago

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.