Обсуждение: TOAST behavior in 8.3 and 8.4
I have a table with a text column, wherein most values range between 800 and 1700 bytes. The data should be highly compressible, however these values appear to be too small to trigger the TOAST mechanism - I understand the default value of TOAST_TUPLE_THRESHOLD is about 2kb. This table is by far the largest in our database (1006450 rows; pg_relation_size() gives 1580 MB), and I am keen to get the data compressed. We compile from source and are currently running 8.3.10. Based on some discussions I found in the archives, I thought it might be worthwhile to lower TOAST_TUPLE_THRESHOLD and rebuild. However, I checked to see whether the behavior of TOAST had been changed in 8.4, since I am hoping to upgrade soon. I found the following comment in the release notes: "Consider TOAST compression on values as short as 32 bytes (previously 256 bytes)" I don't understand what that '256 bytes' refers to. That is a far cry from 2kb. I would be grateful if anyone can fill in the evident gap in my knowledge here. -- Thank you, Lewis Kapell Computer Operations Seton Home Study School
Lewis Kapell <lkapell@setonhome.org> wrote: > I don't understand what that '256 bytes' refers to. That is a far > cry from 2kb. I would be grateful if anyone can fill in the > evident gap in my knowledge here. It doesn't try to compress anything unless the tuple (row instance) as a whole is above TOAST_TUPLE_THRESHOLD. In trying to reduce the tuple size, it won't consider compressing column values below a certain size. That is where the 256 versus 32 bytes comes in. -Kevin
Lewis Kapell wrote: > I have a table with a text column, wherein most values range between > 800 and 1700 bytes. The data should be highly compressible, however > these values appear to be too small to trigger the TOAST mechanism - > I understand the default value of TOAST_TUPLE_THRESHOLD is about > 2kb. > However, I checked to see whether the behavior of TOAST had been > changed in 8.4, since I am hoping to upgrade soon. I found the > following comment in the release notes: > > "Consider TOAST compression on values as short as 32 bytes > (previously 256 bytes)" The 2kB are compared against tuple size. The 32 bytes (previously 256) are compared against a single column value. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Thanks for the clarification. In tuptoaster.h, just above the declaration of TOAST_TUPLES_PER_PAGE, there is a comment which begins: "while these can be modified without initdb..." Does this mean that if I reduce the value of TOAST_TUPLE_THRESHOLD and rebuild, I can use an existing database cluster without having to run initdb again? On 4/13/2010 3:02 PM, Kevin Grittner wrote: > Lewis Kapell<lkapell@setonhome.org> wrote: > >> I don't understand what that '256 bytes' refers to. That is a far >> cry from 2kb. I would be grateful if anyone can fill in the >> evident gap in my knowledge here. > > It doesn't try to compress anything unless the tuple (row instance) > as a whole is above TOAST_TUPLE_THRESHOLD. In trying to reduce the > tuple size, it won't consider compressing column values below a > certain size. That is where the 256 versus 32 bytes comes in. > > -Kevin
Lewis Kapell <lkapell@setonhome.org> wrote: > In tuptoaster.h, just above the declaration of > TOAST_TUPLES_PER_PAGE, there is a comment which begins: "while > these can be modified without initdb..." > > Does this mean that if I reduce the value of TOAST_TUPLE_THRESHOLD > and rebuild, I can use an existing database cluster without having > to run initdb again? Yes, but it's *possible* that some table which would now seem to qualify for a toast table, based on the new threshold, would not yet have one. That would be benign; it just wouldn't be able to toast values for that table unless you recreated it. You can check pg_class to find out which tables currently have toast tables. -Kevin