pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedslow pg_dump with bytea
5+ messages / 3 participants
[nested] [flat]
* slow pg_dump with bytea
@ 2023-10-20 10:26 Janning Vygen <vygen@kicktipp.de>
2023-10-20 11:52 ` Re: slow pg_dump with bytea Laurenz Albe <laurenz.albe@cybertec.at>
2023-10-20 13:18 ` Re: slow pg_dump with bytea Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 2 replies; 5+ messages in thread
From: Janning Vygen @ 2023-10-20 10:26 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
I don't know if the PG developers are aware of this:
https://serverfault.com/questions/1081642/postgresql-13-speed-up-pg-dump-to-5-minutes-instead-of-70-...
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
any bytea data.
regards
Janning
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: slow pg_dump with bytea
2023-10-20 10:26 slow pg_dump with bytea Janning Vygen <vygen@kicktipp.de>
@ 2023-10-20 11:52 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-10-20 12:08 ` Re: slow pg_dump with bytea Janning Vygen <vygen@kicktipp.de>
1 sibling, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2023-10-20 11:52 UTC (permalink / raw)
To: Janning Vygen <vygen@kicktipp.de>; pgsql-bugs@lists.postgresql.org
On Fri, 2023-10-20 at 12:26 +0200, Janning Vygen wrote:
> I don't know if the PG developers are aware of this:
>
> https://serverfault.com/questions/1081642/postgresql-13-speed-up-pg-dump-to-5-minutes-instead-of-70-...
>
> 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
> 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 did,
I wouldn't feel well with a "pg_dump" with enough artificial intelligence to
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: slow pg_dump with bytea
2023-10-20 10:26 slow pg_dump with bytea Janning Vygen <vygen@kicktipp.de>
2023-10-20 11:52 ` Re: slow pg_dump with bytea Laurenz Albe <laurenz.albe@cybertec.at>
@ 2023-10-20 12:08 ` Janning Vygen <vygen@kicktipp.de>
2023-10-20 12:51 ` Re: slow pg_dump with bytea Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 5+ messages in thread
From: Janning Vygen @ 2023-10-20 12:08 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; pgsql-bugs@lists.postgresql.org
Thanks for this quick response. Don't think it is a bug either. So,
wrong list, actually. But I didn't found any "feature request" list.
Sorry for this.
Anyhow, for me it seems odd that you can dump in a fraction of time
compared to the default settings. With the solution in the link you have
both: speed and compression. Which seems to be offered by pg_dump -Z
option too, acording to the level yu set. But it isn't.
regards
Janning
Am 20.10.23 um 13:52 schrieb Laurenz Albe:
> On Fri, 2023-10-20 at 12:26 +0200, Janning Vygen wrote:
>> I don't know if the PG developers are aware of this:
>>
>> https://serverfault.com/questions/1081642/postgresql-13-speed-up-pg-dump-to-5-minutes-instead-of-70-...
>>
>> 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
>> 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 did,
> I wouldn't feel well with a "pg_dump" with enough artificial intelligence to
> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: slow pg_dump with bytea
2023-10-20 10:26 slow pg_dump with bytea Janning Vygen <vygen@kicktipp.de>
2023-10-20 11:52 ` Re: slow pg_dump with bytea Laurenz Albe <laurenz.albe@cybertec.at>
2023-10-20 12:08 ` Re: slow pg_dump with bytea Janning Vygen <vygen@kicktipp.de>
@ 2023-10-20 12:51 ` Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 5+ messages in thread
From: Laurenz Albe @ 2023-10-20 12:51 UTC (permalink / raw)
To: Janning Vygen <vygen@kicktipp.de>; pgsql-bugs@lists.postgresql.org
On Fri, 2023-10-20 at 14:08 +0200, Janning Vygen wrote:
> Anyhow, for me it seems odd that you can dump in a fraction of time
> compared to the default settings. With the solution in the link you have
> both: speed and compression. Which seems to be offered by pg_dump -Z
> option too, acording to the level yu set. But it isn't.
Who says that the only goal of pg_dump is speed?
Like I said, specialized compression software is way better than PostgreSQL's
built-in "pglz".
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: slow pg_dump with bytea
2023-10-20 10:26 slow pg_dump with bytea Janning Vygen <vygen@kicktipp.de>
@ 2023-10-20 13:18 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
1 sibling, 0 replies; 5+ messages in thread
From: Alvaro Herrera @ 2023-10-20 13:18 UTC (permalink / raw)
To: Janning Vygen <vygen@kicktipp.de>; +Cc: pgsql-bugs@lists.postgresql.org
On 2023-Oct-20, Janning Vygen wrote:
> So maybe you can fix it by changing the pg_dump process to not compress any
> bytea data.
The problem with this idea is that the compression applies to the whole
data stream. The backup format doesn't allow to compress some columns
and not others. Doing that would require a lot of changes to pg_dump.
Maybe a workable idea would be to allow excluding individual tables from
being compressed.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"El hombre nunca sabe de lo que es capaz hasta que lo intenta" (C. Dickens)
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2023-10-20 13:18 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-20 10:26 slow pg_dump with bytea Janning Vygen <vygen@kicktipp.de>
2023-10-20 11:52 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-10-20 12:08 ` Janning Vygen <vygen@kicktipp.de>
2023-10-20 12:51 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-10-20 13:18 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
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