Re: Force re-compression with lz4

On Sun, Oct 17, 2021 at 5:12 PM Florents Tselai <florents(dot)tselai(at)gmail(dot)com>
wrote:

> 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 ?
>
>
It should be enough to VACUUM FULL the table. (but it has to be VACUUM
FULL, not a regular vacuum). Or CLUSTER.

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>