Encode Projection Columns with Zstandard Compression: Quick Tip

Posted December 3, 2018 by Jim Knicely, Vertica Principal Solution Architect

Designers discussing project in office sample library
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/latest/HTML/Content/Authoring/SQLReferenceManual/Statements/encoding-type.htm Have fun!