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/
Show quoted text
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