Message-ID: <c2157b92-8802-41c9-a6b8-435d6d3793c0@gmail.com>
Date: Wed, 18 Oct 2023 17:30:43 -0500
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Content-Language: en-US
From: Ron <ronljohnsonjr@gmail.com>
Subject: pg_dump -Z6 (the default) can be pretty slow
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/c2157b92-8802-41c9-a6b8-435d6d3793c0%40gmail.com>
Precedence: bulk

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).

(The servers are VMs on ESX hosts, and on the same subnet.)

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.

This isn't really surprising to anyone who's ever tried to gzip a jpg file...

Quite the speed increase if you can swallow the increased disk usage.

pg_dump -Z3 did the best: only 8.5 minutes, while using just 5.8GB disk space.

-- 
Born in Arizona, moved to Babylonia.