Hierarchical partitioning

The meta-function CALENDAR_HIERARCHY_DAY leverages partition grouping.

The meta-function CALENDAR_HIERARCHY_DAY leverages partition grouping. You specify this function as the partitioning GROUP BY expression. CALENDAR_HIERARCHY_DAY 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 remain ungrouped. Grouping is dynamic: as recent data ages, the Tuple Mover merges their partitions into month groups, and eventually into year groups.

Managing timestamped data

Partition consolidation strategies are especially important for managing timestamped data, where the number of partitions can quickly escalate and risk ROS pushback. For example, the following statements create the store_orders table and load data into it. The CREATE TABLE statement includes a simple partition clause that specifies to partition data by date:

=> DROP TABLE IF EXISTS public.store_orders CASCADE;
=> CREATE TABLE public.store_orders
(
    order_no int,
    order_date timestamp NOT NULL,
    shipper varchar(20),
    ship_date date
)
UNSEGMENTED ALL NODES PARTITION BY order_date::DATE;
CREATE TABLE
=> COPY store_orders FROM '/home/dbadmin/export_store_orders_data.txt';
41834
(1 row)

As COPY loads the new table data into ROS storage, it executes this table's partition clause by dividing daily orders into separate partitions—in this case, 809 partitions, where each partition requires its own ROS container:

=> SELECT COUNT (DISTINCT ros_id) NumROS, node_name FROM PARTITIONS
    WHERE projection_name ilike '%store_orders_super%' GROUP BY node_name ORDER BY node_name;
 NumROS |    node_name
--------+------------------
    809 | v_vmart_node0001
    809 | v_vmart_node0002
    809 | v_vmart_node0003
(3 rows)

This is far above the recommended maximum of 50 partitions per projection. This number is also close to the default system limit of 1024 ROS containers per projection, risking ROS pushback in the near future.

You can approach this problem in several ways:

Consider consolidating table data into larger partitions—for example, partition by month instead of day. However, partitioning data at this level might limit effective use of partition management functions.
Regularly archive older partitions, and thereby minimize the number of accumulated partitions. However, this requires an extra layer of data management, and also inhibits access to historical data.

Alternatively, you can use CALENDAR_HIERARCHY_DAY to automatically merge partitions into a date-based hierarchy of partition groups. Each partition group is stored in its own set of ROS containers, apart from other groups. You specify this function in the table partition clause as follows:

PARTITION BY partition-expression
  GROUP BY CALENDAR_HIERARCHY_DAY( `*`partition-expression`*` [, active-months[, active-years] ] )

Important

Two requirements apply to using CALENDAR_HIERARCHY_DAY in a partition clause:

partition-expression must be a DATE.
The partition expressions specified by the PARTITION BY clause and CALENDAR_HIERARCHY_DAY must be identical.

For example, given the previous table, you can repartition it as follows:


=> ALTER TABLE public.store_orders
      PARTITION BY order_date::DATE
      GROUP BY CALENDAR_HIERARCHY_DAY(order_date::DATE, 2, 2) REORGANIZE;

Grouping DATE data hierarchically

CALENDAR_HIERARCHY_DAY creates hierarchies of partition groups, and merges partitions into the appropriate groups. It does so by evaluating the partition expression of each table row with the following algorithm, to determine its partition group key:

GROUP BY (
CASE WHEN DATEDIFF('YEAR', partition-expression, NOW()::TIMESTAMPTZ(6)) >= active-years
       THEN DATE_TRUNC('YEAR', partition-expression::DATE)
     WHEN DATEDIFF('MONTH', partition-expression, NOW()::TIMESTAMPTZ(6)) >= active-months
       THEN DATE_TRUNC('MONTH', partition-expression::DATE)
     ELSE DATE_TRUNC('DAY', partition-expression::DATE) END);

In this example, the algorithm compares order_date in each store_orders row to the current date as follows:

Determines whether order_date is in an inactive year.

If order_date is in an inactive year, the row's partition group key resolves to that year. The row is merged into a ROS container for that year.
If order_date is an active year, CALENDAR_HIERARCHY_DAY evaluates order_date to determine whether it is in an inactive month.

If order_date is in an inactive month, the row's partition group key resolves to that month. The row is merged into a ROS container for that month.
If order_date is in an active month, the row's partition group key resolves to the order_date day. This row is merged into a ROS container for that day. Any rows where order_date is a future date is treated in the same way.

Important

The CALENDAR_HIERARCHY_DAY algorithm assumes that most table activity is focused on recent dates. Setting active-years and active-months to a low number ≥ 2 serves to isolate most merge activity to date-specific containers, and incurs minimal overhead. Vertica recommends that you use the default setting of 2 for active-years and active-months. For most users, these settings achieve an optimal balance between ROS storage and performance.

As a best practice, never set active-years and active-months to 0.

For example, if the current date is 2017-09-26, CALENDAR_HIERARCHY_DAY resolves active-years and active-months to the following date spans:

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

Now, the total number of ROS containers is reduced to 40 per projection:

=> SELECT COUNT (DISTINCT ros_id) NumROS, node_name FROM PARTITIONS
    WHERE projection_name ilike '%store_orders_super%' GROUP BY node_name ORDER BY node_name;
 NumROS |    node_name
--------+------------------
     40 | v_vmart_node0001
     40 | v_vmart_node0002
     40 | v_vmart_node0003
(3 rows)

Note

Regardless of how the Tuple Mover groups and merges partitions, it always identifies one or more partitions or partition groups as active. For details, see Active and inactive partitions.

Dynamic regrouping

As shown earlier, CALENDAR_HIERARCHY_DAY references the current date when it creates partition group keys and merges partitions. As the calendar advances, the Tuple Mover reevaluates the partition group keys of tables that are partitioned with this function, 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 merged into 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 merged into the new yearly ROS container:

Caution

After older partitions are grouped into months and years, any partition operation that acts on a subset of older partition groups is liable to split ROS containers into smaller ROS containers for each partition—for example, MOVE_PARTITIONS_TO_TABLE, where force-split is set to true. These operations can lead to ROS pushback. If you anticipate frequent partition operations on hierarchically grouped partitions, consider modifying the partition expression so partitions are grouped no higher than months.

Customizing partition group hierarchies

Vertica provides a single function, CALENDAR_HIERARCHY_DAY, to facilitate hierarchical partitioning. Vertica stores the GROUP BY clause as a CASE statement that you can edit to suit your own requirements.

For example, Vertica stores the store_orders partition clause as follows:

=> ALTER TABLE public.store_orders
      PARTITION BY order_date::DATE
      GROUP BY CALENDAR_HIERARCHY_DAY(order_date::DATE, 2, 2);
=> select export_tables('','store_orders');
...
CREATE TABLE public.store_orders ( ... )

PARTITION BY ((store_orders.order_date)::date)
GROUP BY (
CASE WHEN ("datediff"('year', (store_orders.order_date)::date, ((now())::timestamptz(6))::date) >= 2)
       THEN (date_trunc('year', (store_orders.order_date)::date))::date
     WHEN ("datediff"('month', (store_orders.order_date)::date, ((now())::timestamptz(6))::date) >= 2)
       THEN (date_trunc('month', (store_orders.order_date)::date))::date
     ELSE (store_orders.order_date)::date END);

You can modify the CASE statement to customize the hierarchy of partition groups. For example, the following CASE statement creates a hierarchy of months, days, and hours:

=> ALTER TABLE store_orders
PARTITION BY (store_orders.order_date)
GROUP BY (
CASE WHEN DATEDIFF('MONTH', store_orders.order_date, NOW()::TIMESTAMPTZ(6)) >= 2
       THEN DATE_TRUNC('MONTH', store_orders.order_date::DATE)
     WHEN DATEDIFF('DAY', store_orders.order_date, NOW()::TIMESTAMPTZ(6)) >= 2
       THEN DATE_TRUNC('DAY', store_orders.order_date::DATE)
     ELSE DATE_TRUNC('hour', store_orders.order_date::DATE) END);