Re: TOAST usage setting

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

> "Zeugswetter Andreas ADI SD" <ZeugswetterA(at)spardat(dot)at> writes:
>> My expectation would be, that we want to allow a toast tuple to fill a
>> whole page (TOAST_TUPLES_PER_PAGE = 1),

I thought that previously but then I started thinking maybe that's not true.

Picture toasting a whole bunch of 5-7k data. If your toast chunk size is 2k
then each page will be more or less packed full. Either with four 2k chunks or
fewer full size chunks and one or more final fragments.

Whereas if you set toast_tuples_per_page to 8k then the only option for
Postgres will be to put each datum in its own page and waste 1-3k on every
page.

Perhaps that's too artificial a case. If you have a full range of datum sizes
from 2-8k it ought to be able to pack more densely and especially if you have
data over 8k and so you have final chunks under 2k to fill out the free space.

Moreover, the data density might matter less for the random access type access
pattern of toast tables than the reduction in the number of seeks. We know
from tests that data density does matter for random access OLTP performance
due to cache effectiveness. But still it seems it would only take one extra
seek for a 2k chunk placed on a different page to swamp any effect the wasted
space would cause.

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