LiteFS Cloud: Distributed SQLite with Managed Backups
fly.io
fly.io
I've been bugging cloudflare to do this with D1+tunnels since D1 was announced and they constantly seemed confused what I was even talking about.
We're launching PITR support for the new experimental backend within the next 1-2 weeks. Stay tuned.
(I am the eng director for Cloudflare Workers)
I should be able to...
1. Configure a CF tunnel through the config file or the dash, pointing it to a local path of an existing SQLite file.
1a. On startup, the SQLite file should create and link a new D1 db.
2. Configure a CF tunnel through the config file or the dash, pointing it to an existing D1 db.
2a. On startup, the local sqlite db should be either overwritten or created.
3. Create read replicates through CF tunnels on another server or the local computer/wrangler for development.
4. Have automatic global read replicas on the edge, which is inherent to D1 no?
5. Have automatic/rolling backups and export and import those backups automatically with S3/R2.
6. Do a PITR through the dash or CF tunnel CLI.
6a. PITR should be possible to either the existing live DB or restoring it into a separate new D1 copy.
7. Leverage my SQlite files in workers, another automatic bonus of connecting it to D1.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.
For SQLite my guess would be areas of high concurrent write throughput - like a seasonal holiday/rush, a viral influx of users, or the onboarding of a large client.
Its not that SQLite can't handle these situations with careful architectural decisions. Its that out-of-the-box solutions, like the kind people depend on to solve business-issues in short time frames, won't support it as readily as more mainstream options.
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.
I think its less about proving sqlite is awesome for everything then it is about proving it can be awesome and practical for some projects.
In terms of pure perversion, I'm wondering if fanotify or kernel tracepoints could be used to gather the information needed asynchronously rather than sticking a userspace server in the way
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.
It's actually a 3rd party app, WK provides an API so that people can make apps that interface with the system.
Forum thread: https://community.wanikani.com/t/android-flaming-durtles-and...
Source code (may be outdated): https://github.com/ejplugge/com.the_tinkering.wk
For that I am also exploring on SQLite-Y-CRDT (https://github.com/maxpert/sqlite-y-crdt) which can help me treat each row as document, and then try to merge them. I personally think CRDT gets harder to reason sometimes, and might not be explainable to an entry level developers. Usually when something is hard to reason and explain, I prefer sticking to simplicity. People IMO will be much more comfortable knowing they can't use auto incrementing IDs for particular tables (because two independent nodes can increment counter to same values) vs here is a magical way to merge that will mess up your data.
I'm really curious to see how some of these SQLite toolings will work in the "dumb and simple app" case. Ie i'm writing an app that is focused on being local, single instance. Which i know is blasphemy to Fly, but it's my target audience - self hosting first and foremost.
I had planned on trying Fly through the lens of a DigitalOcean replacement. Notably something to manage the machine for me, but with similar cost and ease. In that realm, i wonder which of the numerous SQLite offerings Fly has will be useful to my single-instance-focused app backed by SQLite and Filesystem.
Some awesome tech from the Fly team regardless. Exciting times :)
LiteFS tries to improve upon Litestream by making it simple to integrate backups and make it easy to scale out your application in different regions without changing your app. I don't think every application needs to be on the edge but we're hoping to make it easy enough that it's more a question of "why not?"
Is it a goal of LiteFS to serve single-instance deployments as well as Litestream does? Would you say LiteFS has already achieved that at this point, or would Litestream still be the better match for single-instance apps?
I've experimented with LiteFS and liked it, but all my apps are single-deployment, so I've stuck with Litestream. But I know LiteFS is receiving much more investment, so I'm wondering if Litestream is long for this world.
We do have some nice features coming down the pipe for LiteFS so you can use it with purely ephemeral nodes. Let me know if you get a chance to try it out and if there's any improvements you'd like to see.
The idea is that people with small-to-medium size Rails Turbo apps should be able to deploy them without needing Redis or Postgres.
I’ve gotten as far as deploying this stack _without_ LiteFS and it works great. The only downside is the application queues requests on deploy, but for some smaller apps it’s acceptable to have the client wait for a few seconds while the app restarts.
When I get that PR merged I’ll write about how it works on Fly and publish it to https://fly.io/ruby-dispatch/.
It's not just about making snappier applications; it's also that even ordinary apps burn engineering time (for most shops, the most expensive resource) on minimizing those database round trips --- it's why so much ink has been spilt about hunting and eliminating N+1 query patterns, for instance, which is work you more or less don't have to think about with SQLite.
This premise doesn't hold for all applications, or maybe even most apps! But there is a big class of read-heavy applications where it's a natural fit.
> Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however.
It goes on to say that other databases provide more concurrency.
> However, client/server database engines (such as PostgreSQL, MySQL, or Oracle) usually support a higher level of concurrency and allow multiple processes to be writing to the same database at the same time. This is possible in a client/server database because there is always a single well-controlled server process available to coordinate access. If your application has a need for a lot of concurrency, then you should consider using a client/server database. But experience suggests that most applications need much less concurrency than their designers imagine.
If you want more concurrency, you can break your schema up into multiple .db files. This sounds onerous but SQLite makes it very easy to do.
Is this intended for b2b applications, or b2c? Could you (theoretically) write Facebook with a couple billion users with such a distributed SQLite system (billions of sqlite files, or many many billions of rows in this sort of a system)?
I think with huge amounts of optimization, you could at least attempt to do such a thing with most of the above or dynamodb (although it'd probably have hotspots).
Just trying to wrap my head around this new offering and the types of apps it's aimed at.
The best way to think about SQLite in modern CRUD apps is by thinking about the N+1 query problem. N+1 is an issue largely because of the round-trips between the app and the database server (in an n-tier architecture, those are virtually always separate servers; in a geographically distributed n-tier architecture, they're also far apart from each other). Think of SQLite as a tool that would allow you to just randomly write N+1 query logic without worrying about it. You'd probably still not do that, but that's the kind of thing SQLite ostensibly lets you get away with.
However, Postgres is not and never claimed to be a distributed database, so you are erecting and attacking a strawman argument by choosing something that wasn't even in the list.
> However, we don't write every individual LTX file to object storage immediately. The latency is too high and it's not cost effective when you write a lot of transactions. Instead, the LiteFS primary node will batch up its changes every second and send a single, compacted LTX file to LiteFS Cloud. Once there, LiteFS Cloud will batch these 1-second files together and flush them to storage periodically.
To try to avoid any data loss one would need synchronous replication. There is a SQLite fork that uses RAFT.
I host a multiplayer game on fly. The way I've designed it is, each game server has it's own sqlite database. And each fly server can host multiple game servers, to keep a high utilization. I currently use Litestream to replicate each database to s3 for disaster recovery. I am planning to move from S3 to sftp to save on the high post/put costs that s3 incurs (the actual storage costs are negligible).
I thought what I am doing would be more common place. But it seems that running single machine instances that can recover after a crash is not common after all (or atleast the tooling does not focus on that). Most use cases seem to be serving high availability or scalability.
In the unnecessary (IMO) desire to make everything highly available, I think simpler solutions have been over looked. I can't help but feel that if you need LiteFS, it is possible that you should be looking at a server oriented database like Postgres or Mysql. In that respect, I feel Litestream is underrated and deserves more attention. It serves a use case which is perhaps more in-line with an in-process DB :)
PS. this thread has some really interesting tools though (Marmot, mycelite). Great to see so many options.
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.
So if your main use-case of restoring a DB from the past is to run analytical queries on it, then you should probably consider these solutions instead.
These systems rely on other databases to store their data, so they inherit the backup capabilities of those databases.
Would be interesting to consider, if they could benefit from using SQLite as a backend instead of H2DB/DynamoDB/Postgres or RocksDB/LMDB/Xodus/JDBC.
I still think the replicated sqlite approach is the wrong one, though. It makes sense for fly, but most people don't need replication, they need sharding. The parallel write issue with SQLite remains sadly.
Ideally you would have both - a cluster (let's say 3 instances) for SQLite, in which writes a committed transactionally, and then LiteFS, where you shard on a key (let's say in the instnace of HN, by thread) and create separate SQLite DB for each where you get (stale) fast reads.
As an extra benefit these pools do not push the limits of vertical scaling to which makes them both easier and cheaper to test under high load conditions.
I wonder how viable this would be to use from aws lambda? It seems like the way lambda does concurrency probably doesn't play all that well with litefs. Maybe it's time to move some workloads over to fly.io.
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.
- A single host is the primary node and all writes have to go to this node
- It is the application's responsibility to route requests to the current primary host. That is to say, LiteFS does not transparently forward requests to the current primary node
This model makes a lot of sense when you have say a cluster of nodes in an autoscaling group and some way to route write requests to the leader.
It seems like that model is a lot more challenging with Lamdba, where you have one instantiation of the lambda function per request. I'm not sure how you would route to the primary lambda instantiation in this case. DonutDB locks and hopes that it will be able to grab the write lock fast enough to be able to service the request. Maybe that is also what you would do with litefs? If the lambda instantiation isn't not the primary just retry with hopes that you become the primary, and if it is the primary relinquish leadership that after processing a request?
The donutdb approach won't scale up beyond a small amount of concurrency. Its really meant for lightweight workloads (most of my DBs only do a few writes per day).
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.
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.
You can trade durability for availability (the database isn't useable until the disk is available). You'd have some sort of redundant disk setup (on top of normal backups)
You run into the same problem with RDBMS like Postgres. If you enable synchronous replication, you go from 1 SPOF to 2 (both servers need to be available to ack a write or you lose your redundant data guarantee).
https://litestream.io/guides/sftp/
... so I believe you can use any sftp provider as a target for those backups, correct ?
LiteFS builds on some of the initial concepts of Litestream but it adds the ability to do live read replication so you can have copies of your SQLite database on all your application nodes.
In hindsight, I probably should have thought of less confusing names so they wouldn't be mistaken for one another. :)
We'll introduce pricing in the coming months, but for now LiteFS Cloud is in preview and is free to use. Please go check it out, and let us know how it goes!
I would consider using it instead of running .backup nightly, so having a missed write vs running a cron job is a no brainer.
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.
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...
Write transactions have to occur on the primary node but that's mostly because of latency. SQLite operates in serializable isolation so it only allows one transaction at a time. If you wanted to have all nodes write then you'd need to acquire a lock on one node and then update it and then release the lock. We actually allow this on LiteFS using something called "write forwarding" but it's pretty slow so I wouldn't suggest it for regular use.
We're adding an optional a query API over HTTP [1] soon as well. It's inspired by Turso's approach. That'll let you issue one or more queries in a batch over HTTP and they'll be run in a single transaction.
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.