If you need to query the size of tables in Vertica, then you have several ways to do it. I will demonstrate two options. The first is based on projection storage, while the second is based on columns storage. Both of them aggregate the data based on table schema and table name.
Here is the first query, based on v_monitor.projection_storage:
Transact-SQL
SELECT anchor_table_schema , anchor_table_name , SUM (used_bytes) / (1024^3) AS used_gb FROM v_monitor.projection_storage GROUP BY anchor_table_schema , anchor_table_name ORDER BY SUM (used_bytes) DESC;
1
2
3
4
5
6
7
8
9
10
11
SELECT
anchor_table_schema ,
anchor_table_name ,
SUM (used_bytes) / (1024^3) AS used_gb
FROM
v_monitor.projection_storage
GROUP BY
anchor_table_schema ,
anchor_table_name
ORDER BY
SUM (used_bytes) DESC;
And here is the second query, based on v_monitor.column_storage:
Transact-SQL
SELECT anchor_table_schema , anchor_table_name , SUM (used_bytes) / (1024^3) AS used_gb FROM v_monitor.column_storage GROUP BY anchor_table_schema , anchor_table_name ORDER BY SUM (used_bytes) DESC;
1
2
3
4
5
6
7
8
9
10
11
SELECT
anchor_table_schema ,
anchor_table_name ,
SUM (used_bytes) / (1024^3) AS used_gb
FROM
v_monitor.column_storage
GROUP BY
anchor_table_schema ,
anchor_table_name
ORDER BY
SUM (used_bytes) DESC;
A few comments:
Both queries return the size of tables in GB.
Both queries return the compressed size of the tables.
The second query will also return empty tables, while the first query will not.
Comments