Re: How to force "re-TOAST" after changing STORAGE or COMPRESSION?

On Tue, Oct 3, 2023 at 6:45 AM Michael Paquier <michael(at)paquier(dot)xyz> wrote:

> On Tue, Oct 03, 2023 at 06:31:27AM +0200, Laurenz Albe wrote:
> > On Tue, 2023-10-03 at 12:33 +1100, rob stone wrote:
> >> Would running CLUSTER on the table use the new parameters for the re-
> >> write?
> >
> > No, as far as I know.
>
> Note that under the hoods VACUUM FULL and CLUSTER use the same code
> paths when doing their stuff.
>
> > You'd need something like
> > -- rewrite all tuples
> > UPDATE tab SET id = id;
> > -- get rid of the bloat
> > VACUUM (FULL) tab;
>

OK. I didn't expect this, but I can deal with it. Especially since this is
basically what I'm
doing anyway. Remember the thread on this ML about "chunking" large bytea
values?
Well, this is about trying out several chunk sizes and/or compression, to
find the right
config / tuning for our access patterns. We've already done the
"rechunking", and I'm adding
the changes in compression (and thus storage, when disabling compression).

> I'm afraid so, and get ready for a burst of WAL that depends on the
> size of your relation if you are too aggressive with the updates. You
> could do that in periodic steps, as well.
>

In my case, it's OK not to be transactional, for these experiments. Is
there a way
to lock the table and do the rewriting w/o generating any WAL? I don't have
any experience
with unlogged tables, but should I take an exclusive lock on the table,
switch it to unlogged,
rewrite, and switch it back to logged?

What about my last question about whether storage=extended always being
compressed?
Given that I don't see much compression, at least when looking indirectly
via total-rel-sizes?
Is there a way to evaluate the compression ratios achieved on TOASTED
values?

Thanks, --DD