Postgresql – What happens when checkpoint_segments reached

checkpointpostgresql

I'm a bit confused with checkpoint_segments definition in PostgreSQL documentation. What happens when checkpoint_segments reached?

  1. The dirty pages in shared_buffers written to WAL. Then what is the
    usage of background writer?
  2. WAL is merged to actual data files.

Best Answer

Combing through the docs, I see the following (accidental incorrect details will hopefully (or surely) be pointed out by others):

There is a separate server process called the background writer, whose function is to issue writes of "dirty" (new or modified) shared buffers.

BW does its work according to its config parameters. It writes at most bgwriter_lru_maxpages dirty shared buffers, then has a bgwriter_delay millisecond rest. Now, when it comes to checkpoint_segments log segments (or checkpoint_timeout seconds, if it comes first), it flushes all (remaining) dirty segments to data files. Now the database data has a consistent state on disk, with a corresponding entry in the log as well. It is a good starting point for a subsequent REDO if necessary.

And who does make the checkpoints?

The server's background writer process automatically performs a checkpoint [...]

As for your 2nd point, WAL is WAL (stored in log segment files) and data files are data files. The two will be consistent with each other but not merged.

Related Question