Re: Significantly larger toast tables on 8.4?

"Robert Haas" <robertmhaas(at)gmail(dot)com> writes:
> On Fri, Jan 2, 2009 at 11:01 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I'm not entirely convinced by Alex' analysis anyway; the only way
>> those 39 large values explain the size difference is if they are
>> *tremendously* compressible, like almost all zeroes. The toast
>> compressor isn't so bright that it's likely to get 10X compression
>> on typical data.

> I've seen gzip approach 10X on what was basically a large
> tab-separated values file, but I agree that some more experimentation
> to determine the real cause of the problem would be useful.

If I'm counting on my fingers correctly, you'd need to assume about
23X compression to get the reported size change...

An easy way to prove or disprove the point would be to go into
src/backend/utils/adt/pg_lzcompress.c, and change the second entry
in strategy_default_data from "1024 * 1024" to "INT_MAX", then
re-load the table with the modified backend (or you could even
just do "CREATE TABLE foo AS SELECT * FROM existing_table" and
then compare sizes). If that puts the toast table size back where
it had been, then Alex correctly diagnosed the cause.

regards, tom lane