Re: "full_page_writes" makes no difference?

On Wed, 2011-05-04 at 00:17 -0400, Tian Luo wrote:
> So, "nbytes" should always be multiples of XLOG_BLCKSZ, which in the
> default case, is 8192.
>
> My question is, if it always writes full pages no matter
> "full_page_writes" is on or off, what is the difference?

Most I/O systems and filesystems can end up writing part of a page (in
this case, 8192 bytes) in the event of a power failure, which is called
a "torn page". That can cause problems for postgresql, because the page
will be a mix of old and new data, which is corrupt.

The solution is "full page writes", which means that when a data page is
modified for the first time after a checkpoint, it logs the entire
contents of the page (except the free space) to WAL, and can use that as
a starting point during recovery. This results in extra WAL data for
safety, but it's unnecessary if your filesytem + IO system guarantee
that there will be no torn pages (and that's the only safe time to turn
it off).

So, to answer your question, the difference is that full_page_writes=off
means less total WAL data, which means fewer 8192-byte writes in the
long run (you have to test long enough to go through a checkpoint to see
this difference, however). PostgreSQL will never issue write() calls
with 17 bytes, or some other odd number, regardless of the
full_page_writes setting.

I can see how the name is slightly misleading, but it has to do with
whether to write this extra information to WAL (where "extra
information" happens to be "full data pages" in this case); not whether
to write the WAL itself in full pages.

Regards,
Jeff Davis