3 ms·

Cool stuff as usual, Anders. One of the nice things about running a networked DB is that it makes redeploying the application a bit simpler. You can spin up a n

Cool stuff as usual, Anders. One of the nice things about running a networked DB is that it makes redeploying the application a bit simpler. You can spin up a new EC2 instance or whatever and once it's online kill the old one. That gets 0 or close to 0 downtime. If the DB is on the same instance, replacing it requires loading up the DB onto the new instance, which seems more error prone than just restarting the app on the original instance, but in my experience that typically incurs downtime or some complicated handoff logic. Have you had to deal with anything like that running sqlite in prd?

andersmurphy 10mo ago
Oh there are a bunch of considerations. You're going to want persistent storage on your server, not ephemeral. You'll also want NVME. A lot of the time you're going to end up on bare metal running a single server anyway. You're going to have down time for migrations unless you're very clever with your schema and/or replicas. Litestream for me at least is what makes SQLite viable for a web app as prior to that there wasn't a good replication story. With litestream it's much easier to have a backup on standby. That being said where I have used it in production some amount of downtime has been acceptable so mileage may vary.
- kiitos 10mo ago
  > You're going to have down time for migrations unless you're very clever with your schema and/or replicas. probably worth stating these kinds of design considerations/assumptions up-front i'm sure lots of applications are fine with "downtime for [database] migrations" but lots more are definitely not, especially those interested in synthetic metrics like TPS
  - andersmurphy 10mo ago
    I'd argue the opposite most applications are fine with an hour of downtime a month and arguably much more downtime then that. The recent AWS and Cloudflare outages have proven that. You can achieve zero downtime with Sqlite if you really need to. TPS is not a synthetic metric when you cap out at 100 TPS because of Amdahl's law and your users having a power distribution.
    - kiitos 10mo ago
      1h of downtime per month means you're delivering at best two 9s of availability. again that may be fine for lots of applications but it's trivial scale, and certainly a couple orders of magnitude below what aws and cloudflare provide taking a step back, if your application's db requirements can be satisfied by sqlite [+replication] then that's great, but that set of requirements is much narrower, and much easier to solve, than what postgres is for
    - ngrilly 10mo ago
      How would you achieve zero downtime?
      - andersmurphy 10mo ago
        Personally, I'm a fan of event sourcing if you need zero downtime you probably need auditibility. You have one SQLite database that's an append only event log. You build projections from it into any number of SQLite databases. These databases pull from the log database and update themselves. They are completely expendable. So you never have to vacuum them or migrate them. You just build a new projection and then point to it. This is also how you can spread your sqlite over nodes (if that's your thing, with something like NATS). There are other ways where you're just more disciplined with your schema and indexes. I.e jsonb, partial indexes and existence based programming (from data oriented design). Edit: In the time it took me to write this cloudflare is down again https://news.ycombinator.com/item?id=46158200 https://news.ycombinator.com/item?id=46158200
        ngrilly 10mo ago
        That's indeed a possible (and very resilient) solution. But that's a significant shift for many apps. I'm fan of event sourcing in theory, but I've always been afraid of it making things overly complex in practice, for relatively small apps. But I haven't tried hard enough to have a real opinion on this.
        andersmurphy 10mo ago
- benjiro 10mo ago
  > Litestream for me at least is what makes SQLite viable for a web app as prior to that there wasn't a good replication story. Does Sqlite now not have a build in rsync for replicas? Searches, yep ... https://sqlite.org/rsync.html https://sqlite.org/rsync.html
  - andersmurphy 10mo ago
    Pretty sure rsync was only added in 2024, litestream predates it by quite a bit. What's cool is the newest version of rsync lets you replicate while in journal mode (which litestream doesn't support).
cwillu 10mo ago
Sqlite supports multi-process access, so as long as you've configured to permit that, you can do the same “start new process before retiring the old one” dance.

andersmurphy 10mo ago

Oh there are a bunch of considerations. You're going to want persistent storage on your server, not ephemeral. You'll also want NVME. A lot of the time you're going to end up on bare metal running a single server anyway. You're going to have down time for migrations unless you're very clever with your schema and/or replicas. Litestream for me at least is what makes SQLite viable for a web app as prior to that there wasn't a good replication story. With litestream it's much easier to have a backup on standby. That being said where I have used it in production some amount of downtime has been acceptable so mileage may vary.

kiitos 10mo ago
> You're going to have down time for migrations unless you're very clever with your schema and/or replicas. probably worth stating these kinds of design considerations/assumptions up-front i'm sure lots of applications are fine with "downtime for [database] migrations" but lots more are definitely not, especially those interested in synthetic metrics like TPS
- andersmurphy 10mo ago
  I'd argue the opposite most applications are fine with an hour of downtime a month and arguably much more downtime then that. The recent AWS and Cloudflare outages have proven that. You can achieve zero downtime with Sqlite if you really need to. TPS is not a synthetic metric when you cap out at 100 TPS because of Amdahl's law and your users having a power distribution.
  - kiitos 10mo ago
    1h of downtime per month means you're delivering at best two 9s of availability. again that may be fine for lots of applications but it's trivial scale, and certainly a couple orders of magnitude below what aws and cloudflare provide taking a step back, if your application's db requirements can be satisfied by sqlite [+replication] then that's great, but that set of requirements is much narrower, and much easier to solve, than what postgres is for
  - ngrilly 10mo ago
    How would you achieve zero downtime?
    - andersmurphy 10mo ago
      Personally, I'm a fan of event sourcing if you need zero downtime you probably need auditibility. You have one SQLite database that's an append only event log. You build projections from it into any number of SQLite databases. These databases pull from the log database and update themselves. They are completely expendable. So you never have to vacuum them or migrate them. You just build a new projection and then point to it. This is also how you can spread your sqlite over nodes (if that's your thing, with something like NATS). There are other ways where you're just more disciplined with your schema and indexes. I.e jsonb, partial indexes and existence based programming (from data oriented design). Edit: In the time it took me to write this cloudflare is down again https://news.ycombinator.com/item?id=46158200 https://news.ycombinator.com/item?id=46158200
      - ngrilly 10mo ago
        That's indeed a possible (and very resilient) solution. But that's a significant shift for many apps. I'm fan of event sourcing in theory, but I've always been afraid of it making things overly complex in practice, for relatively small apps. But I haven't tried hard enough to have a real opinion on this.
        andersmurphy 10mo ago
benjiro 10mo ago
> Litestream for me at least is what makes SQLite viable for a web app as prior to that there wasn't a good replication story. Does Sqlite now not have a build in rsync for replicas? Searches, yep ... https://sqlite.org/rsync.html https://sqlite.org/rsync.html
- andersmurphy 10mo ago
  Pretty sure rsync was only added in 2024, litestream predates it by quite a bit. What's cool is the newest version of rsync lets you replicate while in journal mode (which litestream doesn't support).

cwillu 10mo ago

Sqlite supports multi-process access, so as long as you've configured to permit that, you can do the same “start new process before retiring the old one” dance.