Clickhouse: Delete old data without replication to target (i.e. only from source)

[mkaze]

We would like to setup CDC between postgres (source) and clickhouse (target) using PeerDB. However, in our application, the old/cold data in postgres is almost never modified and is only used for analytical purposes. It's only the recent/hot (i.e. past few weeks) data which may be updated.

Is there any way to delete the old data (e.g. older than 3 months) only from postgres while still keeping them in clickhouse (i.e. without these specific delete operations be replicated to clickhouse)?

[ilidemi]

If there is a time window without changes on Postgres or pending transactions, it's possible to delete the mirror, delete the old rows, then recreate the mirror as CDC-only. When the mirror is ongoing, the replication slot will have to receive all the delete events.

[mkaze]

Thanks! @ilidemi

If there is a time window without changes on Postgres or pending transactions

To clarify: do you mean a time window without any type of DML query (INSERT, DELETE, UPDATE) as well as DDL queries?

[ilidemi]

Yes, basically a complete downtime as far as replication is concerned. This would be the most bulletproof solution.

Talked to the team and seems there could be another approach:

1. Find a window of time when there are no deletes that will need to be replicated (inserts/updates are ok)
2. ALTER PUBLICATION ... SET publish = 'insert, update, truncate' for the publication used by PeerDB
3. Delete the cold data
4. ALTER PUBLICATION ... SET publish = 'insert, update, delete, truncate' (revert to default)

If only some tables need to be cleaned up in this way, you can create a separate mirror for them with its own publication, this way the other tables can continue having deletes, if that's relevant for you.

Please keep in mind this is not a well-tested scenario for PeerDB and involves advanced Postgres administration, so if any complications arise we may not be able to provide support.

[mkaze]

The use of ALTER PUBLICATION seems to be a good option; unfortunately, I was not familiar with it and when I saw it in the tutorial about creating a Postgres peer (i.e. CREATE PUBLICATION ...), I just missed the opportunity to look further into it and learn more about its properties. Now, I know it better than before and even I found out the answer to another question of mine: we can replicate only a selected set of tables, not necessarily all of them, by limiting the PUBLICATION to those tables.

Thanks again @ilidemi !