Thread: How to turn off TOAST on a table/column

How to turn off TOAST on a table/column

From
Barry Lind
Date:
I am looking into some performance issues with an application I have.  I
want to do some testing to see how much overhead TOAST adds to my
application.  I have a table that performs a similar function to the
pg_largeobject table.  I have noticed that pg_largeobject doesn't have
toast enabled (i.e. reltoastrelid is 0).  However when I create my table
it always gets a value for reltoastrelid.  Since pg_largeobject is
created without toast, I am assuming this is intentional and that for
certain classes of tables it may make sense not to toast the tuples.
Which makes sense because inserting into the toast table will involve
extra disk IOs and if the tuple would have fit into the base table these
extra IOs could be avoided.

So how do I create a table without toast enabled?  I have looked through
the docs for 'create table' and didn't see anything that indicates this
is possible.  Is there some undocumented syntax?

thanks,
--Barry



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

From
Tom Lane
Date:
Barry Lind <barry@xythos.com> writes:
> So how do I create a table without toast enabled?

Unless you want to muck with the backend code, the only way to create
a table that has no toast table attached is to declare columns that
the backend can prove to itself will never add up to more than BLCKSZ
space per tuple.  For example, use varchar(n) not text.  (If you've got
MULTIBYTE enabled then that doesn't work either, since the n is
measure in characters not bytes.)

However, the mere existence of a toast table doesn't cost anything
(except for some increase of the time for CREATE TABLE).  What you
probably really want to do is turn on and off the *use* of the toast
table.  Which you can do by mucking with the attstorage attributes of
the table columns.  I don't think anyone's gotten round to providing
a nice clean ALTER TABLE interface, but a quick

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'.

See src/include/pg_attribute.h for documentation of the allowed values
for attstorage.

            regards, tom lane

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

From
"Ross J. Reedstrom"
Date:
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?
>
> Unless you want to muck with the backend code, the only way to create
> a table that has no toast table attached is to declare columns that
> the backend can prove to itself will never add up to more than BLCKSZ
> space per tuple.  For example, use varchar(n) not text.  (If you've got
> MULTIBYTE enabled then that doesn't work either, since the n is
> measure in characters not bytes.)
>
> However, the mere existence of a toast table doesn't cost anything
> (except for some increase of the time for CREATE TABLE).  What you
> probably really want to do is turn on and off the *use* of the toast
> table.  Which you can do by mucking with the attstorage attributes of
> the table columns.  I don't think anyone's gotten round to providing
> a nice clean ALTER TABLE interface, but a quick
>
> 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.

>
> 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.

Ross


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

From
Tom Lane
Date:
"Ross J. Reedstrom" <reedstrm@rice.edu> writes:
>> 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?

Right.  Presumably, that's what Barry wants to test.  In practice the
other values are more likely to be useful (for toastable datatypes
that is).

>> 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.

Yeah.  Right now it's too easy to shoot yourself in the foot (for
example, you mustn't set attstorage to anything but 'p' for a
non-varlena datatype).  So we haven't wanted to document the
UPDATE-pg_attribute approach.

            regards, tom lane

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

From
Jan Wieck
Date:
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