Shadar <shauldar@gmail.com> wrote:
> 1. Am I correct in assuming that in my case the TOAST mechanism
> probably results in a performance hit, not boost,
Possibly, but you didn't give enough information to be sure. One
important factor would be how often the table in question has updates
or deletes. If those are very frequent, you might find that TOAST
improves performance, because it won't have to read as many disk pages
to check visibility for the reading transaction. If it is a table
with few updates and deletes and you really do always read the array,
it might be faster to avoid the out-of-line storage aspect of TOAST.
(The compression might still be valuable, if it often squeezes two
rows per page rather than one.)
> and is there a way to measure this impact?
Like many performance issues, the only way to be absolutely sure is to
benchmark it both ways with a good simulation of your expected load.
(Testing one aspect, such as just the SELECTs, in the absence of the
modification load you expect, will not really give you good
information on this point.)
> 2. How can I disable TOAST? Only by changing TOAST_TUPLE_THRESHOLD
> and TOAST_TUPLE_TARGET in the include file and compiling a private
> version?
There have been several suggestions already. If you're going to
modify the source to deal with this, you might want to look at the
patch I proposed, and consider something like that. Keep in mind that
it hasn't been reviewed, it's just a suggestion I threw out there as a
possibility, with all the usual "use at your own risk" caveats.
With that patch, you would avoid errors on values which don't fit on a
single page, but when values are set for a column flagged with ALTER
TABLE SET STORAGE MAIN, they will not be moved out-of-line (into the
separate TOAST table) unless the row won't fit in a single page
without doing so.
-Kevin