I'm planning on doing a table redesign in ClickHouse to avoid some performance and deduplication/versioning issues while using ReplicatedReplacingMergeTree. Here, OPTIMIZE FINAL can be run to remove duplicates but can't be run continuously.
So we are planning to keep a table A, partitioned daily, with incoming data. Then a table B, partitioned monthly, where historical data will be stored. Queries will be directed to a MERGE table (not MergeTree) that will contain both.
And then, we'd set up an action to move the latest partition, from A to B, every day right after the end of the day.
However, moving partition as in MOVE PARTITION TO TABLE where it is stated that "Both tables must have the same partition key.", it is not possible. An approach is to copy data from A's daily partition to B, and then detach/drop daily partition in A. However in the meantime we'll see duplicates.
What would be the best way to transfer this data from A to B with minimal impact? What other options would be?
