Oh, that’s good to know then. So besides ALTER COMPRESSION for future inserts there’s not much one can do for pre-existing values
I think it makes sense to update/ add more info to the docs on this as well, since other people in the thread expected this to work that way too.
Maybe at some point, even allow an explicit option to be defined during VACUUM ?
> On 18 Oct 2021, at 8:18 AM, Michael Paquier <michael@xxxxxxxxxxx> wrote:
>
> On Sun, Oct 17, 2021 at 10:13:48PM +0300, Florents Tselai wrote:
>> I did look into VACUUM(full) for it’s PROCESS_TOAST option which
>> makes sense, but the thing is I already had a cron-ed VACUUM (full)
>> which I ended up disabling a while back; exactly because of the
>> double-space requirement.
>
> Please note that VACUUM FULL does not enforce a recompression on
> existing values. See commit dbab0c0, that disabled this choice as it
> introduced a noticeable performance penality in some cases when
> looking at the compression type of the vacuumed table attributes:
> =# CREATE TABLE cmdata(f1 text COMPRESSION pglz);
> CREATE TABLE
> =# INSERT INTO cmdata VALUES(repeat('1234567890', 1000));
> INSERT 0 1
> =# SELECT pg_column_compression(f1) FROM cmdata;
> pg_column_compression
> -----------------------
> pglz
> (1 row)
> =# ALTER TABLE cmdata ALTER COLUMN f1 SET COMPRESSION lz4;
> ALTER TABLE
> =# VACUUM FULL cmdata;
> VACUUM
> =# SELECT pg_column_compression(f1) FROM cmdata;
> pg_column_compression
> -----------------------
> pglz
> (1 row)
> --
> Michael
