About checkpoint_segments parameter

I don't know the parameter "checkpoint_segments" very well, My personal views is that it means the number of wal log files; I configured the value of the parameter to 64, but I can see 131 files in the pg_xlog directory.

Can someone explain the parameter?

--detail
postgres=# show checkpoint_segments;
 checkpoint_segments 
---------------------
 64
(1 row)

[postgres@pg_root]$ cd $PGDATA
[postgres@pg_root]$ ls -lrt pg_xlog | wc -l
132

Solution 1:

For background: WAL (write-ahead logging) is the mechanism for ensuring that the database can be recovered in the event of a crash without requiring the data files be flushed after every write. Basically, before any change is written to the data files, it is first logged. In the event of a crash, the log can be used to redo any changes that didn't make it to disk.

Each file in pg_xlog is a WAL segment. checkpoint_segments, along with checkpoint_timeout, controls when a WAL checkpoint is done (a checkpoint is done after checkpoint_segments segments have been filled or checkpoint_timeout seconds have passed, whichever comes first); a checkpoint is a moment in time where it is guaranteed that the data files have been flushed, so the redo process doesn't need to consider any transactions in the log from before the checkpoint.

According to the documentation (which is where I cribbed most of the above from), prior to 8.3 there will normally be 2 * checkpoint_segments + 1 segment files in the pg_xlog directory; since 8.3, the expected number is (2 + checkpoint_completion_target) * checkpoint_segments + 1 (or wal_keep_segments in 9.0, if that is greater). In any case, 131 segment files for a checkpoint_segments of 64 is near the minimum to be expected.