Vertica table size !!!!

[adrian.oprea]

Hy all , is there a way to see a single table size in vertica ??????????

- thank you guys !!

[Josh]

Hey Adrian,

I've been using the projection_storage table. You have to add up the storage for all the projections based on the table.

i.e.

Code: Select all

dbadmin=> select node_name,
dbadmin->        sum(used_bytes)
dbadmin->   from projection_storage
dbadmin->  where anchor_table_name = 'D_Department'
dbadmin->    and anchor_table_schema = 'public
dbadmin->  group
dbadmin->     by node_name;
  node_name       |  sum
------------------+-------
 v_vmain_node0001 | 67087
 v_vmain_node0002 | 67087
 v_vmain_node0003 | 67087
(3 rows)

[adrian.oprea]

Thank you for the answer!!
I have no projections made on the table , i am making a benchmarking report for a future client so i need to know what vertica will bring to the table as space savings.
- but this i tink will do it !!
Thanks !!
-if thate are more ideas out there please post them !!

[id10t]

I have no projections made on the table

You have not, but Vertica did (see Concept Guide)! All table have at least one projection - a super projection. It's done for data integrity. Check size of super projection.

-if thate are more ideas out there please post them !!

Josh provided to you a simple and good solution (may be a best) - what you didn't like in it?
Also you can sum each column size for table.

Code: Select all

select sum( used_bytes ) from column_storage where anchor_table_name = 'aTableName';

[adrian.oprea]

Josh solution was great did the job .
Thank you guys !!!

[adrian.oprea]

Hy all ,
After taking a look at the "column_storage" table i have made my table size checking scripts
Take a look and tell me if this is accurate ?!!
In MB

Code: Select all

select sum(ros_used_bytes)/1024/1024  as "Table_Size in MB"from column_storage where anchor_table_name = 'table_name';

In Gb

Code: Select all

select sum(ros_used_bytes)/1024^3  as "Table_Size in Gb"from column_storage where anchor_table_name = 'table_name';

- Good to remember that this search is on a single node !! to use it on more then one node just add the " and node_name='XXXX' group by node_name;"

[id10t]

Hi Adrian!
Hmmm... You count ROS containers, while you want a table size.
So answer: no - solution do not match the problem.