Hierarchical Partitioning
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] ] )
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 andCALENDAR_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
evaluatesorder_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 theorder_date
day. This row is merged into a ROS container for that day. Any rows whereorder_date
is a future date is treated in the same way.
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.
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)
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:
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);