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

81–90 of 94 posts

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#81

Post by tptacek »

evantbyrne wrote: post #79
tptacek wrote: post #75

Earlier quoted context omitted.

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

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.

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#82

Post by webmobdev »

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…

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.

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#83

Post by pritambarhate »

stu_k wrote: post #22

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.

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.

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#84

Post by gregwebs »

benbjohnson wrote: post #71
gregwebs wrote: post #68

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

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

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.

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#85

Post by benbjohnson »

animeshjain wrote: post #80

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…

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.

[1]: https://litestream.io/alternatives/cron/

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#86

Post by freeopinion »

tptacek wrote: post #20
menzoic wrote: post #18

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

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.

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#87

Post by evantbyrne »

tptacek wrote: post #81
evantbyrne wrote: post #79

Earlier quoted context omitted.

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

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

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.

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#88

Post by tptacek »

evantbyrne wrote: post #87
tptacek wrote: post #81

Earlier quoted context omitted.

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

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.

HN famously ran (still runs?) on a series of flat files. You found the actual worst possible rebuttal.

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#89

Post by evantbyrne »

tptacek wrote: post #88
evantbyrne wrote: post #87

Earlier quoted context omitted.

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.

HN famously ran (still runs?) on a series of flat files. You found the actual worst possible rebuttal.

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.

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#90

Post by tptacek »

evantbyrne wrote: post #89
tptacek wrote: post #88

Earlier quoted context omitted.

HN famously ran (still runs?) on a series of flat files. You found the actual worst possible rebuttal.

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.

Yes, maybe that's it.
Post reply on HN