Setting the Table-Level Active Partition Count
Jim_Knicely
- Select Field - Administrator
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=> SELECT get_config_parameter('ActivePartitionCount');
get_config_parameter
----------------------
1
(1 row)
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 with the following SQL statement:
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 Links:
https://my.vertica.com/docs/9.1.x/HTML/index.htm#Authoring/AdministratorsGuide/Partitions/ActivePartitions.htm
Have fun!