Skip site navigation (1) Skip section navigation (2)

Re: Significantly larger toast tables on 8.4?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Robert Haas" <robertmhaas(at)gmail(dot)com>
Cc: "Stephen R(dot) van den Berg" <srb(at)cuci(dot)nl>, "Alex Hunsaker" <badalex(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Significantly larger toast tables on 8.4?
Date: 2009-01-02 18:44:48
Message-ID: 26410.1230921888@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
"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

In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2009-01-02 18:47:51
Subject: Re: Documenting serializable vs snapshot isolation levels
Previous:From: Kevin GrittnerDate: 2009-01-02 18:01:55
Subject: Re: Documenting serializable vs snapshot isolationlevels

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group