They're not using `wal_level = logical`, which has been the "friendly" way of doing CDC on Postgres since ever, but are going straight to `wal_level = replica` which, afaik, has never really been used to build something atop of except Postgres' own replication.
This is very interesting. I'd never have guessed that it'd make such a difference. I also bet this is the sort of thing that would have never end up being implemented without access to coding agents. Having to figure out these protocol-level details is no longer the huge time sink it was
Ack, thank you! The idea was to minimize the operational overhead of logical replication (slot growth, slowdowns from reorder buffering, handling advance schema changes) and reducing load on Postgres. This approach lets us purpose-build replication for ClickHouse. Postgres logical replication was primarily designed keeping in mind with Postgres as the target.
There’s also some interesting work happening in core with a similar goal of decoupling logical decoding from the Postgres process. We plan to share learnings from WalShadow with the core and hopefully help bring this to Postgres someday :) https://hacking.postgres.tv/topics/logical-decoding/
Right, it takes some effort, but it’s worth it for the benefits of sub-second latency and reduced operational overhead. We expect the WAL format for existing commands to remain relatively stable across Postgres versions, while newer commands may need additional handling.
Does this work with toast stored unchanged differently than logical? Or is it the same in terms need to merge with some last seen state of toasted field to project the whole of a changed tupled? Always a pita.
would this work with a postgres hosted on supabase? for our company brain mcp thing we mirror postgres data (along with server logs, banking data, slack, etc) in clickhouse but took a very inefficient polling approach since it works with any read-only db connection string. always looking to improve that and get rid of latency though.
WalShadow requires direct access to the physical WAL, and most managed Postgres providers don't allow that. It works with ClickHouse Managed Postgres or self-hosted Postgres. I talk about this in the blog:
Physical WAL is key to WalShadow’s architecture, but most managed Postgres services don’t expose it to customers, making it impossible to use WalShadow. ClickHouse Managed Postgres manages both sides of the stack, allowing us to integrate WalShadow directly into the Postgres replication layer and provide a native path from Postgres WAL to ClickHouse.
They're not using `wal_level = logical`, which has been the "friendly" way of doing CDC on Postgres since ever, but are going straight to `wal_level = replica` which, afaik, has never really been used to build something atop of except Postgres' own replication.
This is very interesting. I'd never have guessed that it'd make such a difference. I also bet this is the sort of thing that would have never end up being implemented without access to coding agents. Having to figure out these protocol-level details is no longer the huge time sink it was
Ack, thank you! The idea was to minimize the operational overhead of logical replication (slot growth, slowdowns from reorder buffering, handling advance schema changes) and reducing load on Postgres. This approach lets us purpose-build replication for ClickHouse. Postgres logical replication was primarily designed keeping in mind with Postgres as the target.
There’s also some interesting work happening in core with a similar goal of decoupling logical decoding from the Postgres process. We plan to share learnings from WalShadow with the core and hopefully help bring this to Postgres someday :) https://hacking.postgres.tv/topics/logical-decoding/
It’s probably brittle though? Replication implementation has to change in some ways from one version to another.
Right, it takes some effort, but it’s worth it for the benefits of sub-second latency and reduced operational overhead. We expect the WAL format for existing commands to remain relatively stable across Postgres versions, while newer commands may need additional handling.
we require wal_level=logical, would be nice to support looser in future
agreed that there's a huge complexity cost to going this route
Does this work with toast stored unchanged differently than logical? Or is it the same in terms need to merge with some last seen state of toasted field to project the whole of a changed tupled? Always a pita.
yes toast is a pita. we're currently working on 3 modes:
1. disabled
2. clickhouse, stores toast chunks on clickhouse then pulls them to resolve
3. shadow, stores toast in shadow catalog, obviously less latency than clickhouse but demands disk space
would this work with a postgres hosted on supabase? for our company brain mcp thing we mirror postgres data (along with server logs, banking data, slack, etc) in clickhouse but took a very inefficient polling approach since it works with any read-only db connection string. always looking to improve that and get rid of latency though.
WalShadow requires direct access to the physical WAL, and most managed Postgres providers don't allow that. It works with ClickHouse Managed Postgres or self-hosted Postgres. I talk about this in the blog:
Physical WAL is key to WalShadow’s architecture, but most managed Postgres services don’t expose it to customers, making it impossible to use WalShadow. ClickHouse Managed Postgres manages both sides of the stack, allowing us to integrate WalShadow directly into the Postgres replication layer and provide a native path from Postgres WAL to ClickHouse.
Not this quick but better then polling: https://supabase.com/features/supabase-pipelines