Re: Modifying TOAST thresholds - Mailing list pgsql-hackers

Simon Riggs wrote:
> On Mon, 2007-04-02 at 22:23 -0400, Tom Lane wrote:
> > Chris Browne <cbbrowne@acm.org> writes:
> > > tgl@sss.pgh.pa.us (Tom Lane) writes:
> > >> ... tuning the TOAST parameters seems like
> > >> something we understand well enough already, we just need to put some
> > >> cycles into testing different alternatives.  I would have no objection
> > >> to someone working on that during April and delivering a final patch
> > >> sometime before beta.
> > 
> > > Here's a "drafty" patch that *tries* to do this using a GUC variable;
> > > it passes some interactive testing.
> 
> Having both default GUC and individual table-level WITH parameters seems
> like the best way to me.

Agreed.

> > I came across a couple of issues while fooling with decoupling
> > TOAST_TUPLE_THRESHOLD from TOAST_MAX_CHUNK_SIZE:
> > 
> > * Should TOAST_TUPLE_TARGET be configurable separately from
> > TOAST_TUPLE_THRESHOLD?  It certainly doesn't make sense for the target
> > to be larger, but perhaps it is sane to want it to be smaller.
> 
> I can't see I'd ever set them differently in practice. Sounds like too
> many people would get confused and set them wrong anyhow.

OK.

> > * There's a hardwired assumption in the system that
> > TOAST_TUPLE_THRESHOLD is invariant: we do not create a toast table at
> > all when we can prove that the maximum tuple width is less than
> > TOAST_TUPLE_THRESHOLD (see needs_toast_table() in toasting.c).
> > Clearly this will not do if TOAST_TUPLE_THRESHOLD can be changed.
> > Should we abandon the notion altogether, and create a toast table
> > anytime the table contains any toastable types?  
> 
> That will create many more catalog entries than we have now, which seems
> not that great a side-effect.

OK, but we need to throw a clear message when the TOAST table needs to
be created by the administrator.

> > Or should we revel
> > in configurability, and allow CREATE TABLE/ALTER TABLE behavior to vary
> > depending on the current threshold setting?  We'd have to fix the
> > toaster routines to not try to push stuff out-of-line when there is no
> > out-of-line to push to ... but I think we probably had better do that
> > anyway for robustness, if we're allowing any variability at all in these
> > numbers.
> 
> Sounds like the best plan.

Agreed.  If you add a GUC without pg_class storage of the value at
CREATE TABLE/ALTER TABLE time, the GUC has to be checked at INSERT time,
meaning if the GUC changes, you might need a TOAST table during an
INSERT, which is going to fail.

The big question is whether this is for 8.3 or 8.4.  I think adding a
GUC just for 8.3, without pg_class storage, will be a problem because
the GUC behavior will change once pg_class storage exists, i.e. GUC will
control at CREATE TABLE/ALTER TABLE rather than at INSERT time.

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