Обсуждение: What's the difference between SET STORAGE MAIN and EXTENDED?

Hi,

according to
http://www.postgresql.org/docs/8.2/interactive/storage-toast.html:

EXTENDED allows both compression and out-of-line storage.
This is the default for most TOAST-able data types.
Compression will be attempted first, then out-of-line storage if the row
is still too big.

MAIN allows compression but not out-of-line storage.
(Actually, out-of-line storage will still be performed for such columns,
but only as a last resort when there is no other way to make the row
small enough.)

At the end of the day, the behaviour is the same, isn't it?

--
----------------------------------
Zoltán Böszörményi
Cybertec Geschwinde & Schönig GmbH
http://www.postgresql.at/

Zoltan Boszormenyi <zb@cybertec.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.  Normally we push the widest (remaining) column out,
and repeat, until the tuple is small enough.  But MAIN columns stay
in, until there are no EXTENDED columns left.

            regards, tom lane

Tom Lane írta:
> Zoltan Boszormenyi <zb@cybertec.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.  Normally we push the widest (remaining) column out,
> and repeat, until the tuple is small enough.  But MAIN columns stay
> in, until there are no EXTENDED columns left.
>
>             regards, tom lane
>

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.

Best regards,
Zoltán Böszörményi

--
----------------------------------
Zoltán Böszörményi
Cybertec Geschwinde & Schönig GmbH
http://www.postgresql.at/

Zoltan Boszormenyi <zb@cybertec.at> writes:
> Tom Lane =EDrta:
>> Zoltan Boszormenyi <zb@cybertec.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.

            regards, tom lane

On 9/7/2007 11:45 AM, Tom Lane wrote:
> Zoltan Boszormenyi <zb@cybertec.at> writes:
>> Tom Lane =EDrta:
>>> Zoltan Boszormenyi <zb@cybertec.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@Yahoo.com #