Re: any impact to DML sql statements when checkpoint starting

On Thursday, February 3, 2022, James Pang (chaolpan) <chaolpan(at)cisco(dot)com>
wrote:

> When Postgres checkpoint, it always write buffers from pg shared_buffers
> to OS filesystem cache, after that, it finally make a system call
> fsync , that will flush dirty blocks from OS filesystem cache to disk,
> right?
>

Please don’t top-post.

From the docs:

On Linux and POSIX platforms checkpoint_flush_after
<https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-CHECKPOINT-FLUSH-AFTER>
allows
to force the OS that pages written by the checkpoint should be flushed to
disk after a configurable number of bytes. Otherwise, these pages may be
kept in the OS's page cache, inducing a stall when fsync is issued at the
end of a checkpoint. This setting will often help to reduce transaction
latency, but it also can have an adverse effect on performance;
particularly for workloads that are bigger than shared_buffers
<https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-SHARED-BUFFERS>,
but smaller than the OS's page cache.

David J.