Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Tue, Oct 3, 2023 at 6:45 AM Michael Paquier <michael@xxxxxxxxxxx> 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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux