Modifying TOAST_TUPLE_THRESHOLD and TOAST_TUPLE_TARGET?
I notice that one of my tables gets TOASTed, about 40% of it. I read about
TOAST and understand the general motivation (get faster access to the
*other* columns). But in my case the big column (~2.5KB fixed width) is a
list (array) of values (not text) and the usage pattern is that I always
access a single row from the table by key and read the full list. So my
guess is TOAST is doing me more damage than good. Right?
I would have liked to disable TOAST (even altogether for my DB) - is there a
direct way to do this? Alternatively I thought of increasing
TOAST_TUPLE_THRESHOLD and TOAST_TUPLE_TARGET from 2KB (I believe this is the
default?) to 4KB? Do I have to change the source (
http://doxygen.postgresql.org/tuptoaster_8h-source.html here ?) and compile
a private version of Postgres?
Thanks, feel free to reply by email.
-- Shaul (info@shauldar.com)
--
View this message in context: http://www.nabble.com/Modifying-TOAST_TUPLE_THRESHOLD-and-TOAST_TUPLE_TARGET--tp23982524p23982524.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
On Thu, Jun 11, 2009 at 3:32 PM, Shadar<shauldar@gmail.com> wrote:
I notice that one of my tables gets TOASTed, about 40% of it. I read about
TOAST and understand the general motivation (get faster access to the
*other* columns). But in my case the big column (~2.5KB fixed width) is a
list (array) of values (not text) and the usage pattern is that I always
access a single row from the table by key and read the full list. So my
guess is TOAST is doing me more damage than good. Right?
It's possible. It might depend on how much wasted space you'll get in
each 8k block if you store them inline. And also on how cpu-bound
versus i/o-bound your database is.
I would have liked to disable TOAST (even altogether for my DB) - is there a
direct way to do this? Alternatively I thought of increasing
TOAST_TUPLE_THRESHOLD and TOAST_TUPLE_TARGET from 2KB (I believe this is the
default?) to 4KB? Do I have to change the source (
http://doxygen.postgresql.org/tuptoaster_8h-source.html here ?) and compile
a private version of Postgres?
Yeah.
There are a few other options.
You could use "ALTER COLUMN SET STORAGE PLAIN" to disable toasting on
that column entirely. This will disable compression as well though. If
any record doesn't fit in an 8kB block you'll get an error.
Alternately you could leave the column alone but drop the toast table
(presumably having truncated the table first). That will leave
compression enabled but force the system to avoid storing things
externally. Again you'll get an error if a record doesn't fit in 8kB
but only after first trying to compress the data.
--
Gregory Stark
http://mit.edu/~gsstark/resume.pdf
Greg Stark <stark@enterprisedb.com> wrote:
You could use "ALTER COLUMN SET STORAGE PLAIN" to disable toasting
on
that column entirely. This will disable compression as well though.
If
any record doesn't fit in an 8kB block you'll get an error.
Alternately you could leave the column alone but drop the toast
table
(presumably having truncated the table first). That will leave
compression enabled but force the system to avoid storing things
externally. Again you'll get an error if a record doesn't fit in 8kB
but only after first trying to compress the data.
Why not use "ALTER COLUMN SET STORAGE MAIN", to allow compression but
avoid external storage?
http://www.postgresql.org/docs/8.3/interactive/sql-altertable.html
-Kevin
Greg Stark-5 wrote:
On Thu, Jun 11, 2009 at 3:32 PM, Shadar<shauldar@gmail.com> wrote:
I notice that one of my tables gets TOASTed, about 40% of it. I read
about
TOAST and understand the general motivation (get faster access to the
*other* columns). But in my case the big column (~2.5KB fixed width) is a
list (array) of values (not text) and the usage pattern is that I always
access a single row from the table by key and read the full list. So my
guess is TOAST is doing me more damage than good. Right?It's possible. It might depend on how much wasted space you'll get in
each 8k block if you store them inline. And also on how cpu-bound
versus i/o-bound your database is.- I/O bound (in cold state), I don't worry about warm state...
- So how do I find out, or guesstimate, the effect of TOAST in my case?I would have liked to disable TOAST (even altogether for my DB) - is
there a
direct way to do this? Alternatively I thought of increasing
TOAST_TUPLE_THRESHOLD and TOAST_TUPLE_TARGET from 2KB (I believe this is
the
default?) to 4KB? Do I have to change the source (
http://doxygen.postgresql.org/tuptoaster_8h-source.html here ?) and
compile
a private version of Postgres?Yeah.
There are a few other options.
You could use "ALTER COLUMN SET STORAGE PLAIN" to disable toasting on
that column entirely. This will disable compression as well though. If
any record doesn't fit in an 8kB block you'll get an error.Alternately you could leave the column alone but drop the toast table
(presumably having truncated the table first). That will leave
compression enabled but force the system to avoid storing things
externally. Again you'll get an error if a record doesn't fit in 8kB
but only after first trying to compress the data.- I don't believe we have any record over 8K. So the 2nd method is best?
When should I drop the TOAST table, before or after my DB is populated? I.e.
Postgres will then move the TOASTED data inside my table?
--
Gregory Stark
http://mit.edu/~gsstark/resume.pdf--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
--
View this message in context: http://www.nabble.com/Modifying-TOAST_TUPLE_THRESHOLD-and-TOAST_TUPLE_TARGET--tp23982524p23983149.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
Greg Stark <stark@enterprisedb.com> wrote:
You could use "ALTER COLUMN SET STORAGE PLAIN" to disable toasting on
that column entirely. This will disable compression as well though.
Why not use "ALTER COLUMN SET STORAGE MAIN", to allow compression but
avoid external storage?
That only discourages pushing a particular column out; it will still
do so if the row exceeds TOAST_TUPLE_THRESHOLD after compression.
I kinda doubt the OP wants it to fail outright for rows over 8K,
so altering TOAST_TUPLE_THRESHOLD seems like the right answer.
Too bad we don't have that set up as a reloption...
regards, tom lane
On Thu, Jun 11, 2009 at 4:03 PM, Shadar<shauldar@gmail.com> wrote:
- I don't believe we have any record over 8K. So the 2nd method is best?
When should I drop the TOAST table, before or after my DB is populated? I.e.
Postgres will then move the TOASTED data inside my table?
No it won't. Any data in the toast table will be lost and you'll get
errors if you try to access it.
Incidentally please don't quote back the whole message, just quote the
parts you're responding to. Thanks.
--
Gregory Stark
http://mit.edu/~gsstark/resume.pdf
Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
Why not use "ALTER COLUMN SET STORAGE MAIN", to allow compression
but avoid external storage?That only discourages pushing a particular column out; it will still
do so if the row exceeds TOAST_TUPLE_THRESHOLD after compression.
I thought that's the behavior of EXTENDED. The fine manual says "MAIN
allows compression but not out-of-line storage. (Actually, out-of-line
storage will still be performed for such columns, but only as a last
resort when there is no other way to make the row small enough.)"
If that doesn't mean that it will only use out-of-line storage when
the row doesn't fit in the page, then the manual could use a fix.
-Kevin
On Thu, Jun 11, 2009 at 4:24 PM, Kevin
Grittner<Kevin.Grittner@wicourts.gov> wrote:
I thought that's the behavior of EXTENDED. The fine manual says "MAIN
allows compression but not out-of-line storage. (Actually, out-of-line
storage will still be performed for such columns, but only as a last
resort when there is no other way to make the row small enough.)"If that doesn't mean that it will only use out-of-line storage when
the row doesn't fit in the page, then the manual could use a fix.
I agree that "small enough" could be more precise. As it happens
tuptoaster.c only has one definition of "small enough" which is
whether the record is smaller than TOAST_TUPLE_THRESHOLD.
I wonder actually if this case shouldn't use the block size, not the
target size. That seems like it would be a lot more useful.
--
Gregory Stark
http://mit.edu/~gsstark/resume.pdf
Greg Stark <stark@enterprisedb.com> wrote:
tuptoaster.c only has one definition of "small enough" which is
whether the record is smaller than TOAST_TUPLE_THRESHOLD.
So what *is* the difference between EXTENDED and MAIN?
-Kevin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
Greg Stark <stark@enterprisedb.com> wrote:
tuptoaster.c only has one definition of "small enough" which is
whether the record is smaller than TOAST_TUPLE_THRESHOLD.
So what *is* the difference between EXTENDED and MAIN?
EXTENDED columns get pushed out first. So if you have some EXTENDED
columns and some MAIN columns, it'll give preference to keeping the
MAIN columns in-line. In the OP's case there's only one wide column
so this is of no help.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
So what *is* the difference between EXTENDED and MAIN?
EXTENDED columns get pushed out first. So if you have some EXTENDED
columns and some MAIN columns, it'll give preference to keeping the
MAIN columns in-line. In the OP's case there's only one wide column
so this is of no help.
Got it. The documentation seems less than clear on this. Perhaps the
description of MAIN on this page:
http://www.postgresql.org/docs/8.3/interactive/storage-toast.html
should be changed. How does this sound?:
MAIN allows compression but discourages out-of-line storage.
(Out-of-line storage will be performed only if the row is still too
big after compression and out-of-line storage of EXTENDED and EXTERNAL
columns.)
If there is consensus, I'll put that into patch form.
It seems to me that MAIN might be a more useful option if it was more
aggressive about avoiding out-of-line storage; perhaps only if the row
doesn't fit by itself on a single page? Does anyone else think so?
-Kevin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
It seems to me that MAIN might be a more useful option if it was more
aggressive about avoiding out-of-line storage; perhaps only if the row
doesn't fit by itself on a single page? Does anyone else think so?
I think that's exactly what Greg Stark was proposing. Might be
something to look at for 8.5.
regards, tom lane
On Thu, Jun 11, 2009 at 5:12 PM, Kevin
Grittner<Kevin.Grittner@wicourts.gov> wrote:
MAIN allows compression but discourages out-of-line storage.
(Out-of-line storage will be performed only if the row is still too
big after compression and out-of-line storage of EXTENDED and
EXTERNAL columns.)
I had the impression the confusion was over the meaning of "too big"
rather than what "last resort" meant. So this doesn't seem any
clearer.
It seems to me that MAIN might be a more useful option if it was more
aggressive about avoiding out-of-line storage; perhaps only if the row
doesn't fit by itself on a single page? Does anyone else think so?
Yeah I think we're on the same page there. I do suspect the reason
this documentation is vague on the precise meanings of these options
is precisely because the author expected further tweaks of this nature
-- I don't know if this documentation has changed substantially since
toast was committed.
We've been talking about a number of ideas for making toast more
flexible but there are clearly an infinite number of permutations and
the trick will be figuring out how to present the useful ones without
making things too complicated for the user to control.
Greg Stark <stark@enterprisedb.com> wrote:
On Thu, Jun 11, 2009 at 5:12 PM, Kevin
Grittner<Kevin.Grittner@wicourts.gov> wrote:MAIN allows compression but discourages out-of-line storage.
(Out-of-line storage will be performed only if the row is still too
big after compression and out-of-line storage of EXTENDED and
EXTERNAL columns.)I had the impression the confusion was over the meaning of "too big"
rather than what "last resort" meant. So this doesn't seem any
clearer.
Well, in the EXTENDED section I took "too big" to refer back to the
immediately preceding paragraph:
- The TOAST code is triggered only when a row value to be stored in a
- table is wider than TOAST_TUPLE_THRESHOLD bytes (normally 2 kB). The
- TOAST code will compress and/or move field values out-of-line until
- the row value is shorter than TOAST_TUPLE_TARGET bytes (also
- normally 2 kB) or no more gains can be had.
I think I misunderstood the MAIN section precisely because it didn't
use the "too big" term at all, but instead said that it didn't allow
out-of-line storage except "as a last resort when there is no other
way to make the row small enough." If it's going to be conditioned on
the same limit, I would find it less confusing to stick with the same
terminology. That said, if you still find my wording confusing, it's
apparently not good enough. Suggestion?
It seems to me that MAIN might be a more useful option if it was
more aggressive about avoiding out-of-line storage; perhaps only if
the row doesn't fit by itself on a single page?Yeah I think we're on the same page there.
Cool.
We've been talking about a number of ideas for making toast more
flexible but there are clearly an infinite number of permutations
and the trick will be figuring out how to present the useful ones
without making things too complicated for the user to control.
So it would be premature to submit a patch for changing this behavior?
(I was thinking that if people could agree on this, it might be
something I could work in some evening, for some 8.5 commitfest.
If the OP is brave enough to use it, it might possibly solve the
problem.)
-Kevin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
It seems to me that MAIN might be a more useful option if it was
more aggressive about avoiding out-of-line storage; perhaps only if
the row doesn't fit by itself on a single page?Yeah I think we're on the same page there.
Cool.
We've been talking about a number of ideas for making toast more
flexible but there are clearly an infinite number of permutations
and the trick will be figuring out how to present the useful ones
without making things too complicated for the user to control.
So it would be premature to submit a patch for changing this behavior?
No, I don't think so, since there's no apparent need for a control knob.
You just change the limit that's applied during the last phase of
toast_insert_or_update.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
So it would be premature to submit a patch for changing this
behavior?
No, I don't think so, since there's no apparent need for a control
knob.
You just change the limit that's applied during the last phase of
toast_insert_or_update.
Proposed patch attached.
-Kevin
Attachments:
toast-main-out-of-line-reluctantly.diffapplication/octet-stream; name=toast-main-out-of-line-reluctantly.diffDownload
? toast-main-out-of-line-reluctantly.diff
Index: src/backend/access/heap/tuptoaster.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/access/heap/tuptoaster.c,v
retrieving revision 1.93
diff -c -r1.93 tuptoaster.c
*** src/backend/access/heap/tuptoaster.c 11 Jun 2009 14:48:54 -0000 1.93
--- src/backend/access/heap/tuptoaster.c 11 Jun 2009 23:47:09 -0000
***************
*** 798,803 ****
--- 798,804 ----
/*
* Finally we store attributes of type 'm' external, if possible.
*/
+ maxDataLen = TOAST_TUPLE_TARGET_MAIN - hoff;
while (heap_compute_data_size(tupleDesc,
toast_values, toast_isnull) > maxDataLen &&
rel->rd_rel->reltoastrelid != InvalidOid)
Index: src/include/access/tuptoaster.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/access/tuptoaster.h,v
retrieving revision 1.43
diff -c -r1.43 tuptoaster.h
*** src/include/access/tuptoaster.h 1 Jan 2009 17:23:56 -0000 1.43
--- src/include/access/tuptoaster.h 11 Jun 2009 23:47:09 -0000
***************
*** 27,33 ****
/*
* 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 overhead
* and between-fields alignment padding, but we do *not* consider any
* end-of-tuple alignment padding; hence the values can be compared directly
* to a tuple's t_len field.
--- 27,35 ----
/*
* 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 through compressing compressible fields and
! * moving moving EXTENDED and EXTERNAL data out-of-line.
! * Both numbers include all tuple header overhead
* and between-fields alignment padding, but we do *not* consider any
* end-of-tuple alignment padding; hence the values can be compared directly
* to a tuple's t_len field.
***************
*** 39,44 ****
--- 41,52 ----
* Currently we choose both values to match the largest tuple size for which
* TOAST_TUPLES_PER_PAGE tuples can fit on a disk page.
*
+ * We set a separate target for MAIN fields, which we will try to reach by
+ * moving MAIN fields out-of-line only if the above has not already done so.
+ * Since the user is trying to keep the data in-line, it makes sense to set
+ * TOAST_TUPLES_PER_PAGE_MAIN to a lower number (currently 1), which will
+ * result in a larger TOAST_TUPLE_TARGET_MAIN value.
+ *
* XXX while these can be modified without initdb, some thought needs to be
* given to needs_toast_table() in toasting.c before unleashing random
* changes. Also see LOBLKSIZE in large_object.h, which can *not* be
***************
*** 53,58 ****
--- 61,73 ----
#define TOAST_TUPLE_TARGET TOAST_TUPLE_THRESHOLD
+ #define TOAST_TUPLES_PER_PAGE_MAIN 1
+
+ #define TOAST_TUPLE_TARGET_MAIN \
+ MAXALIGN_DOWN((BLCKSZ - \
+ MAXALIGN(SizeOfPageHeaderData + TOAST_TUPLES_PER_PAGE_MAIN * sizeof(ItemIdData))) \
+ / TOAST_TUPLES_PER_PAGE_MAIN)
+
/*
* If an index value is larger than TOAST_INDEX_TARGET, we will try to
* compress it (we can't move it out-of-line, however). Note that this
Guys,
I appreciate your intentions to treat this more flexibly in a future
release. But I'm not sure I got a reply to my original questions, about the
existing 8.3 version:
1. Am I correct in assuming that in my case the TOAST mechanism probably
results in a performance hit, not boost, and is there a way to measure this
impact?
2. How can I disable TOAST? Only by changing TOAST_TUPLE_THRESHOLD and
TOAST_TUPLE_TARGET in the include file and compiling a private version?
Thanks,
-- Shaul
--
View this message in context: http://www.nabble.com/Modifying-TOAST_TUPLE_THRESHOLD-and-TOAST_TUPLE_TARGET--tp23982524p23996728.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
Shadar <shauldar@gmail.com> wrote:
1. Am I correct in assuming that in my case the TOAST mechanism
probably results in a performance hit, not boost,
Possibly, but you didn't give enough information to be sure. One
important factor would be how often the table in question has updates
or deletes. If those are very frequent, you might find that TOAST
improves performance, because it won't have to read as many disk pages
to check visibility for the reading transaction. If it is a table
with few updates and deletes and you really do always read the array,
it might be faster to avoid the out-of-line storage aspect of TOAST.
(The compression might still be valuable, if it often squeezes two
rows per page rather than one.)
and is there a way to measure this impact?
Like many performance issues, the only way to be absolutely sure is to
benchmark it both ways with a good simulation of your expected load.
(Testing one aspect, such as just the SELECTs, in the absence of the
modification load you expect, will not really give you good
information on this point.)
2. How can I disable TOAST? Only by changing TOAST_TUPLE_THRESHOLD
and TOAST_TUPLE_TARGET in the include file and compiling a private
version?
There have been several suggestions already. If you're going to
modify the source to deal with this, you might want to look at the
patch I proposed, and consider something like that. Keep in mind that
it hasn't been reviewed, it's just a suggestion I threw out there as a
possibility, with all the usual "use at your own risk" caveats.
With that patch, you would avoid errors on values which don't fit on a
single page, but when values are set for a column flagged with ALTER
TABLE SET STORAGE MAIN, they will not be moved out-of-line (into the
separate TOAST table) unless the row won't fit in a single page
without doing so.
-Kevin
2. How can I disable TOAST? Only by changing TOAST_TUPLE_THRESHOLD
and TOAST_TUPLE_TARGET in the include file and compiling a private
version?
There have been several suggestions already.
Yes, but other have commented that these (e.g. dropping TOAST table) might
not work and even lose data, so I was left confused as to what would work...
If you're going to modify the source to deal with this...
Again: do I have a choice? I'd rather not touch the code.
Thanks,
-- Shaul
--
View this message in context: http://www.nabble.com/Modifying-TOAST_TUPLE_THRESHOLD-and-TOAST_TUPLE_TARGET--tp23982524p24000355.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
Shadar <shauldar@gmail.com> wrote:
There have been several suggestions already.
Yes, but other have commented that these (e.g. dropping TOAST table)
might not work and even lose data, so I was left confused as to what
would work...
Well, dropping the TOAST table once you have data in it will lose
data. The main problem with the techniques which don't involve
modifying the source code are that you can't force the column to be
stored in-line without getting an error on an attempt to store a value
which makes the row too big to fit on a single page.
Again: do I have a choice? I'd rather not touch the code.
You've got lots of choices. For starters, I'm not at all sure you
will even have a performance benefit from inlining the data. You
could choose to just let PostgreSQL work the way most people do. A
couple options have been mentioned if you are sure your rows will
always fit on one page. None of these involve touching the source
code.
I threw together the patch to give you another option. Touching the
code isn't so scary once you get the hang of building from source.
Obviously you want test carefully before putting custom code into
production.
-Kevin
I wrote:
Proposed patch attached.
That first version was of the "minimally invasive" variety, to stress
how little I was changing and minimize the chance that I would make
some dumb error; however, it involved copy/paste of a few lines which
were already in a source file twice. Attached is what I hope is a
functionally identical patch, with minor refactoring. I think it
results in more readable code.
-Kevin
Attachments:
toast-main-out-of-line-reluctantly-2.diffapplication/octet-stream; name=toast-main-out-of-line-reluctantly-2.diffDownload
Index: src/backend/access/heap/tuptoaster.c
===================================================================
RCS file: /projects/cvsroot/pgsql/src/backend/access/heap/tuptoaster.c,v
retrieving revision 1.93
diff -c -r1.93 tuptoaster.c
*** src/backend/access/heap/tuptoaster.c 11 Jun 2009 14:48:54 -0000 1.93
--- src/backend/access/heap/tuptoaster.c 15 Jun 2009 16:26:58 -0000
***************
*** 798,803 ****
--- 798,804 ----
/*
* Finally we store attributes of type 'm' external, if possible.
*/
+ maxDataLen = TOAST_TUPLE_TARGET_MAIN - hoff;
while (heap_compute_data_size(tupleDesc,
toast_values, toast_isnull) > maxDataLen &&
rel->rd_rel->reltoastrelid != InvalidOid)
Index: src/include/access/tuptoaster.h
===================================================================
RCS file: /projects/cvsroot/pgsql/src/include/access/tuptoaster.h,v
retrieving revision 1.43
diff -c -r1.43 tuptoaster.h
*** src/include/access/tuptoaster.h 1 Jan 2009 17:23:56 -0000 1.43
--- src/include/access/tuptoaster.h 15 Jun 2009 16:26:58 -0000
***************
*** 25,36 ****
/*
! * 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 overhead
* and between-fields alignment padding, but we do *not* consider any
* end-of-tuple alignment padding; hence the values can be compared directly
* to a tuple's t_len field.
*
* The numbers need not be the same, though they currently are. It doesn't
* make sense for TARGET to exceed THRESHOLD, but it could be useful to make
--- 25,46 ----
/*
! * Find the maximum size of a tuple which allows "n" tuples per page.
! * Numbers include all tuple header overhead
* and between-fields alignment padding, but we do *not* consider any
* end-of-tuple alignment padding; hence the values can be compared directly
* to a tuple's t_len field.
+ */
+ #define MaximumBytesPerTuple(tuplesPerPage) \
+ MAXALIGN_DOWN((BLCKSZ - \
+ MAXALIGN(SizeOfPageHeaderData + (tuplesPerPage) * sizeof(ItemIdData))) \
+ / (tuplesPerPage))
+
+ /*
+ * 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 through compressing compressible fields and
+ * moving moving EXTENDED and EXTERNAL data out-of-line.
*
* The numbers need not be the same, though they currently are. It doesn't
* make sense for TARGET to exceed THRESHOLD, but it could be useful to make
***************
*** 39,44 ****
--- 49,60 ----
* Currently we choose both values to match the largest tuple size for which
* TOAST_TUPLES_PER_PAGE tuples can fit on a disk page.
*
+ * We set a separate target for MAIN fields, which we will try to reach by
+ * moving MAIN fields out-of-line only if the above has not already done so.
+ * Since the user is trying to keep the data in-line, it makes sense to set
+ * TOAST_TUPLES_PER_PAGE_MAIN to a lower number (currently 1), which will
+ * result in a larger TOAST_TUPLE_TARGET_MAIN value.
+ *
* XXX while these can be modified without initdb, some thought needs to be
* given to needs_toast_table() in toasting.c before unleashing random
* changes. Also see LOBLKSIZE in large_object.h, which can *not* be
***************
*** 46,58 ****
*/
#define TOAST_TUPLES_PER_PAGE 4
! #define TOAST_TUPLE_THRESHOLD \
! MAXALIGN_DOWN((BLCKSZ - \
! MAXALIGN(SizeOfPageHeaderData + TOAST_TUPLES_PER_PAGE * sizeof(ItemIdData))) \
! / TOAST_TUPLES_PER_PAGE)
#define TOAST_TUPLE_TARGET TOAST_TUPLE_THRESHOLD
/*
* If an index value is larger than TOAST_INDEX_TARGET, we will try to
* compress it (we can't move it out-of-line, however). Note that this
--- 62,75 ----
*/
#define TOAST_TUPLES_PER_PAGE 4
! #define TOAST_TUPLE_THRESHOLD MaximumBytesPerTuple(TOAST_TUPLES_PER_PAGE)
#define TOAST_TUPLE_TARGET TOAST_TUPLE_THRESHOLD
+ #define TOAST_TUPLES_PER_PAGE_MAIN 1
+
+ #define TOAST_TUPLE_TARGET_MAIN MaximumBytesPerTuple(TOAST_TUPLES_PER_PAGE_MAIN)
+
/*
* If an index value is larger than TOAST_INDEX_TARGET, we will try to
* compress it (we can't move it out-of-line, however). Note that this
***************
*** 72,81 ****
*/
#define EXTERN_TUPLES_PER_PAGE 4 /* tweak only this */
! #define EXTERN_TUPLE_MAX_SIZE \
! MAXALIGN_DOWN((BLCKSZ - \
! MAXALIGN(SizeOfPageHeaderData + EXTERN_TUPLES_PER_PAGE * sizeof(ItemIdData))) \
! / EXTERN_TUPLES_PER_PAGE)
#define TOAST_MAX_CHUNK_SIZE \
(EXTERN_TUPLE_MAX_SIZE - \
--- 89,95 ----
*/
#define EXTERN_TUPLES_PER_PAGE 4 /* tweak only this */
! #define EXTERN_TUPLE_MAX_SIZE MaximumBytesPerTuple(EXTERN_TUPLES_PER_PAGE)
#define TOAST_MAX_CHUNK_SIZE \
(EXTERN_TUPLE_MAX_SIZE - \
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
I wrote:
Proposed patch attached.
That first version was of the "minimally invasive" variety, to stress
how little I was changing and minimize the chance that I would make
some dumb error; however, it involved copy/paste of a few lines which
were already in a source file twice. Attached is what I hope is a
functionally identical patch, with minor refactoring. I think it
results in more readable code.
Applied with some editorialization on the comments. I also tweaked the
user-facing TOAST documentation thusly:
*** doc/src/sgml/storage.sgml 17 Jun 2009 21:58:49 -0000 1.29
--- doc/src/sgml/storage.sgml 22 Jul 2009 01:17:23 -0000
***************
*** 347,353 ****
<literal>MAIN</literal> allows compression but not out-of-line
storage. (Actually, out-of-line storage will still be performed
for such columns, but only as a last resort when there is no other
! way to make the row small enough.)
</para>
</listitem>
</itemizedlist>
--- 347,353 ----
<literal>MAIN</literal> allows compression but not out-of-line
storage. (Actually, out-of-line storage will still be performed
for such columns, but only as a last resort when there is no other
! way to make the row small enough to fit on a page.)
</para>
</listitem>
</itemizedlist>regards, tom lane