Re: Bg_writer and checkpointer

On Fri, Apr 19, 2024 at 11:16 AM Rajesh Kumar <rajeshkumar(dot)dba09(at)gmail(dot)com>
wrote:

> I have read multiple online blogs, watched videos and still not clarified
> about 1. Actual difference between bg_writer and checkpointer. When I am
> querying update set, If bgwriter flushes dirty buffer to base every
> 200ms(bgwriter_delay), why would I need checkpointer to invoke bgwriter?
>

Sometimes the documentation is better than blogs and videos. The bgwriter
doesn't write every dirty buffer, only enough that backends needing to
evict pages will generally find that the one selected to evict is already
clean. The checkpointer needs to write out every dirty buffer no already
written out by someone else, as well as fsync them all (regardless of who
wrote them out)

> 1. How the flow works?
>
> Dml-->wal_buffer-->also copied to shared_buffer--> commit-->flushed to
> pg_wal using wal writer -->checkpointer-->fsync to datapath-->bgwriter
> flushed to base.
>

Generally the committing process flushes the pg_wal itself. The wal writer
is only important for synchronous_commit=off processes, or for large
transactions where the wal needs to be written several times over the
course of one transaction.

Also, the changes are written to shared_buffers before wal_buffer, but
since they are protected by the same lock I think the ordering is mostly
just an implementation detail.

Cheers,

Jeff

>