log_checkpoints: count WAL segment creations from all processes

Started by Xuneng Zhou8 months ago10 messageshackers
Jump to latest
#1Xuneng Zhou
xunengzhou@gmail.com

Hi hackers,

This patch implements a suggestion from Andres' "Desired Changes" list
[1]: https://wiki.postgresql.org/wiki/User:Andresfreund/Desired_Changes#Add_information_about_the_number_of_newly_created_WAL_segments_to_log_checkpoints_output
count in 'log_checkpoints' output. The "WAL file(s) added" field in
checkpoint/restartpoint log output currently counts only segments
added through PreallocXlogFiles(). It does not include segments
created by other paths such as backend WAL growth, walreceiver, or
timeline initialization, so on a write-heavy system the reported value
can stay at 0 or 1 even when many new WAL segments were actually
created.

This version fixes the accounting by adding a shared-memory atomic
counter, walSegmentsCreated, to XLogCtlData. The counter is
incremented in the WAL-segment creation/install paths,
XLogFileInitInternal() and XLogFileCopy() (used for timeline
switches). Each checkpoint or restartpoint reports WAL file(s) added
as the difference between the current counter value and a saved
baseline, walSegsCreatedLastCheckpoint, then advances that baseline.
Because the baseline is initialized to 0, the first checkpoint after
startup counts all segments created since startup, including
timeline-initialization segments created before the end-of-recovery
checkpoint is requested.

The semantic change is intentional: 'ckpt_segs_added' (and arg2 of the
TRACE_POSTGRESQL_CHECKPOINT_DONE probe) now means "new WAL segments
created since the previous successful checkpoint or restartpoint, by
any process." The probe arity is unchanged. config.sgml and
monitoring.sgml are updated accordingly.

Patch attached. Any feedback is welcome.

[1]: https://wiki.postgresql.org/wiki/User:Andresfreund/Desired_Changes#Add_information_about_the_number_of_newly_created_WAL_segments_to_log_checkpoints_output

--
Best,
Xuneng

Attachments:

v1-0001-Count-WAL-segment-creations-by-all-processes-in-l.patchapplication/x-patch; name=v1-0001-Count-WAL-segment-creations-by-all-processes-in-l.patchDownload+55-7
#2Zsolt Parragi
zsolt.parragi@percona.com
In reply to: Xuneng Zhou (#1)
Re: log_checkpoints: count WAL segment creations from all processes

Hello!

This is a simple patch, but shouldn't it include at least some basic
tests verifying the new behavior?

#3Xuneng Zhou
xunengzhou@gmail.com
In reply to: Zsolt Parragi (#2)
Re: log_checkpoints: count WAL segment creations from all processes

Hi Zsolt,

On Tue, Mar 24, 2026 at 1:55 PM Zsolt Parragi <zsolt.parragi@percona.com>
wrote:

Hello!

This is a simple patch, but shouldn't it include at least some basic
tests verifying the new behavior?

Thanks for looking into this. I've added a test for it. Please take a look.

--
Best,
Xuneng

Attachments:

v2-0001-Count-WAL-segment-creations-by-all-processes-in-l.patchapplication/octet-stream; name=v2-0001-Count-WAL-segment-creations-by-all-processes-in-l.patchDownload+130-7
#4Japin Li
japinli@hotmail.com
In reply to: Xuneng Zhou (#3)
Re: log_checkpoints: count WAL segment creations from all processes

Hi, Xuneng

On Tue, 24 Mar 2026 at 19:17, Xuneng Zhou <xunengzhou@gmail.com> wrote:

Hi Zsolt,

On Tue, Mar 24, 2026 at 1:55 PM Zsolt Parragi <zsolt.parragi@percona.com> wrote:

Hello!

This is a simple patch, but shouldn't it include at least some basic
tests verifying the new behavior?

Thanks for looking into this. I've added a test for it. Please take a look.

Thanks for updating the patch. A few comments on v2:

1.
+       (The probes listed next fire in sequence during checkpoint processing.)
+       arg0 is the number of buffers written. arg1 is the total number of

These changes seem unnecessary. Additionally, there appears to be an
indentation issue.

2.
+    current = pg_atomic_read_u64(&XLogCtl->walSegmentsCreated);
+    CheckpointStats.ckpt_segs_added = (int)
+        (current - XLogCtl->walSegsCreatedLastCheckpoint);
+    XLogCtl->walSegsCreatedLastCheckpoint = current;

Is integer overflow a concern here? It seems unlikely in practice.

--
Best,
Xuneng

[4. text/x-diff; v2-0001-Count-WAL-segment-creations-by-all-processes-in-l.patch]...

--
Regards,
Japin Li
ChengDu WenWu Information Technology Co., Ltd.

#5Xuneng Zhou
xunengzhou@gmail.com
In reply to: Japin Li (#4)
Re: log_checkpoints: count WAL segment creations from all processes

Hi Japin,

Thanks for looking into this.

On Tue, Mar 24, 2026 at 10:01 PM Japin Li <japinli@hotmail.com> wrote:

Hi, Xuneng

On Tue, 24 Mar 2026 at 19:17, Xuneng Zhou <xunengzhou@gmail.com> wrote:

Hi Zsolt,

On Tue, Mar 24, 2026 at 1:55 PM Zsolt Parragi <zsolt.parragi@percona.com> wrote:

Hello!

This is a simple patch, but shouldn't it include at least some basic
tests verifying the new behavior?

Thanks for looking into this. I've added a test for it. Please take a look.

Thanks for updating the patch. A few comments on v2:

1.
+       (The probes listed next fire in sequence during checkpoint processing.)
+       arg0 is the number of buffers written. arg1 is the total number of

These changes seem unnecessary. Additionally, there appears to be an
indentation issue.

Yeah, I've removed these and fixed the indentation issue.

2.
+    current = pg_atomic_read_u64(&XLogCtl->walSegmentsCreated);
+    CheckpointStats.ckpt_segs_added = (int)
+        (current - XLogCtl->walSegsCreatedLastCheckpoint);
+    XLogCtl->walSegsCreatedLastCheckpoint = current;

Is integer overflow a concern here? It seems unlikely in practice.

I don’t think overflow is a concern here, but it might still be
helpful to add some comments to mention it.

--
Best,
Xuneng

[4. text/x-diff; v2-0001-Count-WAL-segment-creations-by-all-processes-in-l.patch]...

--
Best,
Xuneng

Attachments:

v3-0001-Count-WAL-segment-creations-by-all-processes-in-l.patchapplication/octet-stream; name=v3-0001-Count-WAL-segment-creations-by-all-processes-in-l.patchDownload+135-5
#6Xuneng Zhou
xunengzhou@gmail.com
In reply to: Xuneng Zhou (#5)
Re: log_checkpoints: count WAL segment creations from all processes

On Wed, Mar 25, 2026 at 9:11 AM Xuneng Zhou <xunengzhou@gmail.com> wrote:

Hi Japin,

Thanks for looking into this.

On Tue, Mar 24, 2026 at 10:01 PM Japin Li <japinli@hotmail.com> wrote:

Hi, Xuneng

On Tue, 24 Mar 2026 at 19:17, Xuneng Zhou <xunengzhou@gmail.com> wrote:

Hi Zsolt,

On Tue, Mar 24, 2026 at 1:55 PM Zsolt Parragi <zsolt.parragi@percona.com> wrote:

Hello!

This is a simple patch, but shouldn't it include at least some basic
tests verifying the new behavior?

Thanks for looking into this. I've added a test for it. Please take a look.

Thanks for updating the patch. A few comments on v2:

1.
+       (The probes listed next fire in sequence during checkpoint processing.)
+       arg0 is the number of buffers written. arg1 is the total number of

These changes seem unnecessary. Additionally, there appears to be an
indentation issue.

Yeah, I've removed these and fixed the indentation issue.

2.
+    current = pg_atomic_read_u64(&XLogCtl->walSegmentsCreated);
+    CheckpointStats.ckpt_segs_added = (int)
+        (current - XLogCtl->walSegsCreatedLastCheckpoint);
+    XLogCtl->walSegsCreatedLastCheckpoint = current;

Is integer overflow a concern here? It seems unlikely in practice.

I don’t think overflow is a concern here, but it might still be
helpful to add some comments to mention it.

v4 fixed the inaccurate overflow numbers in comments.

--
Best,
Xuneng

Attachments:

v4-0001-Count-WAL-segment-creations-by-all-processes-in-l.patchapplication/octet-stream; name=v4-0001-Count-WAL-segment-creations-by-all-processes-in-l.patchDownload+137-5
#7Hüseyin Demir
huseyin.d3r@gmail.com
In reply to: Xuneng Zhou (#6)
Re: log_checkpoints: count WAL segment creations from all processes

Hi,

Thanks for the patch. The idea is good and can we introduce a new
counter to pg_stat_checkpointer ? For example, we can add
wal_segments_created to this view and it would be useful for capacity
planning and load estimations.

Additionally I have a few questions

- Should we make walSegsCreatedLastCheckpoint atomic ? In the future
we can also add this to views maybe. It's an auxiliary suggestion.
- ckpt_segs_added is defined as int but overflow threshold is 2^32 in
comments. Shouldn't we use 2^31 for int ?
- Maybe it's a bad idea but should we extend the tests with the
wal_level set to minimal ?

PS: I created a patch to expose wal_segments_created to
pg_stat_checkpointer view. You can review it and enhance your patch if
you need. I created it to explain my idea better so it can be
destroyed after your review. But I couldn't create a patch for
preceding questions.

Regards,
Demir.

Xuneng Zhou <xunengzhou@gmail.com>, 25 Mar 2026 Çar, 03:10 tarihinde şunu yazdı:

Show quoted text

On Wed, Mar 25, 2026 at 9:11 AM Xuneng Zhou <xunengzhou@gmail.com> wrote:

Hi Japin,

Thanks for looking into this.

On Tue, Mar 24, 2026 at 10:01 PM Japin Li <japinli@hotmail.com> wrote:

Hi, Xuneng

On Tue, 24 Mar 2026 at 19:17, Xuneng Zhou <xunengzhou@gmail.com> wrote:

Hi Zsolt,

On Tue, Mar 24, 2026 at 1:55 PM Zsolt Parragi <zsolt.parragi@percona.com> wrote:

Hello!

This is a simple patch, but shouldn't it include at least some basic
tests verifying the new behavior?

Thanks for looking into this. I've added a test for it. Please take a look.

Thanks for updating the patch. A few comments on v2:

1.
+       (The probes listed next fire in sequence during checkpoint processing.)
+       arg0 is the number of buffers written. arg1 is the total number of

These changes seem unnecessary. Additionally, there appears to be an
indentation issue.

Yeah, I've removed these and fixed the indentation issue.

2.
+    current = pg_atomic_read_u64(&XLogCtl->walSegmentsCreated);
+    CheckpointStats.ckpt_segs_added = (int)
+        (current - XLogCtl->walSegsCreatedLastCheckpoint);
+    XLogCtl->walSegsCreatedLastCheckpoint = current;

Is integer overflow a concern here? It seems unlikely in practice.

I don’t think overflow is a concern here, but it might still be
helpful to add some comments to mention it.

v4 fixed the inaccurate overflow numbers in comments.

--
Best,
Xuneng

Attachments:

t53565_7
v1-0001-Add-WAL-segment-file-counts-to-pg_stat_checkpointer.patchapplication/octet-stream; name=v1-0001-Add-WAL-segment-file-counts-to-pg_stat_checkpointer.patchDownload+78-2
#8Hüseyin Demir
huseyin.d3r@gmail.com
In reply to: Hüseyin Demir (#7)
Re: log_checkpoints: count WAL segment creations from all processes

Hi,

Thanks for the patch. The idea is good and can we introduce a new
counter to pg_stat_checkpointer ? For example, we can add
wal_segments_created to this view and it would be useful for capacity
planning and load estimations.

Additionally I have a few questions

- Should we make walSegsCreatedLastCheckpoint atomic ? In the future
we can also add this to views maybe. It's an auxiliary suggestion.
- ckpt_segs_added is defined as int but overflow threshold is 2^32 in
comments. Shouldn't we use 2^31 for int ?
- Maybe it's a bad idea but should we extend the tests with the
wal_level set to minimal ?

PS: I created a patch to expose wal_segments_created to
pg_stat_checkpointer view. You can review it and enhance your patch if
you need. I created it to explain my idea better so it can be
destroyed after your review. But I couldn't create a patch for
preceding questions.

Regards,
Demir.

#9Nitin Jadhav
nitinjadhavpostgres@gmail.com
In reply to: Hüseyin Demir (#8)
Re: log_checkpoints: count WAL segment creations from all processes

Hi Xuneng,

Thank you for working on this. I've reviewed the v4 patch and wanted
to discuss the counter design approach before diving into
implementation details.

The patch uses two fields for tracking: a cumulative atomic counter
(walSegmentsCreated) that grows since startup, and a non-atomic
baseline (walSegsCreatedLastCheckpoint). At each checkpoint, you
compute ckpt_segs_added as the difference between the current counter
and the baseline, then update the baseline for the next interval.

I'd like to propose a simpler alternative using a single reset
counter. Instead of maintaining cumulative state, we could use
pg_atomic_exchange_u64 to atomically read and reset the counter in one
operation. This would require only one shared memory field and
eliminate the need for baseline tracking and subtraction arithmetic.
This approach would be simpler (one field instead of two, no
arithmetic, no baseline maintenance), follow PostgreSQL's pattern of
not keeping unused cumulative state, provide true atomicity
(pg_atomic_exchange_u64 reads and resets in one indivisible
operation), and save 8 bytes of shared memory.

Could you share your reasoning for the cumulative counter approach? I
may be missing a use case. If there's no strong reason for the
cumulative value, would you consider the simpler reset-based approach?

Best Regards,
Nitin Jadhav
Azure Database for PostgreSQL
Microsoft

Show quoted text

On Sun, May 17, 2026 at 2:41 PM Hüseyin Demir <huseyin.d3r@gmail.com> wrote:

Hi,

Thanks for the patch. The idea is good and can we introduce a new
counter to pg_stat_checkpointer ? For example, we can add
wal_segments_created to this view and it would be useful for capacity
planning and load estimations.

Additionally I have a few questions

- Should we make walSegsCreatedLastCheckpoint atomic ? In the future
we can also add this to views maybe. It's an auxiliary suggestion.
- ckpt_segs_added is defined as int but overflow threshold is 2^32 in
comments. Shouldn't we use 2^31 for int ?
- Maybe it's a bad idea but should we extend the tests with the
wal_level set to minimal ?

PS: I created a patch to expose wal_segments_created to
pg_stat_checkpointer view. You can review it and enhance your patch if
you need. I created it to explain my idea better so it can be
destroyed after your review. But I couldn't create a patch for
preceding questions.

Regards,
Demir.

#10solai v
solai.cdac@gmail.com
In reply to: Xuneng Zhou (#6)
Re: log_checkpoints: count WAL segment creations from all processes

Hi all,

On Fri, Jun 19, 2026 at 5:38 PM Xuneng Zhou <xunengzhou@gmail.com> wrote:

On Wed, Mar 25, 2026 at 9:11 AM Xuneng Zhou <xunengzhou@gmail.com> wrote:

Hi Japin,

Thanks for looking into this.

On Tue, Mar 24, 2026 at 10:01 PM Japin Li <japinli@hotmail.com> wrote:

Hi, Xuneng

On Tue, 24 Mar 2026 at 19:17, Xuneng Zhou <xunengzhou@gmail.com> wrote:

Hi Zsolt,

On Tue, Mar 24, 2026 at 1:55 PM Zsolt Parragi <zsolt.parragi@percona.com> wrote:

Hello!

This is a simple patch, but shouldn't it include at least some basic
tests verifying the new behavior?

Thanks for looking into this. I've added a test for it. Please take a look.

Thanks for updating the patch. A few comments on v2:

1.
+       (The probes listed next fire in sequence during checkpoint processing.)
+       arg0 is the number of buffers written. arg1 is the total number of

These changes seem unnecessary. Additionally, there appears to be an
indentation issue.

Yeah, I've removed these and fixed the indentation issue.

2.
+    current = pg_atomic_read_u64(&XLogCtl->walSegmentsCreated);
+    CheckpointStats.ckpt_segs_added = (int)
+        (current - XLogCtl->walSegsCreatedLastCheckpoint);
+    XLogCtl->walSegsCreatedLastCheckpoint = current;

Is integer overflow a concern here? It seems unlikely in practice.

I don’t think overflow is a concern here, but it might still be
helpful to add some comments to mention it.

v4 fixed the inaccurate overflow numbers in comments.

I reviewed and tested v4 of the patch on the current tree branch. The
patch did not apply cleanly due to a conflict in meson.build, which
was caused by changes in the recovery test list on the current master.
I resolved it by keeping both test entries (053_wal_segs_added.pl and
053_standby_login_event_trigger.pl). Then the patch was built and
installed successfully. Before testing, I enabled log_checkpoints and
generated WAL activity using CREATE TABLE AS SELECT generate_series
workload and pgbench (-c 20 -j 4 -T 120). Before applying the patch,
checkpoint logs consistently reported: "0 WAL file(s) added" despite
substantial WAL activity, with checkpoint distances around 500 MB
between checkpoints while after applying the patch, the first
checkpoint reported as: "64 WAL file(s) added, 1 removed, 32 recycled"
for a checkpoint distance of approximately 536 MB. This indicates that
WAL segment creations are now being accounted for beyond the previous
PreallocXlogFiles() path. Subsequent checkpoints reported small values
(0–1 WAL files added), while continuing to recycle existing WAL
segments. This behavior appears reasonable, as PostgreSQL was largely
able to satisfy WAL demand through recycling once a sufficient WAL
pool had been established. I also verified the WAL directory state
during testing. The number of WAL segment files changed from 64 to 61
after additional workload and checkpoints, confirming that WAL segment
creation counts should not be expected to match the number of files
currently present in pg_wal, since removal and recycling occur
independently. Based on the observed behavior, the patch appears to
fix the under-reporting of WAL segment creation counts in checkpoint
log output and behaves as expected during sustained WAL generation
workloads. Also, it would be better to expose wal_segments_created
through pg_stat_checkpointer. This would make the information
available for monitoring and capacity planning without requiring log
parsing and could provide additional value for administrators tracking
WAL growth patterns.

Regards,
Solai