Thread: How to turn off TOAST on a table/column
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
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
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
"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
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