Re: WAL compression setting after PostgreSQL LZ4 default change

On 8/4/26 12:01, Tomas Vondra wrote:
> On 8/4/26 09:03, Hannu Krosing wrote:
>> On Mon, Aug 3, 2026 at 10:36 PM Tomas Vondra <tomas(at)vondra(dot)me> wrote:
>>>
>>> On 8/3/26 15:36, Hannu Krosing wrote:
>> ...
>>
>> My recollection from running some manual tests a year or two ago is
>> that on compression side there was a fair bit of overlap in speed
>> between lz4 and zstd when looking across many compression levels.
>>
>> However, lz4 was a clear winner in decompression speeds, being 2-3
>> times faster than zstd.
>>
>
> Seems plausible.
>
> I'm running some simple tests, to get a better idea how the algorithms
> compare. I have no intention to tune the current compression levels.
>
OK, so here's some results of the benchmarks I ran to get a better idea
how the compression algorithms / levels compare.

I was not interested in microbenchmarks comparing the algorithms in
isolation on synthetic data, but in more realistic tests. I ran two
tests, on OLTP (pgbench -N) and TPC-H data sets.

The executive summary is that lz4/zstd perform about the same, but zstd
has a clearly better compression ratio. I withdraw my initial plan to
default to lz4, and I agree we should default to zstd. (By "default" I
mean that "on" would use that algorithm, but the GUC would still default
to 'off' as before. Maybe we should reconsinder that for PG20, though?)

The test scripts (and results) are available here:

* https://github.com/tvondra/compression-charts-oltp
* https://github.com/tvondra/compression-charts-tpch

Both tests compare these compression options:

- off
- pglz
- lz4 with acceleration 1 5 10 30 50 (default is "1")
- zstd with level 1 3 5 10 16 (default is "3")

Attached is a WIP patch adding "wal_compression_level" GUC to allow
setting the level (only for testing, not meant for commit).

For OLTP, the script does this (for each compression algo/level):

1) sets up a new cluster, with a replica (and shuts the replica down)
2) initializes a pgbench database
3) reindexes the database
4) pgbench -N (with fixed number of transactions)
5) disables FPW
6) pgbench -N (with fixed number of transactions)
7) starts replica, measures recovery time

For TPC-H, it's similar:

1) sets up a new cluster, with a replica (and shuts the replica down)
2) loads 10GB TPC-H data set
3) ALTER to create primary keys etc.
4) creates secondary indexes
5) VACUUM FREEZE
6) starts replica, measures recovery time

For each interesting step, the script measures:

- amount of WAL written
- duration
- CPU usage (from vmstat)
- throughput (for pgbench)

I ran this on 4 local/cloud machines, with different CPUs etc.:

- ryzen 9900X (~2024, local)
- xeon e5-2699v4 (~2016, local)
- azure-1 (Xeon 6973P-C, cloud)
- azure-2 (EPYC 9V74, cloud)

There are some differences (esp. the old Xeon behaves a bit differently,
but let's aim for new CPUs).

Note: All of this was with local storage and local replication. I expect
remote storage/replication to benefit from higher compression ratios. I
also ran the tests with fsync=off, but the behavior is about the same.

Attached is a couple PDFs plotting a couple representative results. I
use scatter plots, with WAL vs. duration / CPU usage / throughput. that
allows comparing by the compression ratio and the other parameter.

For lz4/zstd, the charts show a couple data points connected by a line,
for the various compression levels.

I'm not going to discuss the charts one by one, that'd make this message
way too long and repetitive. But let me share a couple observations that
apply to all of them:

* zstd has a clearly better compression ratio - it often cuts the WAL
amount in half, compared to lz4 (with default compression levels)

* zstd has higher CPU usage, but the difference is tiny (within 1% for
the whole machine), so I consider this difference irrelevant

* both zstd and lz4 are "comparable" with pglz in terms of compression
ratio (lz4 a little worse, zstd a little better), and much better in
timing (both lz4/zstd are much faster, for reasonable levels)

* for lz4, the "acceleration" is pretty useless - it reduces the
compression ratio, but the speedup is negligible (so not worth it)

* for zstd, the "level" makes a bigger difference, but 1-5 is about the
same (same compression ratio/duration), but higher levels are not worth
really it (level=16 makes it 6x slower, for saving ~25% space)

So to conclude:

* zstd seems like the right default for 'on' - clearly better than pglz,
both in compression and CPU costs

* lz4 is a good choice too - significantly faster than pglz, but a bit
worse compression ratio

* the default zstd compression level (i.e. 3) is a good choice - there's
not much difference between 1 to 3, higher values don't seem like a good
trade off (diminishing returns)

* the LZ4 acceleration seems rather useless, not faster, but much worse
compression ratio

Based on this, I agree with the original suggestion to default to

zstd > lz4 > pglz

I don't think we need to "tune" the compression levels for either of the
algorithms. The defaults seem pretty reasonable.

regards

--
Tomas Vondra