Re: Modifying TOAST thresholds

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.

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

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

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

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