Re: Modifying TOAST_TUPLE_THRESHOLD and TOAST_TUPLE_TARGET?

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