Обсуждение: bgwriter and checkpoints
Hi community, I was reading about background writer process and checkpoints, but I still don't understand very well. How do they work for instance I understand this if a row was updated,those changes will be on shared buffers, then those changes will go to path on disk called pg_wall, then when a checkpoint will be executed and finally, those changes will go to data files when bgwriter process will be executed but I found a blog on internet they say that after each search, the BGWRITER chooses some number of modified pages, writes them to disk, and evicts those pages from the shared buffer pool.
--
Ing. Henry G. Cortez
On 2021-Sep-03, Henry Francisco Garcia Cortez wrote: > Hi community, I was reading about background writer process and > checkpoints, but I still don't understand very well. How do they work for > instance I understand this if a row was updated,those changes will be on > shared buffers, then those changes will go to path on disk called pg_wall, > then when a checkpoint will be executed and finally, those changes will go > to data files when bgwriter process will be executed but I found a blog on > internet they say that after each search, the BGWRITER chooses some number > of modified pages, writes them to disk, and evicts those pages from the > shared buffer pool. Everything you wrote here is correct. Row changes are logged to WAL (which is a journal -- always written to disk), and initially remain in shared buffers. They can be written down from shared buffers to disk either at a checkpoint, or by bgwriter. If they're not written down by those processes, and the system crashes, then when the system comes back, WAL is read and replayed. This restores the change that was done before the crash. > https://flylib.com/books/en/2.290.1/the_postgresql_bgwriter_process.html I'm not familiar with this book. -- Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
On Friday, September 3, 2021, Henry Francisco Garcia Cortez <garcortez@gmail.com> wrote:
Hi community, I was reading about background writer process and checkpoints, but I still don't understand very well. How do they work
At a basic level the database is comprised of files representing its current data (tables) and a record of all changes (wal).
For performance, the current data is kept in volatile memory. Since losing data is not an option something (the WAL) has to be written to disk.
Now, it is not feasible for the current data to only exist in RAM, so there are processes that save the current data to disk as well. The bgwriter and checkpointer processes do this. Each has different goals and configurations but they work together and the nuances are outside my scope here.
Since we are recording the current data to disk, at some point the changes that resulted in that particular current data state become obsolete. A checkpoint record is added to the WAL at locations where it is known that all previous change data is obsolete due to the writing of current data to disk. This happens periodically while the server is running, controlled by various configuration variables. A user may also issue a checkpoint SQL command to have the server record all current data to disk immediately so that a new checkpoint record can be written to the WAL.
Shared buffers are the table files (fragments) that are in volatile memory while the heap refers to those tables files on persistent disk.
During crash recovery the current data on disk is taken as the starting point, and then all changes since the last checkpoint are applied to bring the system up to the current data state that was in volatile memory at the time of the crash. Some of the post-final-checkpoint changes may have already made it to the current data disk files, that’s ok, reapplying those changes, so long as done in sequential order, will result in a correct final state.
Side note: The last paragraph also means that during crash recovery one cannot stop the change application process early - those future changes are in the disk table files and cannot be undone, which would have to happen if stopping at a time before they would have occurred.
David J.