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

From: Jan Wieck <janwieck(at)yahoo(dot)com>
To: "Ross J(dot) Reedstrom" <reedstrm(at)rice(dot)edu>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Barry Lind <barry(at)xythos(dot)com>, pgsql-general(at)postgresql(dot)org, PostgreSQL Development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [HACKERS] How to turn off TOAST on a table/column
Date: 2001-12-05 22:38:06
Message-ID: 200112052238.fB5Mc6002269@saturn.jw.home
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

Ross J. Reedstrom wrote:
> On Tue, Nov 27, 2001 at 03:52:27PM -0500, Tom Lane wrote:
> > Barry Lind <barry(at)xythos(dot)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(at)Yahoo(dot)com #

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Steve Wolfe 2001-12-05 22:38:23 Re: Sparc v Intel
Previous Message Doug McNaught 2001-12-05 22:35:37 Re: Sparc v Intel

Browse pgsql-hackers by date

  From Date Subject
Next Message Doug McNaught 2001-12-05 22:38:19 Re: Licensing
Previous Message Tom Lane 2001-12-05 22:23:58 Re: database system was interrupted at...