5 ms·

Marmot: Multi-writer distributed SQLite based on NATS

120 points by summarity 3y ago

simonw 3y ago
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?
- nlh 3y ago
  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.
  - dilyevsky 3y ago
    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
    - deleted 3y ago
      [deleted]
- governmentdude 3y ago
  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)?
  - simonw 3y ago
    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.
    - meepmorp 3y ago
      > 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.
  - spenczar5 3y ago
    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.
    - MobiusHorizons 3y ago
      As I understand it, transactions are still serialized for any given table, just not across tables. Wouldn’t that solve this uniqueness constraint issue?
- maxpert 3y ago
  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.
Mortiffer 3y ago
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.
latchkey 3y ago
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.
- eatonphil 3y ago
  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.
  - deleted 3y ago
    [deleted]
- taywrobel 3y ago
  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.
  - latchkey 3y ago
    > 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."
- FredPret 3y ago
  +1 for Postgres. It's actually ridiculous how much good software you can simply start using for free.
summarity 3y ago
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 https://litesync.io - dqlite is Canonical's distributed SQLite that depends on c-raft and kernel-level async I/O: https://dqlite.io 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 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)
- anovick 3y ago
  - SQLSync: collaborative offline-first wrapper around SQLite
- KRAKRISMOTT 3y ago
  Don't forget Turso's libsql which uses a local node+reconciliation https://medium.com/chiselstrike/introducing-embedded-replicas-deploy-turso-anywhere-2085aa0dc242 https://medium.com/chiselstrike/introducing-embedded-replica...
- spiffytech 3y ago
  Also Expensify's Bedrock, which powers their widely-circulated "Scaling SQLite to 4M QPS" article: https://bedrockdb.com/ https://bedrockdb.com/ https://use.expensify.com/blog/scaling-sqlite-to-4m-qps-on-a-single-server https://use.expensify.com/blog/scaling-sqlite-to-4m-qps-on-a...
- otoolep 3y ago
  rqlite[1] creator here, happy to answer any questions. [1] https://www.rqlite.io https://www.rqlite.io
- zellyn 3y ago
stocknear 3y ago
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
- rmbyrro 3y ago
  What scale does you site operate in terms of QPS?
  - stocknear 3y ago
    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
    - rmbyrro 3y ago
      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...
liuliu 3y ago
> 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?
jensneuse 3y ago
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/1734134926113378393 https://twitter.com/TheWorstFounder/status/17341349261133783...?
- maxpert 3y ago
  Would absolutely love to explore it deeper. Feel free to drop by community discord if you start experimenting with Marmot.
maxpert 3y ago
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.
deleted 3y ago
[deleted]
wtatum 3y ago
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?
- deleted 3y ago
  [deleted]
sigmonsays 3y ago
i'm surprised by this.. No schema changes? Nobody pushes an application out just once.
- maxpert 3y ago
  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.

120 points by summarity 3y ago

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?

nlh 3y ago
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.
- dilyevsky 3y ago
  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
  - deleted 3y ago
    [deleted]
governmentdude 3y ago
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)?
- simonw 3y ago
  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.
  - meepmorp 3y ago
    > 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.
- spenczar5 3y ago
  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.
  - MobiusHorizons 3y ago
    As I understand it, transactions are still serialized for any given table, just not across tables. Wouldn’t that solve this uniqueness constraint issue?
maxpert 3y ago
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.

Mortiffer 3y ago

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.

latchkey 3y ago

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.

eatonphil 3y ago
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.
- deleted 3y ago
  [deleted]
taywrobel 3y ago
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.
- latchkey 3y ago
  > 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."
FredPret 3y ago
+1 for Postgres. It's actually ridiculous how much good software you can simply start using for free.

summarity 3y ago

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 https://litesync.io - dqlite is Canonical's distributed SQLite that depends on c-raft and kernel-level async I/O: https://dqlite.io 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 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)

anovick 3y ago
- SQLSync: collaborative offline-first wrapper around SQLite
KRAKRISMOTT 3y ago
Don't forget Turso's libsql which uses a local node+reconciliation https://medium.com/chiselstrike/introducing-embedded-replicas-deploy-turso-anywhere-2085aa0dc242 https://medium.com/chiselstrike/introducing-embedded-replica...
spiffytech 3y ago
Also Expensify's Bedrock, which powers their widely-circulated "Scaling SQLite to 4M QPS" article: https://bedrockdb.com/ https://bedrockdb.com/ https://use.expensify.com/blog/scaling-sqlite-to-4m-qps-on-a-single-server https://use.expensify.com/blog/scaling-sqlite-to-4m-qps-on-a...
otoolep 3y ago
rqlite[1] creator here, happy to answer any questions. [1] https://www.rqlite.io https://www.rqlite.io
zellyn 3y ago

stocknear 3y ago

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

rmbyrro 3y ago
What scale does you site operate in terms of QPS?
- stocknear 3y ago
  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
  - rmbyrro 3y ago
    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...

liuliu 3y ago

> 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?

jensneuse 3y ago

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/1734134926113378393 https://twitter.com/TheWorstFounder/status/17341349261133783...?

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

maxpert 3y ago

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.

deleted 3y ago

[deleted]

wtatum 3y ago

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?

deleted 3y ago
[deleted]

sigmonsays 3y ago

i'm surprised by this.. No schema changes? Nobody pushes an application out just once.

maxpert 3y ago
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.