Before I go too far down the road, wanted to check if my use case is something that is supported by LiteFS.
The tldr is that my service could have hundreds of thousands of individual SQLite databases. Each database would be rather small (a few megs). You can think of each db as a file for an application like docx / ppt / xlsx.
Would dumping all of these into LiteFS “just work”? As in, writes to the databases would be replicated to the various regions by LiteFS with a reasonable amount (< 200 ms) of replication lag?
The number of active (open) databases at any given point in time would roughly be a function of the number of concurrent users of the service. Some back of the envelope math:
10k users
Each user creates 10 files (databases)
Users are active 30 minutes a day
So that’d be ~100k SQLite dbs (10k users * 10 files per user) and ~210 concurrently open databases (10k users / (24hrs * 1/0.5 hrs of activity)).
As the system ages, the number of DBs will grow but the number of concurrently open databases would only grow with the number of users.
One other oddity? wrinkle? of my use case is that users can modify their local (on-device) SQLite database while offline. Once the network is restored, I ship all these changes to the hosted SQLite copy and merge the state together. This means there could be large batch writes which, from past experience with MySQL, often wreaks havoc on replication. I doubt these batches would exceed a megabyte but what do I need to watch out for in terms of writing batches of data and liteFS replication guarantees?
This is part of exploring what backend to use to support sync as a service for https://vlcn.io/
I likely wouldn’t hit anything close to the numbers above until next half.
In my testing so far, function and virtual table extensions seem to work fine in SQLite on LiteFS. Makes sense given LiteFS is at the filesystem layer but wanted to double check that too.
The goal of LiteFS is to support at least tens of thousands of databases. That being said, we aren’t at the point where we’re testing that scale yet. It may work—but I can’t say for sure. If it doesn’t, though, it’s probably not hard to get it there. There’s nothing in the design that limits the number of databases.
Most of the overhead you’ll see is in total database size and in concurrent connections. It doesn’t sound like either of those are excessive in your situation though so you should be fine.
Right now, LiteFS sends all replication over a single connection so you can experience head-of-line blocking on a per-replica basis. However, 1MB isn’t too large so it shouldn’t be a concern. You might notice it if you have a 1GB database that you imported and it would block replication of other databases until it’s done copying to the replica.
Yep, most SQLite stuff should “just work” since it’s at a higher layer. The one exception is if you used an encryption library (e.g. SEE or SQLCipher). Those can be difficult to support as they’re typically commercial products and licensing can get hairy.
If I may: Are you syncing sqlite tables from mobile devices to the server? If so, this setup sounds really neat if you’ve already modeled that its ops effective and cost effective as it scales.
The tables also have some extra metadata (tracked by a SQLite extension) that turns them into CRDTs so they can be merged even in the face of concurrent edits from other devices. Of course “merged” here means that some rules are followed to resolve said conflicts, rules that may not be appropriate in every use case.
Dang. We are pretty much thinking of a similar system minus that neat setup with CRDTs… we aren’t as sophisticated to pull that off…
Just like you though, we haven’t modeled ops and monetary costs yet. We are waiting for Fly to launch their managed LiteFS offering, but in the meanwhile, plan to experiment with Cloudflare D1 (which is in open alpha, right now). We prefer ops-wise, the overhead is minimal; while cost-wise, the solution is cheap
Btw, is your code FOSS / is there a way to follow this product’s development (say, you’re building in public or something)?
What are the monetary costs you’re modeling? Network & disk? If you have some numbers about number of databases, row sizes, change set sizes, etc then I can try to help with cost estimation.
From results over the past 24h: 1K databases, ingestion of new 5K to 10K rows per day per db (200B to 500B per row), sync’d every day (if possible, every hour), The sqlite3.db file grows about ~500KB (gzipped) or ~2.7MB per day.
We’d only want to retain 30d to 90d historical data per db.
Queries are on-demand and not predictable ahead of time (we don’t have anything yet to compare or profile against), but fair to assume reads (table scans) will be heavier than writes; but expect most databases to go without having to serve any read requests, too.
For us, we’d be super happy if the entire setup costs 1cent to 5cents per db per month.
Thanks for the info. If you don’t need databases materialized all the time since they’re getting infrequent reads then they could probably be stored compressed. At 500KB/day with a 30 day retention, that would mean the database would be 15MB compressed.
We haven’t determined pricing on the LiteFS cloud yet but using our volume pricing ($0.15/GB), that would put storage at ¢0.225 per database. For bandwidth costs, it’d be ¢0.03 per database so even if you replicated the whole database every day then it’d still cost less than a cent (¢0.9).
I added an issue (#261) to the LiteFS repo for materializing databases on demand. That could help reduce storage costs.
I also added an issue (#262) to introduce batch replication. The idea is that if you don’t need changes immediately replicated, then you could have all changes batched up every minute or hour so that it doesn’t have to send multiple copies of the same page.
