Partition groups consolidate partitions into logical subsets that minimize use of ROS storage. Reducing the number of ROS containers to store partitioned data helps facilitate DML operations such as
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 merges individual partitions into these ROS containers accordingly.
Creating Partition Groups
You create partition groups by qualifying the
PARTITION BY clause with a
GROUP BY clause:
ALTER TABLE table-name PARTITION BY partition‑expression [ GROUP BY group‑expression ]
GROUP BY clause specifies how to consolidate partition keys into groups, where each group is identified by a unique partition group key. For example, the following
ALTER TABLE statement specifies to repartition the
store_orders table (shown in Partitioning a New Table) by order dates, grouping partition keys by year. The group expression—
DATE_TRUNC('year', (order_date)::DATE)—uses the partition expression
order_date::DATE to generate partition group keys:
=> ALTER TABLE store_orders PARTITION BY order_date::DATE GROUP BY DATE_TRUNC('year', (order_date)::DATE) REORGANIZE; NOTICE 8364: The new partitioning scheme will produce partitions in 4 physical storage containers per projection NOTICE 4785: Started background repartition table task
In this case, the
order_date column dates span four years. The Tuple Mover creates four partition group keys, and merges
store_orders partitions into group-specific ROS storage containers accordingly:
=> SELECT DUMP_TABLE_PARTITION_KEYS('store_orders'); ... Partition keys on node v_vmart_node0001 Projection 'store_orders_unseg_super' Storage [ROS container] No of partition keys: 173 Partition keys: 2017-01-02 2017-01-03 2017-01-04 ... 2017-09-25 2017-09-26 2017-09-27 Storage [ROS container] No of partition keys: 212 Partition keys: 2016-01-01 2016-01-04 2016-01-05 ... 2016-11-23 2016-11-24 2016-11-25 Storage [ROS container] No of partition keys: 213 Partition keys: 2015-01-01 2015-01-02 2015-01-05 ... 2015-11-23 2015-11-24 2015-11-25 2015-11-26 2015-11-27 Storage [ROS container] No of partition keys: 211 Partition keys: 2014-01-01 2014-01-02 2014-01-03 ... 2014-11-25 2014-11-26 2014-11-27 Projection 'store_orders_unseg_super' Storage [ROS container] No of partition keys: 173 ...
This example demonstrates how partition grouping can facilitate more efficient use of ROS storage. However, grouping all partitions into several large and static ROS containers can adversely affect performance, especially for a table that is subject to frequent DML operations. Frequent load operations in particular can incur considerable merge overhead, which, in turn, reduces performance.
Vertica recommends that you use
CALENDAR_HIERARCHY_DAY, as a partition clause's group expression. This function automatically groups
DATE partition keys into a dynamic hierarchy of years, months, and days. Doing so helps minimize merge-related issues. For details, see Hierarchical Partitioning.
Managing Partitions Within Groups
You can use various partition management functions, such as
MOVE_PARTITIONS_TO_TABLE, to target a range of order dates within a given partition group, or across multiple partition groups. In the previous example, each group contains partition keys of different dates within a given year. You can use
DROP_PARTITIONS to drop order dates that span two years, 2014 and 2015:
=> SELECT DROP_PARTITIONS('store_orders', '2014-05-30', '2015-01-15', 'true');
The drop operation requires Vertica to split the ROS containers that store partition groups for these two years. To do so, the function's
force_split parameter must be set to true.