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
