Parallel Server-Side Backup Compression

I decided to do a little more research on the performance of server-side backup compression, which will be a new feature in PostgreSQL 15 unless, for some reason, the changes need to be reverted prior to release time. The network link I used for my previous testing was, as I mentioned, rather slow, and handicapped by both a VPN link and an SSH tunnel. Furthermore, I was testing using pgbench data, which is extremely compressible. In addition, at the time I did those tests, we had added support for LZ4 compression, but we had not yet added support for Zstandard compression. Now, however, we not only have Zstandard as an option, but it is possible to use the library's multi-threading capabilities. So, I wanted to find out how things would work out on a faster network link, with a better test data set, and with all the compression algorithms that we now have available.

To try to figure that out, I downloaded the UK land registry data which is mentioned in https://wiki.postgresql.org/wiki/Sample_Databases and loaded that up into PostgreSQL database, built from a recent commit off the master branch that will eventually become v15. The resulting database is 3.8GB. I then tried a few backups between this machines and another machine located on the same EDB internal subnet.  Both machines report having 10 Gigabit Ethernet, and iperf reports bandwidth of 8.73 GB/s between the machines.

I tried backups with both with both -Ft (tar format) and -Fp (plain format), in each case testing out various forms of server-side compression. When the backup is taken in tar format, pg_basebackup is basically just writing the server-generated files to disk. When it's taken in plain format, pg_basebackup decompresses and extracts the archive. Here are the results:

Compression	Size -Ft (GB)	Time -Ft	Time -Fp
none	3.8	17.0	18.1
gzip	1.5	234.9	233.1
lz4	2.0	31.5	35.1
zstd	1.3	56.1	59.1
zstd:workers=2	1.3	26.4	29.5
zstd:workers=4	1.3	15.0	21.8
zstd:workers=6	1.3	11.5	23.0
zstd:workers=8	1.3	9.9	22.9
zstd:workers=12	1.3	10.3	20.3
zstd:workers=16	1.3	10.0	20.5
zstd:workers=20	1.3	10.2	20.3
zstd:workers=24	1.3	10.1	21.0

Here, and in every other test I've run, gzip compression - the only type supported by current releases of PostgreSQL, and then only on the client side - is the stand-out loser. It is just incredibly slow. The other choices are all competitive, depending on the situation. LZ4 doesn't compress as well as gzip, but it's the fastest single-threaded algorithm. Single-threaded Zstandard compresses better and faster than gzip, but not as fast as single-threaded LZ4. If you have CPU resources to spare, you can throw 8 or so cores at Zstandard compression and get both the better compression ratio and the fastest time. In fact, when tar format is used, parallel Zstandard compression is about 40% faster than not compressing the backup at all.

If the network connection between these machines were sufficiently slow, the time to perform the backup would be dominated by the backup size, as in my previous post where the database compressed by about a factor of 8.9 and the backup got faster by about a factor of 8.9. Here, with a more realistic data set, the compression ratio is only about 2.9x even when using Zstandard, and so if the network were slow enough to be the bottleneck I think using Zstandard would give us about a 2.9x performance improvement. Here, however, the network is fast enough to be a non-issue, yet compression - when using the tar format - still delivers about a 1.7x speedup. Why is that?

Basically, the gains are attributable to pg_basebackup needing to do less work. When the backup size drops from 3.8GB to 1.3GB, pg_basebackup has 2.5 fewer gigabytes of data that it needs to receive from the network, and 2.5 fewer gigabytes of data that it needs to write out to disk. The network is fast enough that the cost of transmitting 2.5 gigabytes of data isn't a real issue, but the kernel still has to copy all that data received from the network into PostgreSQL's address space, and then it must turn around and copy it from PostgreSQL's address space into the kernel buffer cache so that it can be written out to disk, and finally that data has to be actually written to disk. Both the extra copying and the actual disk writes are costly.

Notice that in plain format, we don't see any performance gains vs. the uncompressed case; in fact, even parallel Zstandard shows a slight regression, and the other compression methods are worse. Because we now have to decompress and extract the archives, pg_basebackup ends up having to write the full 3.8GB regardless of what compression method is used for the data in transit. We still gain the benefit of needing to write less data to the network on the server side and read less data from the network on the client side, but on a fast network connection, that's not a big enough savings to cover the cost of having to compress the whole input on the server side, and then decompress everything on the client side.

I think that it would be interesting to test how this works out with a much larger data set, so that we saturate the local disks for a longer period of time, and maybe also with a highly incompressible data set. I also have a few ideas about how we might be able to improve the efficiency of pg_basebackup. However, overall, I find these results pretty encouraging. I don't think it will be an unusual situation for users to have enough spare CPU capacity on the server to make use of parallel Zstandard compression, and if the data set is reasonably compressible, the benefits of doing so may be significant.