To: "Phillip Smith" <phillip.smith@weatherbeeta.com.au>
cc: "'Ryan Wells'" <ryan.wells@soapware.com>, pgsql-admin@postgresql.org
Subject: Re: Slow pg_dump 
In-reply-to: <004b01c89e86$ebe8fbe0$9b0014ac@wbaus090> 
References: <004b01c89e86$ebe8fbe0$9b0014ac@wbaus090>
Comments: In-reply-to "Phillip Smith" <phillip.smith@weatherbeeta.com.au>
	message dated "Tue, 15 Apr 2008 09:26:10 +1000"
Date: Mon, 14 Apr 2008 20:58:48 -0400
Message-ID: <25879.1208221128@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Phillip Smith" <phillip.smith@weatherbeeta.com.au> writes:
>> Here's my interpretation of those results: the TOAST tables for
>> our image files are compressed by Postgres.  During the backup,
>> pg_dump uncompresses them, and if compression is turned on,
>> recompresses the backup.  Please correct me if I'm wrong there.

No, the TOAST tables aren't compressed, they're pretty much going to be
the raw image data (plus a bit of overhead).

What I think is happening is that COPY OUT is encoding the bytea
data fairly inefficiently (one byte could go to \\nnn, five bytes)
and the compression on the pg_dump side isn't doing very well at buying
that back.

I experimented a bit and noticed that pg_dump -Fc is a great deal
smarter about storing large objects than big bytea fields --- it seems
to be pretty nearly one-to-one with the original data size when storing
a compressed file that was put into a large object.  I dunno if it's
practical for you to switch from bytea to large objects, but in the near
term I think that's your only option if the dump file size is a
showstopper problem for you.

			regards, tom lane