Re: pg_dump with compressible and non-compressible tables

On 05/05/2018 12:13 PM, Adrian Klaver wrote:
> On 05/05/2018 07:14 AM, Ron wrote:
>> Hi,
>>
>> v9.6
>>
>> We've got big databases where some of the tables are highly compressible,
>> but some have many bytea fields containing PDF files.
>
> Can you see a demonstrable difference?

Very much so.  The ASCII hex representations of the PDF files are
compressible, but take a *long* time to compress. Uncompressed backups are
50% faster.

>>
>> When the data format is custom, directory or tar, how feasible would a
>> "--no-blob-compression" option be (where pg_dump just tells the zlib
>> library to just Store tables with bytea columns, while compressing all
>> other tables at the specified -Z level)?
>
> In pg_dump blob refers to large objects:
>
> https://www.postgresql.org/docs/10/static/app-pgdump.html
> "
> -b
> --blobs
>
>     Include large objects in the dump. This is the default behavior except
> when --schema, --table, or --schema-only is specified. The -b switch is
> therefore only useful to add large objects to dumps where a specific
> schema or table has been requested. Note that blobs are considered data
> and therefore will be included when --data-only is used, but not when
> --schema-only is.
> "
>
> These are different critters then bytea.

Ok.  I need the data in my backups anyway, so excluding them is 100%
contrary to what I need.

--
Angular momentum makes the world go 'round.