I did a test on my PC in a VM similar to my VPS, 3 Zen 2 threads, 12 GB RAM, NVME drive and the results are absolutely terrible. The performance got basically cut in half even with lz4
I didn't do any optimization Postgres side, but I did on ZFS side:
recordsize=8k
atime=off
xattr=sa
logbias=latency
redundant_metadata=most
Results:
pgbench -i -s 6000 testdb
ext4
done in 1396.01 s (drop tables 0.00 s, create tables 0.01 s, client-side generate 731.07 s, vacuum 295.22 s, primary keys 369.71 s).
zfs
done in 3183.85 s (drop tables 0.00 s, create tables 0.01 s, client-side generate 1187.85 s, vacuum 571.35 s, primary keys 1424.63 s)
pgbench -c 180 -j 2 -t 10000 testdb
ext4
latency average = 74.381 ms
tps = 2419.988141 (including connections establishing)
tps = 2419.994924 (excluding connections establishing)
zfs
latency average = 144.478 ms
tps = 1245.868334 (including connections establishing)
tps = 1245.870005 (excluding connections establishing)