Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.