4 ms·

May I introduce you to https://litestream.io https://litestream.io

by zie 4y ago

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.

May I introduce you to https://litestream.io https://litestream.io

by zie 4y ago

May I introduce you to https://litestream.io https://litestream.io

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.