Re: What's the difference between SET STORAGE MAIN and EXTENDED?

On 9/7/2007 11:45 AM, Tom Lane wrote:
> Zoltan Boszormenyi <zb(at)cybertec(dot)at> writes:
>> Tom Lane =EDrta:
>>> Zoltan Boszormenyi <zb(at)cybertec(dot)at> writes:
>>>> At the end of the day, the behaviour is the same, isn't it?
>>>
>>> No, there's a difference in terms of the priority for pushing this
>>> column out to toast storage, versus pushing other columns of the row
>>> out to toast.
>
>> Thanks very much for clarifying.
>
>> I was thinking of a binary data that wouldn't fit
>> into the maximum inline tuple size. In this case
>> both MAIN and EXTENDED end up compressed
>> and out-of-line. I didn't consider having multiple
>> bytea or text columns filled with small amount of data.
>
> It'd be pretty unwise to mark a column MAIN if it's likely to contain
> wide values ("wide" meaning more than 1K or so). As you say, it'll
> still get toasted --- but not until after everything else in the row has
> been toasted, even quite narrow values that happen to be of toastable
> types.

Additionally, EXTENDED means that the toaster tries to get the tuple
down to a 1/4 blocksize. With MAIN, it won't do so.

MAIN storage strategy would be for wide columns that you *always* touch
in *every* select *and* update and where the access pattern is always
resulting in an index scan. Only in that case, you save from having the
value right in the main tuple and don't need to pull it from the toast
table and also don't lose the optimization of reusing external toast
values if they aren't touched on update.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #