How to re-TOAST specific data after changing compression?

After changing the default_toast_compression from pglz to lz4 I would like to update existing data to the new, faster, compression.

The only way appears to be to dump the entire table and recreate it from scratch, which is not ideal for very large and/or in-use databases.

I found this mailing list thread which suggests that UPDATE tab SET id = id would do it, but testing shows nothing has happened.

postgres=# SHOW default_toast_compression;
 default_toast_compression
---------------------------
 lz4
(1 row)

postgres=# SELECT pg_column_compression(data) FROM pg_largeobject WHERE loid=49946 AND pageno=0;
 pg_column_compression
-----------------------
 pglz
(1 row)

postgres=# UPDATE pg_largeobject SET data=data WHERE loid=49946 AND pageno=0;
UPDATE 1

postgres=# SELECT pg_column_compression(data) FROM pg_largeobject WHERE loid=49946 AND pageno=0;
 pg_column_compression
-----------------------
 pglz
(1 row)

You need to force a decompresson/recompression cycle, otherwise it optimizes this operation away and just copies "pointers" instead.

UPDATE pg_largeobject SET data=data||'' WHERE loid=49946 AND pageno=0;

StackExchange Database Administrators Q#334160, answer score: 3