Hello,
I am using Vertica 9.1 with KSAFE(1).
I have a large table that I want to partition by date (I am using hierarchical partitioning for timestamptz column):
PARTITION BY ((my_table.my_timestamptz_column AT TIME ZONE 'UTC')::date)
GROUP BY (
CASE WHEN ("datediff"('year', (my_table.my_timestamptz_column AT TIME ZONE 'UTC')::date, ((now() AT TIME ZONE 'UTC')::timestamptz(6))::date) >= 2)
THEN (date_trunc('year', (my_table.my_timestamptz_column AT TIME ZONE 'UTC')::date))::date
WHEN ("datediff"('month', (my_table.my_timestamptz_column AT TIME ZONE 'UTC')::date, ((now() AT TIME ZONE 'UTC')::timestamptz(6))::date) >= 2)
THEN (date_trunc('month', (my_table.my_timestamptz_column AT TIME ZONE 'UTC')::date))::date
ELSE (my_table.my_timestamptz_column AT TIME ZONE 'UTC')::date END)
REORGANIZE;
For each partition and each projection (my_projection_b0 and my_projection_b1) Vertica creates 4 ROS conatiners per node. As I understand from documentation, it is supposed to be 1 ROS container per node per partition per projection (after tuple mover handles any inserted/updated data). I have the number which is 4 times higher then expected. I check it with the query:
SELECT COUNT (DISTINCT ros_id) NumROS, node_name FROM PARTITIONS
WHERE projection_name ilike '%my_projection%'
GROUP BY node_name
ORDER BY node_name;
In this case I get 8 ROS per partition per node (but I think this is because of KSAFE(1) which gives my_projection_b0 and my_projection_b1), which is fine. But why 4?
Thank you.
