Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group