Designing projections to support fast querying without hurting delete performance
It seems that in order to support reasonable deletes/updates, all projections need to be sorted by the predicate of the delete operation, which is the highest cardinality column and usually an id. However, for efficient querying, it is recommended to place low cardinality columns ahead of the higher cardinality columns in the sort order. We have a number of well performing projections designed to improve query performance, which are doing great for our queries but killing our delete operations.
We were thinking of creating separate tables- 1 for etl with projection sorted by pk, and 1 for querying with projections sorted by query criteria, but then how to move data from one to the other- the query projections will still slow down the delete. Dropping partitions is not really an option because our daily updates are not limited to any specific date range, and include a random set of historical updates.
What are the recommendations for doing this?
Thank you.
We were thinking of creating separate tables- 1 for etl with projection sorted by pk, and 1 for querying with projections sorted by query criteria, but then how to move data from one to the other- the query projections will still slow down the delete. Dropping partitions is not really an option because our daily updates are not limited to any specific date range, and include a random set of historical updates.
What are the recommendations for doing this?
Thank you.
0