| The ElectricSQL concept of 'shapes' is interesting. Right now, a shape is just a single table with some column filters and a where clause. The shape is defined on the fly in the query string. Electric does single table sync of those 'shapes' a lot like Supabase realtime. I work as part of the team building Ably LiveSync, a competitor in this space. Our postgres connector[0] works in the opposite way by taking rows from an 'outbox' table and fanning them out to subscribed clients over websockets in realtime. Here's the thing; for any meaningfully complicated data in a relational database the data is likely to be normalised across tables with relations, and there are going to be joins. So I'm really curious how people are making single-table-sync work. (Maybe this is where Electric imagine the future of shapes, solving for joins). In LiveSync we do it the other way around, instead of having a live-query style subscription to a table (like ElectricSQL), we listen for someone writing a row to the 'outbox' table and that row is automatically sent to subscribers. This means that you're writing your denormlised data directly to the outbox and it's being sent on to clients, rather than writing your normalised data to tables but being limited by single table sync. Or worse, your clients having to subscribe to multiple sync streams and trying and stitch the data back together. We opted to have the write-side insert the denormalised data, rather than having the read side have to stitch normalised data back together. Electric will at some point try and solve multi table sync, which isn't easy given how the Postgres replication protocol works. It's also not easy to imagine how shapes (which are defined on the fly right now in the query string) will adapt to multiple tables. There's going to be a tradeoff between a complex query string trying to stitch the normalised tables back together, or a 'shape' becoming an entity in Electric which would define how to stitch the normalised tables back together (which you would have to CRUD manage, and update in Electric every time your schema changed). [0]: https://ably.com/docs/livesync/postgres |
You're right that "single-table sync" does have its limitations. At PowerSync we effectively support one level of "joins", and even then it's often not enough for more complex schemas. An older version of ElectricSQL did also actually have multi-table shape sync support, but I believe doing that at scale proved to be difficult.
One solution to this is often denormalizing data - either adding more denormalized columns in the existing table, or creating new tables dedicated to sync data. Conceptually, keeping these tables up to date is not that different from writing updates to an outbox table.
I'm also interested in seeing what Zero comes up with in the space. They seem to have solved doing multi-table query sync, but it remains to be seen how well that works in practice.