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
What is the smallest data type for a flag indicator?
Moderator: NorbertKrupa
What is the smallest data type for a flag indicator?
Thank you!
Joshua
Joshua
-
- 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?
I would probably use boolean as it only takes 1 byte.
Checkout vertica.tips for more Vertica resources.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: What is the smallest data type for a flag indicator?
It seems like the INT datatype takes up the least amount of space...
In the example column c1 is an INT. Integers must compress very well!
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)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
-
- 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?
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).
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.