Thread

[PATCH] Expose checkpoint timestamp and duration in pg_stat_checkpointer

Soumya S Murali <soumyamurali.work@gmail.com> — 2025-11-24T06:10:44Z

Hi all,

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

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)

2025-11-20 11:58:02.879 IST [18026] LOG:  checkpoint complete
(immediate): wrote 0 buffers (0.0%), wrote 0 SLRU buffers; 0 WAL
file(s) added, 0 removed, 0 recycled; write=0.001 s, sync=0.001 s,
total=0.019 s; sync files=0, longest=0.000 s, average=0.000 s;
distance=0 kB, estimate=478214 kB; lsn=0/7F4EDFE0, redo lsn=0/7F4EDF88

Looking forward to more feedback.

Regards,
Soumya