Re: Proposed adjustments in MaxTupleSize andtoastthresholds

Added to TODO:

* Consider allowing configuration of TOAST thresholds

http://archives.postgresql.org/pgsql-hackers/2007-02/msg00213.php

---------------------------------------------------------------------------

Simon Riggs wrote:
> 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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +