Hacker News Reader

> Presumably all of these things provide a wildly different experience in terms of consistency, availability, latency, complexity, and concurrent use. Would be so nice to read a lengthy blogpost comparing all or some of these with their pros and cons.

I’m glad you’re into it but none of that sounds fun or appealing to me. I have no issue with SQLite or any of these projects, but my default for stuff that needs eg concurrent writes or streaming replication is Postgres which was designed from the ground up to be this sort of database and had those features built into the mainline release for some time.

I trust the default of Postgres way more than I trust myself to pick some new fork of SQLite via what I learn in blog posts and have it be well fit to the problem and reliable and adaptable.

I get that SQLite can be easier to set up and faster on reads but I feel like we’ve been down this road before trading away Postgres for those sorts of seeming gains - I’m getting MongoDB flashbacks.

Don’t get me wrong, I’d choose SQLite for a local app storage solution in a heartbeat but I’m wary of overstretching it.

I wrote a small PHP library that gives you a key-value storage interface to SQlite files: https://github.com/aaviator42/StorX

I've been dogfooding for a while by using it in my side projects.

And there's a basic API too, to use it over a network: https://github.com/aaviator42/StorX-API

ICYMI, similar concept for python https://dataset.readthedocs.io/

Questions for libsql:

- Looks like bottomless does automatic restoring of the database? Litestream seems to avoid this, I assume because of concerns about accidentally creating multiple writers on a rolling-deploy (or similar mistake). Any concerns about this possible footgun?

- Bottomless is a sqlite extension, not a separate process? Pros and cons compared to Litestream?

- Similar questions for sqld. How does sqld handle the lifecycle of deploys and multiple readers/writers talking to the database? Anything a new user should be concerned about?

First of all, let me start by reiterating the first sentence from the bottomless repo - it's work in heavy progress (and we'll move it under the sqld/ repo soon, to keep everything in one place). Now, answers:

> - Looks like bottomless does automatic restoring of the database? Litestream seems to avoid this, I assume because of concerns about accidentally creating multiple writers on a rolling-deploy (or similar mistake). Any concerns about this possible footgun?

It's a valid concern, but what always happens on boot is starting a new generation (a generation is basically a snapshot of the main file + its continuously replicated WAL), distinguished by a uuid v7, the timestamped one. So even if a collision happens, it would be recoverable - e.g. one of the stray generations should be deleted.

> - Bottomless is a sqlite extension, not a separate process? Pros and cons compared to litestream?

The only con I see is that a bug in the extension could interfere with the database. As for pros: way less maintenance work, because everything is already embedded, we're also hooked into the database via a virtual WAL interface (libSQL-only), so we have full control over when to replicate, without having to observe the .wal file and reason about it from a separate process.

> - Similar questions for sqld. How does sqld handle the lifecycle of deploys and multiple readers/writers talking to the database? Anything a new user should be concerned about?

There are going to be multiple flavors of sqld, but the rough idea would be to trust the users to only launch a single primary. In the current state of the code, replicas contact the primary in order to register themselves, so the model is centralized. Once we build something on top of a consensus algorithm, leader election will be pushed to the algorithm itself.

Very cool, thank you for the insights

Related: michaelc @ uktrade did some intresting work streaming sqlite with python in this repo - https://github.com/uktrade/stream-sqlite

Cool but NIH syndrome.. I don't see any point here. SQLite was doing one thing and doing it well. The rest is well handled by pg

bottomless looks really nice, thanks for sharing!