Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qnUJW-00B2fI-Le for pgsql-general@arkaria.postgresql.org; Tue, 03 Oct 2023 01:35:06 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qnUJV-000Z9g-BB for pgsql-general@arkaria.postgresql.org; Tue, 03 Oct 2023 01:35:05 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qnUJU-000Z9R-VT for pgsql-general@lists.postgresql.org; Tue, 03 Oct 2023 01:35:04 +0000 Received: from rpt-glb-asav2.external.tpg.com.au ([60.241.0.11]) by makus.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qnUJQ-007tEz-TO for pgsql-general@lists.postgresql.org; Tue, 03 Oct 2023 01:35:03 +0000 Received: from gateway.pc5.atmailcloud.com (HELO mqr.i-04e5c82eb10979d8c) ([13.54.26.16]) by rpt-glb-asav2.external.tpg.com.au with ESMTP; 03 Oct 2023 12:33:57 +1100 Received: from CMR-KAKADU02.i-054fe9e9b39af1815 by MQR.i-04e5c82eb10979d8c with esmtps (envelope-from ) id 1qnUIO-0004jQ-2b; Tue, 03 Oct 2023 01:33:56 +0000 Received: from [61.68.173.4] (helo=[192.168.1.101]) by CMR-KAKADU02.i-054fe9e9b39af1815 with esmtpsa (envelope-from ) id 1qnUIO-0008EG-1X; Tue, 03 Oct 2023 01:33:56 +0000 Message-ID: <069c100e455299d6e1aa80bda283a397fadf1926.camel@tpg.com.au> Subject: Re: How to force "re-TOAST" after changing STORAGE or COMPRESSION? From: rob stone To: Michael Paquier , Dominique Devienne Cc: pgsql-general@lists.postgresql.org Date: Tue, 03 Oct 2023 12:33:54 +1100 In-Reply-To: References: Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.50.0-1 MIME-Version: 1.0 X-Atmail-Id: floriparob@tpg.com.au X-atmailcloud-spam-action: no action X-atmailcloud-spam-report: Action: no action X-Cm-Analysis: v=2.4 cv=HMQFVKhv c=1 sm=1 tr=0 ts=651b6f84 a=RZ3eB37mGwPeNdlu8WaBig==:117 a=RZ3eB37mGwPeNdlu8WaBig==:17 a=IkcTkHD0fZMA:10 a=bhdUkHdE2iEA:10 a=FVG_Qj_OW4wA:10 a=x7bEGLp0ZPQA:10 a=HvnWhiLzAAAA:8 a=lPzYUS7PAAAA:8 a=4-jKbpqrSMHdyjQrDoQA:9 a=QEXdDO2ut3YA:10 a=K83o47WhqcUA:10 a=YT91KPC6OSPBWssVLg7J:22 a=GnqpCxz9T1SV7x2DyQBA:22 X-Cm-Envelope: MS4xfJXgzSua7neIJXmqDX2fj+ssRNrJa6Ve0fxNXssKqgnGSc9yPRPZinDxUVOEQGc794dcfkt1Z1MUBK6DbpiApwo+cMu7ZItS9XbZilZx6rvDxHKrnHnb 0AcNQLYckaOqt1mJl1ShErgQzuoLxJVYcpFLzbfzQ15p3x2W84oBTet/ List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hello, On Tue, 2023-10-03 at 08:57 +0900, Michael Paquier wrote: > 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. >=20 > 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. >=20 > > VACUUM? > > VACUUM FULL? > > Else? >=20 > 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 > date: Mon, 14 Jun 2021 09:25:50 +0900 > Remove forced toast recompression in VACUUM FULL/CLUSTER >=20 > Related thread: > https://postgr.es/m/20210527003144.xxqppojoiwurc2iz@alap3.anarazel.de > -- > Michael Would running CLUSTER on the table use the new parameters for the re- write? The docs about CLUSTER do not mention anything about toast'ed data. If the toast data is huge it would be held in a separate file on disk. If the toast column is being compressed, and its size is now less than the value used as to whether a separate file is required or not, will the outcome be a single row for the re-write and deletion of the "old" toast'ed entry? Robert