3 ms·

There is absolutely no reason you can't make SQLite go all the way. Starting with it is the only thing that makes sense to me. It is certainly a higher perform

There is absolutely no reason you can't make SQLite go all the way. Starting with it is the only thing that makes sense to me. It is certainly a higher performance solution in the fair comparison of a hermetically sealed VM using SQLite vs application server + Postgres instance + Ethernet cable. We're talking 3-4 orders of magnitude difference in latency. It's not even a contest. There are also a lot of resilience strategies for SQLite that work so much better. For instance, you can just snapshot your VM in AWS every x minutes. This doesn't work for some businesses, but you can also use one of the log replication libraries (perhaps in combination with snapshots). If snapshots work for your business, it's the most trivial thing imaginable to configure and use. Hosted SQL solutions will never come close to this level of simplicity. I personally got 4 banks to agree to the snapshot model with SQLite for a frontline application. Losing 15 minutes of state was not a big deal given that we've still not had any outages related to SQLite in the 8+ years we've been using it in prod.

Kinrany 2y ago
With Pglite it should now be possible to have the cake of Postgres' rich functionality and embed it too
- jddj 2y ago
  *in nodejs (for now)
  - Kinrany 2y ago
    Famous last words, but it's already in a Wasm module and only needs a bit of glue code, so how hard could it be? :D
    - samwillis 2y ago
      Yep, we are actively working on native support. (I work on PGlite)
codeflo 2y ago
Why would you run Postgres on a different box in such a scenario? A docker run command to get a Postgres instance up and running isn’t any more complicated than linking in Sqlite, maybe even simpler. And you get proper ACID transactions for free.
- rcarmo 2y ago
  1) Fault isolation in case of hardware failure 2) You may not even be able to _think_ about using Docker (OS restrictions, airgaps to install images, etc.) These are just 2 that come to mind from working in regulated industries.
  - codeflo 2y ago
    In this thread, we're making a comparison to running Sqlite. Fault isolation is not an argument. And you don't have to run Docker to run Postgres, it's just an easy way to do so. Even airgapped.
    - rcarmo 2y ago
      In my experience, people running airgapped systems prefer OS packages and prebuilt images rather than even considering doing Docker pulls
  - rcarmo 2y ago
    I don't get the downvotes here. These are real world constraints.
cloogshicer 2y ago
Surprised to hear that losing 15 min of state is not a big deal in a banking context. I haven't worked with banks before, genuinely curious, how do they recover from something like this? Wouldn't this potentially destroy all transactions made in that time period?
- viraptor 2y ago
  To be fair they said "frontline application", not a transaction processor specifically. Also in cases where you can't lose stuff it's common to use reliable steam processing, so maybe they can reprocess old events into the system running sqlite. (This is extra general info about "important systems"; I don't know what OP is running)
  - cloogshicer 2y ago
    Ah, maybe I just don't know what "frontline application" means in this context.
- sorokod 2y ago
  At best this will increase the load on customer support with an associated reputational hit. At worst you will need to deal with questions from financial regulators. In all cases, rubbish customer experience.
viraptor 2y ago
> There is absolutely no reason you can't make SQLite go all the way. No reason you can't, but one you to consider if you should. If you're using libraries which make assumptions about your database layer, they may not like the sqlite model. Holding the writer mode for too long is something I experienced in write a few projects. For example paperless-ngx will block the web interface while batch importing documents, even though there's really no reason to do that. It's less of an issue if you write all your own code and you explicitly target sqlite. But worth keeping in mind.
winrid 2y ago
The latency advantage is nothing compared to the advantage you can get with a better query planner.
emccue 2y ago
Sure. I think the caveats I mentioned are real though. Using SQLite locks you out of some platforms as a service and out of some application architectures. It also means you need to be doing stuff like snapshotting your VM every x minutes. Given minimal certainty about the scale, future, and properties of your application and organization: Postgres is the better default.

Kinrany 2y ago

With Pglite it should now be possible to have the cake of Postgres' rich functionality and embed it too

jddj 2y ago
*in nodejs (for now)
- Kinrany 2y ago
  Famous last words, but it's already in a Wasm module and only needs a bit of glue code, so how hard could it be? :D
  - samwillis 2y ago
    Yep, we are actively working on native support. (I work on PGlite)

codeflo 2y ago

Why would you run Postgres on a different box in such a scenario? A docker run command to get a Postgres instance up and running isn’t any more complicated than linking in Sqlite, maybe even simpler. And you get proper ACID transactions for free.

rcarmo 2y ago
1) Fault isolation in case of hardware failure 2) You may not even be able to _think_ about using Docker (OS restrictions, airgaps to install images, etc.) These are just 2 that come to mind from working in regulated industries.
- codeflo 2y ago
  In this thread, we're making a comparison to running Sqlite. Fault isolation is not an argument. And you don't have to run Docker to run Postgres, it's just an easy way to do so. Even airgapped.
  - rcarmo 2y ago
    In my experience, people running airgapped systems prefer OS packages and prebuilt images rather than even considering doing Docker pulls
- rcarmo 2y ago
  I don't get the downvotes here. These are real world constraints.

cloogshicer 2y ago

Surprised to hear that losing 15 min of state is not a big deal in a banking context. I haven't worked with banks before, genuinely curious, how do they recover from something like this? Wouldn't this potentially destroy all transactions made in that time period?

viraptor 2y ago
To be fair they said "frontline application", not a transaction processor specifically. Also in cases where you can't lose stuff it's common to use reliable steam processing, so maybe they can reprocess old events into the system running sqlite. (This is extra general info about "important systems"; I don't know what OP is running)
- cloogshicer 2y ago
  Ah, maybe I just don't know what "frontline application" means in this context.
sorokod 2y ago
At best this will increase the load on customer support with an associated reputational hit. At worst you will need to deal with questions from financial regulators. In all cases, rubbish customer experience.

viraptor 2y ago

> There is absolutely no reason you can't make SQLite go all the way. No reason you can't, but one you to consider if you should. If you're using libraries which make assumptions about your database layer, they may not like the sqlite model. Holding the writer mode for too long is something I experienced in write a few projects. For example paperless-ngx will block the web interface while batch importing documents, even though there's really no reason to do that. It's less of an issue if you write all your own code and you explicitly target sqlite. But worth keeping in mind.

winrid 2y ago

The latency advantage is nothing compared to the advantage you can get with a better query planner.

emccue 2y ago

Sure. I think the caveats I mentioned are real though. Using SQLite locks you out of some platforms as a service and out of some application architectures. It also means you need to be doing stuff like snapshotting your VM every x minutes. Given minimal certainty about the scale, future, and properties of your application and organization: Postgres is the better default.