benbjohnson

Author here. Cool to see the post make it up on HN again. I'm still as excited as ever about the SQLite space. So much great work going on from rqlite, cr-sqlite, & Turso, and we're still plugging away on LiteFS. I'm happy to answer any questions about the post.

Author here. My goal with the article was to write about an use of LiteFS that I found to be useful and show the benefits and trade-offs. I don't think it's a general purpose technique for everyone but I think it has its place in some cases. What was it about the post that oozed arrogance?

hey Ryan, thanks! As for Corrosion, I can't say anything publicly but something may be announced in the near future wink wink :)

I think a spectrum is a great way to look at it. If you're adding one service then it's no big deal but as you add more and more then you get a better sense of the requirements of the API and I think you're in a better place to build one out then.

We've used this in a few other instances but Corrosion is the largest database we've done it with.

LiteFS works similarly to async replication you'd find in Postgres or MySQL so it doesn't try to be as strict as something running a distributed consensus protocol like Raft. The guarantees for async replication are fairly loose so I'm not sure Jepsen testing would be useful for that per se.

On the LiteFS Cloud side, it currently does streaming backups so it has similar guarantees but we are expanding its feature set and I could see running Jepsen testing on that in the future. We worked with Kyle Kingsbury in the past on some distributed systems challenges[1] and he was awesome to work with. Would definitely love to engage with him again.

[1]: https://fly.io/dist-sys/

Yes, Postgres replication would work as well. I agree that the "getting to know the schema" part is an issue. I think there's use cases out there where you have power users that would gladly invest extra time in exchange for query flexibility.

Querying to S3 is a good approach too. Those query tools can be somewhat involved to setup but definitely a good option.

Author here. We do this with some internal applications that share internal state -- no customer data. I would expect stricter restrictions depending on the type of data shared and how bureaucratic the company is.

Author here. It's not meant as a one-size-fits-all approach. It was an interesting side effect that we noticed that we used internally so I wanted to share my experience. The database mentioned in the post is about 8GB and it replicating it works pretty well.

Author here. I hadn't heard of Sequin until today. "Skip the API" was just a snappy title so I went with it.

Author here. I don't see this is a general practice to be used for most applications. It was a side effect that we came across and it allowed us to share data between internal applications in some interesting ways. Internal apps are ideal since you have control of both sides of the contract. It definitely requires some more consideration if you're publishing out to end users.

Author here. We're using LiteFS to replicate SQLite databases in real time so the changes sent are only incremental. I think there are several ideal use cases for sharing databases across applications:

1. Internal tooling: you're able to manage the contract better since you have control of the source & destination applications.

2. Reporting & analytics: these tend to need a lot of query flexibility & they tend to use a lot of resources. Offloading the query computation to the client makes it easier on the source application.

As for database size, the Corrosion program mentioned in the post is about 8GB and has a continuous write load so this doesn't have to just be for tiny databases.

Author here. I think we could have set better expectations with our Postgres docs. It wasn't meant to be a managed service but rather some tooling to help streamline setting up a database and replicas. I'm sorry about the troubles you've had and that it's come off as us being disingenuous. We blog about things that we're working on and find interesting. It's not meant say that we've figured everything out but rather this is what we've tried.

As for this post, it's not managed SQLite but rather an open source project called LiteFS [1]. You can run it anywhere that runs Linux. We use it in few places in our infrastructure and found that sharing the underlying database for internal tooling was really helpful for that use case.

[1]: https://github.com/superfly/litefs

That makes sense! Let me know if you have any questions. There's a Litestream Slack you can find on the home page or email me at benbjohnson@yahoo.com

Litestream author here. The streaming replication was moved to a different project called LiteFS[1]. That may be a fit for you or, at the very least, it could be a helpful reference for your Rust port.

[1]: https://github.com/superfly/litefs

Sorry, I should have been more clear. HA = High Availability. Essentially, folks wanted the ability to automatically failover to a secondary node if the current primary failed.

LiteFS author here. I think single-node deployments are great and it's a model that fits many (or even most) apps out there. However, the two most common feature requests when I was developing Litestream were HA & replication. While most apps won't need those features, it's good to have it available as an option down the road. I've seen many devs choose Postgres or MySQL over SQLite simply because they didn't have these features. Once those are solved, edge computing is really more of a nice side effect.

I think it's also useful to consider that tools like LiteFS aren't just for your main database. We have several internal tools at Fly.io that fan out their data to other internal apps to consume so they can share data without having to build an API or worry about rate limiting.

Litestream & LiteFS author here. I think single-node, easily-recoverable systems are great and it fits a lot of people's use cases. VPS providers are pretty reliable too so even using a regular hourly backup can be good enough for some people. We even have docs for a cron-based backup [1] on the Litestream docs site.

You're right that HA is one benefit of LiteFS. But I think another important difference is reducing geographic latency. It's possible to spin up read replicas & failovers for Postgres or MySQL and then run application servers for each one of those but it's a huge pain. Or you can pay a serverless database provider but that's expensive. One of the goals with LiteFS is to simply be able to add application nodes in different regions and automagically have faster read latency to people near those regions.

[1]: https://litestream.io/alternatives/cron/

That's not true. LiteFS itself is a distributed database so you have redundancy within your cluster outside of LiteFS Cloud. A typical setup is to run two candidate nodes within a single region so they have low replication latency and then adding read-only nodes in other regions. Transactions are replicated immediately after commit so a write on the primary will be sent to the other candidate node within a millisecond or two. It is async replication but the data loss window when you're running a setup like this is quite small.

Author here. We run LiteFS in production for several services internally. The recent issue was pretty rare and it took a while just to be able to reproduce it. There's always going to be bugs in all software. Hopefully they just become increasingly more rare.

As for "production ready", it's tough to define. It's more a question of risk tolerance. I authored a database called BoltDB about ten years ago and there wasn't a point in time where it suddenly crossed into being production ready. At first, it was used for temporary or cached data. Then people used it for derived data like analytics. And eventually it matured enough that it became a commonplace database in the Go ecosystem. It's in tools like etcd which, in turn, is built into systems like Kubernetes.

I expect LiteFS will see similar a similar adoption pattern and eventually move into more and more risk-adverse applications as it continues to mature. LiteFS Cloud aims to help mitigate risk by having streaming backups & point-in-time recovery.

WAL solves the high concurrency read situation. Not the writes. SQLite can do thousands of writes per second in WAL mode which is more than enough for the vast majority of applications out there. It's not like most businesses could fulfill thousands of orders per second even if their database could write them.

No, LiteFS just does physical page replication. We don't really have a way to do merge conflict resolution between two nodes. You may want to look at either vlcn[1] or Mycelite[2] as options for doing that approach.

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

[2]: https://github.com/mycelial/mycelite

It's hard to say definitively. If you're issuing multiple database queries per request and you aren't using really complicated Postgres SQL then I would guess that you'll see a performance boost. In tests I've done with Postgres, I see a ~1ms round trip between the application server and database server—even when both are in the same region. If you run 10 queries to serve a single HTTP request then that's 10ms right there.

I agree with everything the OP said above. Typically if you need to scale writes in SQLite, you'll want to look at sharding. The "single writer" restriction is per database so you can split your SaaS customers across multiple databases.

If your SaaS is in the hundreds or thousands of customers then you could split each customer into their own database. That also provides nice tenant isolation. If you have more customers than that you may want to look at something like a consistent hash to distribute customers across multiple databases.

It's hard to say. If Postgres is working for you then it might not be worth the trouble. Not everything migrates one-to-one between database vendors.

Fly.io takes daily snapshots of your server volumes so that's one approach to backups that's already handled. However, you can lose up to 24h of data since the backups are only daily. Postgres has some options for streaming backups like wal-e. That might be worth checking out depending on your needs.

Yes, you have the current model correct. To support Lambda, we'll need to move the lock to LiteFS Cloud and allow writes directly to it. The write performance won't be as fast as a local LiteFS instance that is always the primary though. I'm hoping we could still eek out ~100 write tx/sec if the lambda & LiteFS Cloud instance are physically close (e.g. both in us-east-1).

We do something similar in LiteFS already with something called "write forwarding". It borrows the lock from the primary, sync to the current state, runs the transaction locally through regular SQLite, and then bundles and ships the page changeset back to the primary. It works well but it's slower than local writes.

Good question. LTX uses a rolling checksum of the entire database at every transaction. Actually, it includes two checksums: one for the state of the database before the transaction and one for after the transaction.

It's incremental so it's fast to compute and it allows us to verify the integrity of the snapshot when we generate it and when we read it back. It also ensures that writes from LiteFS are contiguous and are coming from a known prior state. There's some details about it in our "Introducing LiteFS" blog post[1] from last year.

We don't currently run a "PRAGMA integrity_check" in the cloud service right now for a few reasons. For one, it can be resource intensive for large databases and, two, it won't work once we support LTX encryption. We do have a continuous test runner that writes to LiteFS Cloud, stops, fetches a snapshot, and performs integrity checks, and repeats.

[1]: https://fly.io/blog/introducing-litefs/#split-brain-detectio...

Good question. LiteFS is just a replication layer so it's probably better to answer your question just from a SQLite standpoint. One of the biggest limitations of SQLite is that it only allows a single writer at a time so you can't have long-running write transactions in your application. Writes can be incredibly fast though—sometimes a millisecond or less depending on your hardware and settings. Speaking of which, make sure you use the "WAL" journaling mode if you're using SQLite it improves concurrency & write performance.

There are also differences once you get to a large scale. Many databases support compression whereas SQLite does not so it might get expensive if you have terabytes of data. That's an extreme case though.

Ultimately, SQLite is just a database. It's more similar to Postgres & MySQL than it is different. There are some features that those client/server databases have like LATERAL joins but I feel like SQLite includes the 99% of what I typically use for application development.

I don't think the issue is unique to SQLite. Postgres & MySQL both have async replication options that are commonly used. There's always going to be a latency and throughput trade-off when you use synchronous replication and many people don't want to take the performance hit.

We do have plans for adding synchronous replication in LiteFS now that we have LiteFS Cloud released. Ideally, we'd like to make it so you can do synchronous replication on a per-transaction basis so you can choose when you want to take the latency hit.

Thanks! I thought DonutDB was an awesome approach too. We do have plans for supporting ephemeral serverless (e.g. Lambda, Vercel) by paging in data on-demand and caching it in the temp space but that work is still a little ways out.

I'd love to hear what you think about the LiteFS approach. We're going to provide a VFS option in the near future as well but the FUSE approach makes it pretty easy to use.

Author here. I'm planning on writing up a blog post about FUSE performance. I get a lot of questions about it. In practical terms, it mostly affects write performance. If you have a write-heavy application then it's probably not a good fit for LiteFS right now. On the read side, hot pages end up in the OS page cache or SQLite page cache so most page reads don't touch the FUSE layer at all.

We have plans for a VFS implementation soon that'll help with the write performance. We chose FUSE because it's fits most application performance targets and it can work with legacy applications with little to no code changes. It also makes it easy to SSH in to a server and use the sqlite3 CLI without having to load up an extension or do any funny business.