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) 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.
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.
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.
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.
Is LiteFS+SQLite suitable for something like a SaaS solution? For example - Fresh books, Trello or Craigslist - all three have different needs. So which kind of apps should NOT be built on top of LiteFS+SQlite combination?
Good question. LiteFS is just a replication layer so it's probably better to answer your question just from a SQLite standpoint. One of the biggest limitations of SQLite is that it only allows a single writer at a time so you can't have long-running write transactions in your application. Writes can be incredibly fast though—sometimes a millisecond or less depending on your hardware and settings. Speaking of which, make sure you use the "WAL" journaling mode if you're using SQLite it improves concurrency & write performance.
There are also differences once you get to a large scale. Many databases support compression whereas SQLite does not so it might get expensive if you have terabytes of data. That's an extreme case though.
Ultimately, SQLite is just a database. It's more similar to Postgres & MySQL than it is different. There are some features that those client/server databases have like LATERAL joins but I feel like SQLite includes the 99% of what I typically use for application development.
For compression and encryption, commercial SQLite extensions are available - 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.
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.
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.
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.
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?
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.
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.
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
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.
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.
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). 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) 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.
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.
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.
we have done it a few times. It is a lot more work and a lot of edge cases come up, especially if the same data can be written by multiple people. But if data or use case is such that only one person can write to same data and you limit only one logged in client at a time, it's much easier to do.
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 :)
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?"
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.
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.
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.
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?
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.
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.
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.
SQLite works just fine for website backends. Writes are serialized, first from replicas to the central write leader (like in Postgres), and then with the WAL and transaction isolation.
The SQLite documentation itself mentions write concurrency as a limitation.
> Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however.
It goes on to say that other databases provide more concurrency.
> However, client/server database engines (such as PostgreSQL, MySQL, or Oracle) usually support a higher level of concurrency and allow multiple processes to be writing to the same database at the same time. This is possible in a client/server database because there is always a single well-controlled server process available to coordinate access. If your application has a need for a lot of concurrency, then you should consider using a client/server database. But experience suggests that most applications need much less concurrency than their designers imagine.
Yes: sqlite3 only allows a single open write transaction per database file. Whether this matters or not depends on your application. For a large class of applications, this isn't a real issue at all. By way of example: until a few months ago, we were concurrently merging state updates from tens of thousands of VMs across our global worker fleet into a single SQLite database. As has been said across this thread, SQLite works well as a backend for read-heavy applications. A great many CRUD apps are read-heavy!
If you want more concurrency, you can break your schema up into multiple .db files. This sounds onerous but SQLite makes it very easy to do.
That's great it worked for you, but that's not a website backend. It would be foolish to run HN on SQLite for instance. I would go as far as to call it a vulnerability in such context, due to how easily it could be DoS'd.
Maybe you don't understand SQLite's locking issues? Having a bunch of different files that can be written to concurrently sounds like a better solution than SQLite for a social network backend.
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.
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.
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.
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.
Everything I listed at least claims to be a distributed database (also in the title of this article: "distributed sqlite").
However, Postgres is not and never claimed to be a distributed database, so you are erecting and attacking a strawman argument by choosing something that wasn't even in the list.
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.
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.
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.
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.
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.
The - now free - Datomic (https://www.datomic.com) or the open source XTDB (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.
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.
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.
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.
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.
Thanks! I thought DonutDB was an awesome approach too. We do have plans for supporting ephemeral serverless (e.g. Lambda, Vercel) by paging in data on-demand and caching it in the temp space but that work is still a little ways out.
I'd love to hear what you think about the LiteFS approach. We're going to provide a VFS option in the near future as well but the FUSE approach makes it pretty easy to use.
I'm curious how you'll handle lambda concurrency with LiteFS. I think (please correct anything I've got wrong) the way LiteFS works with respect to concurrency is:
- A single host is the primary node and all writes have to go to this node
- It is the application's responsibility to route requests to the current primary host. That is to say, LiteFS does not transparently forward requests to the current primary node
This model makes a lot of sense when you have say a cluster of nodes in an autoscaling group and some way to route write requests to the leader.
It seems like that model is a lot more challenging with Lamdba, where you have one instantiation of the lambda function per request. I'm not sure how you would route to the primary lambda instantiation in this case. DonutDB locks and hopes that it will be able to grab the write lock fast enough to be able to service the request. Maybe that is also what you would do with litefs? If the lambda instantiation isn't not the primary just retry with hopes that you become the primary, and if it is the primary relinquish leadership that after processing a request?
The donutdb approach won't scale up beyond a small amount of concurrency. Its really meant for lightweight workloads (most of my DBs only do a few writes per day).
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.
HN seems to love sqlite but I don't know how to protect against dataloss using sqlite (yes I know litestream exists). Unless you sync disk state to remote storage on every write, wouldn't you always lose some writes? Losing writes is never a good idea. You might think it doesn't matter at your scale, but suddenly it might and you won't like SQLite the same amount anymore.
I don't think the issue is unique to SQLite. Postgres & MySQL both have async replication options that are commonly used. There's always going to be a latency and throughput trade-off when you use synchronous replication and many people don't want to take the performance hit.
We do have plans for adding synchronous replication in LiteFS now that we have LiteFS Cloud released. Ideally, we'd like to make it so you can do synchronous replication on a per-transaction basis so you can choose when you want to take the latency hit.
>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).
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. :)
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!
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.
Author here. We take a daily snapshot and then store incremental changes in multiple time intervals to optimize the storage usage and optimize the time to restore. Your app just sends us the incremental changes every second and we handle all the optimization on our side.
Hi Ben: Ignoring the full snapshot, does LiteFS Cloud periodically restore backups from its interim LTX blobs to check whatever it has accumulated works? If not, what inherently about LTX ensures that restores will always work? Thanks.
Good question. LTX uses a rolling checksum of the entire database at every transaction. Actually, it includes two checksums: one for the state of the database before the transaction and one for after the transaction.
It's incremental so it's fast to compute and it allows us to verify the integrity of the snapshot when we generate it and when we read it back. It also ensures that writes from LiteFS are contiguous and are coming from a known prior state. There's some details about it in our "Introducing LiteFS" blog post[1] from last year.
We don't currently run a "PRAGMA integrity_check" in the cloud service right now for a few reasons. For one, it can be resource intensive for large databases and, two, it won't work once we support LTX encryption. We do have a continuous test runner that writes to LiteFS Cloud, stops, fetches a snapshot, and performs integrity checks, and repeats.
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?
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
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.
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.
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).
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.
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.
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.
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…
