The challenges of supporting foreign key constraints

Re:The challenges of supporting foreign key constraints by Exuma on Tuesday December 05, 2023 @06:22PM (#38538346)

Awesome, thank you. That's kind of what I was thinking, I'm glad you confirmed it. How exactly did you sync or data from PG -> Clickhouse?

I was considering using something like Airbyte, but then I thought this may actually be complex if PG rows are updating/deleting it means I also need to sync single rows (or groups of rows) to clickhouse, and I wasn't sure how the support was for that.

[ Parent | on HN ]

Re:The challenges of supporting foreign key constraints by financltravsty on Tuesday December 05, 2023 @06:39PM (#38538494)

What I did in my case was setup stream replication to send over the Postgres WAL to another service that would update a ClickHouse cluster. Essentially, every time the WAL file is closed, a batch of all the SQL commands that were committed are sent over the wire.

It might be easier to find some "change data capture" product that will do that for you though (like Airbyte). I can't give any recommendations here, however.

[ Parent | on HN ]

Re:The challenges of supporting foreign key constraints by tw1ser on Tuesday December 05, 2023 @11:18PM (#38540429)

I can recommend a fellow Y member PeerDB [0] for this but I don't know if they support ClickHouse as a destination

[0] - https://www.peerdb.io/

[ Parent | on HN ]