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

Barry Lind <barry(at)xythos(dot)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