Re: Index Scans become Seq Scans after VACUUM ANALYSE

On Fri, 21 Jun 2002, Tom Lane wrote:

> Curt Sampson <cjs(at)cynic(dot)net> writes:
> > And now you know which parts of your page got written, and which
> > parts didn't.
>
> Yes ... and what do you *do* about it?

Ok. Here's the extract from _Inside Microsoft SQL Server 7.0_, page 207:

torn page detection When TRUE, this option causes a bit to be
flipped for each 512-byte sector in a database page (8 KB)
whenever the page is written to disk. This option allows
SQL Server to detect incomplete I/O operations caused by
power failures or other system outages. If a bit is in the
wrong state when the page is later read by SQL Server, this
means the page was written incorrectly; a torn page has
been detected. Although SQL Server database pages are 8
KB, disks perform I/O operations using 512-byte sectors.
Therefore, 16 sectors are written per database page. A
torn page can occur if the system crashes (for example,
because of power failure) between the time the operating
system writes the first 512-byte sector to disk and the
completion of the 8-KB I/O operation. If the first sector
of a database page is successfully written before the crash,
it will appear that the database page on disk was updated,
although it might not have succeeded. Using battery-backed
disk caches can ensure that data is [sic] successfully
written to disk or not written at all. In this case, don't
set torn page detection to TRUE, as it isn't needed. If a
torn page is detected, the database will need to be restored
from backup because it will be physically inconsistent.

As I understand it, this is not a problem for postgres becuase the
entire page is written to the log. So postgres is safe, but quite
inefficient. (It would be much more efficient to write just the
changed tuple, or even just the changed values within the tuple,
to the log.)

Adding these torn bits would allow posgres at least to write to
the log just the 512-byte sectors that have changed, rather than
the entire 8 KB page.

cjs
--
Curt Sampson <cjs(at)cynic(dot)net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC