Shared buffers, WAL buffers and Checkpointers

Question

I am taking this EDB PostgreSQL essential course and Instructor explained about PostgreSQL architecture referring to the diagram that, whenever a client make an update request and suppose data is present in shared buffer (that means no need to fetch it from file storage) then it'll make an entry in WAL buffers and upon committing the WAL writer will write the transaction to transaction logs and make it permanent but not in the file systems (as far as I've understood, that's the task of checkpointer, below.) So far so good.

image courtesy traning.enterprisedb.com

Now comes checkpointer, it is a process which runs after every certain interval of time "usually 5 mins is an ideal time" and, write anything in the shared buffer into the file storage.

My question is, suppose checkpointer just ran and after that I initiated an atomic transaction and transferred 100 bucks to my friend, how is it that my friend can see it immediately, is Postgres making query to transaction logs? Or, how's this happening?

But upon little pondering, I realize that when the request is made to update the data and in order to update it, Postgres has bring it into the main memory and a viable way to do that is to keep track of dirty data in shared buffer and update the data in shared buffer itself and in the transaction logs we can have 0/1 with every DML transaction entry to identify whether data is present in shared buffer or not. This can also come handy while doing analysis.

Can someone help me understand? Thanks in advance!

Answer 1

Data like table pages and index pages are always accessed through shared_buffers. The only time they are accessed directly on disk is when they are being copied into shared_buffers, or being cleaned from shared_buffers back to disk. So there is no "keeping track" of whether it needs to check shared_buffers, as it always needs to check shared_buffers.

When a change is done, the change is first done in shared_buffers, then a record of that change is written to the WAL log buffer (these two actions are tightly coupled). Then the WAL log is written and flushed to disk, then the dirty data in shared_buffers is written to disk. These actions are not tightly coupled, there could be many seconds or minutes separating them, but they are always done in the order listed.

Between when the update is done and when the dirty page is written back (maybe by the checkpointer or maybe by something else) it is locked into shared_buffers. It cannot be evicted until it is written, and it cannot be written until the WAL record protecting it is written and flushed.

So when your friend checks his balance immediately after you commit, it is being checked from the "dirty" table data locked into shared_buffers.