Re: Proposed adjustments in MaxTupleSize andtoastthresholds

On Mon, 2007-02-05 at 19:18 -0500, Jan Wieck wrote:
> 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.

Yes, thats the main use case.

> 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.

Well, sounds like we both want the same thing. The only discussion seems
to be about user interface.

Setting it per column is much better for very fine tuning, but setting
them in isolation doesn't help decide what to do when you have lots of
medium length strings where the sum exceeds the toast target.

IMHO it would be better to have an col-level "storage priority" (default
0) and then an table-level settable toast target. So we start applying
the storage handling mechanisms on the highest priority columns and keep
going in descending order until we are under the limit for the table.

ALTER TABLE foo
ALTER COLUMN foocol
SET STORAGE EXTERNAL PRIORITY 5
WITH
(toast_target = 400); /* must be MAXALIGNed value */

Equal priorities are allowed, in which case lowest attribute id wins,
i.e. current behaviour remains the default.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com