Full Page Writes in PostgreSQL is a crash recovery method to protect us from torn page writes. In this blog, we will explain why we have full page writes and why they exist.
So, first let’s understand the problem.
How does Torn Page Writes Occur?
Postgres keeps tables and indexes as 8KB pages. For example, let’s consider page 39, which looks like (page 39 - 8KB). Now, when a transaction updates a row in that page, the page in the shared buffer becomes dirty (page 39 - modified), and slowly the background writer or checkpointer writes this page to disk.
During this write, a crash occurred, and the disk got half of the page as new and the remaining stays old; that is, the page will be 4KB new and 4 KB old. This is called a torn page. If the checksum is enabled, postgres will detect half-written pages and denote a logical corruption in the page.
Why Full Page Writes Exist?
PostgreSQL has a parameter called full_page_writes, which is on by default. This brings the solution to avoid torn page writes. Here, with full_page_writes being on, when a page is modified after each checkpoint for the first time. Instead of writing just the row update, PostgreSQL embeds a complete 8KB copy of the page (a backup block) directly inside that single WAL record.
For example,
WAL will have a full image of page 39 (8kb), update the record, and commit. This full copy of the page is called FPI or full page image.
After this, even if page 39 is updated 10 times, only those changes are updated to WAL before the next checkpoint. This will reduce the WAL volume we have to keep.
Refer to the image given below:
How FPW helps in Recovery:
Suppose postgres have just done a checkpoint, where all the dirty pages are flushed to the disk (including page 39). Then, a new modification happens to page 39 after the checkpoint, since this is the first change after the checkpoint, postgres will have wrote a FPI (copy of 8Kb) to WAL, and a normal WAL record denoting the update made. Later, a system crash occurred before postgres was able to completely write the entire 8KB block to disk (only half, i.e., 4KB, was written), which led to the 8KB block being in a corrupted or inconsistent state. So during crash recovery, psql will read the WAL and see a full page image, which was taken after the last checkpoint, and use this one to restore page 39 to a consistent state before replaying any subsequent WAL records. This will ensure the corrupted page is recovered and db remain consistent after recovery
Why does PostgreSQL take the FPI after every Checkpoint?
After a new checkpoint postgres thinks that the latest version of this page is already safely written or flushed to disk. And the old FPI (full page image or copy) is not enough anymore to recovery processes, thus the new update or change to that page creates a new full page copy.
Some Performance Considerations
FPW will increase the WAL generation due to the additional 8 KB page copy. This will show a great change in WAL volume when many page modifications happen for the first time after a checkpoint, because many pages will have to keep the full page image for their first change. Once a page has taken its FPI, then the following modifications are only kept as a normal small WAL record.
The FPW might be creating WAL traffic in case of databases with frequent updates and frequent checkpoints. But this overhead is worth it because it gives protection from torn pages.
Can we disable the Full Page Writes?
Yes, full page writes can be kept off using the parameter full_page_writes in Postgresql.conf.
full_page_writes = off
But keeping this off is strongly discouraged as there is a chance of causing page corruption (torn pages) during a crash, which cannot be safely recovered from WAL recovery alone. The full_page_writes parameter is kept on by default in PostgreSQL.
In short, full page writes are not designed to keep the postgres faster but to make postgresql safe from torn pages and recover a corrupted page during crash recovery, which cannot be done with normal WAL records. By recording a complete copy of a page the first time it is modified after a checkpoint, PostgreSQL ensures that crash recovery always has a clean, consistent version of the page available.