Follow-on to #1949 (which added the ability to set the ordering key at mirror-create time). That covers new mirrors, but there's currently no supported path to change the ordering key of a destination table on an existing mirror.
Why this comes up: When the source Postgres table has no usable primary key, PeerDB falls back to an all-columns ordering key on the ClickHouse ReplacingMergeTree target. That has two consequences beyond performance:
- Since
ReplacingMergeTree derives its dedup identity from ORDER BY, an all-columns key means FINAL only collapses byte-identical rows — any re-delivered row with a changed field survives as a duplicate and inflates aggregates.
- The primary index bloats with wide string columns pulled into the key.
Realizing this only after data has been flowing is common, and at that point there's no in-place fix:
- ClickHouse forbids
ALTER TABLE ... MODIFY ORDER BY on existing (leading) key columns — it can only append newly-added columns.
- PeerDB's per-column
ordering is immutable after the table mapping is created, so the only workaround is dropping the table from the mirror and re-adding it with new ordering, then resyncing — undocumented as a "change the sort key" flow and easy to get wrong on replicated + Distributed setups.
Request: a first-class way to change a destination table's ordering key on an existing mirror — e.g. an edit-mapping path that accepts new ordering values and triggers a single-table resync that recreates the target (including the ReplicatedReplacingMergeTree local + Distributed wrapper) with the new ORDER BY. Exposing ordering in the edit UI (not just create) would cover most of this.
Environment: PeerDB v0.36.32, Postgres → ClickHouse mirror (ReplicatedReplacingMergeTree, 1 shard / 3 replicas).
Follow-on to #1949 (which added the ability to set the ordering key at mirror-create time). That covers new mirrors, but there's currently no supported path to change the ordering key of a destination table on an existing mirror.
Why this comes up: When the source Postgres table has no usable primary key, PeerDB falls back to an all-columns ordering key on the ClickHouse
ReplacingMergeTreetarget. That has two consequences beyond performance:ReplacingMergeTreederives its dedup identity fromORDER BY, an all-columns key meansFINALonly collapses byte-identical rows — any re-delivered row with a changed field survives as a duplicate and inflates aggregates.Realizing this only after data has been flowing is common, and at that point there's no in-place fix:
ALTER TABLE ... MODIFY ORDER BYon existing (leading) key columns — it can only append newly-added columns.orderingis immutable after the table mapping is created, so the only workaround is dropping the table from the mirror and re-adding it with newordering, then resyncing — undocumented as a "change the sort key" flow and easy to get wrong on replicated + Distributed setups.Request: a first-class way to change a destination table's ordering key on an existing mirror — e.g. an edit-mapping path that accepts new
orderingvalues and triggers a single-table resync that recreates the target (including theReplicatedReplacingMergeTreelocal +Distributedwrapper) with the newORDER BY. Exposingorderingin the edit UI (not just create) would cover most of this.Environment: PeerDB v0.36.32, Postgres → ClickHouse mirror (
ReplicatedReplacingMergeTree, 1 shard / 3 replicas).