Обсуждение: TOAST behavior in 8.3 and 8.4

I have a table with a text column, wherein most values range between 800
and 1700 bytes.  The data should be highly compressible, however these
values appear to be too small to trigger the TOAST mechanism - I
understand the default value of TOAST_TUPLE_THRESHOLD is about 2kb.

This table is by far the largest in our database (1006450 rows;
pg_relation_size() gives 1580 MB), and I am keen to get the data
compressed.  We compile from source and are currently running 8.3.10.
Based on some discussions I found in the archives, I thought it might be
worthwhile to lower TOAST_TUPLE_THRESHOLD and rebuild.

However, I checked to see whether the behavior of TOAST had been changed
in 8.4, since I am hoping to upgrade soon.  I found the following
comment in the release notes:

"Consider TOAST compression on values as short as 32 bytes (previously
256 bytes)"

I don't understand what that '256 bytes' refers to.  That is a far cry
from 2kb.  I would be grateful if anyone can fill in the evident gap in
my knowledge here.

--

Thank you,

Lewis Kapell
Computer Operations
Seton Home Study School

Lewis Kapell <lkapell@setonhome.org> wrote:

> I don't understand what that '256 bytes' refers to.  That is a far
> cry from 2kb.  I would be grateful if anyone can fill in the
> evident gap in my knowledge here.

It doesn't try to compress anything unless the tuple (row instance)
as a whole is above TOAST_TUPLE_THRESHOLD.  In trying to reduce the
tuple size, it won't consider compressing column values below a
certain size.  That is where the 256 versus 32 bytes comes in.

-Kevin

Lewis Kapell wrote:
> I have a table with a text column, wherein most values range between
> 800 and 1700 bytes.  The data should be highly compressible, however
> these values appear to be too small to trigger the TOAST mechanism -
> I understand the default value of TOAST_TUPLE_THRESHOLD is about
> 2kb.


> However, I checked to see whether the behavior of TOAST had been
> changed in 8.4, since I am hoping to upgrade soon.  I found the
> following comment in the release notes:
>
> "Consider TOAST compression on values as short as 32 bytes
> (previously 256 bytes)"

The 2kB are compared against tuple size.  The 32 bytes (previously 256)
are compared against a single column value.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Thanks for the clarification.

In tuptoaster.h, just above the declaration of TOAST_TUPLES_PER_PAGE,
there is a comment which begins: "while these can be modified without
initdb..."

Does this mean that if I reduce the value of TOAST_TUPLE_THRESHOLD and
rebuild, I can use an existing database cluster without having to run
initdb again?


On 4/13/2010 3:02 PM, Kevin Grittner wrote:
> Lewis Kapell<lkapell@setonhome.org>  wrote:
>
>> I don't understand what that '256 bytes' refers to.  That is a far
>> cry from 2kb.  I would be grateful if anyone can fill in the
>> evident gap in my knowledge here.
>
> It doesn't try to compress anything unless the tuple (row instance)
> as a whole is above TOAST_TUPLE_THRESHOLD.  In trying to reduce the
> tuple size, it won't consider compressing column values below a
> certain size.  That is where the 256 versus 32 bytes comes in.
>
> -Kevin

Lewis Kapell <lkapell@setonhome.org> wrote:

> In tuptoaster.h, just above the declaration of
> TOAST_TUPLES_PER_PAGE, there is a comment which begins: "while
> these can be modified without initdb..."
>
> Does this mean that if I reduce the value of TOAST_TUPLE_THRESHOLD
> and rebuild, I can use an existing database cluster without having
> to run initdb again?

Yes, but it's *possible* that some table which would now seem to
qualify for a toast table, based on the new threshold, would not yet
have one. That would be benign; it just wouldn't be able to toast
values for that table unless you recreated it.  You can check
pg_class to find out which tables currently have toast tables.

-Kevin