Sample Header Ad - 728x90

Shared buffers, WAL buffers and Checkpointers

1 vote
1 answer
735 views
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. enter image description here **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!
Asked by commonSense (123 rep)
Nov 25, 2023, 01:40 AM
Last activity: Nov 25, 2023, 03:08 PM