Setting the Table-Level Active Partition Count

Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

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!

Sign In or Register to comment.