Re: Proposed adjustments in MaxTupleSize andtoastthresholds - Mailing list pgsql-hackers

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@2ndquadrant.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@momjian.us>          http://momjian.us EnterpriseDB
http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +