Re: How to force "re-TOAST" after changing STORAGE or COMPRESSION? - Mailing list pgsql-general

From Michael Paquier
Subject Re: How to force "re-TOAST" after changing STORAGE or COMPRESSION?
Date
Msg-id ZRtY5lEDPlSQuore@paquier.xyz
Whole thread Raw
In response to How to force "re-TOAST" after changing STORAGE or COMPRESSION?  (Dominique Devienne <ddevienne@gmail.com>)
Responses Re: How to force "re-TOAST" after changing STORAGE or COMPRESSION?
List pgsql-general
On Mon, Oct 02, 2023 at 04:42:15PM +0200, Dominique Devienne wrote:
> 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.

Yes, the compression type of the existed toasted values are not
changed after an ALTER SET, and that's not something one would do on a
daily basis, either.

> VACUUM?
> VACUUM FULL?
> Else?

VACUUM was considered as an option to force a rewrte, but it has been
removed because we were not sure that this is the correct path to do
so or that in some cases forcing the hand of the user was incorrect.
It was also creating a penalty in some of the hot loops of area:
commit: dbab0c07e5ba1f19a991da2d72972a8fe9a41bda
committer: Michael Paquier <michael@paquier.xyz>
date: Mon, 14 Jun 2021 09:25:50 +0900
Remove forced toast recompression in VACUUM FULL/CLUSTER

Related thread:
https://postgr.es/m/20210527003144.xxqppojoiwurc2iz@alap3.anarazel.de
--
Michael

Attachment

pgsql-general by date:

Previous
From: "David G. Johnston"
Date:
Subject: Re: How to investigate deadlocks
Next
From: Abhishek Bhola
Date:
Subject: Re: pgBackRest for a 50 TB database