Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.100.2.1.4\))
Subject: Re: pg_dump -Z6 (the default) can be pretty slow
From: Scott Ribe <scott_ribe@elevated-dev.com>
In-Reply-To: <c2157b92-8802-41c9-a6b8-435d6d3793c0@gmail.com>
Date: Wed, 18 Oct 2023 16:37:22 -0600
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <E9615CEC-CB92-4825-8BFC-0099F90107BD@elevated-dev.com>
References: <c2157b92-8802-41c9-a6b8-435d6d3793c0@gmail.com>
To: Ron <ronljohnsonjr@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/E9615CEC-CB92-4825-8BFC-0099F90107BD%40elevated-dev.com>
Precedence: bulk

If you can use the directory format, then you can use multiple jobs to =
really speed up compressed dump (and restore).

Also, I'd suggest trying a run with lz4 compression--lz4 is particularly =
good at not slowing down when it encounters already-compressed data. =
Doesn't give really high compression ratios, but since you're already at =
-Z3, might be worth comparing.

(Personally, I stay away from zstd, as I've seen it create malformed =
backups because the encoder crashes with out-of-memory.)

--
Scott Ribe
scott_ribe@elevated-dev.com
https://www.linkedin.com/in/scottribe/


> On Oct 18, 2023, at 4:30=E2=80=AFPM, Ron <ronljohnsonjr@gmail.com> =
wrote:
>=20
> In preparation for moving from 9.6 to something supported, I ran a =
pg_dump/pg_restore test (since the migrated databases will be on new =
servers, and we purge off old partitions and add new partitions, =
pg_upgrade and logical replication are off the table).
>=20
> (The servers are VMs on ESX hosts, and on the same subnet.)
>=20
> Our databases are chock full of bytea fields holding compressed =
images. pg_dump -Fd -Z6 took 25 minutes, and 5.5GB disk space. =
(remember, it's a test!), while pg_dump -Fd -Z0 only took 90 seconds, =
but consumed 15GB.
>=20
> This isn't really surprising to anyone who's ever tried to gzip a jpg =
file...
>=20
> Quite the speed increase if you can swallow the increased disk usage.
>=20
> pg_dump -Z3 did the best: only 8.5 minutes, while using just 5.8GB =
disk space.
>=20
> --=20
> Born in Arizona, moved to Babylonia.
>=20
>=20