Could any one shed some light on why buddy projection with different sort order is deprecated in 7.2?
What are the better alternatives?
I can't speak for why the decision to deprecate this was made, but...
Buddy projections with different sort orders are problematic when a node is down. Performance will degrade because the data from the buddy needs to be sorted to match the data from the other nodes.
Buddy projections with a different sort order will also drastically increase recovery time. When the buddy has the same sort order, data recovered from the buddy can be a binary copy of the ROS containers. If the buddy has a different definition, then the data needs to sorted during recovery.
If you need both projection definitions to optimize your workload, then create two pairs of projections.
I have worked with a couple of customers who use buddy projections with different sort orders and can justify it because they also have mirror clusters that they switch to at any sign of trouble. So they never run in production with a node down, and recovery time doesn't matter so much because it's the offline cluster that's recovering. These folks might not be happy if this change goes from deprecated to a state where Vertica actually prevents the different definitions.
Thank you Sharon. This helps.