pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedTOAST behavior in 8.3 and 8.4
5+ messages / 3 participants
[nested] [flat]
* TOAST behavior in 8.3 and 8.4
@ 2010-04-13 18:45 Lewis Kapell <lkapell@setonhome.org>
0 siblings, 2 replies; 5+ messages in thread
From: Lewis Kapell @ 2010-04-13 18:45 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: TOAST behavior in 8.3 and 8.4
@ 2010-04-13 19:02 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Lewis Kapell <lkapell@setonhome.org>
1 sibling, 1 reply; 5+ messages in thread
From: Kevin Grittner @ 2010-04-13 19:02 UTC (permalink / raw)
To: pgsql-admin; Lewis Kapell <lkapell@setonhome.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: TOAST behavior in 8.3 and 8.4
@ 2010-04-13 19:22 Alvaro Herrera <alvherre@commandprompt.com>
parent: Lewis Kapell <lkapell@setonhome.org>
1 sibling, 0 replies; 5+ messages in thread
From: Alvaro Herrera @ 2010-04-13 19:22 UTC (permalink / raw)
To: Lewis Kapell <lkapell@setonhome.org>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: TOAST behavior in 8.3 and 8.4
@ 2010-04-13 19:27 Lewis Kapell <lkapell@setonhome.org>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 5+ messages in thread
From: Lewis Kapell @ 2010-04-13 19:27 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: TOAST behavior in 8.3 and 8.4
@ 2010-04-13 19:37 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Lewis Kapell <lkapell@setonhome.org>
0 siblings, 0 replies; 5+ messages in thread
From: Kevin Grittner @ 2010-04-13 19:37 UTC (permalink / raw)
To: Lewis Kapell <lkapell@setonhome.org>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2010-04-13 19:37 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-04-13 18:45 TOAST behavior in 8.3 and 8.4 Lewis Kapell <lkapell@setonhome.org>
2010-04-13 19:02 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-04-13 19:27 ` Lewis Kapell <lkapell@setonhome.org>
2010-04-13 19:37 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-04-13 19:22 ` Alvaro Herrera <alvherre@commandprompt.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox