9 ms·

LiteFS Cloud: Distributed SQLite with Managed Backups

238 points by nalgeon 3y ago

marvin999 3y ago
Good write up. If I were to build a new SaaS and I’m not anticipating lots of users and concurrency. Regardless the single writer concern, does it suffice as the main storage? What’s is the consistency guarantee?
ankit70 3y ago
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 3y ago
  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 3y ago
    Nope. We just buy it.
Kalanos 3y ago
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
- pkhuong 3y ago
  There's https://github.com/libsql/sqld 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 3y ago
  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 https://github.com/superfly/litefs/issues/326
- efdb 3y ago
  Did you have a look at https://rqlite.io/ https://rqlite.io/ ?
- glommer 3y ago
  you can use https://turso.tech 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).
unshavedyak 3y ago
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 :)
- benbjohnson 3y ago
  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 3y ago
    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 3y ago
      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.
Spunkie 3y ago
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.
- jgrahamc 3y ago
  Email me and tell me jgc@cloudflare.com
- benbjohnson 3y ago
  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.
- bjyule 3y ago
  What do you have in mind with respect to D1+Tunnels? 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)
  - deleted 3y ago
    [deleted]
  - Spunkie 3y ago
    I wanted this when D1 was first teased, and before I figured out that D1 is a database product that happens to be built on SQLite rather than SQLite being the star of the D1 show. Nonetheless, what I want is to be able to point a CF tunnel at a local SQLite file and instantly be able to leverage all the benefits of D1. 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 likely plenty of more cool things that could be done if D1 could be exposed as a normal SQLite file. Ultimately, CF tunnels are just in the mix because it seems like the obvious choice for plumbing SQLite files into and out of the CF network/edge.
NicoJuicy 3y ago
Do they backup a snapshot? So every 5 minutes for 30 days would be a lot of snapshots to S3
- benbjohnson 3y ago
  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 3y ago
    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 3y ago
      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-detection https://fly.io/blog/introducing-litefs/#split-brain-detectio...
- deleted 3y ago
  [deleted]
endisneigh 3y ago
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 3y ago
  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 3y ago
    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 3y ago
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 3y ago
  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.
  - freeopinion 3y ago
    You appropriately answered the question that was asked. I would add a little extension that this question (about SQLite specifically) was asked in the context of a discussion about "Distributed SQLite". The considerations around round-trip costs for "Distributed SQLite" are very different than for SQLite.
- evantbyrne 3y ago
  SQLite is not designed for situations like a website backend where you would expect to have multiple actors modifying the db simultaneously. I'm not sure if they've done something with their implementation to improve upon that.
stu_k 3y ago
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.
- benbjohnson 3y ago
  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 https://github.com/vlcn-io/cr-sqlite [2]: https://github.com/mycelial/mycelite https://github.com/mycelial/mycelite
- qweqwe14 3y ago
  Not 100% sure if it uses SQLite, but there's an app called "Flaming Durtles" on Android. It's a frontend for WaniKani (Japanese learning app). The app downloads all the lessons from WK's servers, and you can do them even when offline. When you go online, it uploads them to the backend and syncs everything else. In my experience, it works flawlessly. 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-android-app-with-offline-support https://community.wanikani.com/t/android-flaming-durtles-and... Source code (may be outdated): https://github.com/ejplugge/com.the_tinkering.wk https://github.com/ejplugge/com.the_tinkering.wk
- nijave 3y ago
  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.
- maxpert 3y ago
  Great that you brought it up. I will fill in the perspective of what I am doing for solving this in Marmot (https://github.com/maxpert/marmot https://github.com/maxpert/marmot). Today Marmot already records changes via installing triggers to record changes of a table, hence all the offline changes (while Marmot is not running) are never lost. Today when Marmot comes up after a long off-time (depending upon max_log_size configuration), it realizes that and tries to catch up changes via restoring a snapshot and then applying rest of logs from NATS (JetStream) change logs. I am working on change that will be publishing those change logs to NATS before it restores snapshots, and once it reapplies those changes after restoring snapshot everyone will have your changes + your DB will be up to date. Now in this case one of the things that bothers people is the fact that if two nodes coming up with conflicting rows the last writer wins. For that I am also exploring on SQLite-Y-CRDT (https://github.com/maxpert/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.
psanford 3y ago
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 https://github.com/psanford/donutdb
- benbjohnson 3y ago
  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 3y ago
    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).
crop_rotation 3y ago
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.
- benbjohnson 3y ago
  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.
- nijave 3y ago
  >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).
rsync 3y ago
Unless something has changed, the underlying tool (litestream) supports plain old SFTP: https://litestream.io/guides/sftp/ https://litestream.io/guides/sftp/ ... so I believe you can use any sftp provider as a target for those backups, correct ?
- benbjohnson 3y ago
  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. :)
dmw_ng 3y ago
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 3y ago
  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.
wg0 3y ago
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?
- benbjohnson 3y ago
  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 3y ago
    [deleted]
  - webmobdev 3y ago
    For compression and encryption, commercial SQLite extensions are available - https://sqlite.org/support.html https://sqlite.org/support.html ... I also recall coming across some free open source projects that had implemented compression and encryption for SQLite databases though I have no idea if they were production ready.
- tmpz22 3y ago
  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.
catgirlinspace 3y ago
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 3y ago
  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 3y ago
    Gotcha. Would performance be better with LiteFS? Think I saw someone mention that because there'd be no network requests between machines.
    - benbjohnson 3y ago
      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.
      - catgirlinspace 3y ago
        Hm, will definitely have to look into it then as I’m pretty bad at making efficient queries and use a lot sometimes just have to get over my fear of like, SQLite being only for client and testing stuff and not servers…
lbotos 3y ago
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/ https://fly.io/docs/about/pricing/
- tptacek 3y ago
  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 3y ago
    <3 Classic HN reader here, I skimmed, went to comments, and then went to pricing :P
gunapologist99 3y ago
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 3y ago
  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.
  - superq 3y ago
    so... no to those questions?
    - tptacek 3y ago
      Would I build a billion-record Facebook replacement in SQLite? Probably not. I probably wouldn't try to scale it up on vanilla Postgres, either.
yread 3y ago
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 3y ago
  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.
gregwebs 3y ago
Async replication implemented with 1 second of data loss expected when the primary goes down. > 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.
- benbjohnson 3y ago
  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.
  - gregwebs 3y ago
    I see. So it’s 1 second plus time to write to S3 as the max data loss window. Which you can experience if your network connection, etc to the secondaries go bad, particularly if you aren’t alerting about it and fixing the situation. To try to avoid any data loss one would need synchronous replication. There is a SQLite fork that uses RAFT.
maxpert 3y ago
I am going to sound like broken record throwing in shameless plugs. But for people who might want to look for alternate options, Marmot (https://github.com/maxpert/marmot https://github.com/maxpert/marmot) is another option that can let you both replicate, and backup your database. I have recently embedded NATS into Marmot and based on community feedback introduced example script that gets you up and running with example DB within seconds.
onetom 3y ago
The - now free - Datomic (https://www.datomic.com https://www.datomic.com) or the open source XTDB (https://www.xtdb.com https://www.xtdb.com) can provide instant access to your data at any point in time. Even to different points in time within a single query, without the concept of a "DB restore" operation. 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.
animeshjain 3y ago
This is awesome. Want to just mention my experience trying to replicate sqlite here. 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.
- benbjohnson 3y ago
  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/ https://litestream.io/alternatives/cron/

238 points by nalgeon 3y ago

Good write up. If I were to build a new SaaS and I’m not anticipating lots of users and concurrency. Regardless the single writer concern, does it suffice as the main storage? What’s is the consistency guarantee?

ankit70 3y ago

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 3y ago
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 3y ago
  Nope. We just buy it.

Kalanos 3y ago

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

pkhuong 3y ago
There's https://github.com/libsql/sqld 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 3y ago
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 https://github.com/superfly/litefs/issues/326
efdb 3y ago
Did you have a look at https://rqlite.io/ https://rqlite.io/ ?
glommer 3y ago
you can use https://turso.tech 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).

unshavedyak 3y ago

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

benbjohnson 3y ago
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 3y ago
  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 3y ago
    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.

Spunkie 3y ago

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.

jgrahamc 3y ago
Email me and tell me jgc@cloudflare.com
benbjohnson 3y ago
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.
bjyule 3y ago
What do you have in mind with respect to D1+Tunnels? 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)
- deleted 3y ago
  [deleted]
- Spunkie 3y ago
  I wanted this when D1 was first teased, and before I figured out that D1 is a database product that happens to be built on SQLite rather than SQLite being the star of the D1 show. Nonetheless, what I want is to be able to point a CF tunnel at a local SQLite file and instantly be able to leverage all the benefits of D1. 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 likely plenty of more cool things that could be done if D1 could be exposed as a normal SQLite file. Ultimately, CF tunnels are just in the mix because it seems like the obvious choice for plumbing SQLite files into and out of the CF network/edge.

NicoJuicy 3y ago

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

benbjohnson 3y ago
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 3y ago
  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 3y ago
    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-detection https://fly.io/blog/introducing-litefs/#split-brain-detectio...
deleted 3y ago
[deleted]

endisneigh 3y ago

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 3y ago
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 3y ago
  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 3y ago

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 3y ago
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.
- freeopinion 3y ago
  You appropriately answered the question that was asked. I would add a little extension that this question (about SQLite specifically) was asked in the context of a discussion about "Distributed SQLite". The considerations around round-trip costs for "Distributed SQLite" are very different than for SQLite.
evantbyrne 3y ago
SQLite is not designed for situations like a website backend where you would expect to have multiple actors modifying the db simultaneously. I'm not sure if they've done something with their implementation to improve upon that.

stu_k 3y ago

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.

benbjohnson 3y ago
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 https://github.com/vlcn-io/cr-sqlite [2]: https://github.com/mycelial/mycelite https://github.com/mycelial/mycelite
qweqwe14 3y ago
Not 100% sure if it uses SQLite, but there's an app called "Flaming Durtles" on Android. It's a frontend for WaniKani (Japanese learning app). The app downloads all the lessons from WK's servers, and you can do them even when offline. When you go online, it uploads them to the backend and syncs everything else. In my experience, it works flawlessly. 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-android-app-with-offline-support https://community.wanikani.com/t/android-flaming-durtles-and... Source code (may be outdated): https://github.com/ejplugge/com.the_tinkering.wk https://github.com/ejplugge/com.the_tinkering.wk
nijave 3y ago
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.
maxpert 3y ago
Great that you brought it up. I will fill in the perspective of what I am doing for solving this in Marmot (https://github.com/maxpert/marmot https://github.com/maxpert/marmot). Today Marmot already records changes via installing triggers to record changes of a table, hence all the offline changes (while Marmot is not running) are never lost. Today when Marmot comes up after a long off-time (depending upon max_log_size configuration), it realizes that and tries to catch up changes via restoring a snapshot and then applying rest of logs from NATS (JetStream) change logs. I am working on change that will be publishing those change logs to NATS before it restores snapshots, and once it reapplies those changes after restoring snapshot everyone will have your changes + your DB will be up to date. Now in this case one of the things that bothers people is the fact that if two nodes coming up with conflicting rows the last writer wins. For that I am also exploring on SQLite-Y-CRDT (https://github.com/maxpert/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.

psanford 3y ago

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 https://github.com/psanford/donutdb

benbjohnson 3y ago
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 3y ago
  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).

crop_rotation 3y ago

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.

benbjohnson 3y ago
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.
nijave 3y ago
>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).

rsync 3y ago

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

benbjohnson 3y ago
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. :)

dmw_ng 3y ago

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 3y ago
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.

wg0 3y ago

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?

benbjohnson 3y ago
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 3y ago
  [deleted]
- webmobdev 3y ago
  For compression and encryption, commercial SQLite extensions are available - https://sqlite.org/support.html https://sqlite.org/support.html ... I also recall coming across some free open source projects that had implemented compression and encryption for SQLite databases though I have no idea if they were production ready.
tmpz22 3y ago
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.

catgirlinspace 3y ago

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 3y ago
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 3y ago
  Gotcha. Would performance be better with LiteFS? Think I saw someone mention that because there'd be no network requests between machines.
  - benbjohnson 3y ago
    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.
    - catgirlinspace 3y ago
      Hm, will definitely have to look into it then as I’m pretty bad at making efficient queries and use a lot sometimes just have to get over my fear of like, SQLite being only for client and testing stuff and not servers…

lbotos 3y ago

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/ https://fly.io/docs/about/pricing/

tptacek 3y ago
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 3y ago
  <3 Classic HN reader here, I skimmed, went to comments, and then went to pricing :P

gunapologist99 3y ago

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 3y ago
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.
- superq 3y ago
  so... no to those questions?
  - tptacek 3y ago
    Would I build a billion-record Facebook replacement in SQLite? Probably not. I probably wouldn't try to scale it up on vanilla Postgres, either.

yread 3y ago

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 3y ago
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.

gregwebs 3y ago

Async replication implemented with 1 second of data loss expected when the primary goes down. > 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.

benbjohnson 3y ago
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.
- gregwebs 3y ago
  I see. So it’s 1 second plus time to write to S3 as the max data loss window. Which you can experience if your network connection, etc to the secondaries go bad, particularly if you aren’t alerting about it and fixing the situation. To try to avoid any data loss one would need synchronous replication. There is a SQLite fork that uses RAFT.

maxpert 3y ago

I am going to sound like broken record throwing in shameless plugs. But for people who might want to look for alternate options, Marmot (https://github.com/maxpert/marmot https://github.com/maxpert/marmot) is another option that can let you both replicate, and backup your database. I have recently embedded NATS into Marmot and based on community feedback introduced example script that gets you up and running with example DB within seconds.

onetom 3y ago

The - now free - Datomic (https://www.datomic.com https://www.datomic.com) or the open source XTDB (https://www.xtdb.com https://www.xtdb.com) can provide instant access to your data at any point in time. Even to different points in time within a single query, without the concept of a "DB restore" operation. 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.

animeshjain 3y ago

This is awesome. Want to just mention my experience trying to replicate sqlite here. 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.

benbjohnson 3y ago
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/ https://litestream.io/alternatives/cron/