0

I am setting up Postgres v14 database. I have a question related to the max_wal_size and wal_keep_size parameters.

According to the documentation:

max_wal_size ⇒ Maximum size to let the WAL grow during automatic checkpoints. This is a soft limit; WAL size can exceed max_wal_size under special circumstances, such as heavy load, a failing archive_command, or a high wal_keep_size setting.

wal_keep_size ⇒ Specifies the minimum size of past log file segments kept in the pg_wal directory, in case a standby server needs to fetch them for streaming replication.

What will happen if the value of wal_keep_size is greater than the value of max_wal_size, and the standby server is slow and lagging in replication by more than wal_keep_size?
wal_keep_size worth of data will be retained in the WAL directory on the primary. But since the WAL files size exceeded max_wal_size, will the checkpoint keep running until the size comes down below max_wal_size?

1 Answer 1

0

The behavior or lag on the standby side does not influence the behavior of the primary server, as far as these parameters are concerned. If the standby falls behind by more than max_wal_size, it is dead, but the primary doesn't care.

Also, the duration of a checkpoint doesn't influence how many WAL segments are deleted. A checkpoint flushes all data modifications prior to a certain log sequence number (LSN) to disk. The checkpoint can be triggered by max_wal_size, but its duration is not directly dependent on how much WAL there is.

There is a connection between checkpoints and WAL size, but that only comes into play when the checkpoint is done. At that point, PostgreSQL deletes or recycles WAL segments that are no longer needed. The number of WAL segments recycled depends on a heuristic that takes the amount of recently written WAL into account, and the only important concern is that there have to be at least min_wal_size worth of WAL segments for future use. max_wal_size does not come into play here at all.

max_wal_size is one of the most misunderstood PostgreSQL parameters. For obvious reasons, people think that it is a kind of size limit for WAL. In reality, it only governs when checkpoints are started. Sure, WAL is deleted at the end of a checkpoint, so there is a connection to WAL size, but that is somewhat coincidental.

wal_keep_size does not enter into this at all. Checkpoints are triggered by the size of pg_wal, but by the amount of WAL written since the last checkpoint

2
  • My question is about checkpoint being triggered when wal size become greater than max_wal_size. supppose wal_keep_size = 2GB max_wal_size = 1GB and standby fail. wal will keep accumulating upto 2GB making it greater than 1GB(max_wal_size). then checkpoint will keep getting triggered irrespective of checkpoint interval specified by checkpoint_timeout. No ? max_wal_size is most misunderstood because the name is not very intuitive.
    – dsingh
    Sep 23 at 11:20
  • I added a sentence that should explain that. Sep 23 at 11:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.