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:
- When the administrator runs the CHECKPOINT statement.
- With the interval specified in parameter checkpoint_timeout(default 300 seconds)
- Amount of data written to the WAL has reached a parameter max_wal_size (default: 1GB).
- At the start of an online backup
- At the execution of pg_start_backup function
- At the execution of a pg_basebackup command
- At the shutdown of an instance(Except for the pg_ctl stop -m immediate command execution)
- 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
- 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:
- checkpoint_timeout
- max_wal_size
- min_wal_size
- checkpoint_completion_target
- checkpoint_flush_after
- 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