Hi Andres Freund
Thank you for explanation
> I doubt that slowdown is caused by bgwriter not being active enough. I suspect
> what you're seeing is one or more of:
> a) The overhead of doing full page writes (due to increasing the WAL
> volume). You could verify whether that's the case by turning
> full_page_writes off (but note that that's not generally safe!) or see if
> the overhead shrinks if you set wal_compression=zstd or wal_compression=lz4
> (don't use pglz, it's too slow).
> b) The overhead of renaming WAL segments during recycling. You could see if
> this is related by specifying --wal-segsize 512 or such during initdb.
I am aware of these optimizations, and these optimizations only mitigate the impact, I didn't turn on wal log compression on purpose during stress test ，
shared_buffers = '32GB'
bgwriter_delay = '10ms'
bgwriter_lru_maxpages = '8192'
bgwriter_lru_multiplier = '10.0'
wal_buffers = '64MB'
checkpoint_completion_target = '0.999'
checkpoint_timeout = '600'
max_wal_size = '32GB'
min_wal_size = '16GB'
I think in business scenarios where there are many reads and few writes, it is indeed desirable to keep as many dirty pages in memory as possible,However, in scenarios such as push systems and task scheduling systems, which also have a lot of reads and writes, the impact of checkpoints will be more obvious,Adaptive bgwrite or bgwrite triggered when a dirty page reaches a certain watermark eliminates the impact of checkpoints on performance jitter.From what I understand, quite a few commercial databases residing in postgresql have added the adaptive refresh dirty page feature, and from their internal reports, the whole stress testing process was very smooth! Since it's a trade secret, I don't know how they implemented this feature.
