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 whol table ends up
in WAL, so you won't save anything that way.
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".
> 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.
Yours,
Laurenz Albe