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.
> 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?
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?
>I didn’t expect this to be controversial, but I’d like to know where I’ve erred
I don't think what you are saying is controversial but it feels like you are being contentious for no reason. Your rebuttal doesn't even make sense - telling me I can pay someone to manage it for me, is not the same as it not needing management at all.
Whether I manage it, or someone else does; there is still an operational or financial cost.
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.)
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.
You're technically right, which is to say: Not at all.
You don't replicate east-west with Litestream, you replicate north -> south where the "North" is your writer and your "South" is some S3-like service. Basically every application has "Some S3 Like Service" as a dependency now, so that's not a new network connection.
You make a really good point about trading Sqlite write limits to the limited number of Postgres connections. My comment is mostly about having to run a proxy, which is another service that ends up being a liability. Regardless, if you need multi-writers, then the obvious solution is Postgres.
"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."
As a Postgres fan, the extra Postgres service to me is a far bigger liability than Sqlite. The east-west networking is true for Postgres replication as well, so it's a wash to me.
> 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.
It still has to run in a server process on a server host, and virtually all of the challenges of managing a database server are just moved up to the application layer. There are fewer actual hosts, but for the likes of fly.io or a cloud provider a difference of a handful of hosts is negligible because hosts are cattle rather than pets.
There might be advantages to SQLite (e.g., maybe lightstream's replication/backup/etc story is simpler than Postgres's), but "number of hosts" or "number of server processes" doesn't seem compelling to me.
Of course, it has nothing to do with the number of hosts or server processes, and I didn't see anyone claim that it does.
It's difficult-to-impossible to truly run Postgres as a proper "herd of cattle", due to the need to failover replication roles between reader and reader-writer, ensure replication is in sync on startup before allowing reads, handle upgrades carefully, etc. If you're using something like RDS or another managed Postgres, this is still the case, it's just being handled for you by someone else.
So it's not that you're just reducing the number of hosts; you're eliminating an entire class of hosts that have complex state and maintenance requirements. Your application processes are hopefully a herd of identical cattle and can be killed off and relaunched at will, and that property remains the same after introducing Lightstream.
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.
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.
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.
> 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?
