Thread: 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 itdown 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'sBLCKSZ 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 actuallyzero). * * NOTE: we do not need to count an ItemId for the tuple because * sizeof(PageHeaderData) includes the firstItemId 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? > 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