When checkpoint happens in Postgres

Checkpoint is the processes of writing the dirty buffers from the shared buffer(RAM) to table or index segment files.The server’s checkpointer process automatically performs a checkpoint every so often.

In postgres , a checkpoint is begun every checkpoint_timeout seconds, or if max_wal_size is about to be exceeded, whichever comes first.By default 60 sec and 1 GB.

checkpoint_timeout : Maximum time between automatic WAL checkpoints, in seconds. The valid range is between 30 seconds and one hour. The default is five minutes (5min).

max_wal_size : Maximum size to let the WAL grow to between automatic WAL checkpoints. The default is 1 GB.

It is also possible to force a checkpoint by using the SQL command CHECKPOINT.

When we start the backup using the pg_start_backup() , postgres perform the checkpoint.

NOTE : If no WAL has been written since the previous checkpoint, new checkpoints will be skipped even if checkpoint_timeout has passed.

To monitor the checkpoint message set the “log_checkpoints” parameter to on. This will log the messages in logfiles(by default in pg_log directory).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s