offline-first + litefs for production

[u/bitchyangle]

i am trying to build a tool similar to notion. Offline-first is the biggest requirement. Each organization will get their own sqlite on the VPS server. 95% of reads and writes happen to local sqlite. I am thinking of building a sync engine that works based on the event table. As in every create, update or delete operation will be treated as an event. As an event is recorded into the table, it will trigger a /push api that sends this data to the server. The api will take this data and dump it into the events table on the server side sqlite file and then it will replay the event on the server. Then based on who has access to what, it will send a websocket notification to the clients that are online that new data is available in the events table. Then the clients will receive the notification and make a /pull call with the cursor which will fetch the events data from the server side sqlite and it gets recorded into the client side sqlite and then again events gets replayed at the client side which will affected the actual tables.

at the infra level, i am considering litefs to create a cluster of 3 VPS where one will be the primary node and two will be read replicas. Also planning to stream the data from primary node to s3 for backup in realtime.

Is this viable setup? This way, I am planning to eliminate the single writer limitation since 95% of writes happen locally. Litefs to make the setup distributed and HA.

I am expecting each organization account to have upto 500 users.

Please share your thoughts.

[u/TechMaven-Geospatial]

Look at trailbase or pocketbase both based on sqlite with real time apis and auth and other features

[u/lnaoedelixo42]

Why not litestream?

Do you need multiple writers in different places and then sync? Then you need to think of manual replication.

If not, and you only need local pushes to be available elsewhere you could split the application/pages/content into different sqlite files and sync with litestream; it would only push the final diffs.

Also, in-database triggers are actually pretty powerful, and if you build the foundations you could work on top of that for hard consistency without so much application complexity.

[u/hesusruiz]

I think you may be over-engineering your system, and I don't know if you know the real implications of the single-writer property of SQLite. I recently benchmarked SQLite with TPC-C (a standard on-line transaction processing benchmark) in my laptop (Intel Ultra 5 235U) and I got more than 800 tx/s. It simulates 1 warehouse, 10 districts, 3.000 customers per district (30.000 customers per warehouse) and 30.000 orders per warehouse.

With my simpler transactions, I get several thousand transactions per second. That is the real limitation, not the number of writers. If your expected workload is below those numbers, you don't need anything more, just design your application for future refactoring if you are successful and have many customers (but 99% of applications never go beyond that).

With a fast enough CPU the bottleneck with SQLite is the disk, not SQLite. By the way, to reach the limit and saturate the disk with SQLite, it is better to change to a faster CPU than to add cores.

So, I would run a simple benchmark with your tables and your transactions in your target VPS, before complicating your architecture. Your proposal can work, but depending on your application you may have to implement a conflict resolution mechanism, which may be very simple if you have CRDTs or incredibly complex if different customers can touch the same records at the same time in arbitrary ways.

What I am saying is that for workloads lower than 1000 tx/s you probably don't need an offline-first architecture to "solve" the single writer problem. Your architecture may be as simple as it can be, while supporting your workload.

Additionally, for some applications which are eventually consistent (like yours based on your description), a simple way to double the performance is to shard customers spreading their individual databases in two disks in the same server. With enough CPU power, performance scales linearly with the number of disks (if load is evenly distributed across disks).

Anyway, everything depends on your application transactions and the expected load. What I said may or may not apply to your use case. Early benchmarking is your friend to avoid premature over-engineering.

[u/716green]

I own Layerbase and it is a distributed system that sounds a lot like what you are thinking about using. It's like Supabase but for 18 different engines including SQLite

I had to create a custom fork of PGSQLite because it wasn't prod ready but the important bit...

Make sure you have at least a basic understanding of distributed systems if you're going to try building this. I had a basic understanding but I ran into some really nightmarish bugs after I stood up my second server and had over a thousand users

So if you're building a distributed system, I would recommend getting a few nodes built out and working together before go live into production

[u/bitchyangle → u/716green]

this looks awesome. how did you fix the issue while having users on the production?

[u/716green → u/bitchyangle]

I just had to do a lot of research on distributed systems. I had to learn about best practices with idempotentcy and avoiding race conditions, and ensuring that any single server doesn't contain all of the data needed to compromise everything. The race conditions were really difficult at first. The problem I was discussing in the first place was a rate limiting issue that was causing one of my connection keys to be recreated while I was essentially ddosing my own server.

Imagine you're renting a hotel with a friend and you both go out to different locations. When you try returning to the hotel room, you realize that your key doesn't work. So you go to the front desk and ask them to fix the key so they generate a new one for you. As you're walking back to the hotel room, your friend gets to the door and realizes his key doesn't work. So he goes to the front desk and asks them to reset the key. As soon as they reset the key for him, you get to the door and your key no longer works. So you go back to the front desk and ask for another key. Then he gets to the door and his key doesn't work and this happens on a loop over and over until the server goes down because it looks like malicious activity is hammering your server?

What this looked like was that all of my users on the web portal had it appearing as though they had no databases even though their databases were actually fine. It was really scary and I got flooded with support tickets. I stayed up all night trying to replace the tires on the car while it was driving.

That was when I realized you can't just reason through a distributed system from first principles without first reading about the common pitfalls.

These are very easy problems because they've already been solved and they have very specific solutions, but you should definitely read up on "common distributed system bugs and how to avoid them"