If I understand correctly, when I delete a record and call commit, postgres will update write-ahead log (wal) and wait for the checkpoint then flush changes to the file.

My question is:

• Is there any way I can recover deleted record after committed and before postgres checkpointing?
• Btw, why is this method reducing disk write? Isn't wal an append log file?

I couldn't find anywhere how to do this without paying for postgres engineers.

CodePudding user response：

Changes to the datafiles must be written and flushed by the end of the next checkpoint, but they can also be written earlier if the pages need to be (or are expected to be) evicted to make room for other data to be read in. As soon as the corresponding WAL is flushed, the change to the datafile is eligible to be written.

There is no user-available way to recover the deleted record. You could force a crash, then interfere with the recovery process. But you would have to be quite an expert to pull this off. It would be easier to just retrieve the record from a backup and then re-insert it. You don't even need to be an engineer to make this happen, just have valid backups (possibly including WAL archives) and know how to use them. Or better yet, don't commit things you don't want committed.

The system is designed this way for crash safety, not for reduced disk writing.