pg_dump compression settings

After doing the base backup benchmarks, I thought it would be interesting to benchmark pg_dump run locally and remotely using all the different compression settings.  This will allow me to compare the dump times and the space savings as well as see how much dumping remotely slows the process.  Nobody is storing their dumps on the same server, right?

For the record, all these dumps used pg_dump's custom dump format (-Fc), the PGDATA directory is 93GB on disk and resides on a 4 disk RAID10 of 7200 RPM SATA drives.

CPU on the test server: Intel(R) Xeon(R) CPU X3330  @ 2.66GHz.
CPU on the test remote client: Intel(R) Core(TM) i7 CPU 960  @ 3.20GHz.
Network is gigabit.
The total size reported is from du -sh output.
Both client and test server are running postgresql-9.1.2.

For comparison, our best base backup time from the base backup blog post was: 15m52.221s

The results:

Local pg_dump: ------------------- compression: 0 time: 19m7.455s total size: 77G ------------------- compression: 1 time: 21m53.128s total size: 11G ------------------- compression: 2 time: 22m27.507s total size: 11G ------------------- compression: 3 time: 24m18.966s total size: 9.8G ------------------- compression: 4 time: 30m10.815s total size: 9.2G ------------------- compression: 5 time: 34m26.119s total size: 8.3G ------------------- compression: 6 time: 41m35.340s total size: 8.0G ------------------- compression: 7 time: 49m4.484s total size: 7.9G ------------------- compression: 8 time: 91m28.689s total size: 7.8G ------------------- compression: 9 time: 103m24.883s total size: 7.8G

Remote pg_dump: ------------------- compression: 0 time: 20m1.363s total size: 77G ------------------- compression: 1 time: 22m9.205s total size: 11G ------------------- compression: 2 time: 22m19.158s total size: 11G ------------------- compression: 3 time: 23m7.426s total size: 9.8G ------------------- compression: 4 time: 26m10.383s total size: 9.2G ------------------- compression: 5 time: 28m57.431s total size: 8.3G ------------------- compression: 6 time: 33m23.939s total size: 8.0G ------------------- compression: 7 time: 38m11.321s total size: 7.9G ------------------- compression: 8 time: 62m27.385s total size: 7.8G ------------------- compression: 9 time: 72m5.123s total size: 7.8G

So, a few interesting observations:

• Base backups are indeed faster, but that's no surprise since they have less overhead. 
• Taking the backup locally is only slightly faster than remotely for the smaller compression levels
• At compression level 3 and above the faster CPU on the test client becomes a huge benefit
• Looks like the default compression is level 6. This might not be what you want if you're concerned about minimizing the backup time and have the disk space to spare.