Setting the column COMPRESSION to LZ4 does not seem to work?

Question

Working with Postgres 16.2 for Windows, I am attempting to set LZ4 compression on a column within one of our tables.

  message text COMPRESSION lz4,

When I perform the following query:

SELECT attname, attcompression
FROM pg_attribute
WHERE attrelid = 'message_data'::regclass
  AND attname = 'message';

The result gives me 'l' which stands for LZ4

"attname"   "attcompression"
"message"   "l"

But, when I look at the compression actually used I see that pglz is being used instead of LZ4.

SELECT 
    pg_column_compression(message) as compression_type,
    pg_column_size(message) AS id_compressed_size,
    length(message) AS id_uncompressed_size
FROM message_data LIMIT 10;

"compression_type"  "id_compressed_size"    "id_uncompressed_size"
"pglz"  7800    10485
"pglz"  7803    10485

I tried upgrading to "PostgreSQL 16.3, compiled by Visual C++ build 1938, 64-bit" from EnterpriseDB but with the same result.

I also verified that EnterpriseDB does include the LZ4 support.

I manage to get LZ4 compression working when I set this in the postgresql.conf file:

default_toast_compression = 'lz4'

One thing to note is that message_data is a partitioned table, so maybe there is an issue with the compression setting on columns of partitioned tables?

Answer 1

message_data is a partitioned table

If you CREATE a partitioned table with COMPRESSION lz4, every partition created thereafter inherits that setting. If you ALTER an existing partitioned table, again, every partition created thereafter inherits the setting.
But pre-existing partitions are not affected by altering column settings of the parent table. (It's conceivable that one might want different compression methods for different partitions.)

To check the state of the message column in the parent public.message_data and all its partitions:

SELECT a.attrelid::regclass::text AS partition
     , a.attname as column
     , CASE a.attcompression WHEN 'p' THEN 'pglz' WHEN 'l' THEN 'LZ4' ELSE a.attcompression::text END AS compression
FROM  (
   SELECT 'public.message_data'::regclass AS attrelid
   UNION ALL
   SELECT i.inhrelid
   FROM   pg_catalog.pg_inherits i
   WHERE  i.inhparent = 'public.message_data'::regclass
   ) t
JOIN   pg_catalog.pg_attribute a USING (attrelid)
WHERE  a.attname = 'message'
ORDER  BY 1;

To set compression of a column to LZ4 in parent and all partitions:

DO
$do$
DECLARE
   _tbl regclass := 'public.message_data';  -- YOUR parent table
   _col text     := 'message';              -- YOUR column name
   _part regclass;
BEGIN
   -- parent
   EXECUTE format('ALTER TABLE %s ALTER COLUMN %I SET COMPRESSION lz4', _tbl, _col);

   -- partitions
   FOR _part IN
      SELECT inhrelid::regclass
      FROM   pg_catalog.pg_inherits
      WHERE  inhparent = _tbl
   LOOP
      -- RAISE NOTICE '%',  -- debug first?!
      EXECUTE
         format('ALTER TABLE %s ALTER COLUMN %I SET COMPRESSION lz4;', _part, _col);
   END LOOP;
END
$do$;

Also note that setting a new COMPRESSION for a column does not re-compress pre-existing values. Even applies to values copied from another table! Once compressed, Postgres keeps the compressed value (remembering the method) unless it has to decompress!

You may want to force re-compression of existing values.

Verify like this: pick a row where you still found pglz compression and run an UPDATE with this no-op:

UPDATE message_data
SET    message = message || ''  -- does not actually change anything
WHERE  message_id = 123;        -- the one you found

Makes Postgres re-compress with the currently set compression method. Run the test with pg_column_compression(message) again to verify.

Related:

• Query on json / jsonb column super slow. Can I use an index?

BTW, to see the source partition for each row retrieved:

SELECT tableoid::regclass AS source, *
FROM   public.message_data
LIMIT  10;

See:

• Get the name of a row's source table when querying the parent it inherits from