Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Encode Projection Columns with Zstandard Compression

Vertica 9.2 now supports the Zstandard Compression Encoding Type which offers higher compression than the BZIP and GZIP Encoding Types!

Example:

dbadmin=> CREATE TABLE test (a VARCHAR(10000), b VARCHAR(10000), c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000));
CREATE TABLE

dbadmin=> CREATE PROJECTION test_pr
dbadmin->   (a ENCODING BZIP_COMP,
dbadmin(>    b ENCODING GZIP_COMP,
dbadmin(>    c ENCODING ZSTD_COMP,
dbadmin(>    d ENCODING ZSTD_FAST_COMP,
dbadmin(>    e ENCODING ZSTD_HIGH_COMP) AS
dbadmin->   SELECT a, b, c, d, e
dbadmin->     FROM test;
CREATE PROJECTION

dbadmin=> INSERT /*+ DIRECT */ INTO test
dbadmin->   SELECT REPEAT('A', 10000),
dbadmin->          REPEAT('A', 10000),
dbadmin->          REPEAT('A', 10000),
dbadmin->          REPEAT('A', 10000),
dbadmin->          REPEAT('A', 10000);
OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT column_name, used_bytes, encodings, compressions
dbadmin->   FROM column_storage
dbadmin->  WHERE projection_name = 'test_pr';
column_name | used_bytes | encodings | compressions
-------------+------------+-----------+--------------
a           |         70 | String    | bzip
b           |         53 | String    | gzip
c           |         40 | String    | zstd
d           |         41 | String    | zstd fast
e           |         39 | String    | zstd high
epoch       |         48 | Int_Delta | none
(6 rows)

Helpful Link:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Statements/encoding-type.htm

Have fun!

Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.