From: Tom Lane <tgl@sss.pgh.pa.us>
To: Phillip Smith <phillip.smith@weatherbeeta.com.au>
Cc: 'Ryan Wells' <ryan.wells@soapware.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Slow pg_dump
Date: Mon, 14 Apr 2008 20:58:48 -0400
Message-ID: <25879.1208221128@sss.pgh.pa.us> (raw)
In-Reply-To: <004b01c89e86$ebe8fbe0$9b0014ac@wbaus090>
References: <004b01c89e86$ebe8fbe0$9b0014ac@wbaus090>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, phillip.smith@weatherbeeta.com.au, ryan.wells@soapware.com
Subject: Re: Slow pg_dump
In-Reply-To: <25879.1208221128@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox