8 ms·

the fact that SQLite is a library (embedded DB) means only one node can access the DB at a time. This would not be appropriate for many apps that require HA.

• zie 4y ago
  May I introduce you to https://litestream.io https://litestream.io
  • bsaul 4y ago
    I don’t see how sqlite replicated to other servers can be faster than any other relational dbs replicated on those same servers, at least without taking more chances to loose data. The limit is most likely network latency anyway, isn’t it ?
    • tptacek 4y ago
      Other relational databases require network roundtrips to fetch information from the database once it's replicated. SQLite doesn't. It's a huge performance difference, to the point where it changes how you access your database; for instance, there's not much point in hunting down N+1 query patterns anymore.
      • bsaul 4y ago
        hm i see. so the cost is in the initial setup of the client instance, which needs to download the whole database to its local sqlite ?
  • luckylion 4y ago
    From what I understand, that streams the file to a separate location, but it doesn't change the file-locking, does it?
    • zie 4y ago
      Right, for a SQLite based app, you are mostly limited to 1 instance running at any given time(there are multi-node stuff shoved behind SQLite done by 3rd parties, but that's a whole diff. can of worms). The point being, you run 1 instance and you have litestream replicate to your backup node for HA purposes. Now you are going to think, what about scaling?!!? How many apps actually need to scale beyond 1 node? Very few. If you run into scaling problems, that is when you deal with solving your scaling problem. Because scaling is unique to each application. But before you remotely think about scaling past one node, you just build the node bigger. Nodes can get pretty massive these days.
  • hamandcheese 4y ago
    Litestream didn’t exist until late 2020, meanwhile postgres has existed for decades. Moreover, SQLite requires the place you run your application to have durable storage, which is a huge departure from the status quo. It’s definitely neat, but the stack as a whole doesn’t strike me as mature enough to replace Postgres just yet.
    • ngrilly 4y ago
      > SQLite requires the place you run your application to have durable storage, which is a huge departure from the status quo. Having durable storage used to be the status quo for decades. It changed only recently with cloud providers (or their customers) pushing for stateless workloads because they are much easier to manage in a distributed system than stateful workloads.
    • zie 4y ago
      SQLite has been around for a very long time, it is easily the worlds most deployed SQL database. Litestream is just a way to do live sync/replication to make HA easier. There are certainly use cases where SQLite is not a good fit. There are likewise use-cases where PostgreSQL is not a good fit either. One is not better than the other, it just depends on your particular needs for that particular project. My point is, SQLite is a totally sane and reasonable storage/DB solution for many server side applications as well.
    • tptacek 4y ago
      You're writing this as if LiteFS and Postgres were basically the same thing, and the selection criteria just boils down to the Postgres pedigree and maybe all the Postgres-specific SQL features. But that's not the case at all. The difference between replicated SQLite and Postgres is that SQLite is in-core: it doesn't have to round-trip on the network to fetch data; it can burn through a large set of N+1 queries faster than Postgres can handle a single select. The difference is that SQLite is much faster. You sacrifice things to get that speed (Postgres features, set-and-forget write concurrency). Nobody is saying there's no reason to use Postgres, or maybe even that Postgres is the right call most of the time. But the idea that SQLite is rarely appropriate for concurrent serverside applications? It's received wisdom and it's wrong. Somebody across the thread actually suggested that WordPress was an example of the kind of application that SQLite wouldn't work for, that needed an n-tier database. (Leave aside the fact that WordPress doesn't support SQLite, has instead a longstanding MySQL dependency). WordPress! WordPress is a perfect example of a concurrent serverside application that probably should almost exclusively use SQLite. As I said in a different comment: the whole movement towards static site generators is, in large part, a reaction to how bad n-tier databases are for a very large, popular class of concurrent serverside applications.
• Aperocky 4y ago
  Indeed, SQLite should not be used anywhere that scaling or concurrency is a concern, or in general any external customer facing features. In a similar vein, an internal subsystem can probably do away with a dedicated database server. Latter is likely more common.
  • MobiusHorizons 4y ago
    Care to justify that? The evidence seems to be against you for most read heavy workloads, especially if latency is any concern.
  • tptacek 4y ago
    That's silly. SQLite works fine for all sorts of customer features, and, deployed carefully, is fine with concurrency (writes are serialized, per database, but SQLite makes it easy to use multiple databases). SQLite has this weird reputation, I think, because frameworks like Rails used it as their "test" database, and the industry has broadly slept on how capable SQLite is in serverside applications.
    • AlphaSite 4y ago
      I think if you use a lot of database features, sqlite isn’t really powerful enough for a lot of use cases IMO. Or atleast that’s the impression I have.
      • tptacek 4y ago
        Which features would those be?
        • fierro 4y ago
          won't long running write transactions block eachother? Not all apps can avoid the need for these kinds of transactions.
        • luckylion 4y ago
          Concurrent inserts. There are applications where you don't care about that, but that excludes a lot of web-apps, or things running with multiple processes. If that changes, I'd agree with your point, but currently that's a big constraint.
• tptacek 4y ago
  Only one node can write to a given SQLite database at a time. SQLite handles concurrent readers swimmingly. The fact that SQLite is a library has not that much to do with its concurrency model?
  • fierro 4y ago
    yes this is true, I was imprecise
  • beoberha 4y ago
    What do you do if the node running the SQLite instance goes down?
    • tptacek 4y ago
      The same thing you do in a multi-reader, single-writer MySQL cluster, except with SQLite there's one less thing to go down, because the database is embedded in your process.
      • beoberha 4y ago
        I’m not familiar with MySQL specifically, but people worried about HA have standby replicas. Enterprise DBs make this almost trivial to do and it’s very possible with PG extensions. I’m sure someone has built a system like that with SQLite, but it’s much less ideal than other database systems.
        • snovv_crash 4y ago
          What is the point of having a HA database if your frontend app isn't running to be able to query it? SQLite gives the guarantee that if your frontend app is running, then the DB is available as well. Try doing that with a non-embedded DB.
          • anonymousDan 4y ago
            The point of disaggregating your replicated database is you can scale your app and db tiers independently. Typically you only want to have a handful of database replicas (e.g. to failover after your primary fails), especially if you want strongly consistent replication. But your stateless app tier may require many more servers.
• randomdata 4y ago
  The only real difference between SQLite and something like Postgres or MySQL is that Postgres and MySQL bundle a networking layer on top of their embedded DB engines. If you are worried about high availability, chances are you too are building a networking layer on top of a database, so what do you need two networking layers for?
  • pjmlp 4y ago
    And stored procedured, debugging, JIT compilation, extension in multiple languages, ....
  • beoberha 4y ago
    Do people build their own networking layers above SQLite (besides for fun)? If you’re doing that, then you need to build some kind of replication story. At that point it makes sense to just use PG or MySQL
    • randomdata 4y ago
      Unquestionably. Web apps and the like which are little more than networking frontends to a database are probably the most common type of software written these days.
      • beoberha 4y ago
        Ah, we’re talking about different things. I took “network layer” to mean something like the ability to connect directly to the database over the network, not through some shim application.
        • randomdata 4y ago
          Meaning something like rqlite[1]? The age of fat desktop clients all connecting back to the central SQL server is long behind us, so yeah there is probably little reason beyond fun for something like that these days, but where there is fun! [1] https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite
• hamandcheese 4y ago
  Not only that, but typical devs write slow application code, which needs to be distributed horizontally across many workers.
• Cthulhu_ 4y ago
  There's rqlite (https://github.com/rqlite/rqlite https://github.com/rqlite/rqlite), which looks cool on the surface but... it's a layer on top of sqlite, at which point you should probably think long and hard whether it's still the right tool or you should switch to e.g. postgres.
• ngrilly 4y ago
  To get HA, it is now possible to replicate the SQLite database using LiteFS, which is similar to how you would get HA with MySQL or PostgreSQL: https://fly.io/docs/litefs/ https://fly.io/docs/litefs/