Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

LiteFS Cloud: Distributed SQLite with Managed Backups

fly.io

Post reply on HN
Print view View original

51–60 of 94 posts

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#51

Post by catgirlinspace »

benbjohnson wrote: post #47
catgirlinspace wrote: post #41

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 checki…

Gotcha. Would performance be better with LiteFS? Think I saw someone mention that because there'd be no network requests between machines.

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#52

Post by nijave »

crop_rotation wrote: post #29

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.

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

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#53

Post by benbjohnson »

catgirlinspace wrote: post #51
benbjohnson wrote: post #47

Earlier quoted context omitted.

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 checki…

Gotcha. Would performance be better with LiteFS? Think I saw someone mention that because there'd be no network requests between machines.

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.

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#54

Post by [deleted] »

benbjohnson wrote: post #40
wg0 wrote: post #35

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, m…

[deleted]

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#55

Post by Mertax »

benbjohnson wrote: post #48
tmpz22 wrote: post #44

Earlier quoted context omitted.

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…

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 custom…

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?

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#56

Post by felipeccastro »

benbjohnson wrote: post #48
tmpz22 wrote: post #44

Earlier quoted context omitted.

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…

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 custom…

Doesn't the WAL mode solve the high concurrency write situation? If it can't be relied on busy season, why the push for Sqlite in production?

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#57

Post by benbjohnson »

Mertax wrote: post #55
benbjohnson wrote: post #48

Earlier quoted context omitted.

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 custom…

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.

[1]: https://github.com/vlcn-io/cr-sqlite

[2]: https://github.com/mycelial/mycelite

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#59

Post by gunapologist99 »

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.

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#60

Post by tmpz22 »

felipeccastro wrote: post #56
benbjohnson wrote: post #48

Earlier quoted context omitted.

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 custom…

Doesn't the WAL mode solve the high concurrency write situation? If it can't be relied on busy season, why the push for Sqlite in production?

> If it can't be relied on busy season, why the push for Sqlite in production?

I think its less about proving sqlite is awesome for everything then it is about proving it can be awesome and practical for some projects.

Post reply on HN