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

Hi,

On Mon, Aug 3, 2026 at 7:24 AM Ayush Tiwari <ayushtiwari(dot)slg01(at)gmail(dot)com> wrote:
>
> 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.

Thanks for working on this!

I have the following design thoughts:

1/ Why does this have to be a function? Why not let the checkpointer
or wal writer scale this automatically based on recent heuristics, for
example how many WAL files have been allocated in the last hour or so,
the rate of WAL generation, and so on? It could track a simple metric
in shared memory (or local to the checkpointer or wal writer), do some
basic math, and kick in when enabled by a GUC.

One concern I have with a function like this is that it mostly ends up
unused, because most end users may not know when to use it, and even
if someone does want to use it before a bulk load, that may need
application changes, which require some careful thought and prior
estimation of how many WAL files are needed, which also requires some
expertise of internals and so on.

This could perhaps be designed along the lines of how relation
extension already works, where we extend by more blocks depending on
the number of lock waiters, so that actual usage decides how much we
preallocate rather than relying on the operator to anticipate it.

I don't have a concrete solution here, just some thoughts.

2/ What happens if I allocate, say, a billion WAL files and fill up
the disk space (I'm a legitimate superuser and I use force mode, just
that I got the calculation wrong or such), and then right after
creating them I restart or crash for some reason? Replay time is not
affected, since these segments sit ahead of the insertion point and
carry no useful records. But would it affect checkpoint time, or
snapshot times/size (disk/storage-based snapshots)? A restart is fine,
but the snapshot now has to carry all these files, which are empty in
the PostgreSQL sense but still take up disk space, increasing the
snapshot size. And what if I create them, then fail over to a standby
and try to rejoin this old primary as a new standby. Would pg_rewind
need to go through all these files?

3/ I played with the v4 patch a bit on local NVMe SSD storage. With
max_wal_size=128MB and 16MB segments, a single call for 640 segments
grew pg_wal from 17MB to 11GB in about 40 seconds, roughly 80x
max_wal_size. I noticed that a checkpoint does not reclaim it.
RemoveOldXlogFiles only recycles or removes log files older or equal
to the last segment to be kept, which it computes from the checkpoint
redo pointer, but the preallocated WAL files sit ahead of the
insertion point, so repeated CHECKPOINTs left the count unchanged. The
number of WAL files only reduces once enough WAL is written to reach
and use those files. So force can leave a large multiple of
max_wal_size on disk, and it stays there until that much WAL is
actually written, not until the next checkpoint. Is this intentional?
If the database lands in this situation, how can we recover the disk
space to avoid no-space-left-on-device issues or downtime?

--
Bharath Rupireddy
Amazon Web Services: https://aws.amazon.com