Re: Force re-compression with lz4

Hello,

I have a table storing mostly text data (40M+ rows) that has pg_total_relation_size ~670GB.

I’ve just upgraded to postgres 14 and I’m now eager to try the new LZ4 compression.

I’ve altered the column to use the new lz4 compression, but that only applies to new rows.

What’s the recommended way of triggering the re-evaluation for pre-existing rows? 

I tried wrapping a function like the following, but apparently each old record retains the compression applied.

text_corpus=(SELECT t.text from ...);

delete from t where id=;

insert into t(id, text) values (id, text_corpus);

Fttb, I resorted to preparing an external shell script to execute against the db but that’s too slow as it moves data in&out the db.

Is there a smarter way to do this ?