Re: Proposed adjustments in MaxTupleSize and toastthresholds

On 2/5/2007 11:52 AM, Tom Lane wrote:
> "Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
>> Sounds like a good time to suggest making these values configurable,
>> within certain reasonable bounds to avoid bad behaviour.
>
> Actually, given what we've just learned --- namely that choosing these
> values at random is a bad idea --- I'd want to see a whole lot of
> positive evidence before adding such a configuration knob.

Some of the evidence is TOAST itself. Every time you do not SET a column
that has been toasted into external storage during an UPDATE, you win
because the columns data isn't read during the scan for the row to
update, it isn't read during heap_update(), it isn't actually updated at
all (the toast reference is copied as is and the external value reused),
and not a single byte of the external data is bloating WAL. If someone
knows that 99% of their updates will not hit certain text columns in
their tables, actually forcing them to be compressed no matter what and
to be stored external if they exceed 100 bytes will be a win.

Of course, this is a bit different from Simon's approach. What I
describe here is a per pg_attribute configuration to enforce a certain
new toaster behavior. Since we already have something that gives the
toaster a per column cluestick (like not to bother trying to compress),
it might be much easier to implement then Simon's proposal. It would
require that the toaster goes over the initial heap tuple for those
specially configured columns even if the tuple is below the toast
threshold, which suggests that a pg_class.relhasspecialtoastneeds could
be useful. But I think as for fine tuning capabilities, a column
insensitive maximum tuple size is insufficient anyway.

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 #