Re: Modifying TOAST_TUPLE_THRESHOLD and TOAST_TUPLE_TARGET?

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.