Обсуждение: Bg_writer and checkpointer

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? 

Same way, what happens for insert query?

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.

Correct me the flowchart 

On Fri, Apr 19, 2024 at 11:16 AM Rajesh Kumar <rajeshkumar.dba09@gmail.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