Crdt and Sqlite: Local-First Value Synchronization

Posted3 months agoActive3 months ago

marcobambini

78 points

16 comments

marcobambini.substack.comTechstory

calmmixed

Debate

60/100

CrdtSqliteLocal-First SoftwareData Synchronization

Key topics

Crdt

Sqlite

Local-First Software

Data Synchronization

The article discusses using CRDT (Conflict-free Replicated Data Types) with SQLite for local-first value synchronization, sparking a discussion on the limitations and potential issues with this approach.

Snapshot generated from the HN discussion

Discussion Activity

Active discussion

First comment

Peak period

96-108h

Avg / period

Comment distribution16 data points

Loading chart...

Based on 16 loaded comments

Key moments

01Story posted
Oct 9, 2025 at 10:01 AM EDT
3 months ago
Step 01
02First comment
Oct 13, 2025 at 2:43 PM EDT
4d after posting
Step 02
03Peak activity
12 comments in 96-108h
Hottest window of the conversation
Step 03
04Latest activity
Oct 14, 2025 at 2:17 AM EDT
3 months ago
Step 04

Generating AI Summary...

Analyzing up to 500 comments to identify key contributors and discussion patterns

Discussion (16 comments)

Showing 16 comments

withinboredom

3 months ago

3 replies

This works assuming everyone has the same clock or performs changes causually distant from each other. It fails to work if, say, 1000 people all make a change around the same time. This also applies to lamport timestamps.

marcobambiniAuthor

3 months ago

1 reply

The algorithm has a way to resolve conflicts even if, by any chance, the Lamport clock has the same value in all peers

withinboredom

3 months ago

Yeah, but the fact that they didn’t even mention it in their post is why I brought it up.

p1necone

3 months ago

1 reply

If a thousand people all made a change at the same time in a totally deterministic, always online system a single one of those writes would arbitrarily win in exactly the same way.

In practice "1000 people edit same thing at same time" is not a problem that needs to be solved via software, the users are just doing silly things and getting silly results.

withinboredom

3 months ago

2 replies

If it isn’t handled correctly, you’ll eventually end up with parallel histories on different devices. Even if it isn’t 1000 people, people will share documents with entire classrooms, offices, etc., which increases the probability of this situation tremendously.

ncruces

3 months ago

1 reply

CRDTs only care that the end result is eventually the same.

It doesn't need to make sense, or be the most recent change, only that given the same inputs, everyone independently agrees on the same output.

withinboredom

3 months ago

We are saying the same thing. I was pointing out that the article missed one of the hardest parts of actually implementing this, where your algorithm architecture can totally fuck you over if you didn’t plan for it. I just think it’s interesting that they missed pointing it out. Either they got it right on the first try or they haven’t realized the issue with the schema they’re proposing.

jchanimal

3 months ago

We handle this in Fireproof with a deterministic default algorithm, in addition to having a hash-based tamperproof ledger of changes. Fireproof is not SQL based, it is more like CouchDB or MongoDB, but with cryptographic integrity. Apache 2.0 https://use-fireproof.com

In practice during CouchDB's heyday, with lots of heavy users, the conflict management API almost never mattered, as most people can make do with deterministic merges.

tombert

3 months ago

1 reply

Yeah, I implemented a vector clock a few years ago, and I never really found an elegant way to deal with conflicts like this. My very-much-inelegant solution was every item attached an epoch time in milliseconds which was used in a tiebreaker, and if both timestamps were the same I would hash something and choose the smaller one of those.

It seems wrong to rely on NTP for a distributed system like this, but I couldn't really figure out a better way at the time.

withinboredom

3 months ago

The most elegant solutions is to look at Lamport’s other papers, like Paxos or their derivatives. Tie-breaking doesn’t actually happen at the clock level, but at the conflict resolution level, which is a bit higher. IIRC, paxos traditionally uses the node id as the tie-breaker, making leadership deterministic in the face of conflicts.

Though in all honesty, NTP is mostly fine for datacenter deployments where clocks are usually within nanoseconds of each other, so you can use a timestamp with microsecond precision and probably be fine.

philsnow

3 months ago

1 reply

We shouldn't be surprised because the writer works with both sqlite and AI but

> Here’s a polished section you can insert into your article (it fits naturally after the Sync Phase section):

marcobambiniAuthor

3 months ago

I sincerely apologize for that. I am not a native English speaker, so I always use LLM to polish my articles before publishing.

hahn-kev

3 months ago

1 reply

My problem with this kind of design is that you can't really use any relational constraints. Or constraints between columns in a given table because each column is independently merged

canadiantim

3 months ago

I wonder if a columnar database like DuckDB might be better suited for CRDT Local-first solutions, using batched writes to mitigate

what

3 months ago

Isn’t this just vlcn’s crsql?

briandw

3 months ago

For a primer on CRDTs, Martin Kleppmann has a number of good videos: https://www.youtube.com/watch?v=x7drE24geUw

View full discussion on Hacker News

ID: 45527840Type: storyLast synced: 11/20/2025, 12:53:43 PM

Want the full context?