Vertica store table data in compressed format. To get the size of a table in vertica, you can use the below query. By using column_storage and projection_storage system tables you will get the size of table compressed format. You can check the all the column definition from the official vertica sites using below link.
v_monitor.projection_storage
COLUMN DEFINITION:
———————————-
ANCHOR_TABLE_NAME: VARCHAR
The associated table name for which information is listed.
ANCHOR_TABLE_SCHEMA: VARCHAR
The associated table schema for which information is listed.
USED_BYTES: INTEGER
The number of bytes of disk storage used by the projection.
SELECT anchor_table_schema,
anchor_table_name,
SUM(used_bytes) / (1024/1024/1024/1024) AS TABLE_SIZE_GB
FROM v_monitor.projection_storage
GROUP BY anchor_table_schema,
anchor_table_name
order by sum(used_bytes) desc;
To find number of rows and bytes occupied by each table in the database
——————————————————————————————–
SELECT t.table_name AS table_name,
SUM(ps.wos_row_count + ps.ros_row_count) AS row_count,
SUM(ps.wos_used_bytes + ps.ros_used_bytes) AS byte_count
FROM tables t
JOIN projections p ON t.table_id = p.anchor_table_id
JOIN projection_storage ps on p.projection_name = ps.projection_name
WHERE (ps.wos_used_bytes + ps.ros_used_bytes) > 500000
--and t.table_name='table_name'
GROUP BY t.table_name
ORDER BY byte_count DESC;
To find the size of single table in the database:
———————————————————–
SELECT ANCHOR_TABLE_NAME,PROJECTION_SCHEMA,((SUM(USED_BYTES))/1024/1024/1024) AS TOTAL_SIZE FROM PROJECTION_STORAGE WHERE ANCHOR_TABLE_NAME ='TABLE_NAME' AND ANCHOR_TABLE_SCHEMA='TABLE_SCHEMA' AND PROJECTION_NAME like '&PROJECTION_NAME' GROUP BY PROJECTION_SCHEMA, ANCHOR_TABLE_NAME;
SELECT anchor_table_schema,
anchor_table_name,
SUM(used_bytes) / ( 1024/1024/1024 ) AS TABLE_SIZE_GB
FROM v_monitor.column_storage
GROUP BY anchor_table_schema,
anchor_table_name
order by sum(used_bytes) desc;