Encode Projection Columns with Zstandard Compression

Jim_Knicely

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!