`pg_dump` and compression

vitoyucepi commented

2025-01-23 14:01:23 +00:00

Summary

I'm using borgmatic to backup postgresql database and the default export options are --format custom. By default for custom format pg_dump compresses the export file using gzip as explained in the documentation.

Output a custom-format archive suitable for input into pg_restore. Together with the directory output format, this is the most flexible output format in that it allows manual selection and reordering of archived items during restore. This format is also compressed by default.

For the custom and directory archive formats, this specifies compression of individual table-data segments, and the default is to compress using gzip at a moderate level.

I can set an additional pg_dump flag with options: --compression=none.

Question

What's the best way to export the database if I set compression: auto,zstd in the borgmatic config?
Does pg_dump produce a compressed backup with high entropy due to compression, rendering borg deduplication useless?

Additional

https://www.postgresql.org/docs/current/app-pgdump.html

dump_format = database.get('format', None if database_name == 'all' else 'custom')

## Summary I'm using borgmatic to backup postgresql database and the default export options are `--format custom`. By default for *custom* format `pg_dump` compresses the export file using gzip as explained in the documentation. > Output a custom-format archive suitable for input into pg_restore. Together with the directory output format, this is the most flexible output format in that it allows manual selection and reordering of archived items during restore. This format is also compressed by default. > For the custom and directory archive formats, this specifies compression of individual table-data segments, and the default is to compress using gzip at a moderate level. I can set an additional `pg_dump` flag with `options: --compression=none`. ## Question What's the best way to export the database if I set `compression: auto,zstd` in the borgmatic config? Does `pg_dump` produce a compressed backup with high entropy due to compression, rendering borg deduplication useless? ## Additional https://www.postgresql.org/docs/current/app-pgdump.html https://projects.torsion.org/borgmatic-collective/borgmatic/src/commit/ffb342780b90f773419e07ea2dae163fdccd9215/borgmatic/hooks/data_source/postgresql.py#L153

witten commented

2025-01-23 20:02:47 +00:00

Owner

This is a great question, and I honestly don't know the answer. My instinct says not to double compress, but that's just instinct. I'd actually recommend answering this experimentally. Something like the following:

Start with Borg compression enabled and Postgresql compression enabled.

Create a new, empty repository.
Run borgmatic create and time how long it takes.
Run borgmatic info and observe the compressed size vs. original size vs. deduplicated size of the archive.
Run borgmatic create again, timing how long it takes.
Run borgmatic info again and check the sizes for the second archive.

Disable PostgreSQL compression in the borgmatic configuration file. Then delete the repository and run through the steps above again, noting down the results.

Now compare the results with Postgresql compression and without. Is the observed time/size tradeoff more favorable with or without compression?

Side question: Would you benefit from a compression: option for PostgreSQL databases in borgmatic config?

This is a great question, and I honestly don't know the answer. My instinct says not to double compress, but that's just instinct. I'd actually recommend answering this experimentally. Something like the following: Start with Borg compression enabled and Postgresql compression enabled. 1. Create a new, empty repository. 2. Run `borgmatic create` and time how long it takes. 3. Run `borgmatic info` and observe the compressed size vs. original size vs. deduplicated size of the archive. 4. Run `borgmatic create` again, timing how long it takes. 5. Run `borgmatic info` again and check the sizes for the second archive. Disable PostgreSQL compression in the borgmatic configuration file. Then delete the repository and run through the steps above again, noting down the results. Now compare the results with Postgresql compression and without. Is the observed time/size tradeoff more favorable with or without compression? Side question: Would you benefit from a `compression:` option for PostgreSQL databases in borgmatic config?

witten added the

question / support

label

2025-01-23 20:02:54 +00:00

vitoyucepi commented

2025-01-24 04:03:36 +00:00

Author

My instinct says not to double compress, but that's just instinct. I'd actually recommend answering this experimentally.

The behavior may be different for large databases. By the way, my theory is that the best parameters are --format directory --compression none. I'll test that too.

Would you benefit from a compression: option for PostgreSQL databases in borgmatic config?

The compression: option would be great, the default value should be none, I guess.

> My instinct says not to double compress, but that's just instinct. I'd actually recommend answering this experimentally. The behavior may be different for large databases. By the way, my theory is that the best parameters are `--format directory --compression none`. I'll test that too. > Would you benefit from a `compression:` option for PostgreSQL databases in borgmatic config? The `compression:` option would be great, the default value should be `none`, I guess.

vitoyucepi commented

2025-01-24 15:08:21 +00:00

Author

I didn't notice this at first, but they changed the `--compress' option in postgresql 16.
Before:

-Z 0..9
--compress=0..9
Specify the compression level to use. Zero means no compression. For the custom and directory archive formats, this specifies compression of individual table-data segments, and the default is to compress at a moderate level. For plain text output, setting a nonzero compression level causes the entire output file to be compressed, as though it had been fed through gzip; but the default is not to compress. The tar archive format currently does not support compression at all.

After:

-Z level
-Z method[:detail]
--compress=level
--compress=method[:detail]

I didn't notice this at first, but they changed the `--compress' option in postgresql 16. Before: > ``` > -Z 0..9 > --compress=0..9 > ``` > Specify the compression level to use. Zero means no compression. For the custom and directory archive formats, this specifies compression of individual table-data segments, and the default is to compress at a moderate level. For plain text output, setting a nonzero compression level causes the entire output file to be compressed, as though it had been fed through gzip; but the default is not to compress. The tar archive format currently does not support compression at all. After: > ``` > -Z level > -Z method[:detail] > --compress=level > --compress=method[:detail] > ```

vitoyucepi commented

2025-01-24 15:59:23 +00:00

Author

Double compression

compression: auto,zstd
postgresql_databases:
  - name: db
    format: custom

1st run

Duration: 13.43 seconds

	Original size	Compressed size	Deduplicated size
This archive:	83.08 MB	83.08 MB	83.08 MB
All archives:	83.08 MB	83.08 MB	83.08 MB

2nd run

Duration: 13.43 seconds

	Original size	Compressed size	Deduplicated size
This archive:	83.08 MB	83.08 MB	623.53 kB
All archives:	166.16 MB	166.16 MB	83.71 MB

Single compression

compression: auto,zstd
postgresql_databases:
  - name: db
    format: custom
    options: "--compress=0" # 15.2 btw

1st run

Duration: 18.40 seconds

	Original size	Compressed size	Deduplicated size
This archive:	416.25 MB	79.30 MB	79.30 MB
All archives:	416.25 MB	79.30 MB	79.31 MB

2nd run

Duration: 12.78 seconds

	Original size	Compressed size	Deduplicated size
This archive:	416.25 MB	79.30 MB	471.67 kB
All archives:	832.50 MB	158.60 MB	79.79 MB

## Double compression ```yaml compression: auto,zstd postgresql_databases: - name: db format: custom ``` <details> <summary>1st run</summary> Duration: 13.43 seconds | | Original size | Compressed size | Deduplicated size | | ------------- | ------------- | --------------- | ----------------- | | This archive: | 83.08 MB | 83.08 MB | 83.08 MB | | All archives: | 83.08 MB | 83.08 MB | 83.08 MB | </details> <details> <summary>2nd run</summary> Duration: 13.43 seconds | | Original size | Compressed size | Deduplicated size | | ------------- | ------------- | --------------- | ----------------- | | This archive: | 83.08 MB | 83.08 MB | 623.53 kB | | All archives: | 166.16 MB | 166.16 MB | 83.71 MB | </details> ## Single compression ```yaml compression: auto,zstd postgresql_databases: - name: db format: custom options: "--compress=0" # 15.2 btw ``` <details> <summary>1st run</summary> Duration: 18.40 seconds | | Original size | Compressed size | Deduplicated size | | ------------- | ------------- | --------------- | ----------------- | | This archive: | 416.25 MB | 79.30 MB | 79.30 MB | | All archives: | 416.25 MB | 79.30 MB | 79.31 MB | </details> <details> <summary>2nd run</summary> Duration: 12.78 seconds | | Original size | Compressed size | Deduplicated size | | ------------- | ------------- | --------------- | ----------------- | | This archive: | 416.25 MB | 79.30 MB | 471.67 kB | | All archives: | 832.50 MB | 158.60 MB | 79.79 MB | </details>

vitoyucepi commented

2025-01-24 16:18:28 +00:00

Author

Directory export doesn't work when docker exec database pg_dump.

Directory export doesn't work when `docker exec database pg_dump`.

witten commented

2025-01-24 16:22:39 +00:00

Owner

Thanks for running all that and reporting back! Based on those results, here are my takes:

Other than the first backup, backup time appears to very slightly improve without PostgreSQL compression, but not enough to be material.
It appears that Borg's deduplication works better without PostgreSQL compression. It's not a huge savings in terms of absolute MB in this example, but it could add up over hundreds of backups and/or with larger databases.

So I would say, just based on those results, you may want to leave PostgreSQL compression disabled! And I'll leave this ticket open to add that compress or compression option to borgmatic. Based on your findings with different versions of PostgreSQL, I'll probably just make it a string and lean on the user to set it accordingly based on their version.

Thanks for running all that and reporting back! Based on those results, here are my takes: * Other than the first backup, backup time appears to very slightly improve without PostgreSQL compression, but not enough to be material. * It appears that Borg's deduplication works better without PostgreSQL compression. It's not a huge savings in terms of absolute MB in this example, but it could add up over hundreds of backups and/or with larger databases. So I would say, just based on those results, you may want to leave PostgreSQL compression disabled! And I'll leave this ticket open to add that `compress` or `compression` option to borgmatic. Based on your findings with different versions of PostgreSQL, I'll probably just make it a string and lean on the user to set it accordingly based on their version.

witten commented

2025-01-24 16:25:58 +00:00

Owner

Directory export doesn't work when docker exec database pg_dump.

You might consider filing a separate ticket for this, and I'd be happy to take a look.

> Directory export doesn't work when `docker exec database pg_dump`. You might consider filing a separate ticket for this, and I'd be happy to take a look.

witten added the

design finalized

good first issue

labels

2025-01-24 16:26:29 +00:00

vitoyucepi referenced this issue

2025-01-24 16:58:53 +00:00

pg_dump in directory format can't be done if extracted using docker exec #978

witten referenced this issue from a commit

2025-03-02 01:29:41 +00:00

Add a "compression" option to the PostgreSQL database hook (#975).

witten commented