Support for VersionedCollapsingMergeTree as ClickHouse Engine

[mayuresh1606]

I’m exploring the possibility of syncing a PostgreSQL table to ClickHouse using the VersionedCollapsingMergeTree engine as the destination. While testing, I observed that:

The PeerDB GUI currently supports only MergeTree and ReplacingMergeTree engines.

I manually created a ClickHouse table using the VCM engine with _sign and _version columns, but the mirror validation failed with:
"CDC Mirror is invalid: not all PeerDB columns found in destination table".

Could you please clarify the following:

1. Is it possible to use VersionedCollapsingMergeTree as the destination engine in PeerDB, either via GUI or custom config?
2. If so, how can we map the necessary CDC fields like _sign and _version during sync setup?
3. Is there a recommended approach to integrate VCM cleanly with PeerDB’s CDC mechanism?

Any guidance or documentation you could provide would be very helpful.

Thanks,
Mayuresh Ovhal.

[iamKunalGupta]

@serprex do we want to have this in with #2949 ?

[iamKunalGupta]

Also @mayuresh1606 will an mv on the rmt table created by peerdb work for you?

[serprex]

@serprex do we want to have this in with #2949 ?

enough questions raised in that PR already, would make sense for this to be a separate PR

[serprex]

reading docs, this would be pretty complicated to implement, as updates are required to include a cancel row matching the previous record. we could do that, but it'd be similar to our primary key update logic, while requiring REPLICA IDENTITY FULL

[heavycrystal]

There are edge cases where schema changes could lead to the cancel row not matching exactly

[mayuresh1606]

Also @mayuresh1606 will an mv on the rmt table created by peerdb work for you?

Thanks for acknowledging the raised issue
And no MV wouldn't work, would eventually require VersionedCollapsingMergeTree

[ilidemi]

Hi @mayuresh1606 ! Would ReplacingMergeTree (which also has a version column for deduplication) also work for you ? We have a detailed blog here on how you can model ClickHouse data using RMT :)
https://clickhouse.com/blog/postgres-to-clickhouse-data-modeling-tips-v2#deduplication-strategy

Let me know if this works, else we will take a look at VCM when we have a chance !