
STEP 2: The modified pages are termed as dirty pages. STEP 1: Whenever PostgreSQL needs to change anything on a give a table, it checks if it is in shared_buffers, if yes, it modifies them directly, and if not – it loads the required pages into shared_buffers and then modifies them. Let’s understand the life cycle of a database change.Īs we all know PostgreSQL keeps a cache of pages in RAM(shared_buffers) to make it available to all backends for faster access. In the event of a crash, the crash recovery procedure looks at the latest checkpoint record to determine the point in the Write ahead log from which it should start the REDO operation.

#Database checkpoint in winrunner update#
If it chose to write directly to the data files, it will have to physically locate the data block in the datafile first and then update it which means that after committing, a user has to wait until it finds and writes.

Why doesn’t PostgreSQL write the changes to data files right away when we commit the transaction? If the instance crashed right then, the DB shared buffers will be wiped out but on restarting the database, PostgreSQL will apply the changes recorded in WAL files to the corresponding data files. On commit, the changes we made are written to the wal file on disk thereby making them permanent. Record of the change made is kept in wal buffer. When we update any row, the buffer in DB shared buffers corresponding to the block containing that row is updated in memory. As blocks are read, they are stored in DB shared buffers so that if any user accesses them later, they are available in memory and need not be read from the disk.

PostgreSQL Checkpoint: Database blocks are temporarily stored in Database shared buffers.
