Comment by bob1029 | Hacker News Reader

Comment by bob1029 | Hacker News Reader

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.

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?

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)

Ah, maybe I just don't know what "frontline application" means in this context.

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.

> 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.

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.

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.

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.

In my experience, people running airgapped systems prefer OS packages and prebuilt images rather than even considering doing Docker pulls

I don't get the downvotes here. These are real world constraints.

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.

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

*in nodejs (for now)

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

Yep, we are actively working on native support.

(I work on PGlite)

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

Reply on news.ycombinator.com