Re: WAL compression setting after PostgreSQL LZ4 default change

On 8/6/26 17:32, Tomas Vondra wrote:
>
>
> On 8/6/26 16:50, Ken Marshall wrote:
>> On Thu, Aug 06, 2026 at 04:31:44PM +0200, Hannu Krosing wrote:
>>> On Thu, Aug 6, 2026 at 2:11 PM Tomas Vondra <tomas(at)vondra(dot)me> wrote:
>>>>
>>>> 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?)
>>>
>>> Do you have any ideas why recovery on Azure is 10-20% faster for lz4
>>> (and surprisingly also pglz) than for zstd ?
>>>
>
> I don't, but I'll do a couple more runs with for these parameters
> (zstd/3, lz4/1 and pglz), to see how stable the results are.
>
> Might be interesting to run this on other cloud instances, but I don't
> have access to those at the moment.
>
> Could this be due to difference in decompression speed? I've seen
> figures like 2GB/s for zstd vs. 4GB/s for lz4. We're we'd dealing with
> ~45GB of WAL, so that'd be 25 vs. 10 seconds, which is not far from the
> ~20s difference in recovery time. Then again, it only affects azure, so
> there's some hw influence.
>

I've done more testing on the azure instances. The timings are very
consistent - with lz4 it takes 156s +/- 2s, with zstd it's 172s +/- 2s
(so ~10% difference). After some profiling, I think it really is due to
the lower zstd decompression speed. The CPU profiles look like this:

18.64% postgres postgres [.] pg_checksum_block_avx2
11.13% postgres postgres [.] hash_search_with_hash_value
10.08% postgres liblz4.so.1.10.0 [.] LZ4_decompress_safe
3.56% postgres postgres [.] FlushBuffer.constprop.0
2.65% postgres libc.so.6 [.] __internal_syscall_cancel
2.33% postgres libc.so.6 [.] __memmove_avx512_...
2.04% postgres postgres [.] LWLockAttemptLock

vs.

12.41% postgres libzstd.so.1.5.7 [.] ZSTD_decompressSequences...
12.24% postgres postgres [.] pg_checksum_block_avx2
8.25% postgres libzstd.so.1.5.7 [.] ZSTD_createDCtx_internal
6.76% postgres postgres [.] hash_search_with_hash_value
4.35% postgres libzstd.so.1.5.7 [.] ZSTD_buildFSETable_...
4.17% postgres libzstd.so.1.5.7 [.] HUF_readDTableX1_wksp...
2.34% postgres postgres [.] FlushBuffer.constprop.0
2.23% postgres libzstd.so.1.5.7 [.] HUF_readStats_body_bmi2...
2.19% postgres libzstd.so.1.5.7 [.] FSE_decompress_wksp_body_...
1.73% postgres libc.so.6 [.] __internal_syscall_cancel
1.56% postgres libc.so.6 [.] __memmove_avx512_...
1.32% postgres postgres [.] LWLockAttemptLock

The ZSTD decompression takes much bigger fraction of the CPU time, so
that would explain this.

Why it happens only on the azure instances is fairly simple - those
instances have better local NVMe storage, so the bottleneck is CPU. On
the other machines (ryzen/xeon), the local storage is slower, which
helps the higher compression ratio with zstd.

I suspect using network storage would benefit from that too ...

regards

--
Tomas Vondra