Partition Grouping
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 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 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 ]
The 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 DROP_PARTITIONS
or 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.