7 ms·

Once you have that constraint, it means you will either have the same network latency when writing to SQLite (if it is fronted by some lightweight proxy), or ha

Once you have that constraint, it means you will either have the same network latency when writing to SQLite (if it is fronted by some lightweight proxy), or have a lot more frequent failover of SQLite (if it is running embedded within the app, thus following the app's deployment schedule). I suppose if someone decides to deploy Postgres/MySQL replicas as a sidecar, then it will be the same as what you will end up with?

tptacek 4y ago
Yes: nobody is claiming otherwise. SQLite drastically speeds up reads, and it speeds up writes in single-server settings. In a multi-server setting, writes have comparable (probably marginally poorer, because of database-level locking, in a naive configuration) performance to Postgres. The lay-up wins of SQLite in a multi-server environment are operational simplicity (compared to running, say, a Postgres cluster) and read acceleration.
- throwaway894345 4y ago
  > The lay-up wins of SQLite in a multi-server environment are operational simplicity (compared to running, say, a Postgres cluster) and read acceleration. What's the operational simplicity? You still have to do backups and replication and SSL. Maybe you don't have to worry about connectivity between the app and the database? Maybe auth?
  - tptacek 4y ago
    You don't have to manage a database server if there is no database server.
    - nwienert 4y ago
      Litestream is a database server, isn’t it?
      - tptacek 4y ago
        No; there's no such thing as a sqlite3 server. The database is the file(s). Litestream runs alongside everything else using sqlite3 and ensures that it's replicating. If Litestream crashes, reads from the database keep working fine (though, of course, they'll start to stale if it doesn't come back up). This is why we called out in the post that Litestream is "just sqlite3". It's not sitting between apps and the database.
        throwoutway 4y ago
        That seems disingenuous. If sqlite3 isn't a server, then neither is apache2. But in reality they're both binaries 'serving' 'files' over an interface. You're just hosting them on the same machine, reverting to a monolith-style deployment. Which is fine, but then lets call it what it is.
        dagw 4y ago
        But in reality they're both binaries 'serving' 'files' over an interface. By that definition fopen() is also a server.
        WorldMaker 4y ago
        According to Plan9, fopen() is also a server.
        ignoramous 4y ago
        > That seems disingenuous. If sqlite3 isn't a server, then neither is apache2. Your argument really is with Dr. Richard Hipp: https://sqlite.org/serverless.html https://sqlite.org/serverless.html
        ignoramous 4y ago
        > Litestream crashes, reads from the database keep working fine. fly-app's litestream-base dockerfile suggests that the litestream process supervises the app process... I guess then that's a limitation specific to fly.io's deployment model and not litestream?
    - throwaway894345 4y ago
      I mean, there are managed SQL services too. Comparing managed SQLite to DIY Postgres seems disingenuous. EDIT: I didn’t expect this to be controversial, but I’d like to know where I’ve erred. If you need lightstream to make SQLite operationally simple (beyond single servers, anyway), that seems pretty analogous to RDS to make Postgres operationally simple, right?
      - nouveaux 4y ago
        I didn't downvote you. Postgres as a database server is operationally more complex when compared to Sqlite. Since Postgres is a network service, you have to deal with networking and security. Upgrading Postgres is a big task in and of itself. Backups has to happen over the network. Number of network connections is another sore point. One of Postgres' biggest pain point is the low number of connections it supports. It is not uncommon to have to run a proxy in front of Postgres to increase the number of connections. Sqlite gives you so much for free as long as you can work within its constraint, which is single writer (for the most part.)
        Abishek_Muthian 4y ago
        > Upgrading Postgres is a big task in and of itself. Learnt it the hard way when I first upgraded the major version, Only to realize that the data needs to be migrated first. pg_upgrade requires binaries of the older version and so we need copies of data, as well as binaries of old & new version of postgres[1] i.e. if not manually dumped; Fortunately it was just my home server. [1] https://wiki.archlinux.org/title/PostgreSQL#Upgrading_PostgreSQL https://wiki.archlinux.org/title/PostgreSQL#Upgrading_Postgr...
        vinay_ys 4y ago
        You have a more complex network setup actually. You have north-south traffic between your client->LB->servers. and you have east-west traffic between your servers for sqlite replication. Both happening on the same nodes and no isolation whatsoever. More things can go wrong and will require more tooling to disambiguate between different potential failures. W.r.t security, you have same challenges to secure east/west vs north/south traffic. W.r.t # of connections, Postgres has a limit on number of connections for a reason – if you are running a multi-process or milt-thread app framework that's talking to sqlite, you have just traded connection limit to concurrent process/thread access limit to sqlite. I don't know if one is better than other – it all depends on your tooling to debug things when things inevitably fail at redline stress conditions.
mwcampbell 4y ago
> 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.
    - 4y ago

tptacek 4y ago

Yes: nobody is claiming otherwise. SQLite drastically speeds up reads, and it speeds up writes in single-server settings. In a multi-server setting, writes have comparable (probably marginally poorer, because of database-level locking, in a naive configuration) performance to Postgres. The lay-up wins of SQLite in a multi-server environment are operational simplicity (compared to running, say, a Postgres cluster) and read acceleration.

throwaway894345 4y ago
> The lay-up wins of SQLite in a multi-server environment are operational simplicity (compared to running, say, a Postgres cluster) and read acceleration. What's the operational simplicity? You still have to do backups and replication and SSL. Maybe you don't have to worry about connectivity between the app and the database? Maybe auth?
- tptacek 4y ago
  You don't have to manage a database server if there is no database server.
  - nwienert 4y ago
    Litestream is a database server, isn’t it?
    - tptacek 4y ago
      No; there's no such thing as a sqlite3 server. The database is the file(s). Litestream runs alongside everything else using sqlite3 and ensures that it's replicating. If Litestream crashes, reads from the database keep working fine (though, of course, they'll start to stale if it doesn't come back up). This is why we called out in the post that Litestream is "just sqlite3". It's not sitting between apps and the database.
      - throwoutway 4y ago
        That seems disingenuous. If sqlite3 isn't a server, then neither is apache2. But in reality they're both binaries 'serving' 'files' over an interface. You're just hosting them on the same machine, reverting to a monolith-style deployment. Which is fine, but then lets call it what it is.
        dagw 4y ago
        But in reality they're both binaries 'serving' 'files' over an interface. By that definition fopen() is also a server.
        WorldMaker 4y ago
        According to Plan9, fopen() is also a server.
        ignoramous 4y ago
        > That seems disingenuous. If sqlite3 isn't a server, then neither is apache2. Your argument really is with Dr. Richard Hipp: https://sqlite.org/serverless.html https://sqlite.org/serverless.html
      - ignoramous 4y ago
        > Litestream crashes, reads from the database keep working fine. fly-app's litestream-base dockerfile suggests that the litestream process supervises the app process... I guess then that's a limitation specific to fly.io's deployment model and not litestream?
  - throwaway894345 4y ago
    I mean, there are managed SQL services too. Comparing managed SQLite to DIY Postgres seems disingenuous. EDIT: I didn’t expect this to be controversial, but I’d like to know where I’ve erred. If you need lightstream to make SQLite operationally simple (beyond single servers, anyway), that seems pretty analogous to RDS to make Postgres operationally simple, right?
    - nouveaux 4y ago
      I didn't downvote you. Postgres as a database server is operationally more complex when compared to Sqlite. Since Postgres is a network service, you have to deal with networking and security. Upgrading Postgres is a big task in and of itself. Backups has to happen over the network. Number of network connections is another sore point. One of Postgres' biggest pain point is the low number of connections it supports. It is not uncommon to have to run a proxy in front of Postgres to increase the number of connections. Sqlite gives you so much for free as long as you can work within its constraint, which is single writer (for the most part.)
      - Abishek_Muthian 4y ago
        > Upgrading Postgres is a big task in and of itself. Learnt it the hard way when I first upgraded the major version, Only to realize that the data needs to be migrated first. pg_upgrade requires binaries of the older version and so we need copies of data, as well as binaries of old & new version of postgres[1] i.e. if not manually dumped; Fortunately it was just my home server. [1] https://wiki.archlinux.org/title/PostgreSQL#Upgrading_PostgreSQL https://wiki.archlinux.org/title/PostgreSQL#Upgrading_Postgr...
      - vinay_ys 4y ago
        You have a more complex network setup actually. You have north-south traffic between your client->LB->servers. and you have east-west traffic between your servers for sqlite replication. Both happening on the same nodes and no isolation whatsoever. More things can go wrong and will require more tooling to disambiguate between different potential failures. W.r.t security, you have same challenges to secure east/west vs north/south traffic. W.r.t # of connections, Postgres has a limit on number of connections for a reason – if you are running a multi-process or milt-thread app framework that's talking to sqlite, you have just traded connection limit to concurrent process/thread access limit to sqlite. I don't know if one is better than other – it all depends on your tooling to debug things when things inevitably fail at redline stress conditions.

mwcampbell 4y ago

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