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@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?
Show quoted text
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 8192So, 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