benbjohnson

If you can have multiple writers to a single database then you'd need to look at something like cr-sqlite[1] that uses CRDT to figure out conflicts. If you're just replicating separate databases then you might be able to replicate each one using Litestream and then use SQLite's ATTACH[2] to connect them together. There is a limit on how many databases you can attach together in a session though.

[1]: https://github.com/vlcn-io/cr-sqlite

[2]: https://sqlite.org/lang_attach.html

Litestream author here. You can use the built-in file replication. It'll replicate all your database changes to another path on disk. I use it a lot for testing things out:

https://litestream.io/guides/file/

Litestream author here. Currently we're handling the "single writer" issue outside of Litestream. We have controls in our internal systems that make it work well. But yes, the lease PR is the direction we're looking at going.

I'm not sure you can have readers see something separate than writers. When SQLite promotes a read lock to a write lock under WAL then it checks if any of the data has changed and then fails the transaction if it has.

Author here. It will cache pages in memory right now but it doesn't do disk-based caching currently. That's a good idea though and probably not too difficult to implement. Feel free to post a feature request as a GitHub issue on the project.

The VFS is read only but it will continuously poll for new updates so if you have a writer somewhere else using regular Litestream then it will pick up those updates automatically.

Author here. You can query the state of a table from an hour ago with Litestream VFS. It won't give you versioned tables in the sense that every time you update a row that it writes a new version in a table somewhere though.

Author here. We've done some proof-of-concept work on creating distributed leases using S3. We have some use cases internally where we've considered adding write capabilities to the VFS but we haven't started any work on it yet.

Author here. Litestream VFS will automatically poll for new back up data every second so it keeps itself up to date with any changes made by the original database.

You don't need any additional code (Python or otherwise) to use the VFS. It will work on the SQLite CLI as is.

It worked great! Thanks for your work on it.

Author here. The VFS support right now is currently read only so it's useful for something more like a shared cache of data.

Author here. Yes, Litestream v0.5.3 has been released with a new read-only VFS option: https://github.com/benbjohnson/litestream/releases/tag/v0.5....

Litestream author here. Yes, that's correct. LTX was the biggest hurdle to get over and was impossible to switch over incrementally. The storage layer change brings a lot of benefits and enables a lot of future work that we're really excited about.

Thanks to mtlynch and everyone else who has submitted bug reports. We're squashing issues and working to get everything stable as quickly as possible.

The changes are still in progress. The blog post was just about future work that we're working on. However, we don't have plans to do failover with Litestream at the moment.

Litestream saves WAL segments to a given time granularity. By default, it ships off WAL changes every second so you should be able to restore to any given second in your history (within your retention period).

Backups & read replicas are the primary use cases. If you're interested in local-first, you can check out projects like cr-sqlite[1].

[1]: https://github.com/vlcn-io/cr-sqlite

We don't support plug-ins at the moment but there's several backends at the moment (S3, Azure Blob Storage, Google Cloud Storage, SFTP, etc)

Not all corruption is detectable. You could make a copy during a transaction where only a subset of the transactions saved pages are persisted but all branch & leaf pages are pointed to correctly. That would give you a state of the database that never actually existed and break atomicity.

I haven't read that paper but it seems like it's fixing a different problem of Byzantine fault tolerance. Most consensus systems that are internal for an organization don't have the Byzantine issue so it simplifies the problem.

Thanks so much for letting me know! It's always hard to tell when I put something out there if it just gets lost in the ether. I'm glad to hear it helped so many folks.

It's all done with d3 and JavaScript. The visualizations aren't deterministic so I ended up writing a shitty Raft implementation in JS. Overall it was a terrible approach because it was so time consuming but I made it work. You can find all the source code in this repo: https://github.com/benbjohnson/thesecretlivesofdata

Author here. I'm happy to answer any questions although this project was from 10+ years ago so I could be a little rusty.

Over the years I've been trying to find better ways to do this kind of visualization but for other CS topics. Moving to video is the most realistic option but using something like After Effects takes A LOT of time and energy for long-form visualizations. It also doesn't produce a readable output file format that could be shared, diff'd, & tweaked.

I spent some time on a project recently to build out an SVG-based video generation tool that can use a sidecar file for defining animations. It's still a work in progress but hopefully I can get it to a place where making this style of visualizations isn't so time intensive.

IIRC he was given shares in the company. https://news.ycombinator.com/item?id=39629939

LiteFS author here. I don't disagree with any points in the article but perhaps a reframing could help. I previously wrote a tool called Litestream that would do disaster recovery for a single-node SQLite server and I still think it's a great default option for people starting new projects. Unless you're doing very database-specific things, most SQL will carry over between SQLite and Postgres and MySQL, especially if you add ORMs in the mix. Pick the one that gets you writing code the fastest and you can switch down the road if you need it.

Rather than a paradigm shift or hype, I see distributed SQLite as an extension of a path that devs can go down. With Litestream, the most common complaint I got was that devs were worried that they couldn't horizontally scale with SQLite and they'd be stuck. While you probably won't hit vertical scaling limits of SQLite on most projects, it still caused concern. So LiteFS became a "next step" that a dev could take if they ever got to that point. It doesn't need to be your starting point.

As for the "hacky" solution of txid, I'm not sure why that's hacky. Your application isn't required to use it or the optional built-in proxy but it's available if it fits your application's needs. It also works for plugging legacy applications into distributed SQLite without retrofitting the code. The proposed solution of caching seems orthogonal to the discussion of distributed application data. I don't think any database provider would suggest to avoid caching when it's appropriate but there's plenty of downsides of caching. Hell, it's one of the two hardest problems in computer science.

We have an region-aware S3 replacement that's in beta right now: https://community.fly.io/t/global-caching-object-storage-on-...

Do you mind if I ask who you work for? I’m in Denver as well and I’m always curious what companies are doing 4x8 or even 4x10.

Author here. The comparison was meant to be about how Postgres (or any client/server RDBMS) is typically deployed. Yes, you can deploy Postgres on the same machine but I wouldn't say it's common. Maybe I could have expanded more on that point or simply referenced client/server architecture rather than Postgres so it didn't seem like a straw man argument.

SQLite has very little per-query overhead (as opposed to a database connection over a network) so I would think you could traverse a graph using multiple small queries rather than using a graph query language.

Litestream definitely has a future. Our goal is to keep it as a simple single-node disaster recovery tool though so it won't see as much feature development as something like LiteFS. We've been focused a lot on LiteFS & LiteFS Cloud to get them in a good place but I'm looking forward to going back and updating Litestream more regularly.

Author here. My goal in the comparison was only in terms of scope, not that Postgres folks should be penalized for having good documentation. I think Postgres is great and it makes sense to use it when it's called for. But I think it can be overkill for many projects.

Author here. The single-node restriction for Litestream was one of the main reasons we started LiteFS. There isn't a way to handle streaming backup from multiple nodes with Litestream & S3 as SQLite is a single-writer system and there aren't any coordination primitives available with S3.

I agree that many of the SQLite cloud offerings introduce the same network overhead. With LiteFS, the goal is to have the data on the application node so you can avoid the network latency for most requests. Writes still need to go to the primary so that's unavoidable but read requests can be served directly from the replica. The LiteFS HTTP proxy was introduced as an easy way to have LiteFS manage consistency transparently so you can get read-your-writes consistency on replicas and strict serializability on the primary. That level of consistency works for a lot of applications but if you need stronger guarantees then there's usually trade-offs to be made.