LiteFS Cloud: Distributed SQLite with Managed Backups • Hacker News Board

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#31

Post by dmw_ng » Wed, Jul 05, 2023, 7:16 PM UTC

Is there any documentation showing what kind of performance is given up by shoving a userspace server into the SQLite read path? LiteFS looks cool but I'd be worried about all block IO basically becoming a cross-process RPC negating large chunks of SQLite's efficiency. Instinct is screaming this should be a VFS extension for SQLite, but definitely appreciate the idea of doing it as a virtual filesystem.

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

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#32

Post by glommer » Wed, Jul 05, 2023, 7:19 PM UTC

Kalanos wrote: post #3

my sqlite pain point is not being able to access sqlite over NFS or remotely on a separate server. i want the best of both worlds: behave like a file locally + act like a server

you can use https://turso.tech for that, which makes SQLite replicated, and available over HTTP. It's designed to work on environments where LiteFS won't (since it needs a volume).

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#33

Post by benbjohnson » Wed, Jul 05, 2023, 7:20 PM UTC

rsync wrote: post #28

Unless something has changed, the underlying tool (litestream) supports plain old SFTP: https://litestream.io/guides/sftp/ ... so I believe you can use any sftp provider as a target for those backups, correct ?

You're correct that Litestream supports SFTP, however, the underlying tool here is LiteFS. I'm the author of both. Litestream works great if you're running a single node. The SFTP support works but can be slow.

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. :)

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#34

Post by bradgessler » Wed, Jul 05, 2023, 7:21 PM UTC

unshavedyak wrote: post #5

Man, i already intended to try Fly for my upcoming hosting needs. Ya'll keep making the pot sweeter, though. 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 th…

I’m working on this for Rails apps at https://github.com/oldmoe/litestack/pull/12

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/.

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#35

Post by wg0 » Wed, Jul 05, 2023, 7:24 PM UTC

Is 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?

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#36

Post by benbjohnson » Wed, Jul 05, 2023, 7:26 PM UTC

dmw_ng wrote: post #31

Is there any documentation showing what kind of performance is given up by shoving a userspace server into the SQLite read path? LiteFS looks cool but I'd be worried about all block IO basically becoming a cross-process RPC negating large chunks of SQLite's efficiency. Instinct is screaming this should be a VFS extension for SQLite, but definitely appreciate the idea of doing it as a virtual filesystem. In terms of p…

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.

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#37

Post by benbjohnson » Wed, Jul 05, 2023, 7:29 PM UTC

psanford wrote: post #26

Man 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.

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#38

Post by benbjohnson » Wed, Jul 05, 2023, 7:35 PM UTC

crop_rotation wrote: post #29

HN seems to love sqlite but I don't know how to protect against dataloss using sqlite (yes I know litestream exists). Unless you sync disk state to remote storage on every write, wouldn't you always lose some writes? Losing writes is never a good idea. You might think it doesn't matter at your scale, but suddenly it might and you won't like SQLite the same amount anymore.

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.

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#39

Post by ignoramous » Wed, Jul 05, 2023, 7:50 PM UTC

benbjohnson wrote: post #13

NicoJuicy wrote: post #10

Do they backup a snapshot? So every 5 minutes for 30 days would be a lot of snapshots to S3

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.

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#40

Post by benbjohnson » Wed, Jul 05, 2023, 7:54 PM UTC

wg0 wrote: post #35

Is 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?

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.