Vertica In-active partitions

JunaidKhan

Dear's,
i have partitioned table contineouly feeding data how would i know the active partition or those which are in-active any more, for some reason i can see the very old partition key in my table projection i can tell these can be created by the wrong dattimestamp filed (originaleventtime),
v_my_node0001 201804 0 1
v_my_node0001 206004 0 8
v_my_node0002 201808 0 1
v_my_node0002 201804 0 1
v_my_node0003 201904 0 2
v_my_node0003 201808 0 1
if these are in-active i have perform the below step to make the partiotion active and correct but i am not sure if this can activiate or create new active partition.
ALTER DATABASE mydb SET ACTIVEPARTITIONCOUNT=2;
Br,

Bryan_H

It is not always necessary to increase active partition count. Older partitions are not explicitly read-only, but Tuple Mover will work on inactive partitions with lower priority. The following query will find which partitions are "active" in the sense that they are being processed as "strata" in Tuple Mover:

SELECT p.node_name, p.partition_key, p.ros_id, p.ros_size_bytes, p.ros_row_count, ROS_container_count
FROM partitions p JOIN strata s ON p.partition_key = s.stratum_key AND p.node_name=s.node_name
WHERE p.projection_name = '<your_projection_name>' ORDER BY p.node_name, p.partition_key;

It will help to know what version of Vertica you are using, because Tuple Mover behavior changed a bit between major versions (7.x, 8.x, 9.x, 10.x)

JunaidKhan

Vertica version is 8.x

Bryan_H

I'm not sure I understand the issue. Even data loaded into inactive partitions is available for query and will be processed through strata eventually. It is just not fully optimized at first. In Vertica 8.x, you can assign more resources to TM resource pool, then run SELECT DO_TM_TASK('mergeout'); and TM will start to merge out inactive partition data though it will take longer to process and the manual mergeout may take a noticeable amount of resources causing other jobs and queries to slow.