Is it ok to have a lot of columns that are AUTO encoded?

Moderator: NorbertKrupa

Post Reply
User avatar
Julie
Master
Master
Posts: 221
Joined: Thu Apr 19, 2012 9:29 pm

Is it ok to have a lot of columns that are AUTO encoded?

Post by Julie » Tue Apr 01, 2014 4:08 pm

Hi,

My question is kind of related to another topic:

viewtopic.php?f=4&t=1782

My database has ALOT of projection columns with the encoding type AUTO. Is this something I need to be concerned about?

See:

Code: Select all

dbadmin=> select encoding_type, count(*) from projection_columns group by encoding_type order by 2 desc;
  encoding_type   | count 
------------------+-------
 AUTO             | 25475
 RLE              |  1066
 COMMONDELTA_COMP |   308
 DELTAVAL         |    39
 BLOCKDICT_COMP   |    38
 DELTARANGE_COMP  |    36
(6 rows)
Thanks,
Juliette

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

Re: Is it ok to have a lot of columns that are AUTO encoded?

Post by NorbertKrupa » Tue Apr 01, 2014 4:24 pm

Not necessarily. With AUTO encoded columns:
For CHAR/VARCHAR, BOOLEAN, BINARY/VARBINARY, and FLOAT columns, Lempel-Ziv-Oberhumer-based (LZO) compression is used.

For INTEGER, DATE/TIME/TIMESTAMP, and INTERVAL types, the compression scheme is based on the delta between consecutive column values.

AUTO encoding is ideal for sorted, many-valued columns such as primary keys. It is also suitable for general purpose applications for which no other encoding or compression scheme is applicable. Therefore, it serves as the default if no encoding/compression is specified.

The CPU requirements for this type are relatively small. In the worst case, data might expand by eight percent (8%) for LZO and twenty percent (20%) for integer data.
However, I would go through the columns with an AUTO encoding type and see if they can possibly be improved to RLE.

See all the encoding types.
Checkout vertica.tips for more Vertica resources.

Post Reply

Return to “Vertica Database Development”