Multi-customer schema design

Hi , As part of our multi-talented schema design (for SASS project) we check the option to store data of different customers on the same column tables , the data is mainly related to performance metrics , so one of our tables will looks like : Table ======== Customer_id , Key1 , key2 ,Key3 , Metric1, Metric2,Metric2 Query ======== Select Metric1, Metric2 From Table Where Customer_id =123 And Key1=’A’ and key2 =’B’ and Key3=’C’ Assuming the query’s we run do not filter the Metrics Values only the Key’s (As above) , I have a performance concern regarding column reconstruct extra I/O, assuming vertica implement late materialize , vertica will have the read the entire column data of Metric1, Metric2 to bring the positions columns and then be merge with potions of Customer_id, Key1 , key2 ,Key3 .So in fact we will read matric's data of diffrent customers , If this true fact , we may consider to build different multi-talented schema . Please advice


  • Late materialization is a tactic that Vertica may use to reduce the IO overhead for data (ccolumns) which is part of a result set but not part of any expressions or predicates. It allows it to avoid reading parts of column files which might be thrown away. In your case, only the portions of the metric column files that are relevant will be read. Vertica will use late materialization to avoid reading those portions which won't be in the result set. If you've created your projection with the customer ID as the first column in the sort order, Vertica will be very efficient at reading only the portions of data for the customer ID specified.
  • Hi thanks for replay , just to fully understend a col on the result set which do not have a filter on it , in order to get only relevant record vertica has to read it col postion somehow , can you explain how it being done ? , in addtion how column postion is being found in case of joins
  • The details of the implementation are specific to Vertica. We're hiring, though!

Leave a Comment

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