PostgreSQL Tutorial for Absolute Beginners [Administration]
About Lesson

The point at which the memory and storage to guarantee the persistence by synchronized called a checkpoint. The pages that have been modified on shared memory in order to create a checkpoint writes to storage.

A checkpoint occurs in the following cases:

  1. When the administrator runs the CHECKPOINT statement.
  2. With the interval specified in parameter checkpoint_timeout(default 300 seconds)
  3. Amount of data written to the WAL has reached a parameter max_wal_size (default: 1GB).
  4. At the start of an online backup
  5. At the execution of pg_start_backup function
  6. At the execution of a pg_basebackup command
  7. At the shutdown of an instance(Except for the pg_ctl stop -m immediate command execution)
  8. At the time of database configuration such as CREATE DATABASE / DROP DATABASE statement

During a CHECKPOINT, the database needs to perform these three basic steps

  1. Identify all the dirty pages in shared buffers.
    2. Write the dirty pages to the respective files.
    3. Issue fsync() on all the modified files to disk.

NOTE:The fsync() function is intended to force a physical write of data from the buffer cache, and to assure that after a system crash or other failure that all data up to the time of the fsync() call is recorded on the disk.

Checkpoint parameters in PostgreSQL:

  1. checkpoint_timeout
  2. max_wal_size
  3. min_wal_size
  4. checkpoint_completion_target
  5. checkpoint_flush_after
  6. checkpoint_warning

Default settings in Postgresql.conf file

# – Checkpoints –
#checkpoint_timeout = 5min              # range 30s-1d
max_wal_size = 1GB
min_wal_size = 80MB
#checkpoint_completion_target = 0.5     # checkpoint target duration,0.0- 1.0
#checkpoint_flush_after = 256kB         # measured in pages, 0 disables
#checkpoint_warning = 30s               # 0 disables

Further reading here