lz4 new in version 16.05. zstd new in version 16.16.
Greenplum DB ships with available zlib compression for storage. In addition to zlib, Deepgreen DB incorporates two other compression for storage that are better suited for database workload: zstd and lz4.
Customers who require a very good compression ratio to save on disk space should pick zstd compression algorithm for column stores and append-optimized heap tables. Compared to zlib, zstd has better compression ratio and also utilizes the CPU much more efficiently.
Customers who have mostly read workload on fast I/O devices should select lz4 due to its spectacular decompression speed. Even though the compression ratio of lz4 is not as good as zlib or zstd, we feel it offers a good tradeoff for read-heavy database operation. This is especially true on fast I/O devices, where the savings from reading less disk blocks does not justify the CPU cost in decompressing the data.
For details on these new compression algorithms, please refer to their respective home pages:
Without going into too much details, a quick test of the none / zlib / zstd / lz4 compression algorithms on column store can be found below. Obviously, your mileage may vary depending on your hardware. Our results are tabulated here:
create temp table ttnone (
i int,
t text,
default column encoding (compresstype=none))
with (appendonly=true, orientation=column)
distributed by (i);
CREATE TABLE
Time: 162.064 ms
create temp table ttzlib(
i int,
t text,
default column encoding (compresstype=zlib, compresslevel=1))
with (appendonly=true, orientation=column)
distributed by (i);
CREATE TABLE
Time: 163.772 ms
create temp table ttzstd (
i int,
t text,
default column encoding (compresstype=zstd, compresslevel=1))
with (appendonly=true, orientation=column)
distributed by (i);
CREATE TABLE
Time: 179.972 ms
create temp table ttlz4 (
i int,
t text,
default column encoding (compresstype=lz4))
with (appendonly=true, orientation=column)
distributed by (i);
CREATE TABLE
Time: 166.926 ms
-- -------------------------------
-- WRITE -------------------------
-- -------------------------------
insert into ttnone select i, 'user '||i from generate_series(1, 100000000) i;
INSERT 0 100000000
Time: 92833.687 ms
insert into ttzlib select i, 'user '||i from generate_series(1, 100000000) i;
INSERT 0 100000000
Time: 62898.443 ms
insert into ttzstd select i, 'user '||i from generate_series(1, 100000000) i;
INSERT 0 100000000
Time: 59157.905 ms
insert into ttlz4 select i, 'user '||i from generate_series(1, 100000000) i;
INSERT 0 100000000
Time: 70459.011 ms
-- -------------------------------
-- SIZE --------------------------
-- -------------------------------
select pg_size_pretty(pg_relation_size('ttnone'));
pg_size_pretty
----------------
1708 MB
(1 row)
Time: 0.857 ms
select pg_size_pretty(pg_relation_size('ttzlib'));
pg_size_pretty
----------------
374 MB
(1 row)
Time: 15.111 ms
select pg_size_pretty(pg_relation_size('ttzstd'));
pg_size_pretty
----------------
325 MB
(1 row)
Time: 0.889 ms
select pg_size_pretty(pg_relation_size('ttlz4'));
pg_size_pretty
----------------
785 MB
(1 row)
Time: 0.841 ms
-- -------------------------------
-- READ --------------------------
-- -------------------------------
select sum(length(t)) from ttnone;
sum
------------
1288888898
(1 row)
Time: 2533.964 ms
select sum(length(t)) from ttzlib;
sum
------------
1288888898
(1 row)
Time: 3688.746 ms
select sum(length(t)) from ttzstd;
sum
------------
1288888898
(1 row)
Time: 3197.472 ms
select sum(length(t)) from ttlz4;
sum
------------
1288888898
(1 row)
Time: 2591.616 ms