5 ms·

I see so many SQLite posts these days, but which companies with a lot (>1M) concurrent users are using SQLite in a non embedded fashion? It just seems so acade

I see so many SQLite posts these days, but which companies with a lot (>1M) concurrent users are using SQLite in a non embedded fashion? It just seems so academic. I’d like to use a web service or app backed primarily by SQLite and see how it goes.

jcwayne 4y ago
I'm curious how many companies building for >1M concurrent users have even a tenth of that in reality.
bob1029 4y ago
> I’d like to use a web service or app backed primarily by SQLite and see how it goes. We've been doing this in production for over half a decade now. It goes so well it becomes boring very quickly.
- endisneigh 4y ago
  Stats on your service/app and workload?
  - bob1029 4y ago
    We shard out to 1 SQLite database per user's working session, so there really isn't a meaningful figure if you are looking for apples-to-apples in a traditional sense. Our most heavily loaded SQLite instance probably sees 100 writes per second. In aggregate, we can handle tens of thousands of requests per second.
    - Abishek_Muthian 4y ago
      > 1 SQLite database per user's working session I presume you've designed your application to accommodate only the data types offered by SQLite and without any concurrent writing?
pjc50 4y ago
That's pretty much the anti-use-case for SQLite?
TillE 4y ago
Not everyone is doing only web development for millions of concurrent users. SQLite has been deployed on billions of devices, so "academic" is an odd term here.
- endisneigh 4y ago
  Non embedded. fly.imo for example exposes SQLite in a non embedded fashion.
  - ok_dad 4y ago
    I don't understand your claim, with fly.io it seems like you attach some storage to your application and store the SQLite file there, semi-locally, much the same as any other use of SQLite. The SQLite database code is still inside your chosen application, it's just loading the file from some "persistent volume" abstraction.
tptacek 4y ago
It works fine for read-heavy apps (ie: most apps) and, with things like Litestream, is especially attractive in distributed and edge settings, where you can stream out read replicas to a bunch of regions and direct writes to a single central region. That might sound confining, but it's what people already do with Postgres: read replicas, single write master. And the benefits aren't academic: having your database in-process not only simplifies your database code (because N+1 queries are no longer perf emergencies), but can also drastically speed up response times, because you're not dealing with compounding intra-DC latency responding to an HTTP request that incurs multiple database queries. And, finally: SQLite wants a single writer per database. But there's no rule that says your entire app has to land in a single .db file. That's what you'd naturally do in a Postgres app, but it's not necessarily the natural answer for a SQLite-backed app, where it's pretty trivial to pull from multiple databases with a single SQL statement. Don't get me wrong, I think it's still pretty niche-y as an alternative to standard n-tier app databases. You'd use it deliberately; it's unlikely to be a full-stack default. But people have definitely been sleeping on SQLite, and I think what you're seeing is some of those people beginning to wake up.
- bob1029 4y ago
  > And, finally: SQLite wants a single writer per database. But there's no rule that says your entire app has to land in a single .db file. I like finding hints of our architecture scattered across these SQLite threads :D It's very sketchy trying to slam all users into a single physical database. If you can slice up the users along unit of work, session, customer, account, etc., you can go horizontal very quickly.
- endisneigh 4y ago
  > It works fine for read-heavy apps (ie: most apps) and, with things like Litestream, is especially attractive in distributed and edge settings, where you can stream out read replicas to a bunch of regions and direct writes to a single central region. This is what I'm saying - I keep hearing this, which popular website is using SQLite as their main database? I don't have any doubt you can serve 1000 users, or even 10,000 current users using SQLite. There's a power law distribution with the popularity of sites. The most popular sites have multiple orders of magnitude. Can SQLite serve 500,000 current users with 75% reads and writes with the consistency you'd expect? > And, finally: SQLite wants a single writer per database. But there's no rule that says your entire app has to land in a single .db file. That's what you'd naturally do in a Postgres app, but it's not necessarily the natural answer for a SQLite-backed app, where it's pretty trivial to pull from multiple databases with a single SQL statement. Sounds reasonable in theory. How do you maintain consistency between all of these databases? Schema migrations? Backups and restores and maintaining referential consistency? By the time you implement all of this, and you certainly can, why not just use Postgres?
adamrezich 4y ago
you've almost certainly used software that uses sqlite today, possibly even right now https://www.sqlite.org/mostdeployed.html https://www.sqlite.org/mostdeployed.html
- tptacek 4y ago
  They stipulated non-embedded use cases for it --- really, their subtext is "show me places that use SQLite where ordinarily people would use an n-tier architecture", which is to say, Rails, Node, or Django. I think it's obvious to everyone that SQLite gets a lot of important use behind the scenes.
  - adamrezich 4y ago
    "embedded" can mean a few different things so that's a bit confusing for me, but if the intent was "show me places sqlite is used as a database backend for user-facing web software", the Nim forum (https://github.com/nim-lang/nimforum https://github.com/nim-lang/nimforum) uses sqlite as its database backend.
    - tptacek 4y ago
      That's a good example.
    - sgbeal 4y ago
      > but if the intent was "show me places sqlite is used as a database backend for user-facing web software", the Nim forum Perhaps more appropriately, sqlite's own forum is sqlite-backed: <https://sqlite.org/forum https://sqlite.org/forum> (running the Fossil SCM's forum interface, which itself exclusively uses sqlite for storage: <https://fossil-scm.org https://fossil-scm.org> (that whole site is an instance of fossil, serving all content from an sqlite db))
- zerr 4y ago
  That's because of its prevalence usage as a better flat file or config file.
sgbeal 4y ago
> I’d like to use a web service or app backed primarily by SQLite and see how it goes. Look no further than sqlite's own source control system: <https://fossil-scm.org https://fossil-scm.org> i've used that site almost daily since the end of 2007 and have encountered maybe (maybe) three "db is locked" errors.
Thaxll 4y ago
You won't find anyone because it would mean that you have a single service talking to SQLite which most webservices won't do for HA reasons, you have a few of them behind a loadbalancer. Using SQLite for any serious webservices is bad thing because you can only have a single app. Then you have other tools on top of SQLite for those shortcoming.
- tptacek 4y ago
  Having a single database service as a write master is already commonplace in HA Postgres.
  - Thaxll 4y ago
    Right but they do that over the network not using the filesystem.
    - tptacek 4y ago
      I'm not sure what's complicated about this. Instead of exposing a database instance as the write master, you expose a service as the write master. You can even do it transparently, if your load balancing fabric cooperates: just arrange to have read replicas redirect write requests to the write master.
      - avl999 4y ago
        With that setup you have essentially lost any benefits that SQLite provides, specifically any performance benefits by making a DB read/write a network call instead of a local call in the same process that manipulates some file on the same machine. You are closer to a postgres/mysql patterne except now your writes don't scale as well. What are we actually gaining with this setup other than perhaps easier administration of the "db service" compared to a mysql/postgres deployment?
        tptacek 4y ago
        Obviously, you have not lost those benefits: reads are satisfied from an in-process database, and, for most applications, most requests are reads.

jcwayne 4y ago

I'm curious how many companies building for >1M concurrent users have even a tenth of that in reality.

bob1029 4y ago

> I’d like to use a web service or app backed primarily by SQLite and see how it goes. We've been doing this in production for over half a decade now. It goes so well it becomes boring very quickly.

endisneigh 4y ago
Stats on your service/app and workload?
- bob1029 4y ago
  We shard out to 1 SQLite database per user's working session, so there really isn't a meaningful figure if you are looking for apples-to-apples in a traditional sense. Our most heavily loaded SQLite instance probably sees 100 writes per second. In aggregate, we can handle tens of thousands of requests per second.
  - Abishek_Muthian 4y ago
    > 1 SQLite database per user's working session I presume you've designed your application to accommodate only the data types offered by SQLite and without any concurrent writing?

pjc50 4y ago

That's pretty much the anti-use-case for SQLite?

TillE 4y ago

Not everyone is doing only web development for millions of concurrent users. SQLite has been deployed on billions of devices, so "academic" is an odd term here.

endisneigh 4y ago
Non embedded. fly.imo for example exposes SQLite in a non embedded fashion.
- ok_dad 4y ago
  I don't understand your claim, with fly.io it seems like you attach some storage to your application and store the SQLite file there, semi-locally, much the same as any other use of SQLite. The SQLite database code is still inside your chosen application, it's just loading the file from some "persistent volume" abstraction.

tptacek 4y ago

It works fine for read-heavy apps (ie: most apps) and, with things like Litestream, is especially attractive in distributed and edge settings, where you can stream out read replicas to a bunch of regions and direct writes to a single central region. That might sound confining, but it's what people already do with Postgres: read replicas, single write master. And the benefits aren't academic: having your database in-process not only simplifies your database code (because N+1 queries are no longer perf emergencies), but can also drastically speed up response times, because you're not dealing with compounding intra-DC latency responding to an HTTP request that incurs multiple database queries. And, finally: SQLite wants a single writer per database. But there's no rule that says your entire app has to land in a single .db file. That's what you'd naturally do in a Postgres app, but it's not necessarily the natural answer for a SQLite-backed app, where it's pretty trivial to pull from multiple databases with a single SQL statement. Don't get me wrong, I think it's still pretty niche-y as an alternative to standard n-tier app databases. You'd use it deliberately; it's unlikely to be a full-stack default. But people have definitely been sleeping on SQLite, and I think what you're seeing is some of those people beginning to wake up.

bob1029 4y ago
> And, finally: SQLite wants a single writer per database. But there's no rule that says your entire app has to land in a single .db file. I like finding hints of our architecture scattered across these SQLite threads :D It's very sketchy trying to slam all users into a single physical database. If you can slice up the users along unit of work, session, customer, account, etc., you can go horizontal very quickly.
endisneigh 4y ago
> It works fine for read-heavy apps (ie: most apps) and, with things like Litestream, is especially attractive in distributed and edge settings, where you can stream out read replicas to a bunch of regions and direct writes to a single central region. This is what I'm saying - I keep hearing this, which popular website is using SQLite as their main database? I don't have any doubt you can serve 1000 users, or even 10,000 current users using SQLite. There's a power law distribution with the popularity of sites. The most popular sites have multiple orders of magnitude. Can SQLite serve 500,000 current users with 75% reads and writes with the consistency you'd expect? > And, finally: SQLite wants a single writer per database. But there's no rule that says your entire app has to land in a single .db file. That's what you'd naturally do in a Postgres app, but it's not necessarily the natural answer for a SQLite-backed app, where it's pretty trivial to pull from multiple databases with a single SQL statement. Sounds reasonable in theory. How do you maintain consistency between all of these databases? Schema migrations? Backups and restores and maintaining referential consistency? By the time you implement all of this, and you certainly can, why not just use Postgres?

adamrezich 4y ago

you've almost certainly used software that uses sqlite today, possibly even right now https://www.sqlite.org/mostdeployed.html https://www.sqlite.org/mostdeployed.html

tptacek 4y ago
They stipulated non-embedded use cases for it --- really, their subtext is "show me places that use SQLite where ordinarily people would use an n-tier architecture", which is to say, Rails, Node, or Django. I think it's obvious to everyone that SQLite gets a lot of important use behind the scenes.
- adamrezich 4y ago
  "embedded" can mean a few different things so that's a bit confusing for me, but if the intent was "show me places sqlite is used as a database backend for user-facing web software", the Nim forum (https://github.com/nim-lang/nimforum https://github.com/nim-lang/nimforum) uses sqlite as its database backend.
  - tptacek 4y ago
    That's a good example.
  - sgbeal 4y ago
    > but if the intent was "show me places sqlite is used as a database backend for user-facing web software", the Nim forum Perhaps more appropriately, sqlite's own forum is sqlite-backed: <https://sqlite.org/forum https://sqlite.org/forum> (running the Fossil SCM's forum interface, which itself exclusively uses sqlite for storage: <https://fossil-scm.org https://fossil-scm.org> (that whole site is an instance of fossil, serving all content from an sqlite db))
zerr 4y ago
That's because of its prevalence usage as a better flat file or config file.

sgbeal 4y ago

> I’d like to use a web service or app backed primarily by SQLite and see how it goes. Look no further than sqlite's own source control system: <https://fossil-scm.org https://fossil-scm.org> i've used that site almost daily since the end of 2007 and have encountered maybe (maybe) three "db is locked" errors.

Thaxll 4y ago

You won't find anyone because it would mean that you have a single service talking to SQLite which most webservices won't do for HA reasons, you have a few of them behind a loadbalancer. Using SQLite for any serious webservices is bad thing because you can only have a single app. Then you have other tools on top of SQLite for those shortcoming.

tptacek 4y ago
Having a single database service as a write master is already commonplace in HA Postgres.
- Thaxll 4y ago
  Right but they do that over the network not using the filesystem.
  - tptacek 4y ago
    I'm not sure what's complicated about this. Instead of exposing a database instance as the write master, you expose a service as the write master. You can even do it transparently, if your load balancing fabric cooperates: just arrange to have read replicas redirect write requests to the write master.
    - avl999 4y ago
      With that setup you have essentially lost any benefits that SQLite provides, specifically any performance benefits by making a DB read/write a network call instead of a local call in the same process that manipulates some file on the same machine. You are closer to a postgres/mysql patterne except now your writes don't scale as well. What are we actually gaining with this setup other than perhaps easier administration of the "db service" compared to a mysql/postgres deployment?
      - tptacek 4y ago
        Obviously, you have not lost those benefits: reads are satisfied from an in-process database, and, for most applications, most requests are reads.