Re: journaled FS and and WAL

t(dot)dalpozzo(at)gmail(dot)com wrote:
> two question related to the WAL.
>
> 1) I read in the doc that journaled FS is not important as WAL is
> journaling itself. But who garantees that the WAL is written correctly?
> I know that it's sequential and a partial update of WAL can be discarded
> after a restart. But am I sure that without a journaled FS, if there is
> a crash during the WAL update, nothing already updated in the WAL before
> my commit can get corrupted?

At commit time, the WAL is "synchronized": PostgreSQL instructs the operating
system to write the data to the physical medium (not just a memory cache)
and only return success if that write was successful.

After a successful commit, the WAL file and its metadata are on disk.
Moreover, the file metadata won't change (except for the write and access
timestamps) because WAL files are created with their full size and never
extended, so no WAL file should ever get "lost" because of partial metadata
writes.

> 2) Let's suppose that I have one database, one table of 100000 rows,
> each 256 bytes. Now, in a single SQL commit, I update row 10, row 30000
> and row 80000. How much should I expect the WAL increase by? (supposing
> no WAL segments will be deleted). I could guess 8192x3 but I'm not sure

It will be that much immediately after a checkpoint, but for subsequent writes
to the same disk block only the actually changed parts of the data block will
be written to WAL.

Yours,
Laurenz Albe