Thread: trigger TOASTing quicker?

Hi all,

I'm in the planning stages of replacing a MySQL DB using ISAM tables
with PostgreSQL 8.1.x on Suse 10.0.  I think that sentence right there
will tell you why!

Anyway,  one of the columns in one of the tables is a big chunk of XML
(500 to 500KB).  I'm not normally a fan of that kind of thing, much
preferring storing such things in the file system.  But I see that
TOASTing that column will address most of my concerns.  On to my
questions:

TOASTing is automatic?  I don't have to code anything for it?  Plain
vanilla SQL99 will work with it?  I have terrible memories of Oracle's
LONG RAW columns....

Assuming the above is true, is there anyway to get a column's data to
TOAST at a threshold smaller than the default of 2000B?  For example, I
really would like any amount of data stored in the XML column to be
TOASTed.  So I would like to be able to say something like
ALTER TABLE foo ALTER COLUMN xml SET STORAGE EXTENDED;
ALTER TABLE foo ALTER COLUMN xml SET EXTENDED_THRESHOLD 500;


tia,
arturo

On Thu, May 11, 2006 at 11:15:16PM -0400, Perez wrote:
> TOASTing is automatic?  I don't have to code anything for it?  Plain
> vanilla SQL99 will work with it?  I have terrible memories of Oracle's
> LONG RAW columns....

TOAST is automatic, yes. It's also transparent (ie there's no
functionality difference).

> Assuming the above is true, is there anyway to get a column's data to
> TOAST at a threshold smaller than the default of 2000B?  For example, I
> really would like any amount of data stored in the XML column to be
> TOASTed.  So I would like to be able to say something like
> ALTER TABLE foo ALTER COLUMN xml SET STORAGE EXTENDED;
> ALTER TABLE foo ALTER COLUMN xml SET EXTENDED_THRESHOLD 500;

I beleive you can set it to EXTERNAL, which it will always toast.

Have a ncie day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Martijn van Oosterhout <kleptog@svana.org> writes:
> I beleive you can set it to EXTERNAL, which it will always toast.

I don't think that will help; if the overall row size is below the
threshold, the code is not going to pick it apart to see if anything
is saying "toast me anyway!".  And it shouldn't do so IMHO; the overall
cost in cycles would be catastrophic, because most tables aren't going
to have such columns.

There was discussion just yesterday of making the TOAST thresholds
more configurable, but I didn't see anyone stepping up with a
concrete proposal (much less volunteering to create a patch).

            regards, tom lane

In article <24281.1147444664@sss.pgh.pa.us>,
 tgl@sss.pgh.pa.us (Tom Lane) wrote:

> Martijn van Oosterhout <kleptog@svana.org> writes:
> > I beleive you can set it to EXTERNAL, which it will always toast.
>
> I don't think that will help; if the overall row size is below the
> threshold, the code is not going to pick it apart to see if anything
> is saying "toast me anyway!".  And it shouldn't do so IMHO; the overall
> cost in cycles would be catastrophic, because most tables aren't going
> to have such columns.
>
> There was discussion just yesterday of making the TOAST thresholds
> more configurable, but I didn't see anyone stepping up with a
> concrete proposal (much less volunteering to create a patch).
>
>             regards, tom lane


Well, I suppose I could blank pad the column :-)  That would compress
really well, too.  Or is that exceptionally evil?

tia,
arturo