How to force "re-TOAST" after changing STORAGE or COMPRESSION?

From: Dominique Devienne <ddevienne(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: How to force "re-TOAST" after changing STORAGE or COMPRESSION?
Date: 2023-10-02 14:42:15
Message-ID: CAFCRh-8_EFUgKSNpsyideokDo+j80iPg4GVwNoukniOjpZVnng@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi. If I do either or both of the changes below:

ddevienne=> alter table dd alter column val set compression lz4;
ALTER TABLE
ddevienne=> alter table dd alter column val set storage extended;
ALTER TABLE

According to the doc, the table is NOT changed.
In my case, I DO want to have the bytea column rewritten
according to the new STORAGE and/or COMPRESSION.
The doc doesn't mention how to achieve that.

VACUUM?
VACUUM FULL?
Else?

Also, pre-v14, is eXtended STORAGE always compressed with pglz?
The reason I'm asking, is that I don't see much difference between
sum(length(bytea_col)) and pg_total_relation_size(reltoastrelid), thus
I'm not quite sure whether compression happens or not, in fact.

Note that most of the data is floating point arrays inside a bytea columns,
thus I guess unless one shuffles the bytes around, it doesn't compress too
well.

Thanks, --DD

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ron 2023-10-02 14:59:41 Re: Operating of synchronous master when no standby is available
Previous Message Douglas Reed 2023-10-02 14:38:46 Problems starting slave