Re: database is bigger after dump/restore - why? (60 GB to 109 GB)

On Friday, March 04, 2011 2:03:23 pm Aleksey Tsalolikhin wrote:
> On Fri, Mar 4, 2011 at 7:53 AM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> wrote:
> > Weird. The pgstattuple data shows that the tables are essentially the
> > same, the only difference being the dead tuples, as expected, on the
> > production table. The TOAST size information shows approximately a
> > doubling in size of the TOASTed data on the new machine. By all accounts
> > compression or the lack thereof would be the culprit. At this point I am
> > at a loss for another explanation.
>
> An apt summary, thank you.
>
> > One more request for information. What is the data being stored in the
> > table?
>
> For the main part, it's an XML file, we store it in the third field.
> Our XML files are
> 13KB - 48 KB in length.
>

And you are positive that field has not had its storage changed to something
other than EXTENDED?

>
> I ran char_length() on each XML file, the total is 38 GB, which is pretty
> close to the 40 GB size of the pg_dump of the table in text format.
>
> From my observation, when the table was under 30 GB in size,
> TOAST compression worked fine; when it got above 35 GB in size,
> now TOAST compression is not working.

More questions:)
How do you know that?
I thought the size problem only came to light when you tried to set up another
server.
Was there some sort of monitoring going on previous to setting up the new
server?
Anything else happen around that time?

>
> Yours truly,
> -at

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com