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

On Tue, Oct 3, 2023 at 9:17 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> On Tue, 2023-10-03 at 09:08 +0200, Dominique Devienne wrote:
> > 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?
>
> The only way to avoid WAL is to use unlogged tables, but they lose their
> data after
> a crash. If you change an unlogged table to a logged table, the whole
> table ends up
> in WAL, so you won't save anything that way.
>

Thanks Laurenz and Michael. I didn't know that. Interesting.
And logical I guess, considering replication.

> The best thing that you can do to reduce the amount of WAL is to TRUNCATE
> and
> populate the table in the same transaction. Then, if you have "wal_level
> = minimal",
> PostgreSQL can forego writing WAL information. But you cannot have
> archive recovery
> and replication with "wal_level = minimal"

No idea about the granularity of wal_level (cluster wide? db wide? more
granular?),
but that doesn't sound practical on a cluster shared with others, I suspect.

It would also force to load all the bytea values client-side, instead of
1-by-1, which
is possible (a few GBs typically, max), but not the way we've coded it.

> > 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?
>
> If you don't see much compression in your toasted data, it is possible
> that you are
> storing data that are already compressed. In that case, PostgreSQL will
> attempt
> compression with its algorithms that are geared at speed rather than good
> compression.
> If it finds that the data grew after compression, it will discard the
> compressed value
> and continue with the original value. To avoid that useless compression
> attempt,
> you should use STORAGE EXTERNAL in such cases.
>

I know for sure the bytea values are not compressed. Again, they are
numerical arrays
(float, double), which don't typically compress well, especially the
mantissa part. So it
sounds like there's no way to examine the hidden TOASTed values for
compression ratio then.
Bummer. Thanks again Laurenz. --DD