What is the smallest data type for a flag indicator?

Moderator: NorbertKrupa

Post Reply
User avatar
Josh
Intermediate
Intermediate
Posts: 106
Joined: Thu Jan 26, 2012 9:38 pm

What is the smallest data type for a flag indicator?

Post by Josh » Wed Oct 01, 2014 12:47 pm

Hi all,

Which data type in Vertica is best for a flag type value (i.e. on or off/true or false/yes or no) in a table? I was thinking Boolean.

Thank you
Thank you!
Joshua

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

Re: What is the smallest data type for a flag indicator?

Post by NorbertKrupa » Wed Oct 01, 2014 1:22 pm

I would probably use boolean as it only takes 1 byte.
Checkout vertica.tips for more Vertica resources.

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

Re: What is the smallest data type for a flag indicator?

Post by JimKnicely » Wed Oct 01, 2014 4:43 pm

It seems like the INT datatype takes up the least amount of space...

Code: Select all

dbadmin=> \d jim;
                                   List of Fields by Tables
 Schema | Table | Column |    Type    | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+------------+------+---------+----------+-------------+-------------
 public | jim   | c1     | int        |    8 |         | f        | f           |
 public | jim   | c2     | varchar(1) |    1 |         | f        | f           |
 public | jim   | c3     | char(1)    |    1 |         | f        | f           |
 public | jim   | c4     | boolean    |    1 |         | f        | f           |
(4 rows)

dbadmin=> insert /*+ direct */ into jim values (1, '1', '1', true);
 OUTPUT
--------
      1
(1 row)

dbadmin=> commit;
COMMIT

dbadmin=> select column_name, sum(used_bytes) used_bytes from column_storage where anchor_table_name = 'jim' group by column_name order by 1;
 column_name | used_bytes
-------------+------------
 c1          |          6
 c2          |         56
 c3          |         56
 c4          |         56
 epoch       |         96
(5 rows)​
In the example column c1 is an INT. Integers must compress very well!
Jim Knicely

Image

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

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

Re: What is the smallest data type for a flag indicator?

Post by NorbertKrupa » Wed Oct 01, 2014 5:39 pm

That's not entirely accurate as the boolean column will use LZO compression by default.

When using block dictionary compression, it comes down to about 9 bytes. Although still a bit higher than using int. I don't understand why as the documentation claims it only takes one byte (perhaps just for binary storage).
Checkout vertica.tips for more Vertica resources.

Post Reply

Return to “New to Vertica Database Administration”