
Perhaps I am missing something, but the documentation on external 
storage appears remarkably sparse. It says it is better for substring 
searches, but otherwise doesn't say very much.

I have a table with two modest-length bytea fields (around 9K and 2K 
respectively) of already compressed data. I was looking for a clue as to 
whether or not these should use external storage - I suspect they should 
but I didn't see anything that gave me a definite answer.

I'd prepare doc updates on this if they were required but I don't have 
the requisite knowledge, nor time right now to go trawling endlessly to 
acquire it.

cheers

andrew


Mon, 03 Nov 2003 12:53:46 -0500
Andrew Dunstan <andrew@dunslane.net>
docco on external storage?
Andrew Dunstan wrote:
> I have a table with two modest-length bytea fields (around 9K and 2K 
> respectively) of already compressed data. I was looking for a clue as to 
> whether or not these should use external storage - I suspect they should 
> but I didn't see anything that gave me a definite answer.

 From src/include/access/tuptoaster.h:
-------------------------------------
/*
  * These symbols control toaster activation.  If a tuple is larger than
  * TOAST_TUPLE_THRESHOLD, we will try to toast it down to no more than
  * TOAST_TUPLE_TARGET bytes.  Both numbers include all tuple header and
  * alignment-padding overhead.
  *
  * The numbers need not be the same, though they currently are.
  */
#define TOAST_TUPLE_THRESHOLD   (MaxTupleSize / 4)
#define TOAST_TUPLE_TARGET      (MaxTupleSize / 4)


And from src/include/access/htup.h:
-----------------------------------
/*
  * MaxTupleSize is the maximum allowed size of a tuple, including header
  * and MAXALIGN alignment padding.  Basically it's BLCKSZ minus the
  * other stuff that has to be on a disk page.  The "other stuff"
  * includes access-method- dependent "special space", which we assume
  * will be no more than MaxSpecialSpace bytes (currently, on heap pages
  * it's actually zero).
  *
  * NOTE: we do not need to count an ItemId for the tuple because
  * sizeof(PageHeaderData) includes the first ItemId on the page.
  */
#define MaxSpecialSpace  32
#define MaxTupleSize    \
         (BLCKSZ - MAXALIGN(sizeof(PageHeaderData) + MaxSpecialSpace))


And from src/include/pg_config_manual.h:
----------------------------------------
#define BLCKSZ  8192


So, it looks like the threshold for external storage is somewhere around 
2000 bytes.

Joe


Mon, 03 Nov 2003 18:50:33 -0800
Joe Conway <mail@joeconway.com>
Re: docco on external storage?

Thanks. We don't really expect DBAs to go hunting through the source code,
though, do we?

cheers

andrew


----- Original Message ----- 
From: "Joe Conway" <mail@joeconway.com>
To: "Andrew Dunstan" <andrew@dunslane.net>
Cc: "Postgresql Hackers" <pgsql-hackers@postgresql.org>
Sent: Monday, November 03, 2003 9:50 PM
Subject: Re: [HACKERS] docco on external storage?


> Andrew Dunstan wrote:
> > I have a table with two modest-length bytea fields (around 9K and 2K
> > respectively) of already compressed data. I was looking for a clue as to
> > whether or not these should use external storage - I suspect they should
> > but I didn't see anything that gave me a definite answer.
>
>  From src/include/access/tuptoaster.h:
> -------------------------------------
> /*
>   * These symbols control toaster activation.  If a tuple is larger than
>   * TOAST_TUPLE_THRESHOLD, we will try to toast it down to no more than
>   * TOAST_TUPLE_TARGET bytes.  Both numbers include all tuple header and
>   * alignment-padding overhead.
>   *
>   * The numbers need not be the same, though they currently are.
>   */
> #define TOAST_TUPLE_THRESHOLD   (MaxTupleSize / 4)
> #define TOAST_TUPLE_TARGET      (MaxTupleSize / 4)
>
>
> And from src/include/access/htup.h:
> -----------------------------------
> /*
>   * MaxTupleSize is the maximum allowed size of a tuple, including header
>   * and MAXALIGN alignment padding.  Basically it's BLCKSZ minus the
>   * other stuff that has to be on a disk page.  The "other stuff"
>   * includes access-method- dependent "special space", which we assume
>   * will be no more than MaxSpecialSpace bytes (currently, on heap pages
>   * it's actually zero).
>   *
>   * NOTE: we do not need to count an ItemId for the tuple because
>   * sizeof(PageHeaderData) includes the first ItemId on the page.
>   */
> #define MaxSpecialSpace  32
> #define MaxTupleSize    \
>          (BLCKSZ - MAXALIGN(sizeof(PageHeaderData) + MaxSpecialSpace))
>
>
> And from src/include/pg_config_manual.h:
> ----------------------------------------
> #define BLCKSZ  8192
>
>
> So, it looks like the threshold for external storage is somewhere around
> 2000 bytes.
>
> Joe
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match


Tue, 4 Nov 2003 07:18:45 -0500
"Andrew Dunstan" <andrew@dunslane.net>
Re: docco on external storage?