Encode Projection Columns with Zstandard Compression
Jim_Knicely
- Select Field - Administrator
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!
0