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
