Waiting for PostgreSQL 14 – Allow configurable LZ4 TOAST compression.

On 19th of March 2021, Robert Haas committed patch:

Allow configurable LZ4 TOAST compression.
 
There is now a per-column COMPRESSION option which can be set to pglz
(the default, and the only option in up until now) or lz4. Or, if you
like, you can set the new default_toast_compression GUC to lz4, and
then that will be the default for new table columns for which no value
is specified. We don't have lz4 support in the PostgreSQL code, so
to use lz4 compression, PostgreSQL must be built --with-lz4.
 
In general, TOAST compression means compression of individual column
values, not the whole tuple, and those values can either be compressed
inline within the tuple or compressed and then stored externally in
the TOAST table, so those properties also apply to this feature.
 
Prior to this commit, a TOAST pointer has two unused bits as part of
the va_extsize field, and a compessed datum has two unused bits as
part of the va_rawsize field. These bits are unused because the length
of a varlena is limited to 1GB; we now use them to indicate the
compression type that was used. This means we only have bit space for
2 more built-in compresison types, but we could work around that
problem, if necessary, by introducing a new vartag_external value for
any further types we end up wanting to add. Hopefully, it won't be
too important to offer a wide selection of algorithms here, since
each one we add not only takes more coding but also adds a build
dependency for every packager. Nevertheless, it seems worth doing
at least this much, because LZ4 gets better compression than PGLZ
with less CPU usage.
 
It's possible for LZ4-compressed datums to leak into composite type
values stored on disk, just as it is for PGLZ. It's also possible for
LZ4-compressed attributes to be copied into a different table via SQL
commands such as CREATE TABLE AS or INSERT .. SELECT.  It would be
expensive to force such values to be decompressed, so PostgreSQL has
never done so. For the same reasons, we also don't force recompression
of already-compressed values even if the target table prefers a
different compression method than was used for the source data.  These
architectural decisions are perhaps arguable but revisiting them is
well beyond the scope of what seemed possible to do as part of this
project.  However, it's relatively cheap to recompress as part of
VACUUM FULL or CLUSTER, so this commit adjusts those commands to do
so, if the configured compression method of the table happens not to
match what was used for some column value stored therein.
 
Dilip Kumar. The original patches on which this work was based were
written by Ildus Kurbangaliev, and those were patches were based on
even earlier work by Nikita Glukhov, but the design has since changed
very substantially, since allow a potentially large number of
compression methods that could be added and dropped on a running
system proved too problematic given some of the architectural issues
mentioned above; the choice of which specific compression method to
add first is now different; and a lot of the code has been heavily
refactored.  More recently, Justin Przyby helped quite a bit with
testing and reviewing and this version also includes some code
contributions from him. Other design input and review from Tomas
Vondra, Álvaro Herrera, Andres Freund, Oleg Bartunov, Alexander
Korotkov, and me.
 
Discussion: http://postgr.es/m/20170907194236.4cefce96%40wp.localdomain
Discussion: http://postgr.es/m/CAFiTN-uUpX3ck%3DK0mLEk-G_kUQY%3DSNOTeqdaNRR9FMdQrHKebw%40mail.gmail.com

As you perhaps know, when you store large text it gets internally compressed. Of course not only text, but that's what I will focus on now.

So far compression was done using PostgreSQL's implementation of LZ algorithm.

Now, thanks to this patch, we can have one more algorithm. TO use it I had to install liblz4-dev package, and then use –with-lz4 option for configure.

After compilation, I can see that LZ4 is available by doing:

=$ pg_config --configure | grep -oP '\S+lz4\S+'
'--with-lz4'

To test it I made simple table:

=$ CREATE TABLE pgdocs (
    id serial PRIMARY KEY,
    filename text NOT NULL UNIQUE,
    body text
);

Then, I loaded there whole Pg documentation (in html format). Each file was one row. It looked like this:

$ SELECT id, filename, LENGTH(body) FROM pgdocs ORDER BY random() LIMIT 5;
 id  │              filename              │ LENGTH
─────┼────────────────────────────────────┼────────
 829 │ sql-altertsconfig.html             │   8617
   2 │ admin.html                         │  17620
 746 │ spi-spi-cursor-open-with-args.html │   7572
 248 │ ecpg-concept.html                  │   6130
 726SOURCE.html                        │   3042
(5 ROWS)

Then I made a copy using LZ4 compression for body:

=$ CREATE TABLE pgdocs4 (
    id serial PRIMARY KEY,
    filename text NOT NULL UNIQUE,
    body text
);
=$ ALTER TABLE pgdocs4 ALTER COLUMN body SET compression lz4;
=$ INSERT INTO pgdocs4 SELECT * FROM pgdocs;

I checked \d+ of both tables, to make sure that compression is as I wanted:

$ \d+ pgdocs
                                                        TABLE "public.pgdocs"
  COLUMNTYPE   │ Collation │ NULLABLEDEFAULT               │ Storage  │ Compression │ Stats target │ Description
──────────┼─────────┼───────────┼──────────┼────────────────────────────────────┼──────────┼─────────────┼──────────────┼─────────────
 id       │ INTEGER │           │ NOT NULLNEXTVAL('pgdocs_id_seq'::regclass) │ plain    │             │              │
 filename │ text    │           │ NOT NULL │                                    │ extended │ pglz        │              │
 body     │ text    │           │          │                                    │ extended │ pglz        │              │
Indexes:
    "pgdocs_pkey" PRIMARY KEY, btree (id)
    "pgdocs_filename_key" UNIQUE CONSTRAINT, btree (filename)
Access method: heap
 
$ \d+ pgdocs4
                                                        TABLE "public.pgdocs4"
  COLUMNTYPE   │ Collation │ NULLABLEDEFAULT               │ Storage  │ Compression │ Stats target │ Description
──────────┼─────────┼───────────┼──────────┼─────────────────────────────────────┼──────────┼─────────────┼──────────────┼─────────────
 id       │ INTEGER │           │ NOT NULLNEXTVAL('pgdocs4_id_seq'::regclass) │ plain    │             │              │
 filename │ text    │           │ NOT NULL │                                     │ extended │ pglz        │              │
 body     │ text    │           │          │                                     │ extended │ lz4         │              │
Indexes:
    "pgdocs4_pkey" PRIMARY KEY, btree (id)
    "pgdocs4_filename_key" UNIQUE CONSTRAINT, btree (filename)
Access method: heap

As you can see in Compression column, there is lz4 for body of pgdocs4 table.

First thing I checked were sizes:

$ \dt+
                                        List OF relations
 Schema │   Name   │ TYPE  │ Owner  │ Persistence │ Access Method │    SIZE    │ Description
────────┼──────────┼───────┼────────┼─────────────┼───────────────┼────────────┼─────────────
 public │ pgdocs   │ TABLE │ depesz │ permanent   │ heap          │ 5952 kB    │
 public │ pgdocs4  │ TABLE │ depesz │ permanent   │ heap          │ 5936 kB    │
(2 ROWS)

This proved to be my mistake, as pointed in comment by Kris. I forgot that copying data will not recompress it. So, I had to vacuum full the table, and afterwards:

$ \dt+
                                        List OF relations
 Schema │   Name   │ TYPE  │ Owner  │ Persistence │ Access Method │    SIZE    │ Description
────────┼──────────┼───────┼────────┼─────────────┼───────────────┼────────────┼─────────────
 public │ pgdocs   │ TABLE │ depesz │ permanent   │ heap          │ 5952 kB    │
 public │ pgdocs4  │ TABLE │ depesz │ permanent   │ heap          │ 6592 kB    │
(2 ROWS)

So – lz4 is using more disk space, by about 10%.

But what about speed?

To test it I figured I'll update the table, wrapping body with [ / ], by doing:

=$ UPDATE pgdocs SET body = '[' || body || ']';
=$ UPDATE pgdocs4 SET body = '[' || body || ']';

Ran it 3 times, and got nice surprise. Times for pglz update were: 279.084, 264.014, and 278.946 ms. And times for LZ4 were: 101.977, 130.052, and 112.037 ms.

This suggests that the same operation using lz4 compression using less than 50% of the time that pglz needed!

Based on Kris comment I also checked sizes afterwards: pgdocs was 25,520kB, while pgdocs4 was 25792kB – difference of a bit less than 10%.

Obviously my test was very small, but it seems to suggest that it is a very promising change, at least for the ones that store non-trivially sized blocks of data in Pg.

Thanks a lot to everyone that worked on this.

5 thoughts on “Waiting for PostgreSQL 14 – Allow configurable LZ4 TOAST compression.”

  1. It suggests in the commit message that the insert select that you did wouldn’t have recompressed.

    Did you vacuum full before checking the sizes?

    Did you check the sizes again after your wrapping update?

  2. Not related with above but what happened to storage engines like zheap. It was supposed to be included in PG12. Can’t find any update on this

Comments are closed.