What’s New in Vertica 9.0: Hierarchical Partitioning

Posted November 20, 2017 by Soniya Shah, Information Developer

This blog post was authored by Michael Kronenberg.

With Vertica 9.0, you can consolidate partitions into groups that minimize use of ROS storage. Reducing the number of ROS containers to store partitioned data helps facilitate DML operations such as DELETE and UPDATE, and avoid ROS pushback. For example, you can group date partitions by year. By doing so, the Tuple Mover allocates ROS containers for each year group, and groups individual partitions in these ROS containers accordingly.

Partition consolidation strategies are especially important for managing timestamped data, where the number of partitions can quickly escalate and risk ROS pushback. Recognizing that, Vertica 9.0 leverages group partitioning with the meta-function CALENDAR_HIERARCHY_DAY. This function organizes a table’s date partitions into a hierarchy of groups: the oldest date partitions are grouped by year, more recent partitions are grouped by month, and the most recent date partitions are grouped by day. Grouping is dynamic: as recent data ages, the Tuple Mover groups day partitions into month groups, and eventually into year groups.


The following CREATE TABLE statement includes a partition clause that uses CALENDAR_HIERARCHY_DAY: => CREATE TABLE public.store_orders ( order_no int, order_date timestamp NOT NULL, shipper varchar(20), ship_date date ) PARTITION BY order_date::DATE GROUP BY CALENDAR_HIERARCHY_DAY(order_date::DATE, 2, 2); In this case the function specifies arguments of two active years and two active months. If the current date is 2017 09-26, CALENDAR_HIERARCHY_DAY resolves active years and active months to the following date spans:

• Active months: 2017-08-01 to 2017-09-30. Partitions in active months are grouped into daily ROS containers.

• Active years: 2016-01-01 to 2017-12-31. Partitions in active years are grouped into monthly ROS containers or are grouped into daily ROS containers. Partitions from earlier years are regarded as inactive and grouped into yearly ROS containers.

As the calendar advances, the Tuple Mover reevaluates the partition group keys, and moves partitions as needed to different ROS containers.

Thus, given the previous example, on 2017-10-01 the Tuple Mover creates a monthly ROS container for August partitions. All partition keys between 2017-08-01 and 2017-08-31 are grouped in the new ROS container 2017-08:

Likewise, on 2018-01-01, the Tuple Mover creates a ROS container for 2016 partitions. All partition keys between 2016-01-01 and 2016-12-31 that were previously grouped by month are grouped in the new yearly ROS container:

For More Information

See Hierarchical Partitioning in the Vertica documentation.