Re: Modifying TOAST thresholds

Simon Riggs wrote:
> On Mon, 2007-04-02 at 22:23 -0400, Tom Lane wrote:
> > Chris Browne <cbbrowne(at)acm(dot)org> writes:
> > > tgl(at)sss(dot)pgh(dot)pa(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

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