Re: Bg_writer and checkpointer

Thanks. Please share any good links related to this topic

On Sat, 20 Apr 2024, 20:24 Jeff Janes, <jeff(dot)janes(at)gmail(dot)com> wrote:

> 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
>
>>