Add a pg_wal_preallocate() SQL function to eagerly create future WAL segments

Hi

2026年7月23日(木) 0:07 Ayush Tiwari <ayushtiwari.slg01@gmail.com>:

Hi,

(...)

Two things I'm unsure about and would welcome opinions on: whether a byte
count defaulting to min_wal_size is the right interface or a plain segment
count would be more honest;

A plain segment count feels like the more intuitive value to provide, especially
as the function returns the number of segments created. OTOH min/max_wal_size
etc. are all specified by size, so maybe that's more consistent.

Regarding min_wal_size, if there is already more than that amount of WAL
present, the function is basically just adding an arbitrary number of
WAL segments.
Maybe the function could, if no value is provided, just create segments until
min_wal_size is reached?

and whether there should be a cap, since nothing
currently stops a request large enough to fill the disk.

Could max_wal_size be a reasonable default cap or soft upper limit?

Minor code nitpick:

-#define CATALOG_VERSION_NO    202607201
+#define CATALOG_VERSION_NO    202607220

I don't think the catalog version bump is needed in patches,
it's up to the committer to set the appropriate value at commit time.

Regards

Ian Barwick

Hi,

On Wed, 22 Jul 2026 at 21:30, Ian Lawrence Barwick <barwick@gmail.com>
wrote:

2026年7月23日(木) 0:07 Ayush Tiwari <ayushtiwari.slg01@gmail.com>:

Hi,

(...)

Two things I'm unsure about and would welcome opinions on: whether a byte
count defaulting to min_wal_size is the right interface or a plain

segment

count would be more honest;

A plain segment count feels like the more intuitive value to provide,
especially
as the function returns the number of segments created. OTOH
min/max_wal_size
etc. are all specified by size, so maybe that's more consistent.

Thanks for the review!

I went back and forth too. I'd keep bytes for now, for consistency with
min/max_wal_size and so it composes with pg_size_bytes('1GB'), but I'm
happy to
switch if the count reads better to people.

Regarding min_wal_size, if there is already more than that amount of WAL
present, the function is basically just adding an arbitrary number of
WAL segments.
Maybe the function could, if no value is provided, just create segments
until
min_wal_size is reached?

That's what it already does, just undocumented: existing segments are
skipped,
so the default only creates the missing ones in the min_wal_size window
ahead
of the insertion point (0 if they're already there). I'll make the docs say
so.

and whether there should be a cap, since nothing
currently stops a request large enough to fill the disk.

Could max_wal_size be a reasonable default cap or soft upper limit?

Possibly. I left it uncapped for now since it's a superuser-only explicit
action, but max_wal_size is a sensible ceiling if we want one. Happy to add
a
soft clamp if there's appetite for it.

Minor code nitpick:

-#define CATALOG_VERSION_NO    202607201
+#define CATALOG_VERSION_NO    202607220

I don't think the catalog version bump is needed in patches,
it's up to the committer to set the appropriate value at commit time.

Agreed, I'll drop it in the next rebase / v2.

Regards,
Ayush

Hi,

On Thu, 23 Jul 2026 at 11:14, Ayush Tiwari <ayushtiwari.slg01@gmail.com>
wrote:

Hi,

On Wed, 22 Jul 2026 at 21:30, Ian Lawrence Barwick <barwick@gmail.com>
wrote:

2026年7月23日(木) 0:07 Ayush Tiwari <ayushtiwari.slg01@gmail.com>:

Hi,

(...)

Two things I'm unsure about and would welcome opinions on: whether a

byte

count defaulting to min_wal_size is the right interface or a plain

segment

count would be more honest;

A plain segment count feels like the more intuitive value to provide,
especially
as the function returns the number of segments created. OTOH
min/max_wal_size
etc. are all specified by size, so maybe that's more consistent.

Thanks for the review!

I went back and forth too. I'd keep bytes for now, for consistency with
min/max_wal_size and so it composes with pg_size_bytes('1GB'), but I'm
happy to
switch if the count reads better to people.

Regarding min_wal_size, if there is already more than that amount of WAL
present, the function is basically just adding an arbitrary number of
WAL segments.
Maybe the function could, if no value is provided, just create segments
until
min_wal_size is reached?

That's what it already does, just undocumented: existing segments are
skipped,
so the default only creates the missing ones in the min_wal_size window
ahead
of the insertion point (0 if they're already there). I'll make the docs
say so.

and whether there should be a cap, since nothing
currently stops a request large enough to fill the disk.

Could max_wal_size be a reasonable default cap or soft upper limit?

Possibly. I left it uncapped for now since it's a superuser-only explicit
action, but max_wal_size is a sensible ceiling if we want one. Happy to
add a
soft clamp if there's appetite for it.

Minor code nitpick:

-#define CATALOG_VERSION_NO    202607201
+#define CATALOG_VERSION_NO    202607220

I don't think the catalog version bump is needed in patches,
it's up to the committer to set the appropriate value at commit time.

Agreed, I'll drop it in the next rebase / v2.

Rebased with updated doc and reverted catalog_version_no.

Regards,
Ayush

Hi all,

On Fri, Jul 31, 2026 at 11:16 AM Ayush Tiwari
<ayushtiwari.slg01@gmail.com> wrote:

Hi,

On Thu, 23 Jul 2026 at 11:14, Ayush Tiwari <ayushtiwari.slg01@gmail.com> wrote:

Hi,

On Wed, 22 Jul 2026 at 21:30, Ian Lawrence Barwick <barwick@gmail.com> wrote:

2026年7月23日(木) 0:07 Ayush Tiwari <ayushtiwari.slg01@gmail.com>:

Hi,

(...)

Two things I'm unsure about and would welcome opinions on: whether a byte
count defaulting to min_wal_size is the right interface or a plain segment
count would be more honest;

A plain segment count feels like the more intuitive value to provide, especially
as the function returns the number of segments created. OTOH min/max_wal_size
etc. are all specified by size, so maybe that's more consistent.

Thanks for the review!

I went back and forth too. I'd keep bytes for now, for consistency with
min/max_wal_size and so it composes with pg_size_bytes('1GB'), but I'm happy to
switch if the count reads better to people.

Regarding min_wal_size, if there is already more than that amount of WAL
present, the function is basically just adding an arbitrary number of
WAL segments.
Maybe the function could, if no value is provided, just create segments until
min_wal_size is reached?

That's what it already does, just undocumented: existing segments are skipped,
so the default only creates the missing ones in the min_wal_size window ahead
of the insertion point (0 if they're already there). I'll make the docs say so.

and whether there should be a cap, since nothing
currently stops a request large enough to fill the disk.

Could max_wal_size be a reasonable default cap or soft upper limit?

Possibly. I left it uncapped for now since it's a superuser-only explicit
action, but max_wal_size is a sensible ceiling if we want one. Happy to add a
soft clamp if there's appetite for it.

Minor code nitpick:

-#define CATALOG_VERSION_NO    202607201
+#define CATALOG_VERSION_NO    202607220

I don't think the catalog version bump is needed in patches,
it's up to the committer to set the appropriate value at commit time.

Agreed, I'll drop it in the next rebase / v2.

Rebased with updated doc and reverted catalog_version_no.

Thanks for the updated patch. I tested this patch and was able to
verify the new pg_wal_preallocate() functionality on a freshly
initialized cluster. Before invoking the function, the pg_wal
directory contained a single WAL segment. Executing: SELECT
pg_wal_preallocate(), returned 5, and I confirmed that 5 additional
WAL segment files were created in the pg_wal directory, matching the
configured min_wal_size target. I also verified the updated default
behavior discussed in the thread. The default invocation only created
the missing segments needed to satisfy the min_wal_size window ahead
of the current insertion point, rather than creating an arbitrary
number of WAL segments. I then reset the WAL and I/O statistics and
executed a workload generating approximately 3.3 GB of WAL using a
5-million-row INSERT. Since the default invocation only preallocated
enough segments to satisfy min_wal_size (80 MB in my setup), the
workload still required additional WAL segment creation during
execution. This behavior seems consistent with the intended design and
the discussion in the thread. I did not encounter any functional
issues during testing.

Regards,
Solai

Hi,

On Fri, 31 Jul 2026 at 15:31, solai v <solai.cdac@gmail.com> wrote:

Hi all,

Thanks for the updated patch. I tested this patch and was able to
verify the new pg_wal_preallocate() functionality on a freshly
initialized cluster. Before invoking the function, the pg_wal
directory contained a single WAL segment. Executing: SELECT
pg_wal_preallocate(), returned 5, and I confirmed that 5 additional
WAL segment files were created in the pg_wal directory, matching the
configured min_wal_size target. I also verified the updated default
behavior discussed in the thread. The default invocation only created
the missing segments needed to satisfy the min_wal_size window ahead
of the current insertion point, rather than creating an arbitrary
number of WAL segments. I then reset the WAL and I/O statistics and
executed a workload generating approximately 3.3 GB of WAL using a
5-million-row INSERT. Since the default invocation only preallocated
enough segments to satisfy min_wal_size (80 MB in my setup), the
workload still required additional WAL segment creation during
execution. This behavior seems consistent with the intended design and
the discussion in the thread. I did not encounter any functional
issues during testing.

Thanks for testing!

Rebased patch attached.

Regards,
Ayush

Hi,

Thank you for working on this!

On Mon, 3 Aug 2026 at 08:13, Ayush Tiwari <ayushtiwari.slg01@gmail.com> wrote:

Hi,

Rebased patch attached.

Patch LGTM, it works as intended. My only concern is the unbounded max
limit; which can cause problems. I think 'max_wal_size' is a
reasonable limit. Perhaps we can add a 'force' boolean option to
function, then it can bypass the 'max_wal_size' limit; what do you
think?

--
Regards,
Nazir Bilal Yavuz
Microsoft

Hi,

On Mon, 3 Aug 2026 at 16:38, Nazir Bilal Yavuz <byavuz81@gmail.com> wrote:

Hi,

Thank you for working on this!

On Mon, 3 Aug 2026 at 08:13, Ayush Tiwari <ayushtiwari.slg01@gmail.com>
wrote:

Hi,

Rebased patch attached.

Patch LGTM, it works as intended. My only concern is the unbounded max
limit; which can cause problems. I think 'max_wal_size' is a
reasonable limit. Perhaps we can add a 'force' boolean option to
function, then it can bypass the 'max_wal_size' limit; what do you
think?

Thanks for the review! I like the idea you suggested.

Let me try implementing it that way.

Regards,
Ayush

Hi,

On Mon, 3 Aug 2026 at 17:30, Ayush Tiwari <ayushtiwari.slg01@gmail.com>
wrote:

Hi,

On Mon, 3 Aug 2026 at 16:38, Nazir Bilal Yavuz <byavuz81@gmail.com> wrote:

Patch LGTM, it works as intended. My only concern is the unbounded max
limit; which can cause problems. I think 'max_wal_size' is a
reasonable limit. Perhaps we can add a 'force' boolean option to
function, then it can bypass the 'max_wal_size' limit; what do you
think?

Thanks for the review! I like the idea you suggested.

Let me try implementing it that way.

v4 attached.

By default the request is now limited to the whole segments that fit within
max_wal_size, and force => true bypasses that when you really do want a
bigger
warm-up. A NOTICE is issued only when an explicit request is reduced; a
plain
no-argument call stays quiet.

Regards,
Ayush