1

I know there's "torn writes"(aka, "partial writes") issue about PostgreSQL data file, and to prevent it, FPW(full page write) mechanism are adopted.
So is there also kind of "partial writes" issue on WAL segment file? If so, is there any mechanism to prevent it? If no prevention, that means a commited transaction would lost on "WAL segment file partial writes"?

Actually, I have question about FPW too. Let's based on the normally modern Linux, the checkpoint adoptes "sync" mode normally, right? If so, a 8K writing only returns on the successes of both 4K OS page writings, is that right? If so how could the "partial writes" happen? Please correct my understand.

Thanks in advance!

UPDATE:
jjanes answers me that:

A partial write on a WAL page would fail the checksum when it is read back in, and so would be interpreted as being just beyond the end of WAL. So none of the partial record would be replayed.

I think I can understand this, while wouldn't this cause any data loss? Especially the WAL partial writes happened on a committed transaction then restart to replay, is it possible, although very rare?

UPDATE:

Below provides some notes FYI, please correct me if necessary:

  • torn writes(aka partial writes)
    • an on-disk block that contains a mix of old and new data.
    • PostgreSQL uses 8kB pages(by default), but OS filesystems typically use 4kB pages.
    • buffered IO are adopted for both data files and WAL files, so both could have torn writes
      • WAL
        • committed WAL: transactions are not reported as being committed until the WAL record was completely written and synced. So no tron writes
        • uncommitted WAL: A partial write on a uncommitted WAL page would fail the checksum when it is read back in, and so would be interpreted as being just beyond the end of WAL. So none of the partial record would be replayed.
      • data file
        • Even checkpoint calls fsync() usually, writes from bgwriter are buffered IO, so a crash could lead to torn writes
        • Unlike WAL, The checksum method won't work with the data files, because data files can be overwritten, and we cannot just discard the blocks with failed checksum.
        • Unlike WAL replaying, there's no chance to check blocks one by one neither
        • Where to find the correct data of partial writes blocks?
        • The WAL replaying must base on a healthy block, so it will not be enough to completely restore partial writes block during post-crash recovery
        • FPW is used for data file partial writes

1 Answer 1

2

A partial write on a WAL page would fail the checksum when it is read back in, and so would be interpreted as being just beyond the end of WAL. So none of the partial record would be replayed.

If so, a 8K writing only returns on the successes of both 4K OS page writings, is that right? If so how could the "partial writes" happen?

A partial write can only happen as part of a crash or serious error. But we hope to be able to recover from those...

while wouldn't this cause any data loss? Especially the WAL partial writes happened on a committed transaction then restart to replay

Unless synchronous_commit is turned off, transactions are not reported as being committed until the WAL record was completely written and synced. So the data which was lost would be data which was never reported as committed in the first place. If you issue a COMMIT and you don't get back a success message because the system crashes first, then you don't know if the data will be there or not once the system comes back up.

Now if the system claimed the data was written and synced when it wasn't, that could lead to loss of committed data, but that is not particular to torn writes--that is just OS-induced data corruption. If the OS says "something went wrong", the database needs to be able to recover. But if the OS claims everything went right even thought it did not, then you are just hosed. The database can't fix that and is not expected to.

The checksum method won't work with the data files, because they can be overwritten. So a bad checksum will tell you the current data page is corrupted, but without a FPW there is no way to retrieve the old uncorrupted value to recover it. Since WAL is formally never overwritten, the problem doesn't occur there.

5
  • Thanks for your answer, would you check my update in question? May 30, 2023 at 1:52
  • THX. I understand that "sync mode" is needed to ensure WAL integrity, so can't we use a similar mechanism to prevent data file torn writes?(then there's no need of FWP) Do you mean, the data file torn writes are caused by 'OS-induced data corruption', and even so, we still "hope to be able to recover from those...", is that you point? May 31, 2023 at 2:22
  • Syncing all data files on COMMIT would make for very bad performance. That's not an option. May 31, 2023 at 6:49
  • @LaurenzAlbe and jjanes, thanks a lot. So far I understand WAL isn't in trouble of torn writes, but may not be clear enough about how torn writes happen on data files. I thought torn writes happened during checkpoint disk flushing, that seems to be wrong as checkpoint uses sync mode, isn't it? My current understanding is that, even checkpoint uses sync mode, the following writes from bgwriter are async, so a crash during such async write could cause unhealthy block meanwhile WAL replaying must base on healthy block, so FPW is necessary. Would you confirm my current understanding? Jun 1, 2023 at 7:30
  • 1
    That is right, torn writes will mostly happen if the operating system crashes during a checkpoint, because that's when most of the data are persisted. Checkpoints use buffered I/O like everything else in PostgreSQL and sync files to persist them to disk. Torn page writes will mostly occur if the crash happens while the operating system syncs a file to disk. Jun 1, 2023 at 7:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.