Comment by samwillis | Hacker News Reader

Comment by samwillis | Hacker News Reader

Parent Full thread

For a tenanted SAAS app SQLite at the edge is a really compelling architecture. You have a single DB per customer/company/group with all their users woking against that. It can operate at the edge, closest to where the majority of the customers users are.

SQLite could scale to even quite large customers with this.

Another really compelling architecture is a DB per user, with partial/selective sync between the nodes. If you then couple this with a "local first" design, the "edge" just becomes an other local deployment that the users db can sync against. Collaborative apps, where the users have their own documents but can also share/fork them would align well with this.

I believe starting with "local first" and using the edge for sync and "online only" modes is going to become the default for a significant number of apps moving forward. SQLite with CRDT based syncing and merge conflict resolution is the way to do this. There are a couple of exciting projects working on this:

https://electric-sql.com/

https://vlcn.io/

Yea, but does it scale? Yes, for 100 users maybe. But you have 100 000 small sqlite db:s I would be a bit worried about how it scales.

Electric is super cool. Can't wait until it is a bit more mature.

In broad strokes having 100k SQLite dbs is a bit like having 100k git repositories. It's doable and just about having tooling to manage it.

It actually makes a few things simpler, say progressively rolling out visiting upgrades. You need to write schema migrations anyway, this lets you upgrade one customer at a time.

The problem is that one at a time times 100,000 DBs takes a long time. For every second a migration takes its 1.1 days.

You can parallelize it, of course, but that's a challenge in of itself.

> You can parallelize it, of course, but that's a challenge in of itself.

Not a challenge at all really.

GNU Parallel + some Python code?

Yea, pretty much tooling and stuff is what I doubt scales. I'm sure a machine can handle it. It will just be very hard to manage because lack of tooling.

> Yea, but does it scale? Yes, for 100 users maybe. But you have 100 000 small sqlite db:s

If it's a case of 1 node per customer (node being vm, lambda, cloudflare worker, whatever) then there should be no limit.

Maybe you're thinking of a more "traditional" approach with a server process managing 1 sqlite db per customer, which might make sense in order to keep cloud costs down. Even in that case it should be trivially easy to distribute the load.

So store sqlite on S3. Start a new lambda (or similar) per user, download the db from sqlite and write to it and upload it again. And making sure there can never be more than 1 function running per user (to avoid two lambdas editing the same database). Seems a bit fragil but I might be wrong.

This also sounds like CouchDB: https://couchdb.apache.org/

more at https://localfirstweb.dev/

the irony of "join our discord community" really hits pretty hard on that one

Reply on news.ycombinator.com