5 ms·

> 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

> 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 5y ago
You don't have to manage a database server if there is no database server.
- nwienert 5y ago
  Litestream is a database server, isn’t it?
  - tptacek 5y 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 5y 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 5y ago
        But in reality they're both binaries 'serving' 'files' over an interface. By that definition fopen() is also a server.
        WorldMaker 5y ago
        According to Plan9, fopen() is also a server.
      - ignoramous 5y 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 5y 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?
    - nwienert 5y ago
      That’s my point though, if you want to use SQLite in production / with Litestream, you’re comparing two databases that need servers to function just implemented quite differently. In the same vein as your article, it seems very fair to say that if the Litestream server went down in production you’d have a broken app for most use cases within a few minutes. So in practical effect, the server of Litestream is about as essential.
  - deleted 5y ago
    [deleted]
- throwaway894345 5y 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 5y 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 5y 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 5y 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.

tptacek 5y ago

You don't have to manage a database server if there is no database server.

nwienert 5y ago
Litestream is a database server, isn’t it?
- tptacek 5y 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 5y 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 5y ago
      But in reality they're both binaries 'serving' 'files' over an interface. By that definition fopen() is also a server.
      - WorldMaker 5y ago
        According to Plan9, fopen() is also a server.
    - ignoramous 5y 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 5y 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?
  - nwienert 5y ago
    That’s my point though, if you want to use SQLite in production / with Litestream, you’re comparing two databases that need servers to function just implemented quite differently. In the same vein as your article, it seems very fair to say that if the Litestream server went down in production you’d have a broken app for most use cases within a few minutes. So in practical effect, the server of Litestream is about as essential.
- deleted 5y ago
  [deleted]
throwaway894345 5y 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 5y 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 5y 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 5y 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.