Partition grouping

Partition groups consolidate partitions into logical subsets that minimize use of ROS storage.

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_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_super'
   Storage [ROS container]
     No of partition keys: 173
...

Caution

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');

Important

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.