Determining the Per-Node Storage of a Segmented Table

[Deleted User] · July 2018

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!