[HN Gopher] LiteFS Cloud: Distributed SQLite with Managed Backups
___________________________________________________________________
LiteFS Cloud: Distributed SQLite with Managed Backups
Author : nalgeon
Score : 157 points
Date : 2023-07-05 16:41 UTC (6 hours ago)
(HTM) web link (fly.io)
(TXT) w3m dump (fly.io)
| NicoJuicy wrote:
| Do they backup a snapshot? So every 5 minutes for 30 days would
| be a lot of snapshots to S3
| benbjohnson wrote:
| 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.
| ignoramous wrote:
| 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.
| benbjohnson wrote:
| 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...
| [deleted]
| ankit70 wrote:
| This is awesome. Fly.io is now a good replacement for my small
| team and some indie projects. Currently on render before but
| their team pricing turned me off.
| ignoramous wrote:
| Fly gives away a _lot_ for free and yet is cheaper than most
| _NewCloud_ alternatives. Their patient (and painful) investment
| in building atop CoLos (instead of _BigCloud_ ) will pay off in
| the long run, just like it did for Cloudflare. Not sure if Fly
| already builds their own hardware, though.
| tptacek wrote:
| Nope. We just buy it.
| rsync wrote:
| 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 ?
| benbjohnson wrote:
| 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. :)
| wg0 wrote:
| 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?
| tmpz22 wrote:
| 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.
| benbjohnson wrote:
| 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.
| felipeccastro wrote:
| Doesn't the WAL mode solve the high concurrency write
| situation? If it can't be relied on busy season, why the
| push for Sqlite in production?
| benbjohnson wrote:
| 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.
| tmpz22 wrote:
| > If it can't be relied on busy season, why the push for
| Sqlite in production?
|
| 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.
| tptacek wrote:
| 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.
| Mertax wrote:
| In scenarios where an individual customer/tenant can have
| isolated data this makes sense. Is there any reason why the
| client application itself can't be one of the nodes in the
| distributed system? Does LiteFS support a more peer-2-peer
| distribution model (similar to a git repo) where the
| client/customer's SQLite database is fully distributed to
| them and then it's just a matter of merging diffs?
| benbjohnson wrote:
| 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
| benbjohnson wrote:
| 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.
| [deleted]
| Kalanos wrote:
| 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
| efdb wrote:
| Did you have a look at https://rqlite.io/ ?
| pkhuong wrote:
| There's https://github.com/libsql/sqld , but sqlite's
| concurrency model doesn't always work well with long-lived
| transactions (and just the network hop can be slower than a
| local transaction), especially if you want to write.
| benbjohnson wrote:
| LiteFS author here. LiteFS works sorta like that. It provides
| read replicas on all your application servers so you can use it
| just like vanilla SQLite for queries.
|
| 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.
|
| [1]: https://github.com/superfly/litefs/issues/326
| glommer wrote:
| 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).
| crop_rotation wrote:
| 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.
| nijave wrote:
| >Unless you sync disk state to remote storage on every write,
| wouldn't you always lose some writes
|
| 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).
| benbjohnson wrote:
| 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.
| lbotos wrote:
| How does the billing/pricing for LiteFS and LiteFS Cloud work? I
| tried to figure it out but it was unclear from docs.
|
| https://fly.io/docs/about/pricing/
| tptacek wrote:
| From the post:
|
| _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!_
| lbotos wrote:
| <3 Classic HN reader here, I skimmed, went to comments, and
| then went to pricing :P
| yread wrote:
| Looks cool. But is LiteFS really ready for production? The github
| issues page seems to have some lost writes fixed just three weeks
| ago.
|
| I would consider using it instead of running .backup nightly, so
| having a missed write vs running a cron job is a no brainer.
| benbjohnson wrote:
| 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.
| gunapologist99 wrote:
| What is the use case for this distributed SQLite compared to a
| "traditional" distributed database like DynamoDB, CouchDB,
| Foundation, Riak, TiDB or CockroachDB, Mongo, etc.
|
| 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.
| tptacek wrote:
| It's a general approach for SQL-backed CRUD applications. If
| your application is (1) read-heavy (most are), especially if
| it's _particularly_ read-heavy, and (2) benefits from snappy
| responses --- ie, if it 's the kind of thing where you'd invest
| significant time in, like, serverside Javascript compilation
| --- and, especially, if (3) it wants to run in more than one
| geographical region at the same time, then LiteFS is an
| interesting way to slash database server round trip times out
| of your response budget.
|
| 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.
| endisneigh wrote:
| Very similar to mvsqlite (also has snapshots with its own MVCC).
| Sadly you cannot self host this, though. It's true you can open
| source LiteFS, but this seems to have additional features.
|
| 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.
| tptacek wrote:
| Why do most people need sharding? Most people have a single RDS
| instance that they scale vertically. Most people aren't pushing
| the limits of vertical scaling, and LiteFS isn't a "horizontal"
| scaling alternative to conventional n-tier SQL systems.
| FranGro78 wrote:
| Definitely not a concern for "most" folks, but I've seen
| sharding used to separate users into smaller pools which do
| not have shared fate wrt infra and deployments.
|
| 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.
| menzoic wrote:
| What are the reasons to continue using SQLite over MySQL/MariaDB
| when you start to require distributed architectures? Wouldn't it
| be better to switch at that point? Assuming that being able to
| read from a database on the same filesystem as the application
| doesn't provide any tangible benefits for 99.99% of applications
| that don't have such low latency requirements?
| tptacek wrote:
| There's an operational argument to make, but fundamentally it's
| about performance, and secondarily about what having an ultra-
| fast local database does to your performance budget and thus
| how you build things. The premise is that in a typical web
| application, each request incurs multiple round trips to the
| database, and those round trips add up to eat a chunk of your
| budget. A database like SQLite can essentially eliminate that
| round-trip cost.
|
| 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.
| stu_k wrote:
| Has 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.
| nijave wrote:
| 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.
| qweqwe14 wrote:
| [dead]
| benbjohnson wrote:
| I haven't personally but typically people use a CRDT-based
| approach like vlcn[1] or Mycelite[2].
|
| [1]: https://github.com/vlcn-io/cr-sqlite
|
| [2]: https://github.com/mycelial/mycelite
| unshavedyak wrote:
| 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 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 :)
| bradgessler wrote:
| 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/.
| benbjohnson wrote:
| Author here. I love single-instance deployments. I think they
| make a lot of sense when you don't need high-availability. We
| have quite a number of people that run Litestream on a single
| instance with a volume attached. We run Litestream internally
| in several places as well.
|
| 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?"
| mtlynch wrote:
| Hey Ben, great to see this progress!
|
| 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.
| benbjohnson wrote:
| Thanks, Michael! Before LiteFS Cloud, I would have said
| that LiteFS doesn't add much value over vanilla SQLite for
| single-node deployments. But with the new streaming backup
| feature, I think it makes it easier to run single-node
| applications than Litestream. There's some additional
| benefits like being able to import/export the database
| remotely and doing live point-in-time restores. The main
| drawback to LiteFS compared to Litestream is write
| performance but we're alleviating that by implementing a
| VFS option in the future.
|
| 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.
| mtlynch wrote:
| Cool, I'll give it a spin. Thanks, Ben!
| Spunkie wrote:
| I'm really glad to see this complete solution drop. I've cobbling
| together sqlite point-in-time restores, automatic snapshots, and
| S3 backups with litestream and scripts for too long.
|
| 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.
| benbjohnson wrote:
| Let me know how it works for you if you get a chance to try it
| out. We learned a lot since making Litestream so hopefully it's
| a big step up! The underlying format of how we're storing
| snapshots and incremental backups should make restores A LOT
| faster. Litestream has to replay all WAL writes since the last
| snapshot whereas LiteFS is able to perform a lot of
| deduplication and use multi-level compaction to create a
| minimal set of restore data.
| jgrahamc wrote:
| Email me and tell me jgc@cloudflare.com
| psanford wrote:
| 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/donutdb
| benbjohnson wrote:
| 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.
| psanford wrote:
| 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).
| benbjohnson wrote:
| 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.
| psanford wrote:
| Ah very cool. I'll absolutely try this out on lambda when
| you release it.
| dmw_ng wrote:
| 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
| benbjohnson wrote:
| 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.
| catgirlinspace wrote:
| 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.
| benbjohnson wrote:
| 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.
| catgirlinspace wrote:
| Gotcha. Would performance be better with LiteFS? Think I saw
| someone mention that because there'd be no network requests
| between machines.
| benbjohnson wrote:
| 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.
___________________________________________________________________
(page generated 2023-07-05 23:00 UTC)