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

Hi all,

On Fri, Jul 31, 2026 at 11:16 AM Ayush Tiwari
<ayushtiwari(dot)slg01(at)gmail(dot)com> wrote:
>
> Hi,
>
> On Thu, 23 Jul 2026 at 11:14, Ayush Tiwari <ayushtiwari(dot)slg01(at)gmail(dot)com> wrote:
>>
>> Hi,
>>
>> On Wed, 22 Jul 2026 at 21:30, Ian Lawrence Barwick <barwick(at)gmail(dot)com> wrote:
>>>
>>> 2026年7月23日(木) 0:07 Ayush Tiwari <ayushtiwari(dot)slg01(at)gmail(dot)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