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

Hi Bharath,

On Tue, Aug 4, 2026 at 2:56 PM Bharath Rupireddy
<bharath(dot)rupireddyforpostgres(at)gmail(dot)com> wrote:
>
> 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.

I've hovered on this patch before. I guess the current function
interface is chosen mainly from the suggestion of Andres in the prior
thread and the feasibility of implementing it. The more ambitious part
you suggested has been stranded in the past for some reasons I haven't
looked into.

> 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?

--
Regards,
Xuneng Zhou
HighGo Software Co., Ltd.