Re: docco on external storage?

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

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

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(at)joeconway(dot)com>
To: "Andrew Dunstan" <andrew(at)dunslane(dot)net>
Cc: "Postgresql Hackers" <pgsql-hackers(at)postgresql(dot)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