Estimating Size of a table

Moderator: NorbertKrupa

Post Reply
User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

Estimating Size of a table

Post by nnani » Fri Sep 20, 2013 8:09 am

Hello All,

Just thought of sharing this with all.
This can be handy SQL snippet in projects

Suppose you want to calculate the individual table size of any table in your database.
You can use the COLUMN_STORAGE system table for calculating the size.

Lets see and understand this step by step

COLUMN_STORAGE table shows the space in bytes used for every column in database.
so Let so go ahead querying a table

Code: Select all

nnani=> select anchor_table_schema, anchor_table_name,node_name, column_name, used_bytes as column_used_bytes
nnani-> from column_storage where anchor_table_schema='DV0' and anchor_table_name='feed_prvdr' and
nnani-> node_name ='vnode0002';
 anchor_table_schema | anchor_table_name |    node_name    |   column_name   | column_used_bytes
---------------------+-------------------+-----------------+-----------------+-------------------
 DV0         | feed_prvdr  | vnode0002 | prvdr_id   |                 6
 DV0         | feed_prvdr  | vnode0002 | prvdr_nm   |                65
 DV0         | feed_prvdr  | vnode0002 | prvdr_desc |               120
 DV0         | feed_prvdr  | vnode0002 | pdt_by     |                62
 DV0         | feed_prvdr  | vnode0002 | dt_tm      |                20
 DV0         | feed_prvdr  | vnode0002 | dt_tm      |                20
 DV0         | feed_prvdr  | vnode0002 |            |                96
 DV0         | feed_prvdr  | vnode0002 | prvdr_id   |                 3
 DV0         | feed_prvdr  | vnode0002 | prvdr_nm   |                32
 DV0         | feed_prvdr  | vnode0002 | prvdr_desc |                49
 DV0         | feed_prvdr  | vnode0002 | pdt_by     |                31
 DV0         | feed_prvdr  | vnode0002 | dt_tm      |                10
 DV0         | feed_prvdr  | vnode0002 | dt_tm      |                10
 DV0         | feed_prvdr  | vnode0002 |            |                48
(14 rows)

The above query shows column for the specific table present on node 2 and their storage bytes, Now the thing to observe here is the why are they repeated twice on the same node.
This might be due to super projection and the buddy projection residing on the same node so all the column are appearing twice.

Now lets find the size of this table

Code: Select all

nnani=> select sum(column_used_bytes) as table_size_in_bytes from
nnani-> (select anchor_table_schema, anchor_table_name,node_name, column_name, sum(used_bytes) as column_used_bytes
nnani(> from column_storage where anchor_table_schema='DV0' and anchor_table_name='feed_prvdr' and
nnani(> node_name IN (select node_name from nodes)
nnani(> group by 1,2,3,4) sub;
 table_size_in_bytes
---------------------
                2952
(1 row)
So the total table size of this table is 2952 bytes in your database.

Hope this helps.. :)
nnani........
Long way to go

You can check out my blogs at vertica-howto

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Estimating Size of a table

Post by id10t » Fri Sep 20, 2013 10:39 am

Hi!

Why audit is't an option?

Code: Select all

daniel=> select audit('public.lj', 0, 1);
 audit 
-------
 6
(1 row)

BTW: Your query calculates wrong value, because you also explicitly calculates an epoch column.

Code: Select all

daniel=> select sum(column_used_bytes) as table_size_in_bytes from^J(select anchor_table_schema, anchor_table_name,node_name, column_name, sum(used_bytes) as column_used_bytes^Jfrom column_storage where anchor_table_schema='public' and anchor_table_name='lj' and^Jnode_name IN (select node_name from nodes)^Jgroup by 1,2,3,4) sub;
 table_size_in_bytes 
---------------------
                  53
(1 row)
6 bytes vs 53 bytes ?

Code: Select all

daniel=> select anchor_table_schema, anchor_table_name,node_name, column_name, sum(used_bytes) as column_used_bytes^Jfrom column_storage where anchor_table_name='lj' group by 1,2,3,4;
 anchor_table_schema | anchor_table_name |    node_name    | column_name | column_used_bytes 
---------------------+-------------------+-----------------+-------------+-------------------
 public              | lj                | v_test_node0001 | id          |                 5
 public              | lj                | v_test_node0001 | epoch       |                48

daniel=> \d lj
                                List of Fields by Tables
 Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key 
--------+-------+--------+------+------+---------+----------+-------------+-------------
 public | lj    | id     | int  |    8 |         | f        | f           | 
(1 row)

scutter
Master
Master
Posts: 301
Joined: Tue Aug 07, 2012 2:15 am

Re: Estimating Size of a table

Post by scutter » Fri Sep 20, 2013 12:53 pm

audit() gives raw table size, column_storage gives compressed size. Two useful values, but different.

The projection_storage table also adds up the used bytes for you by node - just sum up across nodes.

I think it's appropriate to include the epoch column in the math when considering total space used by a projection, but if you're doing a compression calculation you might want to exclude it to be thorough, but leaving it in really does give you the actual compression ratio.
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Estimating Size of a table

Post by id10t » Fri Sep 20, 2013 1:08 pm

:oops:

User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

Re: Estimating Size of a table

Post by nnani » Fri Sep 20, 2013 4:17 pm

Thanks for the suggestion guys.

I learned the fact about the getting the table size ( RAW / COMPRESSED )
This is something new which I got to know.

As scutter said,
audit() gives raw table size, column_storage gives compressed size. Two useful values, but different.
This can be really useful information

Thanks for correcting me. :)
nnani........
Long way to go

You can check out my blogs at vertica-howto

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Estimating Size of a table

Post by NorbertKrupa » Mon Jan 27, 2014 3:22 am

Since this is a fairly common question, I thought it would be beneficial to expand with blog posts. I hope it's alright to share here.
  1. Vertica table size
Checkout vertica.tips for more Vertica resources.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1775
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Estimating Size of a table

Post by JimKnicely » Mon Jan 27, 2014 8:08 pm

I hope it's alright to share here.
It is most definitely okay to share here! Thanks for doing so :D
Jim Knicely

Image

Note: I work for HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Tips, Lessons and Examples”