Re: [GENERAL] How to turn off TOAST on a table/column

Ross J. Reedstrom wrote:
> On Tue, Nov 27, 2001 at 03:52:27PM -0500, Tom Lane wrote:
> > Barry Lind <barry@xythos.com> writes:
> > > So how do I create a table without toast enabled?
> >
> > UPDATE pg_attribute SET attstorage = 'p'
> > WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'mytable')
> > AND attnum > 0
> >
> > would suffice to disable toasting of all columns in 'mytable'.
>
> This would reimpose the max-tuple limit on that table, would it not?
> So trying to store 'too large' a text would error? Definitely one for
> the regression tests, once we've got that ALTER TABLE interface.

    Yes, it would.

>
> >
> > See src/include/pg_attribute.h for documentation of the allowed values
> > for attstorage.
>
> This needs to get into the admin docs. I suppose it's also waiting on the
> ALTER TABLE interface.

    One  thing  I'd  like to add is that people should not be too
    surprised  if  turning  off  toast  will  slow   down   their
    application.

    One nice side effect of toast is, that often especially those
    fields you don't use in the where  clause  get  toasted.  Now
    while  a  query is executed and the tuples travel through the
    system, from the heap through the  filters,  in  and  out  of
    sort,  getting  merged  and  joined,  and  some of them later
    thrown away, you don't need  these  attributes.  If  toasted,
    more  tuples  with  the  key  fields  fit into the blocks, so
    you'll get better cache hit rates and  lesser  disk  IO.  The
    sort  sets  will  be  alot  smaller,  more  sorts can be done
    completely in memory without temp files. The huge  attributes
    will only be pulled if the client wanted them and that at the
    time the result is sent to the client,  by  the  type  output
    function.   And  if  you  update  a  row  and don't touch the
    toasted attribute, the value get's never read from the  disk,
    nor does it get updated.

    Just  to  give a few reasons why I like toast. One day I will
    implement a real BLOB datatype - but probably name it poptart
    :-)


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com