• Eitan Blumin

How to Get the Size of Tables in Vertica

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:

  1. Both queries return the size of tables in GB.

  2. Both queries return the compressed size of the tables.

  3. The second query will also return empty tables, while the first query will not.

#tablesize #bigdata #query #DataWarehouse #Vertica #script #scripts

CONTACT US

 3 Rapaport St. Kfar Saba, Israel

JOIN OUR MAILING LIST

 +972-9-7400101

  • Google+ - White Circle
  • Facebook - White Circle
  • Twitter - White Circle
  • LinkedIn - White Circle