Re: TOAST usage setting

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

> The shorter-than-normal tuples carrying the last chunk of any particular
> datum are going to result in wasted space to the extent that we can't
> pack them together on a page, but that's true now. Right now, if you
> have a large toasted datum, it mostly will consist of just-under-2K
> tuples that are sized so that there's no noticeable wasted space on a
> page with 4 of them. There isn't any advantage to that compared to one
> just-under-8K tuple AFAICS, and it takes 4 times as much work to insert
> or retrieve 'em.

That will be true if your data are usually larger than 8k. But if your data
are all between TOAST_TUPLE_THRESHOLD and TOAST_MAX_CHUNK_SIZE you won't have
any fragments to put in the remaining space.

If there's no daylight between those two parameters then you'll get an average
amount of wasted space of about half a chunk per page (or perhaps even less).

But if there's a gap and your use case happens to have mostly or entirely data
sized in that gap then you won't have many or even any fragments available to
fill in that space.

As I described, picture a case where you have TOAST_MAX_CHUNK_SIZE set to 8k
and TOAST_TUPLE_THRESHOLD set under 4k and all your datums are 4k+1. Each one
will have to go on its own page, wasting 50% of the space.

If in that worst case it's still faster than having to pay the cost of two
seeks to use a smaller chunk size and get better data density then we know
there's no argument for a smaller chunk size.

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