Message-ID: <cb80c6f3c21a798d29f7db46c78e650bbcd95705.camel@cybertec.at>
Subject: Re: slow pg_dump with bytea
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Janning Vygen <vygen@kicktipp.de>, pgsql-bugs@lists.postgresql.org
Date: Fri, 20 Oct 2023 13:52:17 +0200
In-Reply-To: <1325559b-6a40-411d-a1ab-1e1162e3722b@kicktipp.de>
References: <1325559b-6a40-411d-a1ab-1e1162e3722b@kicktipp.de>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.48.4 (3.48.4-1.fc38) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/cb80c6f3c21a798d29f7db46c78e650bbcd95705.camel%40cybertec.at>
Precedence: bulk

On Fri, 2023-10-20 at 12:26 +0200, Janning Vygen wrote:
> I don't know if the PG developers are aware of this:
>=20
> https://serverfault.com/questions/1081642/postgresql-13-speed-up-pg-dump-=
to-5-minutes-instead-of-70-minutes=20
>=20
> But this question is quite famous and many users like the solution.
> So maybe you can fix it by changing the pg_dump process to not compress=
=20
> any bytea data.

Doesn't sound like a bug to me.

Compression is determined when "pg_dump" starts.  How should it guess that
there is a binary column with compressed data in some table?  Even if it di=
d,
I wouldn't feel well with a "pg_dump" with enough artificial intelligence t=
o
do this automatically for me (and get it wrong occasionally).

In addition, I don't think that this problem is limited to compressed
binary data.  In my experience, compressed dumps are always slower than
uncompressed ones.  It is a speed vs. size thing.

By the way, PostgreSQL v16 introduced compression with "lz4" und "zstd"
to "pg_dump", which is much faster.

Yours,
Laurenz Albe