Re: TOASTing smaller things

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> I would suggest that *all* of those TODOs are premature in the absence
> of experimental evidence about the effect of varying the parameters.
> If we end up finding out that the existing settings are about right
> anyway across a range of test cases, who needs more knobs? We've got
> too many mostly-useless knobs already.

Isn't it obvious that the "right" value is going to depend extraordinarily
heavily on the precise usage pattern though?

A typical table with 100-byte columns which are normally read with the rest of
the columns, sometimes in sequential scans or updates, will find TOASTing them
counter-productive as it t urns those all into additional random access i/o
and extra inserts and deletes.

Another system with a dozen 100-byte columns that are virtually never accessed
and a handful of heavily-updated integer columns will benefit heavily from
TOAST as it changes the table from an awfully performing 5-6 tuple/page table
into a tremendously performing 100+ tuple/page table.

We're not ever going to be able to predict when data is being stored what
future usage pattern to expect, at least not realistically. The best we can
hope for is to find the tipping point at which the cost if we guess wrong is
some tolerable level of pain and set that to be the default and giving the
tools to the user to obtain the benefit in the cases where he knows it'll
help.

Or perhaps TOAST is the wrong kind of vertical partitioning for this. Perhaps
we should be using TOAST to deal with the "large datum" problem and have a
different looking tool entirely for the "vertical partitioning rarely used
columns" problem.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com