[gregwebs]

Users generally don't plug ad-hoc log shipping solutions into Postgres. They generally use the built-in battle-tested Postgres replication features, and they can setup synchronous replication to avoid data loss. Shipping a log is trivial but synchronous replication and failover are quite difficult to get right (see jepsen.io), and setting up failover for Postgres is still quite difficult. Newer DBs have been built from the groundup (CRDB, TIDB, etc) in part because of the difficulty of attempting to operate traditional DBs as reliable distributed systems.

[tptacek]

They do now, but that wasn't always the case, and people didn't say that you weren't running Postgres when you did that.

Cockroach is not the same thing we're talking about here; it's a much more ambitious design, just like rqlite is much more ambitious than shipping SQLite transactions. What we're talking here is the tooling needed to generate a single-writer multi-reader cluster the way you would for Postgres, but for SQLite instead. I don't know if single-writer multi-reader clusters for Postgres qualify as "easy", but they're not science projects.

If it's not obvious: we love Cockroach. Our commercial bias is that we built a platform that is especially useful for distributed services and clusters, and Cockroach is very much that.

[gregwebs]

Okay, I read the fly.io documentation. What is being done now to globally distribute PG results is asynchronous replication and thus stale reads and presumably the potential for data loss during failover. So I can see why it would make sense to use SQLite to achieve a similar setup.

Still, I would expect the windows of read staleness and data loss to be much wider with an approach of just shipping logs rather than re-engineering things like RQLite, etc. Trying to ship a copy of SQLite to every app will mostly achieve lower latency at the cost of staler reads, although I can see how ideally it could cut the latency in half by streaming data ahead of time. But of course it increases storage costs dramatically as things scale out.

[tptacek]

Sure, that seems very likely to be true! The flip side of the coin though is that rqlite really is a different database than SQLite. I'm not making a case against things like rqlite and Cockroach; I think they're great, and also my bread is buttered on the same side as theirs. I'm saying that if you're not going to use rqlite --- and most people aren't --- you can get SQLite to the same multi-reader single-writer cluster setup that most people do with Postgres.

And there's a distinct advantage to doing this with SQLite: if you can viably deploy SQLite (because your tooling is asymptotically approaching the state of the art for Postgres), your reads are ultra fast, because you're not hitting the network for an extra round trip every time you query your database.