would it be beneficial to me to migrate from Fly Postgres to LiteFS Cloud? Fly Postgres has been working fine, but I’m not sure how to do backups and stuff.
LiteFS Cloud: Distributed SQLite with Managed Backups
41–50 of 94 posts
Re: LiteFS Cloud: Distributed SQLite with Managed Backups
#42Earlier quoted context omitted.
Author here. We take a daily snapshot and then store incremental changes in multiple time intervals to optimize the storage usage and optimize the time to restore. Your app just sends us the incremental changes every second and we handle all the optimization on our side.
Hi Ben: Ignoring the full snapshot, does LiteFS Cloud periodically restore backups from its interim LTX blobs to check whatever it has accumulated works? If not, what inherently about LTX ensures that restores will always work? Thanks.
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...
Re: LiteFS Cloud: Distributed SQLite with Managed Backups
#43Man this is cool. While I really enjoy my own solution of using a custom SQLite vfs that stores your db transparently in dynamodb[0], this really is a compelling alternative. 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. [0]: https://github.com/psanford/donu…
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.
I'm curious how you'll handle lambda concurrency with LiteFS. I think (please correct anything I've got wrong) the way LiteFS works with respect to concurrency is:
- 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).
Re: LiteFS Cloud: Distributed SQLite with Managed Backups
#44Is LiteFS+SQLite suitable for something like a SaaS solution? For example - Fresh books, Trello or Craigslist - all three have different needs. So which kind of apps should NOT be built on top of LiteFS+SQlite combination?
Technical requirements for a SaaS app have a tendency to become something like a Jenga tower over time. You'll be able to sail through launch and initial customers but then slam hard into difficult architectural challenges as you suddenly onboard larger customers or unique use cases.
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.
Re: LiteFS Cloud: Distributed SQLite with Managed Backups
#45Earlier quoted context omitted.
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.
I'm curious how you'll handle lambda concurrency with LiteFS. I think (please correct anything I've got wrong) the way LiteFS works with respect to concurrency is: - 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 Th…
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.
Re: LiteFS Cloud: Distributed SQLite with Managed Backups
#46Earlier quoted context omitted.
I'm curious how you'll handle lambda concurrency with LiteFS. I think (please correct anything I've got wrong) the way LiteFS works with respect to concurrency is: - 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 Th…
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 wit…
Ah very cool. I'll absolutely try this out on lambda when you release it.
Re: LiteFS Cloud: Distributed SQLite with Managed Backups
#47would it be beneficial to me to migrate from Fly Postgres to LiteFS Cloud? Fly Postgres has been working fine, but I’m not sure how to do backups and stuff.
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.
Re: LiteFS Cloud: Distributed SQLite with Managed Backups
#48Is LiteFS+SQLite suitable for something like a SaaS solution? For example - Fresh books, Trello or Craigslist - all three have different needs. So which kind of apps should NOT be built on top of LiteFS+SQlite combination?
Technical requirements for a SaaS app have a tendency to become something like a Jenga tower over time. You'll be able to sail through launch and initial customers but then slam hard into difficult architectural challenges as you suddenly onboard larger customers or unique use cases. For SQLite my guess would be areas of high concurrent write throughput - like a seasonal holiday/rush, a viral influx of users, or the…
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.
Re: LiteFS Cloud: Distributed SQLite with Managed Backups
#49Has anyone built a mobile app on top of SQLite that can work offline, but then sync to a server when it gets connectivity? It feels like this could be built with a similar approach to this distributed SQLite, you'd "just" need more robust conflict handling.
Not sure how it's implemented but seems like a pretty common pattern on Android. For instance, Gmail and Mint apps both allow offline changes that get synced. Not sure if they use SQLite but afaik that's sort of the defacto standard for Android app storage.
Re: LiteFS Cloud: Distributed SQLite with Managed Backups
#50Earlier quoted context omitted.
Technical requirements for a SaaS app have a tendency to become something like a Jenga tower over time. You'll be able to sail through launch and initial customers but then slam hard into difficult architectural challenges as you suddenly onboard larger customers or unique use cases. For SQLite my guess would be areas of high concurrent write throughput - like a seasonal holiday/rush, a viral influx of users, or the…
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 custom…
I'm flinching a bit at using the word "sharding" here, because I think people do sleep on how straightforward it is to break up a SQL schema into multiple sqlite3 databases, but when people think about "sharding" they tend to be thinking things like range-partitioned keys, with each shard hosting a portion of the keyspace of the entire schema, which is not necessarily how you'd want to design a sqlite3 system.