Analysis of PostgreSQL DBA (20)-WAL full-page-write

Full-page-write

At T1, the database executes checkpoint successfully

At T2, execute the DML statement, at which point the relevant data is written to WAL (WAL buffer is ignored here)

At T3, commit the transaction

In T4 BGWriter, dirty pages is written into Data file, but the failure of the machine in the writing process leads to Crash (such as power loss, etc.), and partial writing occurs.

To deal with this situation, when PG writes to WAL at T2, it writes the entire page of the changed page to WAL, not just tuple data. When database restart performs recovery, when Redo point starts to play back WAL, if XLOG Record is found to be FPI (full-page-image), then full-page replacement, through this mechanism to solve the problem of partial writing.

Second, the price of full-page-write

Of course, this mechanism is not free, and its main negative effect is writing magnification.

As a result of full-page writing, redundant data is inevitable; consider such a situation: if the database is very busy, and the hot spots of the data are scattered on different table, and the execution interval of checkpoint is short, then a lot of page will be written through full-page-write in the WAL, resulting in a rapid expansion of log space. In extreme cases, updating one of the records with page "fully loaded" (with little free space) will result in a full page being written to WAL.

For the mechanisms and solutions in this section, "how to curb the crazy growth of PostgreSQL WAL" in Resources is discussed in detail.

III. Reference materials

Write Ahead Logging-WAL

How to curb the crazy growth of PostgreSQL WAL

PostgreSQL Reliability Analysis-about redo block Atomic Writing