Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Determining the Per-Node Storage of a Segmented Table

SarahLSarahL Administrator
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:


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.