4 ms·

For quick viewing, here's how it works in Postgres (using Yjs): create table posts ( id serial primary key, content crdt.ydoc default crdt.new_

For quick viewing, here's how it works in Postgres (using Yjs): create table posts ( id serial primary key, content crdt.ydoc default crdt.new_ydoc() ); insert into posts (content) values (crdt.new_ydoc()); update posts set content = content || crdt.new_ydoc() where id = 1; The heavy-lifting is done by the teams at Yjs[0] and Automerge[1], who have re-implemented their JS libs in Rust. This made it possible for us to wrap them into a Postgres extension using pgx[2] (which is fast becoming a de-facto tool at supabase) [0] Yjs: https://github.com/yjs/yjs https://github.com/yjs/yjs [1] Automerge: https://github.com/automerge/automerge https://github.com/automerge/automerge [3] pgx: https://github.com/tcdi/pgx https://github.com/tcdi/pgx

zombodb 4y ago
Always nice to see pgx being used in the wild! Awesome work.
samwillis 4y ago
Super excited to see this Paul, and amused to see the nod to my comment in the post :-) I'm going to enjoy getting stuck into this! (When I'm not stuck driving for six hours... just a quick coffee break reading this) Support for CRDT columns within a db to enable JSON like indexing/querying capabilities is fundamental to then building some exciting further developments. Supporting both Yjs and Automerge is exactly the right route forward.
- samwillis 4y ago
  Had a chance to take a little more of a look, did you investigate querying/indexing within the documents? (Didn't spot any sign in the repository) Without the ability to index properties, or do ad-hock queries, on the documents there is little reason to to the merging in-DB. It then makes more sense to merge outside the DB and save both the raw CRDT object and a serialised JSON next to it for querying. I assume that if you had found it's performance better, indexing+querying would have been the next step? However I'm not surprised you found performance not ideal, I suspect a layer in-front of PG throttling writes is the only way forward for a real-time use case. Personally I find the offline + eventual consistency the most interesting use case. The ability to sync a users document set to a local db (SQLite or a future mini-pg/supabase) with local querying and then merge them back later. This could be as basic as a three column table (guid, doc, clock), the clock being a global Lamport like clock to enable syncing only changed documents.
  - aaronblohowiak 4y ago
    Read modify write outside the db seems worse though?
  - kiwicopple 4y ago
    > This could be as basic as a three column table (guid, doc, clock), the clock being a global Lamport This would be achievable, but also perhaps quite strict - we're dictating a table structure rather than working with existing CRDT libs. That said, we could probably do this today, without any mods/extensions > querying/indexing Yeah, there is nothing in this implementation for that > Without the ability to index properties, or do ad-hock queries, on the documents there is little reason to to the merging in-DB One benefit is the ability to send "directly" to the database, but I agree that lack of additional features in the current form outweighs this benefit > a serialised JSON This is how we might do it with with "Realtime as an authority" approach (and is the most likely approach tbh).
Horusiath 4y ago
Yrs (Yjs on Rust) maintainer here: we actually had some idea about building extension to Postgres ;) Great to see that others share the excitement about this idea as well. See: https://github.com/y-crdt/y-crdt/issues/220 https://github.com/y-crdt/y-crdt/issues/220
- cyanydeez 4y ago
  I really hope plv8 maintainers can finally ditch v8 And pave the way for using yjs in all tits glory. Unfortunately, v8 basically is a bloated mess and postgres just stopped supporting it cause it's build process is horrendous.
- kiwicopple 4y ago
  perfect, we'd love to collaborate with you. I'll jump into your github after our Launch Week (this week), or feel free to reach out directly (contact details in my bio)

zombodb 4y ago

Always nice to see pgx being used in the wild! Awesome work.

samwillis 4y ago

Super excited to see this Paul, and amused to see the nod to my comment in the post :-) I'm going to enjoy getting stuck into this! (When I'm not stuck driving for six hours... just a quick coffee break reading this) Support for CRDT columns within a db to enable JSON like indexing/querying capabilities is fundamental to then building some exciting further developments. Supporting both Yjs and Automerge is exactly the right route forward.

samwillis 4y ago
Had a chance to take a little more of a look, did you investigate querying/indexing within the documents? (Didn't spot any sign in the repository) Without the ability to index properties, or do ad-hock queries, on the documents there is little reason to to the merging in-DB. It then makes more sense to merge outside the DB and save both the raw CRDT object and a serialised JSON next to it for querying. I assume that if you had found it's performance better, indexing+querying would have been the next step? However I'm not surprised you found performance not ideal, I suspect a layer in-front of PG throttling writes is the only way forward for a real-time use case. Personally I find the offline + eventual consistency the most interesting use case. The ability to sync a users document set to a local db (SQLite or a future mini-pg/supabase) with local querying and then merge them back later. This could be as basic as a three column table (guid, doc, clock), the clock being a global Lamport like clock to enable syncing only changed documents.
- aaronblohowiak 4y ago
  Read modify write outside the db seems worse though?
- kiwicopple 4y ago
  > This could be as basic as a three column table (guid, doc, clock), the clock being a global Lamport This would be achievable, but also perhaps quite strict - we're dictating a table structure rather than working with existing CRDT libs. That said, we could probably do this today, without any mods/extensions > querying/indexing Yeah, there is nothing in this implementation for that > Without the ability to index properties, or do ad-hock queries, on the documents there is little reason to to the merging in-DB One benefit is the ability to send "directly" to the database, but I agree that lack of additional features in the current form outweighs this benefit > a serialised JSON This is how we might do it with with "Realtime as an authority" approach (and is the most likely approach tbh).

Horusiath 4y ago

Yrs (Yjs on Rust) maintainer here: we actually had some idea about building extension to Postgres ;) Great to see that others share the excitement about this idea as well. See: https://github.com/y-crdt/y-crdt/issues/220 https://github.com/y-crdt/y-crdt/issues/220

cyanydeez 4y ago
I really hope plv8 maintainers can finally ditch v8 And pave the way for using yjs in all tits glory. Unfortunately, v8 basically is a bloated mess and postgres just stopped supporting it cause it's build process is horrendous.
kiwicopple 4y ago
perfect, we'd love to collaborate with you. I'll jump into your github after our Launch Week (this week), or feel free to reach out directly (contact details in my bio)