LiteFS Cloud: Distributed SQLite with Managed Backups • Hacker News Board

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#71

Post by benbjohnson » Thu, Jul 06, 2023, 12:34 AM UTC

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 millisecond or two. It is async replication but the data loss window when you're running a setup like this is quite small.

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#72

Post by onetom » Thu, Jul 06, 2023, 1:27 AM UTC

The - now free - Datomic (https://www.datomic.com) or the open source XTDB (https://www.xtdb.com) can provide instant access to your data at any point in time. Even to different points in time within a single query, without the concept of a "DB restore" operation.

So if your main use-case of restoring a DB from the past is to run analytical queries on it, then you should probably consider these solutions instead.

These systems rely on other databases to store their data, so they inherit the backup capabilities of those databases.

Would be interesting to consider, if they could benefit from using SQLite as a backend instead of H2DB/DynamoDB/Postgres or RocksDB/LMDB/Xodus/JDBC.

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#73

Post by evantbyrne » Thu, Jul 06, 2023, 1:57 AM UTC

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?

SQLite is not designed for situations like a website backend where you would expect to have multiple actors modifying the db simultaneously. I'm not sure if they've done something with their implementation to improve upon that.

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#74

Post by superq » Thu, Jul 06, 2023, 2:03 AM UTC

tptacek wrote: post #61

gunapologist99 wrote: post #59

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

It's a general approach for SQL-backed CRUD applications. If your application is (1) read-heavy (most are), especially if it's particularly read-heavy, and (2) benefits from snappy responses --- ie, if it's the kind of thing where you'd invest significant time in, like, serverside Javascript compilation --- and, especially, if (3) it wants to run in more than one geographical region at the same time, then LiteFS is a…

so... no to those questions?

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#75

Post by tptacek » Thu, Jul 06, 2023, 2:09 AM UTC

evantbyrne wrote: post #73

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?

SQLite is not designed for situations like a website backend where you would expect to have multiple actors modifying the db simultaneously. I'm not sure if they've done something with their implementation to improve upon that.

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.

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#76

Post by tptacek » Thu, Jul 06, 2023, 2:10 AM UTC

superq wrote: post #74

tptacek wrote: post #61

Earlier quoted context omitted.

It's a general approach for SQL-backed CRUD applications. If your application is (1) read-heavy (most are), especially if it's particularly read-heavy, and (2) benefits from snappy responses --- ie, if it's the kind of thing where you'd invest significant time in, like, serverside Javascript compilation --- and, especially, if (3) it wants to run in more than one geographical region at the same time, then LiteFS is a…

so... no to those questions?

Would I build a billion-record Facebook replacement in SQLite? Probably not. I probably wouldn't try to scale it up on vanilla Postgres, either.

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#77

Post by maxpert » Thu, Jul 06, 2023, 3:37 AM UTC

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.

Great that you brought it up. I will fill in the perspective of what I am doing for solving this in Marmot (https://github.com/maxpert/marmot). Today Marmot already records changes via installing triggers to record changes of a table, hence all the offline changes (while Marmot is not running) are never lost. Today when Marmot comes up after a long off-time (depending upon max_log_size configuration), it realizes that and tries to catch up changes via restoring a snapshot and then applying rest of logs from NATS (JetStream) change logs. I am working on change that will be publishing those change logs to NATS before it restores snapshots, and once it reapplies those changes after restoring snapshot everyone will have your changes + your DB will be up to date. Now in this case one of the things that bothers people is the fact that if two nodes coming up with conflicting rows the last writer wins.

For that I am also exploring on SQLite-Y-CRDT (https://github.com/maxpert/sqlite-y-crdt) which can help me treat each row as document, and then try to merge them. I personally think CRDT gets harder to reason sometimes, and might not be explainable to an entry level developers. Usually when something is hard to reason and explain, I prefer sticking to simplicity. People IMO will be much more comfortable knowing they can't use auto incrementing IDs for particular tables (because two independent nodes can increment counter to same values) vs here is a magical way to merge that will mess up your data.

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#78

Post by superq » Thu, Jul 06, 2023, 3:50 AM UTC

tptacek wrote: post #76

superq wrote: post #74

Earlier quoted context omitted.

so... no to those questions?

Would I build a billion-record Facebook replacement in SQLite? Probably not. I probably wouldn't try to scale it up on vanilla Postgres, either.

None of those listed options included Postgres, but Facebook originally was running on 30k+ MySQL servers, so clearly they made do.

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#79

Post by evantbyrne » Thu, Jul 06, 2023, 3:53 AM UTC

tptacek wrote: post #75

evantbyrne wrote: post #73

Earlier quoted context omitted.

SQLite is not designed for situations like a website backend where you would expect to have multiple actors modifying the db simultaneously. I'm not sure if they've done something with their implementation to improve upon that.

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 PostgreSQL, MySQL, or Oracle) usually support a higher level of concurrency and allow multiple processes to be writing to the same database at the same time. This is possible in a client/server database because there is always a single well-controlled server process available to coordinate access. If your application has a need for a lot of concurrency, then you should consider using a client/server database. But experience suggests that most applications need much less concurrency than their designers imagine.

https://www.sqlite.org/faq.html#q5

Re: LiteFS Cloud: Distributed SQLite with Managed Backups

#80

Post by animeshjain » Thu, Jul 06, 2023, 4:08 AM UTC

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/put costs that s3 incurs (the actual storage costs are negligible).

I thought what I am doing would be more common place. But it seems that running single machine instances that can recover after a crash is not common after all (or atleast the tooling does not focus on that). Most use cases seem to be serving high availability or scalability.

In the unnecessary (IMO) desire to make everything highly available, I think simpler solutions have been over looked. I can't help but feel that if you need LiteFS, it is possible that you should be looking at a server oriented database like Postgres or Mysql. In that respect, I feel Litestream is underrated and deserves more attention. It serves a use case which is perhaps more in-line with an in-process DB :)

PS. this thread has some really interesting tools though (Marmot, mycelite). Great to see so many options.