Marmot: Multi-writer distributed SQLite based on NATS | Hacker News Reader

Marmot: Multi-writer distributed SQLite based on NATS

github.com

120 pointssummarity44 comments

If you're interested in this, here are some related projects that all take slightly different approaches:

- LiteSync directly competes with Marmot and supports DDL sync, but is closed source commercial (similar to SQLite EE): https://litesync.io

- dqlite is Canonical's distributed SQLite that depends on c-raft and kernel-level async I/O: https://dqlite.io

- cr-sqlite is a Rust-based loadable extension that adds CRDT changeset generation and reconciliation to SQLite: https://github.com/vlcn-io/cr-sqlite

Slightly related but not really (no multi writer, no C-level SQLite API or other restrictions):

- comdb2 (Bloombergs multi-homed RDBMS using SQLite as the frontend)

- rqlite: RDBMS with HTTP API and SQLite as the storage engine, used for replication and strong consistency (does not scale writes)

- litestream/LiteFS: disaster recovery replication

- liteserver: active read-only replication (predecessor of LiteSync)

- SQLSync: collaborative offline-first wrapper around SQLite

Don't forget Turso's libsql which uses a local node+reconciliation

https://medium.com/chiselstrike/introducing-embedded-replica...

Also Expensify's Bedrock, which powers their widely-circulated "Scaling SQLite to 4M QPS" article:

https://bedrockdb.com/

https://use.expensify.com/blog/scaling-sqlite-to-4m-qps-on-a...

rqlite[1] creator here, happy to answer any questions.

[1] https://www.rqlite.io

Is there a system where a single thread writes (ala David Crawshaw's “One Process Programming -- https://crawshaw.io/blog/one-process-programming-notes), and then changes are replicated out quickly and efficiently?

That plus a globally incrementing version number is what I want…

That's a (very) simple way to describe what rqlite mostly does (and similar systems, many of which are listed below), though it depends on how "quickly" you want it. If you want speed, it's hard to do it fast -- though accepting a small possibility of data loss will get you much faster performance. rqlite also offers that, via Queued Writes[1].

[1] https://rqlite.io/docs/api/queued-writes/

Author of Marmot here. Marmot was born out of my own use-case (was building replicated SQLite based cache). While working on various problems I realized how well suited it might be for read heavy sites/workloads. If I take a typical CMS site 90% of the time it's just reading and SQLite is perfect for that, but then how I get independently deployed nodes to replicate data. The philosophy I am sticking to so far:

- Sidecar! I would avoid any kind of in process library at any cost. Call me biased but I don't trust someone's code in my process space causing it to crash.

- No master - each node should be able to make progress on its own, if these processes go down your own process will keep functioning. They will converge once everything is back.

- Easy to start, yet hard to master - You can get up and running pretty quickly, but make no mistake this tool is not for rookie who doesn't understand how incremental primary keys are bad, and how to they can keep things conflict free.

I am far from getting everything I need in there, and again my philosophy might evolve over time as well. Talking to people on Discord has helped me think through use-cases a lot, so keep the good feedback coming. Would love to answer any questions people might have here.

I can totally see some junior dev who doesn't fully grok transactions (and not reading/understanding the "What happens when there is a race condition?" section), trying to deploy something like this and scratching their head why it falls over completely in production. The site worked fine when it was just me testing it!

This is a neat proof of concept and I encourage experimentation. But, if you're developing something, please just use postgres, and don't try to cobble things together with something like this.

Edit: already seeing the downvotes. Yes... classic HN... anything that goes against plain old sanity is punished.

Postgres transactions are not SERIALIZABLE by default either. Though, yes, Postgres defaults will catch some more conditions than this project will.

I think the important thing is to encourage devs to understand transactions in the first place.

There’s a reason that this is called “hacker news” and not “just use the industry standard for the last 3 decades news”.

Won’t downvote you for giving pragmatic advice, but I appreciate projects like this that slap together disparate technologies for an interesting goal, even if it isn’t the best choice for your usual Fortune 500 company.

> I appreciate projects like this that slap together disparate technologies for an interesting goal

That's exactly why I said: "This is a neat proof of concept and I encourage experimentation."

+1 for Postgres. It's actually ridiculous how much good software you can simply start using for free.

If you let juniors design and deploy a DB layer to prod it's your fault not the DBs. Transaction isolation is generally complicated topic that a lot of senior devs have a pretty tenuous grasp on ime and distributed Postgres solutions don't solve this particularly well either last I checked

> If you let juniors design and deploy a DB layer to prod it's your fault not the DBs.

Some companies only have juniors. 28 years ago, I was the junior at my company, and the first/only engineer.

Let's also be real here, most applications don't need distributed Postgres either and those that do, will have senior engineers on staff.

I’m aware such places exist and my point still stands. By your own logic they dont need anything beyond webflow, squarespace or the like and shoudnt be messing with dbs (and this includes postgres)

By your own logic, nobody becomes senior through experience.

You generally become senior by getting exposure and proficiency with lots of different concepts which is the opposite to the spirit of your original comment here (hence the downvotes, I suspect). Only going with what everyone else is doing is how you become junior N times over.

> Only going with what everyone else is doing is how you become junior N times over.

Wild thought, this is not my experience at all. I don't even understand how you can come to the conclusion.

I completely agree with your analysis. Understanding the complexities of achieving convergence with basic auto-increment counters without advanced CRDT types is 101 IMO. Those familiar with these issues inherently comprehend the challenges involved. While it's plausible for someone to leverage a library atop Marmot to construct and synchronize such types, it's important to note that this tool isn't tailored for junior developers grappling with transactional intricacies. I've witnessed instances where inexperienced developers initiate transactions and make HTTP calls while holding locks, resulting in system outages. Marmot isn't intended for individuals lacking a solid understanding of distributed systems. My recommendation aligns with advising entry-level individuals to explore these tools only when they reach a scale where such complexities become pertinent.

> My recommendation aligns with advising entry-level individuals to explore these tools only when they reach a scale where such complexities become pertinent.

I posit that if you're at that scale, you're figuring out how to get distributed postgres to work and not messing with things like Marmot.

Depends on use-case. What you are building, here is one use-case for example where it made sense over postgres https://www.youtube.com/watch?v=HycGtLjlikI

Ok, I watched that. I wasn't able to understand what the actual use-case was other than a demo of setting this up in an infinitely over complicated system.

Agree 100%.

If you absolutely need to distribute data, it means you're working at a scale where things are already complex.

Using SQLite for that will not simplify your life, it will just make it harder to tackle the complexity.

Unless you are extremely careful, eventually consistent writes are almost guaranteed to produce behaviors that are hard / impossible to anticipate during development. And a PITA to debug.

> In Marmot every row is uniquely mapped to a JetStream. This guarantees that for any node to publish changes for a row it has to go through same JetStream as everyone else.

and

> This means there is NO serializability guarantee of a transaction spanning multiple tables. This is a design choice, in order to avoid any sort of global locking, and performance.

But since the serialization happens at per row level, does this also mean no serializability guarantee of a transaction within a table too, not only spanning multiple tables?

I'm using Marmot for my own website on production. Up to this date there were no problems.

If I had any technical issues (i.e. questions, optimizations etc) I always asked the developer maxpert and he gave me in-depth answers that helped me personally a lot.

In my case I have much love for Marmot and hopefully it grows and helps a bigger community

What scale does you site operate in terms of QPS?

Not much at the current stage. In range of 50-500 QPS.

However, my main goal for Marmot was to decrease the latency of my website by putting the db as close as I can to the end user

50-500 QPS is quite substancial scale.

It's about +5M requests per day or ~2B per year. That's on the lower end! The higher end is ~20B requests per year!

I think using distributed SQL for that kind of scale may not be a wise choice, but that's my opinion...

I see a lot of projects started in this space and all of them appear to have multi-writer as a goal. I've been interested for a long time (and have started and stopped) in a solution for single-write multi-read with eventual consistency. I chatted with @benbjohnson on a LiteStream ticket about the possibility of adding a mobile client to receive the replicas to mobile devices but I think that option isn't really consistent with the new direction of that work for LiteFS at Fly.

To me the multi-writer "collaborative" use case is super powerful but also has a lot of challenges. I personally would see a lot of value in a solution for eventually consistent read-replicas that are available for end-client (WASM or mobile native) replication but still funnel updates through traditional APIs with store-and-forward for the offline case.

Is anybody aware of an open-source project pursuing that goal that maybe I haven't come across?

This is really cool. We've just created a POC that bridges Federated GraphQL Subscriptions and NATS, so this could maybe work together? Here's a small video of combining Federated Subscriptions and event driven architecture through NATS: https://twitter.com/TheWorstFounder/status/17341349261133783...?

Would absolutely love to explore it deeper. Feel free to drop by community discord if you start experimenting with Marmot.

Something that wasn't clear to me from the README: how does this handle duplicate IDs?

If I have a table with a string primary key and I insert a row in one node with ID "hello" and do the same thing (but with different column data) on another node, what happens?

I'm not 100% sure of this but from the README section on race conditions:

"the last writer will always win. This means there is NO serializability guarantee of a transaction spanning multiple tables. This is a design choice, in order to avoid any sort of global locking, and performance."

So it sounds like in your example, whichever node writes last with a given primary key will be the data you'll end up with.

Based on their description of how it works with jetstream i think last insert will fail because it will see the row already exists in jetstream

Within a single table they have tx serializability, just not with multiple tables

In what context would you use a single string as the ID that would be subject to collisions (instead of including a user id as part of the ID)?

People do all sorts of weird things with databases, and if you want to run existing software against a database (as opposed to greenfield development deliberately targeting Marmot) you need to understand what will happen in different cases.

> if you want to run existing software against a database (as opposed to greenfield development deliberately targeting Marmot) you need to understand what will happen in different cases.

A great point, but from the readme in the Marmot repo:

> It does not require any changes to your existing SQLite application logic for reading/writing.

I suspect you’re probably still right, but that’s not what the author claims.

A better example might be a field that must be unique, like a URL slug. Suppose you have a database of products and the product table has a field used for URLs, like “/tshirt” and “/sweater”. You need those to be unique within the table.

The question remains - how does Marmot enforce a uniqueness constraint? If you don’t like the product example, fine, but it is easy to think of others. It would be unfortunate if marmot is incapable of supporting uniqueness.

As I understand it, transactions are still serialized for any given table, just not across tables. Wouldn’t that solve this uniqueness constraint issue?

Glad you asked the question. I never recommend use auto-incrementing IDs in production always generate one (e.g. Twitter Snowflake). With ID generators you get rid of collisions.

I wonder if this kind of a setup would be useful for federated protocols. Mastodon and Farcaster both maintain websocket connections open from all servers to all servers.

i'm surprised by this.. No schema changes?

Nobody pushes an application out just once.

I have got an issue discussing this in place and exploring couple of proposals. Turns out folks using SQLite use plethora of techniques to maintain their schemas, so enforcing a framework that works with the rest of their ecosystem is easier said than done.

Reply on news.ycombinator.com