Setting the Table-Level Active Partition Count: Quick Tip

Jim Knicely authored this tip. The Tuple Mover assumes that all loads and updates to a partitioned table are targeted to one or more partitions that it identifies as active. In general, the partitions with the largest partition keys—typically, the most recently created partitions—are regarded as active. As the partition ages, it commonly transitions to a mostly read-only workload and requires much less activity. The configuration parameter ActivePartitionCount determines how many partitions are active for partitioned tables in the database. By default, ActivePartitionCount is set to 1. As of Vertica 9.1.1, you can override ActivePartitionCount by setting a Table-Level Active Partition Count! Example: dbadmin=> CREATE TABLE partition_example (birth_year INT NOT NULL) UNSEGMENTED ALL NODES; CREATE TABLE dbadmin=> ALTER TABLE partition_example PARTITION BY birth_year; ALTER TABLE dbadmin=> INSERT /+ DIRECT / INTO partition_example SELECT 1969; COMMIT; OUTPUT ——– 1 (1 row) COMMIT dbadmin=> INSERT /+ DIRECT / INTO partition_example SELECT 2001; COMMIT; OUTPUT ——– 1 (1 row) COMMIT dbadmin=> INSERT /+ DIRECT / INTO partition_example SELECT 1999; COMMIT; OUTPUT ——– 1 (1 row) COMMIT I can identify the Active Partitions like this: dbadmin=> SELECT p.node_name, p.projection_name, p.partition_key, p.ros_id, p.ros_size_bytes, p.ros_row_count, ROS_container_count dbadmin-> FROM projections pr dbadmin-> JOIN partitions p dbadmin-> ON p.projection_id = pr.projection_id dbadmin-> AND p.node_name = pr.node_name dbadmin-> JOIN strata s dbadmin-> ON p.partition_key = s.stratum_key dbadmin-> AND p.node_name = s.node_name dbadmin-> AND p.projection_id = s.projection_id dbadmin-> WHERE pr.anchor_table_name = 'partition_example' dbadmin-> ORDER BY p.node_name, p.partition_key; node_name | projection_name | partition_key | ros_id | ros_size_bytes | ros_row_count | ROS_container_count ——————+————————-+—————+——————-+—————-+—————+——————— v_vmart_node0001 | partition_example_super | 1999 | 45035996296702249 | 52 | 1 | 1 v_vmart_node0002 | partition_example_super | 1999 | 49539595924019621 | 52 | 1 | 1 v_vmart_node0003 | partition_example_super | 1999 | 54043195551436797 | 52 | 1 | 1 (3 rows) Now I’ll alter the table so that it has 2 Active Partitions instead of the default of 1: dbadmin=> ALTER TABLE partition_example SET ACTIVEPARTITIONCOUNT 2; ALTER TABLE dbadmin=> SELECT p.node_name, p.projection_name, p.partition_key, p.ros_id, p.ros_size_bytes, p.ros_row_count, ROS_container_count dbadmin-> FROM projections pr dbadmin-> JOIN partitions p dbadmin-> ON p.projection_id = pr.projection_id dbadmin-> AND p.node_name = pr.node_name dbadmin-> JOIN strata s dbadmin-> ON p.partition_key = s.stratum_key dbadmin-> AND p.node_name = s.node_name dbadmin-> AND p.projection_id = s.projection_id dbadmin-> WHERE pr.anchor_table_name = ‘partition_example’ dbadmin-> ORDER BY p.node_name, p.partition_key; node_name | projection_name | partition_key | ros_id | ros_size_bytes | ros_row_count | ROS_container_count ——————+————————-+—————+——————-+—————-+—————+——————— v_vmart_node0001 | partition_example_super | 1999 | 45035996296702249 | 52 | 1 | 1 v_vmart_node0001 | partition_example_super | 2001 | 45035996296702233 | 52 | 1 | 1 v_vmart_node0002 | partition_example_super | 1999 | 49539595924019621 | 52 | 1 | 1 v_vmart_node0002 | partition_example_super | 2001 | 49539595924019615 | 52 | 1 | 1 v_vmart_node0003 | partition_example_super | 1999 | 54043195551436797 | 52 | 1 | 1 v_vmart_node0003 | partition_example_super | 2001 | 54043195551436791 | 52 | 1 | 1 (6 rows) Helpful link: https://my.vertica.com/docs/latest/HTML/index.htm#Authoring/AdministratorsGuide/Partitions/ActivePartitions.htm Have fun!