Partitioning tables in Vertica ?.

Answers

• Options

  Sudhakar_B Vertica Customer ✭✭

  January 2021 edited January 2021

  @jackyjoy123
  Before we go into partitioning tables in Vertica, can you please provide some additional information on your Vertica cluster and query performance problem you are trying to solve.
  It is true that partition pruning will improve query-performance, in Vertica I would use table-partitioning as one of the last resort. Too many partition will result in ROS pushback. Recommended number of partitions is around ~40. For data-management partitioning is helpful.
  For time-stamp based data, I have found Hierarchical partitioning most effective.
  https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/Partitions/HierarchicalPartitioning.htm

  Normally you don't want to use primary key columns for partitioning but for segmentation.
  https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/Partitions/PartitioningAndSegmentingData.htm

  1. You said the table has few-million rows and expected to grow fast. 10 million, 50 million, 500 million? What is the expected growth rate?
  2. How many nodes in your cluster? What is the approximate size of each node # of core, and RAM?
  3. What is the version of Vertica you are using?

  Typically 90% of query performance SLA in Vertica can be met by designing proper "projections (segmentation and ordering)" on the table.
  Some of the following links will be helpful.
  You can use DBD to recommend comprehensive and/or query specific projections.
  https://www.vertica.com/blog/query-tuning-vertica-dos-donts/
  https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AnalyzingData/Optimizations/OptimizingQueryPerformance.htm?TocPath=Analyzing Data|Query Optimization|_____0
  https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AnalyzingData/Optimizations/InitialProcessForImprovingQueryPerformance.htm?TocPath=Analyzing Data|Query Optimization|_____1

  Furthermore, Vertica performs Container pruning even on non-partitioned tables when appropriate.
  https://forum.vertica.com/discussion/240698/storage-container-optimization-for-tables-not-partitioned
  Hope this helps.

  0
• Options

  Nimmi_gupta - Select Field - Employee

  January 2021

  @jackyjoy123
  Can I use primary key field for partitioning tables in Vertica?

  You should choose a partitioning expression based on time (date field).

  For instance, if you have a table Orders with the field OrderDate you would most likely partition based on the month and year of OrderDate. When records age out and are no longer relevant you can move those partitions off to an archive table or database so they are no longer processed.
  Partitioning will work with pretty much any field, but in order for it to work WELL the field(s) you partition on should be used in most, if not all, of your queries. If you don't include your partition keys then you will get essentially an expensive table scan that goes across multiple tables (partitions).
  You can also check below link, optimizing vertica data with partitions and it has very good explanation about partition with an example.
  https://www.vertica.com/blog/optimizing-vertica-data-with-partitions/

  0
• Options

  marcothesane - Select Field - Administrator

  February 2022

  You can easily combine partitioning by the element of time (at a reasonable granularity), even if the element of time is a constituent of the primary key, and segmenting by the primary key itself.

  Here is a typical Slowly Changing Dimension (SCD) design that I use:

  CREATE TABLE scd.d_custym_scd (
    cust_key     INT NOT NULL
      DEFAULT HASH(cust_id,cust_from_dt)
  , cust_id      INT NOT NULL
  , cust_from_dt DATE NOT NULL
  , cust_to_dt   DATE NOT NULL
  , cust_is_hist BOOLEAN NOT NULL
  , cust_ins_ts  TIMESTAMP NOT NULL
  , cust_upd_ts  TIMESTAMP NOT NULL
  , cust_fname   VARCHAR(30)
  , cust_lname   VARCHAR(30)
  , cust_phoneno VARCHAR(30)
  , cust_loy_lvl INT
  , cust_org_id  INT
  , CONSTRAINT d_cust_scd_pk PRIMARY KEY(cust_key)
  )
  SEGMENTED BY HASH(cust_key) ALL NODES
  PARTITION BY YEAR(cust_from_dt)*100+MONTH(cust_from_dt)
  ;
  

  SCD tables have two ways of being uniquely identified:

  • the natural/business identifier and the valid-from date/timestamp - constraint would be UNIQUE -
  • the single-column integer surrogate key - this one's constraint is then the PRIMARY KEY

  If you don't use the surrogate key approach, nothing would speak against a design like this:

  CREATE TABLE l_cust (
    cust_id      INT NOT NULL
  , cust_from_dt DATE NOT NULL
  , cust_to_dt   DATE NOT NULL
  , cust_is_hist BOOLEAN NOT NULL
  , cust_ins_ts  TIMESTAMP NOT NULL
  , cust_upd_ts  TIMESTAMP NOT NULL
  , cust_fname   VARCHAR(30)
  , cust_lname   VARCHAR(30)
  , cust_phoneno VARCHAR(30)
  , cust_loy_lvl INT
  , cust_org_id  INT
  , CONSTRAINT l_cust_pk PRIMARY KEY(cust_id,cust_from_dt)
  )
  SEGMENTED BY HASH(cust_id) ALL NODES
  PARTITION BY YEAR(cust_from_dt)*100+MONTH(cust_from_dt)
  ;
  

  And remenber: if you really segment by the primary key, any query filtering by the primary key will prune all nodes except the one where your PK value resides from participating in the query. In a 12-node cluster, eleven nodes will turn their thumbs while one does all the work.

  0