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 1qnSnD-00AyTp-9w for pgsql-general@arkaria.postgresql.org; Mon, 02 Oct 2023 23:57:39 +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 1qnSnA-000EH0-RE for pgsql-general@arkaria.postgresql.org; Mon, 02 Oct 2023 23:57:36 +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 1qnSnA-000EGj-FD for pgsql-general@lists.postgresql.org; Mon, 02 Oct 2023 23:57:36 +0000 Received: from wout2-smtp.messagingengine.com ([64.147.123.25]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qnSn7-007sR4-OA for pgsql-general@lists.postgresql.org; Mon, 02 Oct 2023 23:57:35 +0000 Received: from compute1.internal (compute1.nyi.internal [10.202.2.41]) by mailout.west.internal (Postfix) with ESMTP id 5894A3200992; Mon, 2 Oct 2023 19:57:32 -0400 (EDT) Received: from mailfrontend1 ([10.202.2.162]) by compute1.internal (MEProxy); Mon, 02 Oct 2023 19:57:32 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=paquier.xyz; h= cc:cc:content-type:content-type:date:date:from:from:in-reply-to :in-reply-to:message-id:mime-version:references:reply-to:sender :subject:subject:to:to; s=fm2; t=1696291051; x=1696377451; bh=7H tyrPI6SNsX999AFD9aRQOffUy3huIIR7pYpuKsWuM=; b=ir9RQJ3LV7NrhXfMY4 rsemYvhJd9vtu5kAteT4zq/HF4fGRXRdXks2yPII7flWP1wp6ciFmxLajqzRg414 VwLqnmJn/FDdHjs02AKo315b7xRL9XTEdqd2K1d8FciFhWXP65gOt2KSLB5Cvbtw 9g9jRsj9ecR9ENpN7AhXIAc5+6r7o97dWKffglFeYc04cOpBFqWYRiYWyhqEjZaO b4URfuh9IfMdCut4s2mXEEMAql51vBF7H1OkJEFD0Ayh8cGFBQmG02m4rPid5oIQ oOYUFi8oWW9aMb/fETOYyO7f98E2h481qLN8b8oZdMa6EEd0DopW7WvAbuaHB9DP Rvzw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-type:content-type:date:date :feedback-id:feedback-id:from:from:in-reply-to:in-reply-to :message-id:mime-version:references:reply-to:sender:subject :subject:to:to:x-me-proxy:x-me-proxy:x-me-sender:x-me-sender :x-sasl-enc; s=fm2; t=1696291051; x=1696377451; bh=7HtyrPI6SNsX9 99AFD9aRQOffUy3huIIR7pYpuKsWuM=; b=jVWSGDDM73Vph5J8X/WBwZPCwVcad qtkJNf71jWRZiMnr/KQS/J5h2mf2i+Kh8rU3wj9KL8XZPzm1B7h25ljQqmxlNuv+ A5WqKsmiTc9qI1O4QQUXuaHDn5GUGRbRV8v+fgWyNfRaUwgPWgMmLlNSV0j0b77g yGEpst7n/023F+xtwv1poKngrfOBcI1j811v1UhBrebkfQb9cyYrdxti87ioNiXn oYXv+3nQf54s3c5exFcQFkxcGstOj+8uw0H8Kv4I8QPFOvfPBennI16hW2AxiX0E +4BILO5/qOYwEi6Hjc1h3qJHiXAevaAgScsB9I1Vy2IptEgROfMOBZU/g== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvkedrfedugddvjecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenfg hrlhcuvffnffculdefhedmnecujfgurhepfffhvfevuffkfhggtggujgesghdtreertddt vdenucfhrhhomhepofhitghhrggvlhcurfgrqhhuihgvrhcuoehmihgthhgrvghlsehprg hquhhivghrrdighiiiqeenucggtffrrghtthgvrhhnpeetteegvddvveeuieduudevkeff veevtdduleehleelffdtvdegfeegtdejtedtkeenucffohhmrghinhepphhoshhtghhrrd gvshenucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpehm ihgthhgrvghlsehprghquhhivghrrdighiii X-ME-Proxy: Feedback-ID: i0fe9450f:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Mon, 2 Oct 2023 19:57:29 -0400 (EDT) Date: Tue, 3 Oct 2023 08:57:26 +0900 From: Michael Paquier To: Dominique Devienne Cc: pgsql-general@lists.postgresql.org Subject: Re: How to force "re-TOAST" after changing STORAGE or COMPRESSION? Message-ID: References: MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="4cg/hroFnketYtgO" Content-Disposition: inline In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --4cg/hroFnketYtgO Content-Type: text/plain; charset=us-ascii Content-Disposition: inline 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 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 --4cg/hroFnketYtgO Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAmUbWOYACgkQnvQgOdby QH01mw/9G8f8zJ/0Bamc70QZ5fj7GR34+FMU9MEZISiXz0nNIlIXF4uI9hxVJWa7 S2XP9i86jo8A4OfyleEyHivJbKw/vlW8ThtGb1MMsIbMuoA9I0hrDfHfyA0xDRRL MQvvGxmuAh257DiC3Mse6FD/QwNf9YguQVDG4TDXbGgpVB44QPa9cUc7zZ7fUhJ6 xevSz51GqmjHslsmmlEFRDZm3vnVki8CqwksYvbukfXKZig642grTD+Zfw0DudSo 3qvg1mmbLBZYcAqH8sXWCQcJwKHzhTYvOmCiz6NWyXVPhq+6lhQNJJBleJnQe976 i6FpA+cstVmjKtHyW+pK7xntitfk+PV3gtTob7X1PI41xwZJmBEWiozkQJEHJXnK e3e7WdkVYzlW//ykdqVAslpDaUnZwewTgsI0lnHlECCV+sgSuLaga6PrqjUgqdFl FCFbb5lTOfV+tJWCUhcj6Kj39HixuvACBvdkY/FPOn1OqgIg7ie8diYGWyvixhho xZfn+GMS55LSB/Y1z3k7I6cM+wW57znZUZgUhA27Veq+JA6eSnsD96bi3875r/jP h1YjOhqVnguFBd+HIMKeoxujZC5OlszhcJ1xYis5+hsKGYpfwil9nBNe7kXEN0ES m6qICZsGydd/LzfRvm/CPS3wEMLSZPDGZbeWCFkz5Ug1Et0Q0xo= =LkAk -----END PGP SIGNATURE----- --4cg/hroFnketYtgO--