The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Determining the Per-Node Storage of a Segmented Table

edited July 2018 in Tips from the Team

Jim Knicely authored this blog.

You can join a few system tables together to find the disk space used and the row counts by node for segmented tables:

Example:

dbadmin=> SELECT ps.anchor_table_schema,
dbadmin->        ps.anchor_table_name,
dbadmin->        ps.node_name,
dbadmin->        ROUND(SUM(ps.ros_used_bytes)/1024/1024/1024, 5)::NUMERIC(25,5) AS GB,
dbadmin->        SUM(ps.ros_row_count) AS Rows
dbadmin->   FROM v_catalog.projections p
dbadmin->   JOIN v_monitor.projection_storage ps
dbadmin->  USING (projection_id)
dbadmin->  WHERE p.is_segmented
dbadmin->    AND ps.anchor_table_schema || '.' || ps.anchor_table_name = 'public' || '.' || 'big_table'
dbadmin->  GROUP BY 1, 2, 3
dbadmin->  ORDER BY 1, 2, 3;
 anchor_table_schema | anchor_table_name |     node_name      |   GB    |   Rows
---------------------+-------------------+--------------------+---------+-----------
 public              | big_table         | v_test_db_node0001 | 1.70385 | 810901653
 public              | big_table         | v_test_db_node0002 | 1.70385 | 810919791
 public              | big_table         | v_test_db_node0003 | 1.70395 | 810968034
(3 rows)

Skew in the data distribution across the nodes is an indicator of a poor choice of the table’s segmentation key.

Have fun!

Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.