bytea vs. pg_dump

From: Bernd Helmle <mailings(at)oopsware(dot)de>
To: pgsql-hackers(at)postgresql(dot)org
Subject: bytea vs. pg_dump
Date: 2009-05-05 10:39:23
Message-ID: 5936D36FFD98DB6180C12A79@teje
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

>From time to time we had complains about slow dump of large tables with
bytea columns, people often complaining about a) size and b) duration of
the dump.

That latter occurred recently to me, a customer would like to dump large
tables (approx. 12G in size) with pg_dump, but he was annoyed about the
performance. Using COPY BINARY reduced the time (unsurprisingly) to a
fraction (from 12 minutes to 3 minutes).

As discussed in the past[1], we didn't implement pg_dump to support BINARY
to preserve portability and version independence of dumps using pg_dump. I
would like to bring that topic up again, since implementing an option like
--binary-copy seems interesting in use cases, where portability and version
issues doesn't matter and someone wants to have a fast COPY of his
documents . This would make this task much easier, especially in the
described case, where the customer has to dump referenced tables as well.

Another approach would be to just dump bytea columns in binary format only
(not sure how doable that is, though).

Opinions, again?

[1] <>



Browse pgsql-hackers by date

  From Date Subject
Next Message Matthew Wakeling 2009-05-05 11:24:42 Re: [HACKERS] high shared buffer and swap
Previous Message Laurent Laborde 2009-05-05 10:20:01 Re: [HACKERS] high shared buffer and swap