pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17513: recompressing already-compressed data via VACUUM FULL or CLUSTER does not work
3+ messages / 3 participants
[nested] [flat]
* BUG #17513: recompressing already-compressed data via VACUUM FULL or CLUSTER does not work
@ 2022-06-08 19:41 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2022-06-08 19:41 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: kg.postgresql@olympiakos.com
The following bug has been logged on the website:
Bug reference: 17513
Logged by: Kiriakos Georgiou
Email address: kg.postgresql@olympiakos.com
PostgreSQL version: 14.3
Operating system: Linux
Description:
Per
https://www.postgresql.org/message-id/E1lNKw9-0008DT-1L%40gemulon.postgresql.org
and https://www.enterprisedb.com/blog/configurable-lz4-toast-compression
if you change a column's compression algorithm and run VACUUM FULL or
CLUSTER, the data will be recompressed with the new compression algorithm.
This is does not work for 14.2 and 14.3 (the only two versions I tested).
TEST CASE:
I run the small test from Dilip Kumar's post (2nd link above) and after the
VACUUM FULL I get this, which is not the desired outcome (both should be
pglz):
test=> SELECT pg_column_compression(f1) FROM cmdata;
pg_column_compression
-----------------------
lz4
pglz
(2 rows)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #17513: recompressing already-compressed data via VACUUM FULL or CLUSTER does not work
@ 2022-06-08 22:59 David G. Johnston <david.g.johnston@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: David G. Johnston @ 2022-06-08 22:59 UTC (permalink / raw)
To: kg.postgresql@olympiakos.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Wed, Jun 8, 2022 at 12:46 PM PG Bug reporting form <
noreply@postgresql.org> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 17513
> Logged by: Kiriakos Georgiou
> Email address: kg.postgresql@olympiakos.com
> PostgreSQL version: 14.3
> Operating system: Linux
> Description:
>
> Per
>
> https://www.postgresql.org/message-id/E1lNKw9-0008DT-1L%40gemulon.postgresql.org
> and https://www.enterprisedb.com/blog/configurable-lz4-toast-compression
> if you change a column's compression algorithm and run VACUUM FULL or
> CLUSTER, the data will be recompressed with the new compression algorithm.
> This is does not work for 14.2 and 14.3 (the only two versions I tested).
>
The described behavior didn't even make it into 14.0; and I have no clue
where such a thing would be documented to guess if the lack was intentional
or not. I can confirm that as of today the behavior in HEAD is to not
decompress and recompress during rewrite. Frankly, this seems like the
expected behavior absent any documentation to the contrary. Whether there
is a use case for someone to code up a way to get a recompression to happen
is another matter. Oddly, I kinda expected that "update cmdata set f1 =
f1;" might do it but alas it does not (...set f1 = f1 || ''... works; I'm
not quite sure how much worse that, immediately followed by vacuum full,
would be compared to whatever it would take to do it all during the full
vacuum...especially if one is changing every single row on the table).
David J.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #17513: recompressing already-compressed data via VACUUM FULL or CLUSTER does not work
@ 2022-06-09 01:10 Michael Paquier <michael@paquier.xyz>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Michael Paquier @ 2022-06-09 01:10 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: kg.postgresql@olympiakos.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Wed, Jun 08, 2022 at 03:59:51PM -0700, David G. Johnston wrote:
> The described behavior didn't even make it into 14.0; and I have no clue
> where such a thing would be documented to guess if the lack was intentional
> or not. I can confirm that as of today the behavior in HEAD is to not
> decompress and recompress during rewrite. Frankly, this seems like the
> expected behavior absent any documentation to the contrary. Whether there
> is a use case for someone to code up a way to get a recompression to happen
> is another matter. Oddly, I kinda expected that "update cmdata set f1 =
> f1;" might do it but alas it does not (...set f1 = f1 || ''... works; I'm
> not quite sure how much worse that, immediately followed by vacuum full,
> would be compared to whatever it would take to do it all during the full
> vacuum...especially if one is changing every single row on the table).
See commit dbab0c0 about the reasons of this choice, with this thread:
https://www.postgresql.org/message-id/20210527003144.xxqppojoiwurc2iz@alap3.anarazel.de
The docs of ALTER TABLE .. SET COMPRESSION include a note on the
matter: https://www.postgresql.org/docs/14/sql-altertable.html
"This does not cause the table to be rewritten, so existing data may
still be compressed with other compression methods."
--
Michael
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2022-06-09 01:10 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-06-08 19:41 BUG #17513: recompressing already-compressed data via VACUUM FULL or CLUSTER does not work PG Bug reporting form <noreply@postgresql.org>
2022-06-08 22:59 ` David G. Johnston <david.g.johnston@gmail.com>
2022-06-09 01:10 ` Michael Paquier <michael@paquier.xyz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox