Mastering PostgreSQL 10

Understanding checkpoints

As I have mentioned earlier, every change is written to the WAL in binary format (it does not contain SQL). The problem is this: the database server cannot keep writing to the WAL forever as it will consume more and more space over time. So, at some point, the transaction log has to be recycled. This is done by a checkpoint, which happens automatically in the background. The idea is the following: when data is written, it first goes to the transaction log, and then a dirty buffer is put into shared buffers. Those dirty buffers have to go to disk and are written out to the data files by the background writer or during a checkpoint. As soon as all dirty buffers up to that point have been written, the transaction log ...