3 ms·

> have a lot more frequent failover of SQLite (if it is running embedded within the app, thus following the app's deployment schedule). That does sound like it

> have a lot more frequent failover of SQLite (if it is running embedded within the app, thus following the app's deployment schedule). That does sound like it's going to be difficult to get right. But if Litestream eventually implements a robust solution for this problem, then I think some added complexity in the deployment process will be a reasonable price to pay for increased app performance the rest of the time.

tptacek 4y ago
For what it's worth, I think this problem (the complexity that bleeds into the app for handling leaders) is mostly orthogonal to the underlying database. You have the same complexity with multi-reader single-writer Postgres. But the code that makes multi-reader SQLite work is a lot easier to reason about. Let me know if you think I'm off about that.
- mwcampbell 4y ago
  Unless I'm misunderstanding something, I do think using SQLite makes a significant difference in the complexity of app deployment. When using multi-region Postgres, it's true that you only want the Postgres leader to be accessed by app instances in the same region, so the app instances all have to know which region is running the leader. But multiple app instances in that region can connect to that Postgres leader, so it's easy to do a typical rolling deploy. With SQLite, only one app instance at a time can write to the database, so IIUC, there will have to be a reliable way of doing failover with every app deploy. I suppose the same thing has to happen in the Postgres scenario when updating Postgres itself, but that's way less frequent than deploying new versions of the app.
  - nickcox 4y ago
    > multiple app instances in that region can connect to that Postgres leader, so it's easy to do a typical rolling deploy This is mentioned as a drawback at towards the end of the blogpost, isn't it? It does seem it would make deployments rather awkward.
  - ngrilly 4y ago
    You can have two app instances writing to the database concurrently, as long as they are running on the same machine. Then it’s possible to deploy a new version without downtime by starting the new binary, switching traffic to the new instance, draining connections to the old instance, and shut it down. That’s kind of how nginx for example upgrades itself without downtime. That’s not the usual way nowadays with containers and VMs, but it was very common before. It’s definitely doable with SQLite, and not very complex.
    - nijave 4y ago
      Your app machine has to be overspecced so it can support running 2 copies of the app at once
      - ngrilly 4y ago
        No. The number of processes requests in unchanged. They are just dispatched between 2 instances: the new one and the old one. Actually, all new requests are going to the new instance, and only the pending requests are processed by the old instance.
        nijave 4y ago
        Sure but most applications need a certain amount of memory regardless of the amount of requests they're servicing
        ngrilly 4y ago
        Agreed, RAM usage may be slightly elevated during the switch. But it doesn’t seem to be a big issue in practice. nginx for example is updating itself like this and it works fine.

tptacek 4y ago

For what it's worth, I think this problem (the complexity that bleeds into the app for handling leaders) is mostly orthogonal to the underlying database. You have the same complexity with multi-reader single-writer Postgres. But the code that makes multi-reader SQLite work is a lot easier to reason about. Let me know if you think I'm off about that.

mwcampbell 4y ago
Unless I'm misunderstanding something, I do think using SQLite makes a significant difference in the complexity of app deployment. When using multi-region Postgres, it's true that you only want the Postgres leader to be accessed by app instances in the same region, so the app instances all have to know which region is running the leader. But multiple app instances in that region can connect to that Postgres leader, so it's easy to do a typical rolling deploy. With SQLite, only one app instance at a time can write to the database, so IIUC, there will have to be a reliable way of doing failover with every app deploy. I suppose the same thing has to happen in the Postgres scenario when updating Postgres itself, but that's way less frequent than deploying new versions of the app.
- nickcox 4y ago
  > multiple app instances in that region can connect to that Postgres leader, so it's easy to do a typical rolling deploy This is mentioned as a drawback at towards the end of the blogpost, isn't it? It does seem it would make deployments rather awkward.
- ngrilly 4y ago
  You can have two app instances writing to the database concurrently, as long as they are running on the same machine. Then it’s possible to deploy a new version without downtime by starting the new binary, switching traffic to the new instance, draining connections to the old instance, and shut it down. That’s kind of how nginx for example upgrades itself without downtime. That’s not the usual way nowadays with containers and VMs, but it was very common before. It’s definitely doable with SQLite, and not very complex.
  - nijave 4y ago
    Your app machine has to be overspecced so it can support running 2 copies of the app at once
    - ngrilly 4y ago
      No. The number of processes requests in unchanged. They are just dispatched between 2 instances: the new one and the old one. Actually, all new requests are going to the new instance, and only the pending requests are processed by the old instance.
      - nijave 4y ago
        Sure but most applications need a certain amount of memory regardless of the amount of requests they're servicing
        ngrilly 4y ago
        Agreed, RAM usage may be slightly elevated during the switch. But it doesn’t seem to be a big issue in practice. nginx for example is updating itself like this and it works fine.