Re: TOAST usage setting

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Zeugswetter Andreas ADI SD <ZeugswetterA(at)spardat(dot)at>
Cc: Gregory Stark <stark(at)enterprisedb(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: TOAST usage setting
Date: 2007-06-01 17:50:12
Message-ID: 200706011750.l51HoCs19159@momjian.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Zeugswetter Andreas ADI SD wrote:
> It is good. It shows, that we even see a small advantage in the
> everything cached case.
>
> What we don't have yet is numbers for whether EXTERN_TUPLES_PER_PAGE=1
> substantially increases the toast table size for real life scenarios,
> what happens in the worst case (~48% wastage compared to previous 12%),
> and whether 1 row per page works well with autovacuum ?
>
> The bad case (with EXTERN_TUPLES_PER_PAGE=1) is when most toast tuples
> have a size over TOAST_MAX_CHUNK_SIZE_for_2+1 but enough smaller than a
> page that we care about the wasteage. Maybe we can special case that
> range.
> Maybe determine (and lock) the freespace of any cheap-to-get-at non
> empty page (e.g. the current insert target page) and splitting the toast
> data there.

I think we can look at the BSD file system as a parallel. It has 8k
blocks, with 2k fragments. Files > 6k get a full block, and smaller
files are made up of full fragments.

The big difference is that the BSD file system only allocates in 2k or
8k chunks, while use a single chunk size. However, we allow the filling
of arbitrary free space in an 8k block, which is particularly important
for saving the trailing data that doesn't fill a full chunk.

I think the long-term solution is to go to a 2k/8k fragment/block model,
but that isn't going to happen for 8.3.

The big question is do we want to drop the target tuple size down to
512, and increase the chunk size to 8k for 8.3? Dropping the tuple size
down to 512 is going to give us some smaller TOAST values to fill in
free space created by the 8k chuck size, assuming you have both types of
values in the table. Do we want to increase the access time of long
TOAST by 6% if it means having more wasted space for lots of 4.1k
values?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Florian G. Pflug 2007-06-01 18:06:03 Re: Do we need a TODO? (was Re: Concurrently updating anupdatable view)
Previous Message Bruce Momjian 2007-06-01 16:38:09 Re: TOAST usage setting