Embeddable, replicated and fault tolerant SQL engine based on Sqlite | Hacker News Reader

Embeddable, replicated and fault tolerant SQL engine based on Sqlite

github.com

89 pointsKingOfCoders32 comments

What I found to be the biggest problem with SQLite is concurrent write operations. WAL helps for concurrent read but not for concurrent write.

On a moderate use case (< 20 write operations per s) I kept getting timeouts so I first devised a quick fix system using RQ jobs passing writes to a single process and migrated to postgres as fast as I could.

Does this software help with concurrent write operations?

SQLite does have a branch that solves the concurrent write issue - not sure if this dqlite uses it https://sqlite.org/src/doc/754ad35c/README-server-edition.ht...

I have no problem with concurrent writes and wal mode on a recent build of SQLite. Perhaps you are standing up a new sqlite connection per operation or otherwise sharing between separate processes?

With exclusive access using a single connection in WAL mode, I can insert tens of thousands of rows per second on flash storage no problem.

Something that is not widely announced is the fact that SQLite serializes all writes by default, so you should share the same connection instance as often as possible.

Maybe stupid question, but when would someone choose this or rqlite over something like Postgres?

Some things that come to my mind:

* no tuning (pg_hba, memory, WAL options etc etc.)

* presumably fixed memory consumption (if similar to sqlite)

* raft gives you usable failover out of the box, whereas with postgres, you need additional (and complex) solutions

* less operational complexity

rqlite author here.

Good summary, I generally agree. rqlite installation is also very easy, thanks to Go. Single, statically-linked binary you can just drop on your machine. rqlite also has a really simple build process (another explicit goal -- just execute 'go build') making it easy to build it yourself -- giving you a high-level of control over the actual software you run.

https://github.com/rqlite/rqlite/blob/master/CONTRIBUTING.md...

rqlite author here. It's not a stupid question. :-) In fact, I answer it head-on in the rqlite FAQ. To quote:

"rqlite is very simple to deploy, run, and manage. It's lightweight. It's a single binary you can drop anywhere on a machine, and just start it. This makes it very convenient. It takes literally seconds to configure and form a cluster. With rqlite you have complete control over your database infrastructure, and the data it stores. That said, it's always possible it's too simple for your needs."

https://github.com/rqlite/rqlite/blob/master/DOC/FAQ.md#why-...

Ignoring Postgres and comparing apples to apples, rqlite has limitations on "safe" SQL statements[1]. This might be a big deal breaker for you than it might for others. As dqlite replicates the pages and not the SQL statements over raft, which means dqlite doesn't have _that_ problem. Using random() and now() are classified as non-deterministic[2].

Using the application to send the time, although not idea could be used as a workaround, but attempting to use non-deterministic functions in triggers becomes a lot harder.

1. https://github.com/rqlite/rqlite#limitations 2. https://www.sqlite.org/deterministic.html

Yes, that's correct. rqlite does statement-based replication, which means it has the limitation you outlined. This could be solved by parsing the entire SQLite statement passed into rqlite before it's written to the Raft log, but I haven't got around to that yet.

Is this like rqlite[1], but in C?

[1]: https://github.com/rqlite/rqlite

rqlite author here, no, they are quite different. To quote the rqlite FAQ:

"dqlite is library, written in C, that you need to Integrate with your own software. That requires programming. rqlite is a standalone application -- it's a full RDBMS (albeit a relatively simple one). rqlite has everything you need to read and write data, and backup, maintain, and monitor the database itself.

rqlite and dqlite are completely separate projects, and rqlite does not use dqlite. In fact, rqlite was created before dqlite."

https://github.com/rqlite/rqlite/blob/master/DOC/FAQ.md#how-...

I know it's only tangentially related, but is there any similar database that allows offline replicas or delayed synchronization? I.e. allow for copies to have local offline writes and be brought up to synchronize? There is couchdb, but that seems to have fallen out of favor. I'm totally fine with having to explicitly handle collisions or prevent collisions client-side.

Both dqlite and rqlite seem to use raft which requires an online quorum (I assume).

[edit] I know CRDTs would be a potential solution, but I haven't seen any readymade, simple DB based on them.

The SQLite built-in changeset and patchset extensions might be what you're looking for:

https://www.sqlite.org/sessionintro.html

Thanks, that's extremely interesting. Sqlite continues to surprise me!

I wonder if this will make it to the CLI client once they've stabilised the API. (Most of the sqlite files I want to offline/delay sync are accessed 99% of the time by the CLI client, not via the library.)

https://pouchdb.com

Right, but that only runs in browsers? Is there a standalone executable or can I embed it in a non-js-language?

Care to elaborate on "couchdb has fallen out of favour"? Currently evaluating it for the very case you describe

Well, I've read about couchdb every few years, and people in the last ~5 years were complaining about an awkward query syntax, slow embedded JS engine (IIRC), client-side complexity of the document data model and effort to maintain.

Similar things could probably be said about mongodb. If your data is small enough, it probably doesn't matter.

Thanks, we're not dealing with very large data sizes: maybe up to 100K documents, maybe using large binary attachments up to 100s MB per attachment. We use another DB product (ArangoDB) as our main application DB, so would be using something like CouchDB only for the offline-first and peer-to-peer replication capability - then referencing items in CouchDB from our main application DB, where we're more than happy about query language and other features. I had a somewhat wild idea that someone (maybe us...) could implement the couch DB replication protocol for ArangoDB using the ArangoDB FOXX framework. Not sure how feasible that might turn out to be though, wonder if anyone else thought of it...

I started using CouchDB around 2010.

Overtime CouchDB has been removing more and more functionality and features that made CouchDB great IMHO (e.g couchapps), the 2.x (IIRC) made a lot of changes and totally broke a large application and made it too challenging to update so we stayed on a 1.x version (whatever version was last before the big couch merge.) we're moving off of Couch entirely in the future.

I'd have a look at Couchbase if you're looking for replication features too.

We have a pwa with pouchdb sync thousands handphones from couchdb every morning. Couchdb sync is great and pouchdb take care the db of whatever browser in your user phone.

I could say we pick couchdb just because of their sync and pouchdb. For our main db we use postgres.

Litestream might be what you're looking for?

https://litestream.io/

That's more of a backup/restore kind of thing - you can't restore to an existing file, for example, which means any local changes (even non-conflicting ones) would need to be blatted before getting any remote ones. Might suit some workflows, though?

I'm working on an offline/delayed syncing sqlite that deals with conflicts. It uses last-write-win for conflict resolution, the target audience is small sql databases you want replicated among a small group of people.

If it sounds like something that might be useful to you, I'd love to talk about your needs.

Looking for solution as well. I want a p2p solution where users can "subscribe" to a public SQLite database and received signed blocks/wal from the swarm. Edits would go into a distributed mempool to be signed by the key owner.

Yes that would pretty much fit my requirements. Feel free to email me at p <at> atrifle <dot> net.

Depending on what you want to do with it, there's always https://github.com/cannadayr/git-sqlite

This is really cool, but I was a little disappointed. I clicked on it thinking it'd be the exact opposite: An implementation of git with all the version control stored in a sqlite DB. I've always wanted git-like branching, forking, commit, access to my datasets.

Using CRDTs requires that you define all the relevant operations as monoids. A lot of work has to go into the choices of monoids to yield usable results. Monoids work fantastically well for things like upvotes on videos or whatever (so you'll notice that's always the first example given), but they don't really work for things like primary / unique keys, yet primary/unique keys are a critical requirement of most RDBMSes and schemas. Ergo, CRDTs don't work that well for relational databases. The situation is very different for collaborative text editing as there's no primary keys nor schema there, and humans are able to detect and correct nonsensical results.

Mind you, I want CRDTs to work for RDBMSes -- the promise of CRDTs is fantastic. But do they? I suspect they do not.

The docs page nags you to accept cookies. How offensive.

I know I'm annoying but this would be a really good project to write in Rust.

Reply on news.ycombinator.com