Re: Modifying TOAST thresholds

tgl(at)sss(dot)pgh(dot)pa(dot)us (Tom Lane) writes:
> 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.
>
> 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.

In the longer run, it would be desirable for there to be by-table
configurability. Ergo my use of the word "default" in the variable
name; that default can remain relevant even in a future 8.4
enhancement.

I'm not sure what to prefer with regards to TOAST_TUPLE_TARGET; as you
say, it oughtn't be larger than the THRESHOLD value, but I'm not sure
how to rationally set it to a specific lower value.

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

In the 8.3 context, it seems to me that "simplicity rulez."

In some future version, it would be attractive to have this all
variable on a table by table basis; it would at present seem
preferable for the default behaviour to be as little divergent from
past behaviour as possible.

I think I'd be willing to live with the logic that there's no toast
table defined if it was "proven" at create time that we couldn't need
TOAST. That would conform with present behaviour, and remains simple.

The other logical option would be to always create the TOAST table if
there exist extendible columns.

Those two seem to be the options that are most rational to choose
between. I'm happy to defer to well-argued opinions on the matter...
--
(reverse (concatenate 'string "moc.enworbbc" "@" "enworbbc"))
http://cbbrowne.com/info/lsf.html
"Please, Captain. Not in front of the Klingons."
-- Leonard Nimoy as Spock in Star Trek V, The Final Frontier