Re: Proposed adjustments in MaxTupleSize and toastthresholds

From: "Simon Riggs" <simon(at)2ndquadrant(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-hackers(at)postgreSQL(dot)org>, "Jan Wieck" <JanWieck(at)Yahoo(dot)com>, "Pavan Deolasee" <pavan(dot)deolasee(at)gmail(dot)com>
Subject: Re: Proposed adjustments in MaxTupleSize and toastthresholds
Date: 2007-02-05 16:45:36
Message-ID: 1170693936.3645.400.camel@silverbirch.site
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Fri, 2007-02-02 at 15:11 -0500, Tom Lane wrote:
> 2. Fix TOAST_TUPLE_THRESHOLD and TOAST_TUPLE_TARGET to be correctly
> calculated (properly allowing for line pointers) and to be MAXALIGN
> multiples. The threshold value should be exactly the size of the
> largest tuple that you can put four of onto one page. Fix
> TOAST_MAX_CHUNK_SIZE so that it is *not* necessarily a MAXALIGN
> multiple, but rather causes the total length of a toast tuple to come
> out that way. This guarantees minimum space wastage on toast pages.

Jan suggested to me a while back that having a configurable toast
threshold would be a useful thing, when that table is also updated
reasonably frequently. ISTM like a good idea, so a prototype has been
written - nothing to do with Pavan's comments though. As you might
expect, it does help in cases where we would otherwise produce lots of
UPDATEd versions of a 1000 byte row, as well as on MIS queries that
often don't pay much attention to text strings.

This then allows the user some control over how much data gets toasted
out of the main row. Many applications have long text fields of 100s of
characters, for example a customer's stated, cleaned and previous
addresses might together be VARCHAR(750), yet we may also want to UPDATE
them regularly to store their current_balance.

TOAST_MAX_CHUNK_SIZE can be fixed, though TOAST_TUPLE_THRESHOLD and
TOAST_TUPLE_TARGET could be settable for a table using a WITH parameter.
It would seem like overkill to allow the threshold and target to differ
when setting the parameter.

If configurable, only MAXALIGNed values would be allowed.

Sounds like a good time to suggest making these values configurable,
within certain reasonable bounds to avoid bad behaviour.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Joshua D. Drake 2007-02-05 16:49:16 Re: psql possible TODO
Previous Message Tom Lane 2007-02-05 16:34:23 Re: VC2005 build and pthreads