On Mon, Nov 24, 2025 at 2:48 PM Michael Banck <[email protected]> wrote: > > Hi, > > On Mon, Nov 24, 2025 at 11:40:44AM +0530, Soumya S Murali wrote: > > While debugging checkpointer write behavior, I recently found some of the > > enhancements related to extending pg_stat_checkpointer by including > > checkpoint type (manual/timed/immediate), last_checkpoint_time and > > checkpoint_total_time information to checkpoint completion logs through SQL > > when `log_checkpoints` is enabled. I am attaching my observations, > > screenshots and patch in support for this. > > > > 1. Log for type of checkpoint occured: > > > > 2025-11-20 11:51:06.128 IST [18026] LOG: checkpoint complete > > (immediate): wrote 7286 buffers (44.5%), wrote 4 SLRU buffers; 0 WAL > > file(s) added, 0 removed, 27 recycled; write=0.095 s, sync=0.034 s, > > total=0.279 s; sync files=17, longest=0.004 s, average=0.002 s; > > distance=447382 kB, estimate=531349 kB; lsn=0/7F4EDED8, redo > > lsn=0/7F4EDE80 > > I think that'd be useful; the checkpoint complete log line clearly has > the more interesting output, and having it state the type would make it > easier to answer question like "how many buffers did the last wal-based > checkpoint write?
Thank you for the feedback and glad to hear that exposing the checkpoint type in the completion log seems useful. My main motivation was exactly this kind of analysis: being able to know the buffer write patterns with the type of checkpoint that triggered them. > > 2. Log for the checkpoint_total_time and last_checkpoint_time: > > > > checkpoint_total_time | last_checkpoint_time > > -----------------------+---------------------------------- > > 175138 | 2025-11-20 11:58:02.879149+05:30 > > (1 row) > > Reading throught the patch, it looks like checkpoint_total_time is the > total time of the last checkpoint? > > > + proparallel => 'r', prorettype => 'float8', proargtypes => '', > > + prosrc => 'pg_stat_get_checkpointer_checkpoint_total_time' }, > > If so, the naming is pretty confusing, last_checkpoint_duration or > something might be clearer. > > In general I doubt how much those gauges (as oppposed to counters) only > pertaining to the last checkpoint are useful in pg_stat_checkpointer. > What would be the use case for those two values? > > Also, as a nitpick, your patch adds unnecessary newlines and I think > stats_reset should be kept as last column in pg_stat_checkpointer as > usual. > > > Michael Yes, the field is intended to represent the duration of the most recently completed checkpoint, and I agree that renaming it to last_checkpoint_duration would make the purpose more clear. Even though it is a single value, it can still help monitoring tools capture and store each duration over time, so I’ll refine the naming, remove the unnecessary newlines, and keep stats_reset as the last column as suggested. Regards Soumya
