Re: TOAST condition for column size

On 2021-01-19 19:32, Amit Kapila wrote:
> On Mon, Jan 18, 2021 at 7:53 PM torikoshia
> Because no benefit is to be expected by compressing it. The size will
> be mostly the same. Also, even if we somehow try to fit this data via
> toast, I think reading speed will be slower because for all such
> columns an extra fetch from toast would be required. Another thing is
> you or others can still face the same problem with 17-byte column
> data. I don't this is the right way to fix it. I don't have many good
> ideas but I think you can try by (a) increasing block size during
> configure, (b) reduce the number of columns, (c) create char columns
> of somewhat bigger size say greater than 24 bytes to accommodate your
> case.
> 
> I know none of these are good workarounds but at this moment I can't
> think of better alternatives.

Thanks for your explanation and workarounds!



On 2021-01-20 00:40, Tom Lane wrote:
> Dilip Kumar <dilipbalaut@gmail.com> writes:
>> On Tue, 19 Jan 2021 at 6:28 PM, Amit Kapila <amit.kapila16@gmail.com> 
>> wrote:
>>> Won't it be safe because we don't align individual attrs of type
>>> varchar where length is less than equal to 127?
> 
>> Yeah right,  I just missed that point.
> 
> Yeah, the minimum on biggest_size has nothing to do with alignment
> decisions.  It's just a filter to decide whether it's worth trying
> to toast anything.
> Having said that, I'm pretty skeptical of this patch: I think its
> most likely real-world effect is going to be to waste cycles (and
> create TOAST-table bloat) on the way to failing anyway.  I do not
> think that toasting a 20-byte field down to 18 bytes is likely to be
> a productive thing to do in typical situations.  The given example
> looks like a cherry-picked edge case rather than a useful case to
> worry about.

I agree with you, it seems only work when there are many columns with
19 ~ 23 bytes of data and it's not a normal case.
I'm not sure, but a rare exception might be some geographic data.
That's the situation I heard that problem happened.


Regards,

--
Atsushi Torikoshi