Re: Modifying TOAST_TUPLE_THRESHOLD and TOAST_TUPLE_TARGET?

I notice that one of my tables gets TOASTed, about 40% of it. I read about
TOAST and understand the general motivation (get faster access to the
*other* columns). But in my case the big column (~2.5KB fixed width) is a
list (array) of values (not text) and the usage pattern is that I always
access a single row from the table by key and read the full list. So my
guess is TOAST is doing me more damage than good. Right?

I would have liked to disable TOAST (even altogether for my DB) - is there a
direct way to do this? Alternatively I thought of increasing
TOAST_TUPLE_THRESHOLD and TOAST_TUPLE_TARGET from 2KB (I believe this is the
default?) to 4KB? Do I have to change the source (
http://doxygen.postgresql.org/tuptoaster_8h-source.html here ?) and compile
a private version of Postgres?

Thanks, feel free to reply by email.

-- Shaul (info(at)shauldar(dot)com)
--
View this message in context: http://www.nabble.com/Modifying-TOAST_TUPLE_THRESHOLD-and-TOAST_TUPLE_TARGET--tp23982524p23982524.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

On Thu, Jun 11, 2009 at 3:32 PM, Shadar<shauldar(at)gmail(dot)com> wrote:
>
> I notice that one of my tables gets TOASTed, about 40% of it. I read about
> TOAST and understand the general motivation (get faster access to the
> *other* columns). But in my case the big column (~2.5KB fixed width) is a
> list (array) of values (not text) and the usage pattern is that I always
> access a single row from the table by key and read the full list. So my
> guess is TOAST is doing me more damage than good. Right?

It's possible. It might depend on how much wasted space you'll get in
each 8k block if you store them inline. And also on how cpu-bound
versus i/o-bound your database is.

> I would have liked to disable TOAST (even altogether for my DB) - is there a
> direct way to do this? Alternatively I thought of increasing
> TOAST_TUPLE_THRESHOLD and TOAST_TUPLE_TARGET from 2KB (I believe this is the
> default?) to 4KB? Do I have to change the source (
> http://doxygen.postgresql.org/tuptoaster_8h-source.html here ?) and compile
> a private version of Postgres?

Yeah.

There are a few other options.

You could use "ALTER COLUMN SET STORAGE PLAIN" to disable toasting on
that column entirely. This will disable compression as well though. If
any record doesn't fit in an 8kB block you'll get an error.

Alternately you could leave the column alone but drop the toast table
(presumably having truncated the table first). That will leave
compression enabled but force the system to avoid storing things
externally. Again you'll get an error if a record doesn't fit in 8kB
but only after first trying to compress the data.

--
Gregory Stark
http://mit.edu/~gsstark/resume.pdf

Greg Stark <stark(at)enterprisedb(dot)com> wrote:

> You could use "ALTER COLUMN SET STORAGE PLAIN" to disable toasting
on
> that column entirely. This will disable compression as well though.
If
> any record doesn't fit in an 8kB block you'll get an error.
>
> Alternately you could leave the column alone but drop the toast
table
> (presumably having truncated the table first). That will leave
> compression enabled but force the system to avoid storing things
> externally. Again you'll get an error if a record doesn't fit in 8kB
> but only after first trying to compress the data.

Why not use "ALTER COLUMN SET STORAGE MAIN", to allow compression but
avoid external storage?

http://www.postgresql.org/docs/8.3/interactive/sql-altertable.html

-Kevin

Greg Stark-5 wrote:
>
> On Thu, Jun 11, 2009 at 3:32 PM, Shadar<shauldar(at)gmail(dot)com> wrote:
>>
>> I notice that one of my tables gets TOASTed, about 40% of it. I read
>> about
>> TOAST and understand the general motivation (get faster access to the
>> *other* columns). But in my case the big column (~2.5KB fixed width) is a
>> list (array) of values (not text) and the usage pattern is that I always
>> access a single row from the table by key and read the full list. So my
>> guess is TOAST is doing me more damage than good. Right?
>
> It's possible. It might depend on how much wasted space you'll get in
> each 8k block if you store them inline. And also on how cpu-bound
> versus i/o-bound your database is.
>
>>- I/O bound (in cold state), I don't worry about warm state...
>>- So how do I find out, or guesstimate, the effect of TOAST in my case?
>
>> I would have liked to disable TOAST (even altogether for my DB) - is
>> there a
>> direct way to do this? Alternatively I thought of increasing
>> TOAST_TUPLE_THRESHOLD and TOAST_TUPLE_TARGET from 2KB (I believe this is
>> the
>> default?) to 4KB? Do I have to change the source (
>> http://doxygen.postgresql.org/tuptoaster_8h-source.html here ?) and
>> compile
>> a private version of Postgres?
>
> Yeah.
>
> There are a few other options.
>
> You could use "ALTER COLUMN SET STORAGE PLAIN" to disable toasting on
> that column entirely. This will disable compression as well though. If
> any record doesn't fit in an 8kB block you'll get an error.
>
> Alternately you could leave the column alone but drop the toast table
> (presumably having truncated the table first). That will leave
> compression enabled but force the system to avoid storing things
> externally. Again you'll get an error if a record doesn't fit in 8kB
> but only after first trying to compress the data.
>
>>- I don't believe we have any record over 8K. So the 2nd method is best?
When should I drop the TOAST table, before or after my DB is populated? I.e.
Postgres will then move the TOASTED data inside my table?
>
> --
> Gregory Stark
> http://mit.edu/~gsstark/resume.pdf
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
>

--
View this message in context: http://www.nabble.com/Modifying-TOAST_TUPLE_THRESHOLD-and-TOAST_TUPLE_TARGET--tp23982524p23983149.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> Greg Stark <stark(at)enterprisedb(dot)com> wrote:
>> You could use "ALTER COLUMN SET STORAGE PLAIN" to disable toasting on
>> that column entirely. This will disable compression as well though.

> Why not use "ALTER COLUMN SET STORAGE MAIN", to allow compression but
> avoid external storage?

That only discourages pushing a particular column out; it will still
do so if the row exceeds TOAST_TUPLE_THRESHOLD after compression.

I kinda doubt the OP wants it to fail outright for rows over 8K,
so altering TOAST_TUPLE_THRESHOLD seems like the right answer.
Too bad we don't have that set up as a reloption...

regards, tom lane

On Thu, Jun 11, 2009 at 4:03 PM, Shadar<shauldar(at)gmail(dot)com> wrote:
>
>>>- I don't believe we have any record over 8K. So the 2nd method is best?
>
> When should I drop the TOAST table, before or after my DB is populated? I.e.
> Postgres will then move the TOASTED data inside my table?

No it won't. Any data in the toast table will be lost and you'll get
errors if you try to access it.

Incidentally please don't quote back the whole message, just quote the
parts you're responding to. Thanks.

--
Gregory Stark
http://mit.edu/~gsstark/resume.pdf

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
>> Why not use "ALTER COLUMN SET STORAGE MAIN", to allow compression
>> but avoid external storage?
>
> That only discourages pushing a particular column out; it will still
> do so if the row exceeds TOAST_TUPLE_THRESHOLD after compression.

I thought that's the behavior of EXTENDED. The fine manual says "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.)"

If that doesn't mean that it will only use out-of-line storage when
the row doesn't fit in the page, then the manual could use a fix.

-Kevin

On Thu, Jun 11, 2009 at 4:24 PM, Kevin
Grittner<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>
> I thought that's the behavior of EXTENDED.  The fine manual says "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.)"
>
> If that doesn't mean that it will only use out-of-line storage when
> the row doesn't fit in the page, then the manual could use a fix.

I agree that "small enough" could be more precise. As it happens
tuptoaster.c only has one definition of "small enough" which is
whether the record is smaller than TOAST_TUPLE_THRESHOLD.

I wonder actually if this case shouldn't use the block size, not the
target size. That seems like it would be a lot more useful.

--
Gregory Stark
http://mit.edu/~gsstark/resume.pdf

Greg Stark <stark(at)enterprisedb(dot)com> wrote:
> tuptoaster.c only has one definition of "small enough" which is
> whether the record is smaller than TOAST_TUPLE_THRESHOLD.

So what *is* the difference between EXTENDED and MAIN?

-Kevin

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> Greg Stark <stark(at)enterprisedb(dot)com> wrote:
>> tuptoaster.c only has one definition of "small enough" which is
>> whether the record is smaller than TOAST_TUPLE_THRESHOLD.

> So what *is* the difference between EXTENDED and MAIN?

EXTENDED columns get pushed out first. So if you have some EXTENDED
columns and some MAIN columns, it'll give preference to keeping the
MAIN columns in-line. In the OP's case there's only one wide column
so this is of no help.

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
>> So what *is* the difference between EXTENDED and MAIN?
>
> EXTENDED columns get pushed out first. So if you have some EXTENDED
> columns and some MAIN columns, it'll give preference to keeping the
> MAIN columns in-line. In the OP's case there's only one wide column
> so this is of no help.

Got it. The documentation seems less than clear on this. Perhaps the
description of MAIN on this page:

http://www.postgresql.org/docs/8.3/interactive/storage-toast.html

should be changed. How does this sound?:

MAIN allows compression but discourages out-of-line storage.
(Out-of-line storage will be performed only if the row is still too
big after compression and out-of-line storage of EXTENDED and EXTERNAL
columns.)

If there is consensus, I'll put that into patch form.

It seems to me that MAIN might be a more useful option if it was more
aggressive about avoiding out-of-line storage; perhaps only if the row
doesn't fit by itself on a single page? Does anyone else think so?

-Kevin

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> It seems to me that MAIN might be a more useful option if it was more
> aggressive about avoiding out-of-line storage; perhaps only if the row
> doesn't fit by itself on a single page? Does anyone else think so?

I think that's exactly what Greg Stark was proposing. Might be
something to look at for 8.5.

regards, tom lane

On Thu, Jun 11, 2009 at 5:12 PM, Kevin
Grittner<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>
> MAIN allows compression but discourages out-of-line storage.
> (Out-of-line storage will be performed only if the row is still too
> big after compression and out-of-line storage of EXTENDED and
> EXTERNAL columns.)

I had the impression the confusion was over the meaning of "too big"
rather than what "last resort" meant. So this doesn't seem any
clearer.

> It seems to me that MAIN might be a more useful option if it was more
> aggressive about avoiding out-of-line storage; perhaps only if the row
> doesn't fit by itself on a single page?  Does anyone else think so?

Yeah I think we're on the same page there. I do suspect the reason
this documentation is vague on the precise meanings of these options
is precisely because the author expected further tweaks of this nature
-- I don't know if this documentation has changed substantially since
toast was committed.

We've been talking about a number of ideas for making toast more
flexible but there are clearly an infinite number of permutations and
the trick will be figuring out how to present the useful ones without
making things too complicated for the user to control.

--
greg
http://mit.edu/~gsstark/resume.pdf

Greg Stark <stark(at)enterprisedb(dot)com> wrote:
> On Thu, Jun 11, 2009 at 5:12 PM, Kevin
> Grittner<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>>
>> MAIN allows compression but discourages out-of-line storage.
>> (Out-of-line storage will be performed only if the row is still too
>> big after compression and out-of-line storage of EXTENDED and
>> EXTERNAL columns.)
>
> I had the impression the confusion was over the meaning of "too big"
> rather than what "last resort" meant. So this doesn't seem any
> clearer.

Well, in the EXTENDED section I took "too big" to refer back to the
immediately preceding paragraph:

- The TOAST code is triggered only when a row value to be stored in a
- table is wider than TOAST_TUPLE_THRESHOLD bytes (normally 2 kB). The
- TOAST code will compress and/or move field values out-of-line until
- the row value is shorter than TOAST_TUPLE_TARGET bytes (also
- normally 2 kB) or no more gains can be had.

I think I misunderstood the MAIN section precisely because it didn't
use the "too big" term at all, but instead said that it didn't allow
out-of-line storage except "as a last resort when there is no other
way to make the row small enough." If it's going to be conditioned on
the same limit, I would find it less confusing to stick with the same
terminology. That said, if you still find my wording confusing, it's
apparently not good enough. Suggestion?

>> It seems to me that MAIN might be a more useful option if it was
>> more aggressive about avoiding out-of-line storage; perhaps only if
>> the row doesn't fit by itself on a single page?
>
> Yeah I think we're on the same page there.

Cool.

> We've been talking about a number of ideas for making toast more
> flexible but there are clearly an infinite number of permutations
> and the trick will be figuring out how to present the useful ones
> without making things too complicated for the user to control.

So it would be premature to submit a patch for changing this behavior?
(I was thinking that if people could agree on this, it might be
something I could work in some evening, for some 8.5 commitfest.
If the OP is brave enough to use it, it might possibly solve the
problem.)

-Kevin

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
>>> It seems to me that MAIN might be a more useful option if it was
>>> more aggressive about avoiding out-of-line storage; perhaps only if
>>> the row doesn't fit by itself on a single page?
>>
>> Yeah I think we're on the same page there.

> Cool.

>> We've been talking about a number of ideas for making toast more
>> flexible but there are clearly an infinite number of permutations
>> and the trick will be figuring out how to present the useful ones
>> without making things too complicated for the user to control.

> So it would be premature to submit a patch for changing this behavior?

No, I don't think so, since there's no apparent need for a control knob.
You just change the limit that's applied during the last phase of
toast_insert_or_update.

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:

>> So it would be premature to submit a patch for changing this
behavior?
>
> No, I don't think so, since there's no apparent need for a control
knob.
> You just change the limit that's applied during the last phase of
> toast_insert_or_update.

Proposed patch attached.

-Kevin

Guys,

I appreciate your intentions to treat this more flexibly in a future
release. But I'm not sure I got a reply to my original questions, about the
existing 8.3 version:

1. Am I correct in assuming that in my case the TOAST mechanism probably
results in a performance hit, not boost, and is there a way to measure this
impact?

2. How can I disable TOAST? Only by changing TOAST_TUPLE_THRESHOLD and
TOAST_TUPLE_TARGET in the include file and compiling a private version?

Thanks,

-- Shaul
--
View this message in context: http://www.nabble.com/Modifying-TOAST_TUPLE_THRESHOLD-and-TOAST_TUPLE_TARGET--tp23982524p23996728.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

Shadar <shauldar(at)gmail(dot)com> wrote:

> 1. Am I correct in assuming that in my case the TOAST mechanism
> probably results in a performance hit, not boost,

Possibly, but you didn't give enough information to be sure. One
important factor would be how often the table in question has updates
or deletes. If those are very frequent, you might find that TOAST
improves performance, because it won't have to read as many disk pages
to check visibility for the reading transaction. If it is a table
with few updates and deletes and you really do always read the array,
it might be faster to avoid the out-of-line storage aspect of TOAST.
(The compression might still be valuable, if it often squeezes two
rows per page rather than one.)

> and is there a way to measure this impact?

Like many performance issues, the only way to be absolutely sure is to
benchmark it both ways with a good simulation of your expected load.
(Testing one aspect, such as just the SELECTs, in the absence of the
modification load you expect, will not really give you good
information on this point.)

> 2. How can I disable TOAST? Only by changing TOAST_TUPLE_THRESHOLD
> and TOAST_TUPLE_TARGET in the include file and compiling a private
> version?

There have been several suggestions already. If you're going to
modify the source to deal with this, you might want to look at the
patch I proposed, and consider something like that. Keep in mind that
it hasn't been reviewed, it's just a suggestion I threw out there as a
possibility, with all the usual "use at your own risk" caveats.

With that patch, you would avoid errors on values which don't fit on a
single page, but when values are set for a column flagged with ALTER
TABLE SET STORAGE MAIN, they will not be moved out-of-line (into the
separate TOAST table) unless the row won't fit in a single page
without doing so.

-Kevin

> 2. How can I disable TOAST? Only by changing TOAST_TUPLE_THRESHOLD
> and TOAST_TUPLE_TARGET in the include file and compiling a private
> version?

>> There have been several suggestions already.

Yes, but other have commented that these (e.g. dropping TOAST table) might
not work and even lose data, so I was left confused as to what would work...

>> If you're going to modify the source to deal with this...

Again: do I have a choice? I'd rather not touch the code.
Thanks,

-- Shaul

--
View this message in context: http://www.nabble.com/Modifying-TOAST_TUPLE_THRESHOLD-and-TOAST_TUPLE_TARGET--tp23982524p24000355.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

Shadar <shauldar(at)gmail(dot)com> wrote:

>>> There have been several suggestions already.
>
> Yes, but other have commented that these (e.g. dropping TOAST table)
> might not work and even lose data, so I was left confused as to what
> would work...

Well, dropping the TOAST table once you have data in it will lose
data. The main problem with the techniques which don't involve
modifying the source code are that you can't force the column to be
stored in-line without getting an error on an attempt to store a value
which makes the row too big to fit on a single page.

> Again: do I have a choice? I'd rather not touch the code.

You've got lots of choices. For starters, I'm not at all sure you
will even have a performance benefit from inlining the data. You
could choose to just let PostgreSQL work the way most people do. A
couple options have been mentioned if you are sure your rows will
always fit on one page. None of these involve touching the source
code.

I threw together the patch to give you another option. Touching the
code isn't so scary once you get the hang of building from source.
Obviously you want test carefully before putting custom code into
production.

-Kevin

I wrote:

> Proposed patch attached.

That first version was of the "minimally invasive" variety, to stress
how little I was changing and minimize the chance that I would make
some dumb error; however, it involved copy/paste of a few lines which
were already in a source file twice. Attached is what I hope is a
functionally identical patch, with minor refactoring. I think it
results in more readable code.

-Kevin

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> I wrote:
>> Proposed patch attached.

> That first version was of the "minimally invasive" variety, to stress
> how little I was changing and minimize the chance that I would make
> some dumb error; however, it involved copy/paste of a few lines which
> were already in a source file twice. Attached is what I hope is a
> functionally identical patch, with minor refactoring. I think it
> results in more readable code.

Applied with some editorialization on the comments. I also tweaked the
user-facing TOAST documentation thusly:

*** doc/src/sgml/storage.sgml 17 Jun 2009 21:58:49 -0000 1.29
--- doc/src/sgml/storage.sgml 22 Jul 2009 01:17:23 -0000
***************
*** 347,353 ****
<literal>MAIN</literal> 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.)
</para>
</listitem>
</itemizedlist>
--- 347,353 ----
<literal>MAIN</literal> 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 to fit on a page.)
</para>
</listitem>
</itemizedlist>

regards, tom lane